Cognizant Database Management Systems 2 — Questions and Answers
Question 1: What is the difference between DELETE and TRUNCATE in SQL?
- DELETE is faster and cannot be rolled back; TRUNCATE can be rolled back
- TRUNCATE removes all rows without logging individual deletions; DELETE logs each row deletion and can be rolled back (Correct answer)
- DELETE drops the table structure; TRUNCATE removes only data
- There is no difference between DELETE and TRUNCATE
Correct answer: TRUNCATE removes all rows without logging individual deletions; DELETE logs each row deletion and can be rolled back
TRUNCATE is a DDL operation that removes all rows quickly without individual row logging, while DELETE is a DML operation that logs each row and can be rolled back within a transaction.
Question 2: Which normal form requires that every non-key attribute is fully functionally dependent on the entire primary key?
- First Normal Form (1NF)
- Second Normal Form (2NF) (Correct answer)
- Third Normal Form (3NF)
- Boyce-Codd Normal Form (BCNF)
Correct answer: Second Normal Form (2NF)
2NF eliminates partial dependencies, meaning every non-key attribute must depend on the whole primary key, not just part of it.
Question 3: What does a database VIEW provide?
- A physical copy of data stored separately for performance
- A virtual table based on the result of a SQL query (Correct answer)
- A backup mechanism for database tables
- A stored procedure that returns tabular data
Correct answer: A virtual table based on the result of a SQL query
A VIEW is a virtual table derived from a stored SQL query; it does not store data physically but presents data from underlying tables.
Question 4: Which type of index physically reorders the table data to match the index?
- Non-clustered index
- Composite index
- Clustered index (Correct answer)
- Unique index
Correct answer: Clustered index
A clustered index determines the physical order of data in a table, so each table can have only one clustered index.
Question 5: What is a deadlock in database systems?
- A situation where a query runs indefinitely due to missing indexes
- A condition where two or more transactions wait for each other to release locks, causing all to be stuck (Correct answer)
- An error caused by inserting duplicate primary keys
- A state where the database connection pool is exhausted
Correct answer: A condition where two or more transactions wait for each other to release locks, causing all to be stuck
A deadlock occurs when two or more transactions each hold locks that the other needs, causing all involved transactions to wait indefinitely.
Question 6: Which SQL set operator removes duplicate rows from the combined result?
- UNION ALL
- INTERSECT ALL
- UNION (Correct answer)
- EXCEPT ALL
Correct answer: UNION
UNION combines results from two SELECT statements and removes duplicate rows, whereas UNION ALL keeps all rows including duplicates.
Question 7: In a database transaction, what does the ROLLBACK command do?
- Saves all changes made during the transaction permanently
- Undoes all changes made during the current transaction (Correct answer)
- Deletes the transaction log
- Commits partial changes made before the error
Correct answer: Undoes all changes made during the current transaction
ROLLBACK reverts all changes made during the current transaction, restoring the database to its state before the transaction began.
Cognizant Database Management Systems 2
What is the difference between DELETE and TRUNCATE in SQL?