CPP Database Programming & SQL

Question 1

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

Accepted Answer

LEFT OUTER JOIN

Answer

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.

Question 2

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

Accepted Answer

Atomicity

Answer

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

Question 3

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

Accepted Answer

Transitive dependencies

Answer

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).

Question 4

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

Accepted Answer

HAVING

Answer

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

Question 5

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

Accepted Answer

Clustered index

Answer

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

CPP - Certified Professional Programmer Practice Test