CS Database Management & SQL 3 — Questions and Answers
Question 1: Which SQL command permanently removes a table and all of its data and structure?
- DROP TABLE (Correct answer)
- DELETE TABLE
- TRUNCATE ROWS
- REMOVE TABLE
Correct answer: DROP TABLE
DROP TABLE deletes both the table's data and its definition from the database.
Question 2: A foreign key constraint enforces which type of integrity?
- Referential integrity (Correct answer)
- Domain integrity
- Entity integrity
- Physical integrity
Correct answer: Referential integrity
Foreign keys ensure a value in one table matches an existing key in the referenced table.
Question 3: Which query returns employees whose salary is above the company average?
- SELECT name FROM emp WHERE salary > (SELECT AVG(salary) FROM emp); (Correct answer)
- SELECT name FROM emp WHERE salary > AVG(salary);
- SELECT name FROM emp HAVING salary > AVG(salary);
- SELECT name, AVG(salary) FROM emp WHERE salary > AVG;
Correct answer: SELECT name FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);
Aggregate functions cannot appear directly in WHERE, so a subquery computes the average first.
Question 4: What is the main purpose of a database index?
- Speed up data retrieval at the cost of extra storage and slower writes (Correct answer)
- Enforce data types on columns
- Compress table data automatically
- Guarantee transaction atomicity
Correct answer: Speed up data retrieval at the cost of extra storage and slower writes
Indexes accelerate lookups but add storage overhead and slow INSERT/UPDATE operations.
Question 5: Which statement about SQL UNION vs UNION ALL is correct?
- UNION removes duplicate rows while UNION ALL keeps them (Correct answer)
- UNION ALL removes duplicates while UNION keeps them
- Both always remove duplicates
- UNION requires identical table names
Correct answer: UNION removes duplicate rows while UNION ALL keeps them
UNION performs duplicate elimination, making UNION ALL faster when duplicates are acceptable.
Question 6: In an ER diagram, a many-to-many relationship between two entities is typically implemented in a relational schema using:
- A junction (associative) table with two foreign keys (Correct answer)
- A foreign key in either one of the tables
- A composite attribute
- A single merged table
Correct answer: A junction (associative) table with two foreign keys
A junction table holds foreign keys to both entities, decomposing M:N into two 1:N relationships.
Question 7: Which isolation level allows dirty reads?
- READ UNCOMMITTED (Correct answer)
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
Correct answer: READ UNCOMMITTED
READ UNCOMMITTED lets a transaction see uncommitted changes made by other transactions.
CS Database Management & SQL 3
Which SQL command permanently removes a table and all of its data and structure?