1. What is the difference between a primary key and a foreign key?
Answer: A primary key is a column or set of columns that uniquely identifies each row in a table. A foreign key is a column or set of columns in one table that references the primary key of another table.
2. What are the three main types of SQL joins?
Answer: The three main types of SQL joins are:
* Inner join: Returns rows that have matching values in both tables.
* Left join: Returns all rows from the left table, even if there are no matching values in the right table.
* Right join: Returns all rows from the right table, even if there are no matching values in the left table.
3. What is the difference between a WHERE clause and a HAVING clause?
Answer: A WHERE clause filters rows in a table before any aggregation functions are applied. A HAVING clause filters rows in a table after aggregation functions have been applied.
4. What is the purpose of an index in a database?
Answer: An index is a data structure that speeds up data retrieval by organizing rows in a table according to their values.
5. What are the two types of transactions in a database?
Answer: The two types of transactions in a database are:
* ACID transactions: ACID transactions are guaranteed to be atomic, consistent, isolated, and durable.
* Non-ACID transactions: Non-ACID transactions are not guaranteed to be atomic, consistent, isolated, or durable.
6. What is the difference between a schema and a table?
Answer: A schema is a collection of tables and other objects in a database. A table is a data structure that stores data in rows and columns.
7. What are the different levels of isolation in a database?
Answer: The different levels of isolation in a database are:
* Read uncommitted: This is the lowest level of isolation. Reads can see uncommitted changes made by other transactions.
* Read committed: This level of isolation ensures that reads can only see committed changes made by other transactions.
* Repeatable read: This level of isolation ensures that reads will see the same data even if other transactions commit changes after the read has started.
* Serializable: This is the highest level of isolation. It ensures that transactions are executed in a serializable order, so that the outcome is the same as if the transactions were executed one at a time.
8. What are the different types of database locks?
Answer: The different types of database locks are:
* Exclusive locks: Exclusive locks prevent other transactions from accessing the data that is locked.
* Shared locks: Shared locks allow multiple transactions to access the data that is locked, but they prevent other transactions from making changes to the data.
* Intent locks: Intent locks are used to indicate that a transaction intends to acquire a lock on a piece of data.
9. What is a deadlock?
Answer: A deadlock occurs when two or more transactions are waiting for each other to release locks.
10. How can you resolve a deadlock?
Answer: There are three ways to resolve a deadlock:
* Rollback one of the transactions: This is the most common way to resolve a deadlock. One of the transactions is rolled back, and the other transaction is allowed to continue.
* Choose a victim: One of the transactions is chosen as a victim and is rolled back. The other transaction is allowed to continue.
* Time out: If a transaction is waiting for a lock for too long, it can be timed out and rolled back.