Database Programming & SQL Flashcards

Which SQL JOIN returns all rows from the left table and matching rows from the right table, with NULLs for non-matching right rows?

Answer: LEFT OUTER JOIN

A LEFT OUTER JOIN returns all rows from the left table plus matched rows from the right; unmatched right-side columns are filled with NULL.

Which property of the ACID model ensures that a transaction either fully completes or has no effect on the database?

Answer: Atomicity

Atomicity guarantees that a transaction is treated as a single unit — it either commits entirely or rolls back entirely, leaving no partial state.

A table is in Third Normal Form (3NF) if it is in 2NF and contains no:

Answer: Transitive dependencies

3NF requires the table to be in 2NF and that no non-key attribute depends transitively on the primary key (i.e., no non-key → non-key dependencies).

Which SQL clause is used to filter results after an aggregate function has been applied?

Answer: HAVING

HAVING filters rows after GROUP BY aggregation, whereas WHERE filters rows before aggregation occurs.

What type of database index stores the data rows themselves in the index key order?

Answer: Clustered index

A clustered index physically orders the table's data rows by the index key, so each table can have only one clustered index.

In SQL, which command is used to permanently save a transaction's changes to the database?

Answer: COMMIT

COMMIT makes all changes made during the current transaction permanent and visible to other transactions.

Which of the following best describes a foreign key in a relational database?

Answer: A column that references the primary key of another table

A foreign key is a column (or set of columns) in one table that references the primary key of another table, enforcing referential integrity.