CS Database Management & SQL 2 — Questions and Answers
Question 1: Which SQL clause is used to filter groups after aggregation with GROUP BY?
- HAVING (Correct answer)
- WHERE
- FILTER
- ORDER BY
Correct answer: HAVING
HAVING filters aggregated groups, while WHERE filters rows before grouping.
Question 2: A table's primary key must be:
- Unique and not null (Correct answer)
- Numeric only
- Auto-incrementing
- Indexed with a full-text index
Correct answer: Unique and not null
A primary key uniquely identifies each row and cannot contain NULL values.
Question 3: Which JOIN returns all rows from the left table plus matching rows from the right table?
- LEFT OUTER JOIN (Correct answer)
- INNER JOIN
- CROSS JOIN
- RIGHT OUTER JOIN
Correct answer: LEFT OUTER JOIN
A LEFT OUTER JOIN keeps every left-table row, filling unmatched right-side columns with NULL.
Question 4: What does the SQL statement 'DELETE FROM orders;' do without a WHERE clause?
- Removes all rows from the orders table (Correct answer)
- Deletes the orders table structure
- Deletes only the first row
- Raises a syntax error
Correct answer: Removes all rows from the orders table
DELETE without WHERE removes every row but leaves the table definition intact, unlike DROP TABLE.
Question 5: A relation is in second normal form (2NF) when it is in 1NF and:
- No non-key attribute depends on only part of a composite key (Correct answer)
- All attributes are atomic
- No transitive dependencies exist
- Every determinant is a candidate key
Correct answer: No non-key attribute depends on only part of a composite key
2NF eliminates partial dependencies of non-key attributes on a composite primary key.
Question 6: Which SQL keyword eliminates duplicate rows from a query result?
- DISTINCT (Correct answer)
- UNIQUE
- DEDUPE
- SINGLE
Correct answer: DISTINCT
SELECT DISTINCT returns only unique rows in the result set.
Question 7: In the ACID properties of transactions, 'isolation' means:
- Concurrent transactions do not interfere with each other's intermediate states (Correct answer)
- Data survives system crashes
- Transactions either fully complete or fully roll back
- Constraints remain valid after each transaction
Correct answer: Concurrent transactions do not interfere with each other's intermediate states
Isolation ensures concurrent transactions behave as if executed serially, hiding intermediate states.
CS Database Management & SQL 2
Which SQL clause is used to filter groups after aggregation with GROUP BY?