CPP Database Programming & SQL 1 — Questions and Answers
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?
- INNER JOIN
- LEFT OUTER JOIN (Correct answer)
- RIGHT OUTER JOIN
- FULL OUTER JOIN
Correct 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.
Question 2: Which property of the ACID model ensures that a transaction either fully completes or has no effect on the database?
- Consistency
- Isolation
- Durability
- Atomicity (Correct answer)
Correct 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.
Question 3: A table is in Third Normal Form (3NF) if it is in 2NF and contains no:
- Repeating groups
- Partial dependencies
- Transitive dependencies (Correct answer)
- Multi-valued attributes
Correct 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).
Question 4: Which SQL clause is used to filter results after an aggregate function has been applied?
- WHERE
- HAVING (Correct answer)
- GROUP BY
- ORDER BY
Correct answer: HAVING
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?
- Composite index
- Non-clustered index
- Clustered index (Correct answer)
- Covering index
Correct 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.
Question 6: In SQL, which command is used to permanently save a transaction's changes to the database?
- SAVE
- ROLLBACK
- COMMIT (Correct answer)
- FLUSH
Correct answer: COMMIT
COMMIT makes all changes made during the current transaction permanent and visible to other transactions.
Question 7: Which of the following best describes a foreign key in a relational database?
- A key that uniquely identifies every row in its own table
- A column that references the primary key of another table (Correct answer)
- An index used to speed up foreign-table lookups
- A composite key made of two or more columns
Correct 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.
CPP Database Programming & SQL 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?