Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Java-Angular-AWS > Week 3 > Flashcards

Week 3 Flashcards

1
Q

SQL Sublanguages (5)

A
  1. DDL - data definition language
  2. DML - data manipulation language
  3. DQL - data query language
  4. DCL - data control language
  5. TCL - transaction control language
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Define DDL

A
  • data definition language - statements used to create tables and databases as well as defined properties
  • create, alter, drop, truncate
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Define DML

A
  • data manipulation language - statements used to insert or remove data from tables
  • insert, update, delete
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Define DQL

A
  • data query language - statements used to query data from a table
  • select
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Define DCL

A
  • data control language - statements used to control who can access data
  • grant, revoke
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Define TCL

A
  • transaction control language - statements used to commit and restore data through transaction. Transactions group a set of tasks into a single execution unit.
  • commit, rollback, savepoint
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Numeric SQL Datatypes (7)

A
  1. INT
  2. TINYINT
  3. BIGINT
  4. DECIMAL
  5. NUMERIC
  6. FLOAT
  7. REAL
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Date/Time SQL Datatypes (5)

A
  1. DATE
  2. TIMESTAMP
  3. DATETIME
  4. TIME
  5. YEAR
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Character/String SQL Datatypes (5)

A
  1. CHAR
  2. NCHAR
  3. VARCHAR
  4. NVARCHAR
  5. NTEXT
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Binary SQL Datatypes (3)

A
  1. BINARY
  2. VARBINARY
  3. IMAGE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Miscellaneous SQL Datatypes (4)

A
  1. CLOB
  2. BLOB
  3. XML
  4. JSON
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

“Industry etiquette” for writing SQL

A
  • use UPPERCASE to refer to keywords, and lowercase for non-SQL specific entities (like tables or column names)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Define database schema

A
  • Refers to the structure of the database - the columns of each table, their data types, and any constraints on them
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Define constraints and list (7)

A
  • allow us to enforce the schema by ensuring consistency and integrity of the data in the table
    1. PRIMARY KEY
    2. FOREIGN KEY
    3. NOT NULL
    4. UNIQUE
    5. CHECK
    6. DEFAULT
    7. AUTO INCREMENT
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Explain PRIMARY KEY constraint

A
  • uniquely identifies a record in a table

- inherently composed of two other constraints - unique and not null

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What are the properties a transaction must follow (4)?

A

ACID

  1. Atomicity - all or nothing (transaction must fully complete or rollback)
  2. Consistency - data remains in a consistent state after the transaction
  3. Isolation - transactions do not interfere with one another when running
  4. Durability - data will persist even in case of catastrophic failure
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Explain the different isolation levels (4).

What read phenomena do each prevent?

A
  1. Serializable - slowest, prevents phantom reads
  2. Repeatable Reads - prevents non-repeatable reads, allows phantom reads
  3. Read Committed - prevents dirty reads, allows non-repeatable reads
  4. Read Uncommitted - fastest, allows dirty reads
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is RDBMS?

A
  • relation database management system
  • A database is a collection of data, and the management system is a set of programs to store and access that data in a quick and effective manner
  • Developed to handle large amounts of data
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What vendors are supported by RDS?

A
  • MySQL
  • Oracle
  • SQLServer
  • Postgres
  • Amazon Aurora
  • MariaDB
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Relational vs non-relational databases

A
  • non-relational does no store data within tables that relate to each other, thus does not use SQL to interact with the database
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Explain FOREIGN KEY constraint

A
  • signifies that a column represents a reference to the primary key of another table. This allows us to create relationships between tables
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Explain NOT NULL constraint

A
  • enforces that all records must have a field for the column on which this constraint is applied
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Explain UNIQUE constraint

A
  • records cannot be inserted if another record already has the same value for the column on which this is declared (similar to NOT NULL)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Explain CHECK constraint

A
  • provides a way of performing validation on values before records are entered into the table
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Explain DEFAULT constraint

A
  • allows setting default values on columns for records that are inserted into the table
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Explain AUTO INCREMENT constraint

A
  • allows a unique number to be generated automatically when a new record is inserted into a table
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What is a candidate key?

What is a composite key?

What is a surrogate key?

A

candidate - multiple columns that together create a primary key to uniquely identify rows

composite - the actual combination of columns to use as the primary keys

surrogate - a column that can uniquely identify a record (can act as Primary Key)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Multiplicity relationship: one to one

A
  • each entity in a table only relates to a single entity in another table
  • to enforce the one to one aspect, use a foreign key to relate to the primary key on another table; putUNIQUE constraint on the foreign key column
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Multiplicity relationship: one to many / many to one

