CPA Database Administration 2 — Questions and Answers
Question 1: Which SQL clause is used to filter rows after aggregation has been applied?
- WHERE
- HAVING (Correct answer)
- FILTER
- QUALIFY
Correct answer: HAVING
HAVING filters groups produced by GROUP BY, whereas WHERE filters individual rows before aggregation.
Question 2: A table has no primary key defined. Which term best describes such a table?
- Denormalized table
- Heap table (Correct answer)
- Clustered table
- Partitioned table
Correct answer: Heap table
A heap table is a table stored without any ordered index or defined primary key, resulting in unordered row storage.
Question 3: Which isolation level prevents dirty reads but still allows non-repeatable reads?
- Read Uncommitted
- Read Committed (Correct answer)
- Repeatable Read
- Serializable
Correct answer: Read Committed
Read Committed prevents dirty reads by only seeing committed data, but another transaction can modify the row between two reads in the same transaction.
Question 4: What does the SQL keyword DISTINCT do in a SELECT statement?
- Sorts the result set ascending
- Removes duplicate rows from the result (Correct answer)
- Filters NULL values automatically
- Limits the number of rows returned
Correct answer: Removes duplicate rows from the result
DISTINCT eliminates duplicate rows from the query result so each unique combination of selected columns appears only once.
Question 5: Which type of database index is most efficient for range queries on a column?
- Hash index
- Bitmap index
- B-tree index (Correct answer)
- Full-text index
Correct answer: B-tree index
B-tree indexes maintain sorted order, making them ideal for range queries using operators like BETWEEN, <, and >.
Question 6: In a relational database, what is a composite key?
- A key that references another table
- A key made up of two or more columns (Correct answer)
- An auto-incrementing integer key
- A key stored in an encrypted format
Correct answer: A key made up of two or more columns
A composite key uses two or more columns together to uniquely identify a row when no single column is sufficient.
Question 7: Which SQL command permanently removes a table and all its data from the database?
- DELETE
- TRUNCATE
- DROP (Correct answer)
- REMOVE
Correct answer: DROP
DROP TABLE removes the table definition and all its data, indexes, and constraints permanently from the database.
CPA Database Administration 2
Which SQL clause is used to filter rows after aggregation has been applied?