CPA Database Administration 3 — Questions and Answers
Question 1: What is a database view?
- A physical copy of a table stored on disk
- A virtual table defined by a stored SELECT query (Correct answer)
- An index on a frequently queried column
- A snapshot of the database at a point in time
Correct answer: A virtual table defined by a stored SELECT query
A view is a named, stored SQL query that behaves like a virtual table; it does not store data itself unless it is a materialized view.
Question 2: Which normal form eliminates transitive dependencies?
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF) (Correct answer)
- Boyce-Codd Normal Form (BCNF)
Correct answer: Third Normal Form (3NF)
3NF requires that every non-key attribute depend only on the primary key, eliminating transitive dependencies between non-key columns.
Question 3: What does an INNER JOIN return?
- All rows from the left table with NULLs for non-matches
- All rows from both tables regardless of match
- Only rows where there is a match in both tables (Correct answer)
- All rows from the right table with NULLs for non-matches
Correct answer: Only rows where there is a match in both tables
INNER JOIN returns only the rows where the join condition is satisfied in both tables, excluding non-matching rows from either side.
Question 4: Which constraint ensures that a column value in one table exists as a primary key in another table?
- UNIQUE constraint
- CHECK constraint
- FOREIGN KEY constraint (Correct answer)
- NOT NULL constraint
Correct answer: FOREIGN KEY constraint
A FOREIGN KEY constraint enforces referential integrity by requiring that the column value matches a primary key in the referenced table.
Question 5: What is the purpose of the ROLLBACK statement in SQL?
- Save all changes made in the current transaction
- Undo all changes made since the last COMMIT or SAVEPOINT (Correct answer)
- Remove old transaction logs from the database
- Create a checkpoint in the current transaction
Correct answer: Undo all changes made since the last COMMIT or SAVEPOINT
ROLLBACK undoes all data modifications made within the current transaction, restoring the database to its state before the transaction began.
Question 6: Which aggregate function returns the number of non-NULL values in a column?
- SUM()
- AVG()
- COUNT() (Correct answer)
- TOTAL()
Correct answer: COUNT()
COUNT(column_name) returns the number of non-NULL values in the specified column, while COUNT(*) counts all rows including those with NULLs.
Question 7: What is a stored procedure in a relational database?
- A database table that stores procedural data
- A precompiled set of SQL statements stored in the database (Correct answer)
- A trigger that runs automatically on data changes
- A scheduled job that backs up the database
Correct answer: A precompiled set of SQL statements stored in the database
A stored procedure is a named, precompiled collection of SQL statements saved in the database that can be executed by calling its name.