A
  • one entity can belong to, own, or otherwise relate to multiple other entities
  • to create this in the database, we add the foreign key only on the many side of the relationship
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Multiplicity relationship: many to many

A
  • implies a one-to-many relationship in both directions on the entities
  • we cannot provide a direct link between the tables in the database - instead, use a 3rd table, called a junction table, to connect them. the junctiontable will have 2 foreign keys and will be the many side of both relations
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

what are junction tables?

A
  • direct link between tables in a many to many relationship

- also known as bridge table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

what is normalization? normal forms (3)?

A
  • The process of reducing redundancy in a database
    1. 1NF
    2. 2NF
    3. 3NF

“swearing to tell the truth, the whole truth, and nothing but the truth”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

How would you describe AWS? What is “the cloud” or “cloud computing” and why is it so popular now?

A
  • AWS is leading cloud platform
  • Provides resources and services
  • Business benefits: no need to manage infrastructure; useful tooling; worldwide distribution/deployment
  • Downsides: locked into cloud provider
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

Define Infrastructure, Platform, and Software as a Service

A
  1. IaaS: direct access to hardware; most control
  2. PaaS: hardware abstracted; developer provides the software to run
  3. SaaS: software abstracted
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

What’s the difference between a Region and an Availability Zone (AZ)?

A

Regions cover a geographic area, all over the world

AZs are individual data centers; 3 AZs per region for redundancy and fault tolerance

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

How are you charged for using AWS services? Does it vary by service?

A

By uptime, data stored, data exchanged

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

Different ways to interact with AWS services?

A
  • Web console
  • AWS CLI / REST API
  • AWS SDK (software development kit)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

What is RDS?

A

relational database service - a PaaS offering that loads RDBMS software based on your configuration

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

Explain the AWS service - Route 53

A
  • DNS (domain name service) for registering domain names like ‘.com’ or ‘.net’ addresses
  • Works to resolve ‘somesite.come’ into an IP address where your server is located
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

Explain the AWS service - VPN

A
  • Virtual Private Network - can place your cloud resources in a VPN for network security purposes
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

Explain the AWS service - IAM

A
  • Identity and Access Management - manage users, roles, and policies to provide access to your cloud resources
42
Q

Explain the AWS service - ELB

A
  • Elastic load balancer - distribute incoming requests between different services
43
Q

What is JDBC?

A
  • Java Database Connectivity

- A Java API used to execute queries on various databases. Uses JDBC drivers to connect with the database

44
Q

What are the core interfaces / classes in JDBC (8)?

A
  1. DriverManager class - to make a connection with a database driver
  2. DataSource interface - for retrieving connections, an alternative to DriverManager
  3. Connection interface - represents a physical connection with a database
  4. SQLException class - a general exception thrown when something goes wrong when accessing the database
  5. Statement interface - used for executing static SQL statements
  6. PreparedStatement interface - represents pre-compiled SQL statements
  7. CallableStatement interface - used to execute stored procedures
  8. ResultSet interface - represents data returned from the database
45
Q

What is a stored procedure and how would you call it in Java?

A

A stored procedure is an executable block of code that is written in PL/SQL and stored in the Oracle database. A stored procedure is called from a Java class using a CallableStatement object. When the procedure is called, its name and any relevant parameters are sent over the JDBC connection to the DBMS, which executes the procedure and returns the results (if applicable) via the connection.

46
Q

What is the difference between Statement and PreparedStatement?

A

PreparedStatements are pre-compiled by the JVM. The database doesn’t have to compile the SQL each and every time it is executed. PreparedStatement can be parameterized, which can make the SQL more readable. Furthermore, PreparedStatement will properly escape reserved characters to prevent SQL injection attacks

47
Q

Steps to executing an SQL query using JDBC (6)?

A
  1. Register the driver using .forName() (or let DriverManager detect and load automatically from classpath)
  2. Create the connection (DriverManager.getConnection(url,username,password))
  3. Create a statement for executing the SQL query (Statement st = conn.createStatement());
  4. Execute the SQL query (ResultSet rs = st.executeQuery(String sql))
  5. Use ResultSet to get values returned (rs.getInt(1), etc)
  6. Close the connection (conn.close())
48
Q

How to execute stored procedures using JDBC?

A

Use the Callable statement interface

49
Q

Which interface is responsible for transaction management?

A

The Connection interface - can commit, rollback, and savepoint

50
Q

What is Maven?

A

A build automation and dependcy management tool for Java applications

51
Q

What is the Maven lifecycle (8)?

A

Validate => project is correct and all necessary information is available

