BCS Database Management Systems 2 — Questions and Answers
Question 1: What does SQL JOIN do?
- Combines rows from two or more tables based on a related column (Correct answer)
- Deletes duplicate rows
- Creates a new table from query results
- Merges two databases into one
Correct answer: Combines rows from two or more tables based on a related column
A JOIN clause combines rows from two or more tables based on a matching condition between related columns, typically primary and foreign key pairs.
Question 2: What is the difference between DELETE and TRUNCATE in SQL?
- They are identical commands
- DELETE removes specific rows with a WHERE clause and is logged; TRUNCATE removes all rows and is faster (Correct answer)
- TRUNCATE can use a WHERE clause; DELETE cannot
- DELETE is for DDL; TRUNCATE is for DML
Correct answer: DELETE removes specific rows with a WHERE clause and is logged; TRUNCATE removes all rows and is faster
DELETE removes specific rows (or all rows) with transaction logging, while TRUNCATE removes all rows more efficiently by deallocating data pages and cannot be filtered with WHERE.
Question 3: What is an index in a database?
- A copy of an entire table
- A data structure that improves the speed of data retrieval at the cost of additional storage space (Correct answer)
- A constraint that enforces uniqueness
- A temporary table used during queries
Correct answer: A data structure that improves the speed of data retrieval at the cost of additional storage space
A database index is a data structure (like a B-tree) that allows faster row lookup on indexed columns at the cost of extra storage and slower write operations.
Question 4: In relational algebra, what does the PROJECTION operation do?
- Selects rows based on a condition
- Selects specific columns from a relation (Correct answer)
- Combines two relations based on a condition
- Removes duplicate rows
Correct answer: Selects specific columns from a relation
The PROJECTION operation (π) selects specific columns from a relation, eliminating all other attributes from the result.
Question 5: What is a stored procedure in a database?
- An automatic backup process
- A precompiled set of SQL statements stored in the database that can be executed as a single unit (Correct answer)
- A read-only view of a table
- A constraint on column values
Correct answer: A precompiled set of SQL statements stored in the database that can be executed as a single unit
A stored procedure is a precompiled collection of SQL statements stored in the database that can be called by name, improving performance and code reuse.
Question 6: What is the purpose of a database view?
- To store aggregated data permanently
- To present a virtual table based on a query, simplifying complex queries and controlling data access (Correct answer)
- To create physical copies of data
- To replace primary keys
Correct answer: To present a virtual table based on a query, simplifying complex queries and controlling data access
A view is a virtual table defined by a SELECT query, providing a simplified interface to complex data and allowing row/column-level security.
BCS Database Management Systems 2
What does SQL JOIN do?