1
Q
What is an RDBMS?
A
It stands for Relational Database Management system which is a program that allows you to create, update, and save data in a relational database.
2
Q
What is an ERD?
A
An ERD is a Entity Relationship Diagram, which is a diagram that maps out your database tables and the multiplicity and their relationships to each other.
3
Q
What is SQL?
A
SQL stands for Structured Query Language and it’s a programming language used to communicate with the data stored in relational database management system.
4
Q
What are sub-languages of SQL?
A
Data Definition Language, Data Manipulation Language, Data Query Language, Data Control Language, Transaction Control Language
5
Q
What is DDL?
A
It stands for Data Definition Language and it is used to define tables and set their properties. With it, you can create a table, alter the properties of the table and drop the table.
6
Q
What is DML?
A
DML stands for Data Manipulation Language, commands used are to insert data into the database, modify the data of the database and to delete data from the database.
7
Q
What is DCL?
A
DCL stands for data control language, and its the sub language that’s used to grant and revoke users of their rights and permissions to a database
8
Q
What is DQL?
A
DQL stands for Data Query Language, it is used to retrieve data from the database.
9
Q
What is TCL?
A
TCL stands for Transaction control language, which are used to manage transactions in database with commit, rollback, and savepoint.
10
Q
What is a database?
A
It is a structured collection of data that is stored in a computer or server and can be accessed and manipulated in various ways.
11
Q
What are Objects in SQL?
A
- Objects in SQL are any defined object that can store or reference data.
- The database contains Schema objects that contain table objects and views
- Columns are the smallest data object
12
Q
What are Tables?
A
Tables are data objects under a schema object and it holds columns that contain your data entries.
13
Q
What are Views?
A
- Views are virtual tables based on the stored result-set of a SQL statement.
- A views fields are fields from one or more real tables in the database.
- You can store views in variables and perform operations to update or alter the view.
14
Q
What are Triggers? When can it execute?
A
- A trigger is a database object that executes one or more SQL statements when a specified operation is done.
- You can define when a trigger fires when you define the trigger
15
Q
What is an Index?
A
- An index is used to speed up the performance of queries.
- It does so by reducing the number of database data pages that have to be scanned/visited.
- In SQL Server, a clustered index determines the physical order of data in a table.
There can only be one clustered index per table (the clustered index is the table).
16
Q
What is a Cursor?
A
- A cursor is a temporary work area created in the system memory when a SQL statement is executed.
- A cursor contains information on a select statement and the rows accessed by it.
- This temporary work area is used to store the data retrieved from the database, and manipulate this data.
17
Q
What is a Sequence?
A
- A sequence is a user-defined, schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created.
- The sequence of numeric values is generated in an ascending order or descending order at a defined interval and can be configured to restart when exhausted.
18
Q
What is a Constraint?
A
Constraints are rules used to limit the type of data that can go into a table.
19
Q
What are the different relationships in SQL?
A
- One-to-one - both tables can have only one record on either side of the relationship.
- One-to-many - one table can only have one record on its side of the relationship while others can have many records of the relationship
- Many-to-many - both sides can have many records on either side of the relationship.
20
Q
What is a Primary Key?
A
A primary key is a field in a table which uniquely identifies each row/record in a database table.
21
Q
What is a Foreign Key?
A
- A foreign key is a column (or columns) that references a column that is the primary key of another table.
- The purpose of the foreign key is to insure referential integrity of the data.
22
Q
What are the different types of Joins?
A
- Inner Join - Returns data that occurs in both tables.
- Left Join - Returns the data that occurs in the left table and the values that matched records in the right table.
- Right Join - Returns the data from the right table and the matching data from the left table
- Full Outer Join - returns all records when there is a match in either table.
23
Q
What are the set operators?
A
- Set operators combine the results of two component queries into a single result.
- Queries containing set operators are called compound queries.
- Include:
- UNION
- UNION ALL
- INTERSECT
- MINUS
24
Q
What are the Transaction Properties?
A
- ACID.
- Atomicity.
- Consistency.
- Isolation.
- Durability.
25
What is Atomicity?
Atomicity - each transaction is all or nothing, if any part of the transaction fails the whole thing fails, and then the database is rolled back to its last consistent state.
26
What is Consistency?
Consistency - ensures that any transaction will bring the database from one valid state to another. (does not protect for correctness of transaction)
27
What is Isolation?
Isolation - ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were used sequentially. One transaction has nothing to do with any of the others.
28
What is Durability?
Durability - ensures that once a transaction has been committed, it will remain committed even in the event of power loss, crashes, or errors. Once a sequence of statements execute the results need to be stored permanently in non-volatile memory to defend against these faults.
29
What are the different Isolation Levels? What anomalies does each allow?
- Read Uncommitted - allows all anomalies.
- Read Committed - prevents dirty reads, suffers non-repeatable and phantom reads.
- Repeatable Read - prevents non-repeatable reads, suffers phantom reads.
- Serialized - prevents all anomalies including phantom reads
30
What is a Dirty Read?
A transaction anomaly that occurs when a transaction is allowed to read data from a row that has been modified by another running transaction that has not been committed yet.
31
What is a Non-Repeatable Read?
A transaction anomaly that occurs when data is read twice in the same transaction and returns different values because another transaction has modified the data between the reads. (more about update problems)
32
What is a Phantom Read?
Data getting changed in a transaction by other transactions in which rows are added or removed resulting in different result sets. (more about insert/delete problems)
33
What is Referential Integrity?
- A concept that states that when data references another source of data, that that data exists.
- In SQL data sources are tables, and foreign keys are the external references.
- In SQL you can’t delete a value from a table if it’s referenced from other tables, this would cause the loss of referential integrity otherwise.
34
What is Normalization? What is it used for?
- Normalization is the process of organizing the database into different forms each with their own sets of rules.
- Makes the database more consistent and safe.
- Minimize data redundancy, reduce duplicate data
35
What is 1NF?
- It should only have single(atomic) valued attributes/columns.
- Values stored in a column should be of the same domain
- All the columns in a table should have unique names.
36
What is 2NF?
- It should be in the First Normal form.
| - it should not have Partial Dependency.
37
What i 3NF?
- It is in the Second Normal form.
| - It doesn't have Transitive Dependency.
38
What is Transitive Dependency?
An indirect relationship between values in the same table that causes a functional dependency.
39
What is an Aggregate Function?
An aggregate function is a function where the values of multiple rows are grouped together to form a single value of more significant meaning or measurement like sum().
40
What is a Scalar Function?
A scalar function is a function that takes data and modifies it, it doesn’t do any calculations with it, just altering the look or returning metadata of the query like toUpper().
41
What is JDBC?
JDBC - Java Database Connectivity.
| An API specification for connecting java programs to popular databases
42
What do you need to get a JDBC Connection?
- First you must have a configured driver.
- DriverManager loads the specified driver and gets a connection to the database.
- DataSource is an interface that’s prefered to DriverManager because it allows some transparency for some parts of the datasource.
43
What are the steps to setup a JDBC Connection?
- Import JDBC packages
- Load and register the driver
- Try with resources to get a connection from the driver
manager or a class abstracting it.
- Create a statement object to perform a query.
- Execute the statement and retrieve the result set.
44
What is DAO?
- Stands for Data Access Object
- The interface for your database
- Establishes connections and executes queries sent to it.
- Passes result set.
45
What is a static cursor?
- Populates the result set at the time of cursor creation and query results is cached for the lifetime of the cursor.
- Can move forward and backward.
- Changes made with UPDATE, INSERT, or DELETE are not reflected in a static cursor
46
What is a dynamic cursor?
Allows you to see updation, deletion, and insertion in the data source while the cursor is open. Hence a dynamic cursor is sensitive to changes to the data source.
47
What is a forward only cursor?
- The fastest among all cursors but it doesn’t support backward scrolling.
- You can update, delete data using Forward only cursor.
- It is sensitive to changes in the original data source.
- There are different types of forward only cursors.
- Forward only keyset.
- Forward only static.
- Forward only fast forward.
48
What is a keyset driven cursor?
- A keyset driven cursor is controlled by a set of unique - identifiers as the keys in the keyset.
- The keyset depends on all the rows that qualified the select statement at the time the cursor was opened.
- A keyset driven cursor is sensitive to any changes to the data source and supports update and delete operations.
49
What are the different types of Indexes?
- Unique - Guarantees unique values for the column(s) included in the index.
- Covering - includes all of the columns that are used in a particular query(set of queries), allowing the database to use only the index and not actually have to look at table data to retrieve the results.
- Clustered - this is a way in which the actual data is ordered on the disk, which means if a query uses the clustered index for looking up the values, it does not have to take the additional step of looking up the actual table row for any data not included in the index.
50
Which are the main interfaces of JDBC?
- Connection
- Statement
- Prepared statement
- Callable statement
- Result Set
51
What is a SELF JOIN?
- A self-join is a query in which a table is joined (compared) to itself. Self joins are used to compare values in a column with other values in the same column in the same table.
52
Union vs Union All
- Union all command is equal to the union command, except that union all selects all values.
- The difference between them is that union all will not eliminate duplicate rows, instead it pulls all rows from all tables fitting the query specifics and combines them into a table.
53
What is a natural join?
- A join operation that creates an implicit join clause for you based on the common columns in the two tables being joined.
- Common columns are columns that have the same name in both tables.
- Can be an inner join, left outer join, or a right outer join.
JavaBatchReview
flashcards
Decks in class (13)
# Cards