Compile => compiles project source code

Test => tests all compiled code

Package => packages all compiled code to WAR/JAR file

Integration => performs all integration tests on WAR/JAR

Verify => runs checks on the results of integration tests

Install => installs WAR/JAR to local repository

Deploy => copies final WAR/JAR to the remote repository

52
Q

Where / when does Maven retrieve dependencies from? Where are they stored locally?

A

Maven first looks to see if the dependency is in the local repo under .m2 directory. If not, it will download the necessary .jar file(s) from the remote central Maven repository into the .m2 directory

53
Q

What is the POM and what is the pom.xml?

A

POM stands for project object model and is the model used by Maven to understand project attributes and dependencies. The pom.xml is the xml document which lists those attributes and dependencies

54
Q

The first normal form (1NF) enforces that a table must:

A
  • Have a primary key
  • Each column should be as granular as possible (e.g. “Name” column should be broken up into: “First Name”, “Last Name”, “Middle Name”, etc..)
55
Q

To be in second normal form (2NF), a table must:

A
  • must already be in 1NF
  • Cannot have columns that are dependent on only one part of the key
  • If there are no composite primary keys, you are automatically in 2NF
56
Q

To get to third normal form (3NF), a table must:

A
  • must already be in 2NF
  • Not have transitive dependencies
  • This means that if column C relates to column B which relates to column A which is the primary key, this is not in 3NF because C is related to the primary key but indirectly (it is a transitive dependency)
57
Q

Explain Query Clause WHERE

A

operates on individual records

58
Q

Explain Query Clause HAVING

A

used with GROUP BY and aggregate functions to operate on groups ofrecords

added to SQL because the WHERE keyword cannot be used with aggregate functions.

59
Q

Explain Query Clause ORDER BY

A

sorts the results set on a particular column (ascending or descending)

60
Q

Explain Query Clause GROUP BY

A

used with an aggregate function to combine records together

61
Q

what is an alias in SQL?

A
  • used to give a table, or a column in a table, a temporary name.
  • often used to make column names more readable.
  • only exists for the duration of that query.
  • created with the AS keyword
62
Q

What are the different joins (8)?

A
  1. INNER JOIN
  2. OUTER JOIN
  3. LEFT (OUTER) JOIN
  4. RIGHT (OUTER) JOIN
  5. FULL (OUTER) JOIN
  6. CROSS JOIN
  7. SELF JOIN
  8. NATURAL JOIN
63
Q

Describe INNER JOIN

A

only joins records that match based on join predicate

64
Q

Describe OUTER JOIN

A

joins all records in both tables, will have null as value if doesn’texist

65
Q

Describe LEFT [OUTER] JOIN

A

joins all records in left table, plus any records that match joinpredicate on right

66
Q

Describe RIGHT [OUTER] JOIN

A

joins all records in right table, plus any records that match joinpredicate on left

67
Q

Describe FULL [OUTER] JOIN

A

Returns all rows from both tables specified including the ones which had null values on either side

68
Q

Describe NATURAL JOIN

A

joins tables based on columns that have the same name in both tables

69
Q

Describe CROSS JOIN

A

cartesian product of records in both tables, i.e. each record in one table paired with each in another (results in M x N records)

70
Q

Describe SELF JOIN

A

joins a table with itself, FK points to PK of same table - e.g. employees table has reportsTo column for referencing manager in same table

71
Q

What are joins?

A
  • Used to combine two or more tables, joins are a database technique used in SELECT statements.
  • Joins are normally performed comparing primary keys to foreign keys, however, they can be performed with any type of column, as long as the types match.
  • Joins are a way of denormalizing a set of tables in order to aggregate or process some query.
72
Q

What is the difference between joins and set operators?

A

Joins combine tables horizontally

Set operators combine results sets vertically

73
Q

What are the 4 set operators?

A

UNION (ALL) - combines results sets, removes duplicates; like UNION but allows duplicates

INTERSECT - returns the records that are in common between the two results sets

MINUS - Removes from the first result set any rows that appear in the second result set and returns what remains

EXCEPT - Same as MINUS, but for SQLServer instead of Oracle

74
Q

What is SQL?

A
  • Structured Query Language

- allows us to interact with and manipulate relational databases

75
Q

How is data structured in a SQL database?

A

Data is inserted as records, or rows, in a table. A database can have many tables that are related to each other. Each table has different columns, each with its own data type and possibly constraints.

76
Q

What is an ERD? How is it useful?

A

Entity relationship diagram - used to model and show relationships between different tables in the database

77
Q

Explain the concept of referential integrity

