Databases and SQL Mastery

1. What is a 'transaction' in database terms?

• A. A sequence of operations performed as a single logical unit of work ✓
• B. A single SQL query
• C. A type of index
• D. A type of join

💡 A database transaction is a sequence of operations treated as a single, indivisible unit of work.

2. What does 'JOIN' do in SQL?

• A. Combines rows from two or more tables based on a related column ✓
• B. Deletes a table
• C. Creates a new database
• D. Sorts a table

💡 A JOIN combines rows from two or more tables based on a related column between them.

3. What does 'NoSQL' typically refer to?

• A. Non-relational databases that don't use traditional table-based structures ✓
• B. A type of SQL syntax error
• C. A faster version of SQL
• D. A programming language

💡 NoSQL refers to non-relational databases that use flexible data models beyond traditional tables, such as documents or key-value stores.

4. What is a 'LEFT JOIN'?

• A. Returns all rows from the left table and matched rows from the right table ✓
• B. Returns only matching rows
• C. Returns all rows from the right table
• D. Deletes unmatched rows

💡 A LEFT JOIN returns all rows from the left table, along with matched rows from the right table, filling unmatched columns with NULL.

5. What is a 'view' in SQL?

• A. A virtual table based on the result of a SQL query ✓
• B. A physical copy of a table
• C. A type of index
• D. A stored procedure

💡 A view is a virtual table whose contents are defined by a stored SQL query, without duplicating the underlying data.

6. What does 'UNIQUE' constraint ensure in a database column?

• A. All values in the column are different from one another ✓
• B. All values are the same
• C. No null values are allowed
• D. Values are sorted

💡 A UNIQUE constraint ensures that all values in a column are distinct from one another.

7. What is 'denormalization'?

• A. Intentionally introducing redundancy to improve read performance ✓
• B. Removing all redundancy
• C. Encrypting a database
• D. Deleting duplicate rows

💡 Denormalization intentionally introduces some redundancy into a database to improve read performance.

8. What does 'COMMIT' do in a SQL transaction?

• A. Saves all changes made during the transaction permanently ✓
• B. Cancels the transaction
• C. Deletes the database
• D. Creates a new table

💡 COMMIT permanently saves all changes made during the current transaction.

9. What does 'ROLLBACK' do in a SQL transaction?

• A. Undoes changes made during the current transaction ✓
• B. Saves changes permanently
• C. Creates an index
• D. Deletes a table

💡 ROLLBACK undoes any changes made during the current transaction, reverting the database to its previous state.

10. What is 'normalization' in database design?

• A. Organizing data to reduce redundancy and improve data integrity ✓
• B. Making all data uppercase
• C. Sorting data alphabetically
• D. Encrypting data

💡 Normalization organizes database tables to minimize redundancy and improve data integrity.

11. What is an 'index' in a database used for?

• A. Speeding up data retrieval operations ✓
• B. Encrypting data
• C. Deleting duplicate records
• D. Formatting output

💡 A database index speeds up data retrieval by providing a faster lookup path for query operations.

12. What is a 'primary key' used for in a relational database?

• A. Uniquely identifying each record in a table ✓
• B. Encrypting data
• C. Sorting a table
• D. Joining tables only

💡 A primary key uniquely identifies each record in a database table.

13. What is a 'stored procedure'?

• A. A precompiled collection of SQL statements stored in the database ✓
• B. A type of index
• C. A virtual table
• D. A backup file

💡 A stored procedure is a precompiled set of SQL statements saved in the database that can be executed as a unit.

14. What does 'CASCADE' typically do when used with foreign key constraints on delete?

• A. Automatically deletes or updates related rows in referencing tables ✓
• B. Prevents any deletion
• C. Creates a backup
• D. Encrypts related data

💡 The CASCADE option automatically propagates deletions or updates to related rows in referencing tables.

15. What does the SQL 'WHERE' clause do?

• A. Filters records based on a specified condition ✓
• B. Sorts records
• C. Joins tables
• D. Groups records

💡 The WHERE clause filters records in a query, returning only those that meet a specified condition.

16. What does 'INNER JOIN' return?

• A. Only rows with matching values in both tables ✓
• B. All rows from both tables regardless of match
• C. Only rows from the left table
• D. Only rows from the right table

💡 An INNER JOIN returns only the rows where there is a match in both joined tables.

17. What is 'sharding' in database architecture?

• A. Splitting a large database into smaller, more manageable pieces across multiple servers ✓
• B. Encrypting a database
• C. Backing up a database
• D. Indexing a database

💡 Sharding splits a large database into smaller pieces, or shards, distributed across multiple servers for scalability.

18. What does 'ACID' stand for in database transactions?

• A. Atomicity, Consistency, Isolation, Durability ✓
• B. Access, Control, Integrity, Data
• C. Automatic, Consistent, Isolated, Durable
• D. Atomic, Complete, Integrated, Data

💡 ACID stands for Atomicity, Consistency, Isolation, and Durability, key properties of reliable database transactions.

19. What does 'GROUP BY' do in SQL?

• A. Groups rows sharing a property so aggregate functions can be applied ✓
• B. Sorts rows alphabetically
• C. Deletes duplicate rows
• D. Joins two tables

💡 GROUP BY groups rows that share a common value, allowing aggregate functions like SUM or COUNT to be applied to each group.

20. What does 'referential integrity' ensure?

• A. Relationships between tables remain consistent ✓
• B. All data is encrypted
• C. Data is sorted alphabetically
• D. Indexes are created automatically

💡 Referential integrity ensures that relationships between tables, such as foreign keys, remain valid and consistent.