A
  • Every foreign key in the database points to a valid, existing record
  • If this is broken, we have what is called an “orphan record”
78
Q

What is a cascade delete?

A
  • Setting that allows referenced records to be deleted as well, if record being deleted has a foreign key
  • Ensures referential integrity, prevents orphaned records
79
Q

What is the difference between DELETE, DROP, and TRUNCATE commands?

A

DROP removes a table entirely; DELETE will remove specific rows (unless no WHERE clause); TRUNCATE will empty a table of all data but leave the schema intact

DROP and DELETE can be rolled back; TRUNCATE cannot

80
Q

What are some SQL clauses you can use with SELECT statements (8)?

A
  1. WHERE
  2. HAVING
  3. AND
  4. OR
  5. GROUP BY
  6. ORDER BY
  7. LIMIT
  8. LIKE
81
Q

What is an index?

A

allow databases to speed up the process of retrieving data (like an index of a book, know where to go for info)

82
Q

What is the purpose of a view? What about an index?

A

View is like a stored/cached query, useful for removing sensitive fields when giving access to a table

Index increases performance of queries when done on the column with the index

83
Q

What’s the difference between a clustered and non-clustered index?

A

Clustered - written to disk, only one allowed per table

Non-clustered - uses references in memory, multiple allowed

84
Q

What is a trigger? Give the syntax for creating a trigger.

A

Trigger is a special type of stored procedure that runs when an event happens in the database

85
Q

How would you setup a primary key that automatically increments with every INSERT statement?

A
  • Postgres >10: use the IDENTITY keyword
  • Oracle 11g: use trigger and sequence together so that sequence increments on insert
  • Oracle 12c: IDENTITY keyword now exists
86
Q

What is the difference between scalar and aggregate functions? Give examples of each

A

Scalar - operates on a single value, e.g. UPPER, LOWER, TRIM, CONCAT, DATE

Aggregate - operates on multiple values, e.g. MIN, MAX, AVG, SUM

87
Q

What’s the difference between implicit and explicit cursors?

A
  • Implicit cursor created automatically for you when you run a query
  • Cursor initially points to before the first record in a resultset
  • Explicit cursors must be created manually but give more control
88
Q

Write a query to find all data in the table Employees

A

SELECT * FROM employees

89
Q

Write a query to find employees with a salary over 75

A

SELECT * FROM employees WHERE salary > 75

90
Q

Write a query to find employees whose first name contains an ‘e’ or whose last name begins with ‘S’\

A

SELECT * FROM employees WHERE firstName LIKE ‘e’ OR lastName LIKE ‘S’

91
Q

Write a query to find the first name of all employees who do not work in accounting

A

SELECT firstName FROM employees WHERE dept != ‘Accounting’

92
Q

Write a query to find the highest paid salesperson

A

SELECT * FROM employees ORDER BY salary DESC LIMIT 1

93
Q

Write a query to combine the resultsets of any two previous queries

A

Must use UNION or UNION ALL

94
Q

Steps for creating a database connection

A
  1. Register the JDBC driver
  2. Open a connection using:
    - Database URL
    - Username
    - Password
  3. Execute some SQL statement using either:
    - Statement
    - PreparedStatement
    - CallableStatement
  4. Retrieve the results that are returned in a ResultSet object
95
Q

Explain JDBC String

A

The database URL is an address pointing to the database to be used

The String is made up of the JDBC URL, your username, and password

Connection conn = DriverManager.getConnection(DB_URL,USERNAME,PASSWORD)

96
Q

Explain Autocommit Mode

A

By default, when a connection is created it is in auto-commit mode, so every SQL statement acts as a transaction and is committed immediately after execution. In order to manually group statements into a transaction, simply call:

Connection conn = DriverManager.getConnection(DB_URL,USERNAME,PASSWORD);conn.setAutoCommit(false);

97
Q

Explain Data Access Objects (design pattern)

A
  • logically separate the code that accesses the database into Data Access Objects
  • to use: define an interface which declares methods through which the database will be queried. Then, concrete implementation classes can implement the interface and contain the data access logic to return the required data
98
Q

Explain PL/SQL - Triggers

A

stored programs, which are automatically executed or fired when some events occur

99
Q

Explain PL/SQL - Cursors

A

holds the rows (one or more) returned by a SQL statement

100
Q

Explain PL/SQL - Procedures

A

do not return a value directly; mainly used to perform an action

101
Q

Explain PL/SQL - Sequences

A

an object that is used to generate a number sequence

this can be useful when you need to create a unique number to act as a primary key

Java-Angular-AWS (9 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions