Web Development Database Basics 3 — Questions and Answers
Question 1: What is an index in a database used for?
- To enforce data uniqueness
- To speed up data retrieval (Correct answer)
- To link two tables together
- To store backup copies of data
Correct answer: To speed up data retrieval
An index is a data structure that improves the speed of data retrieval operations at the cost of additional storage and write overhead.
Question 2: Which SQL command is used to remove all rows from a table quickly without logging individual row deletions?
- DELETE
- DROP
- TRUNCATE (Correct answer)
- REMOVE
Correct answer: TRUNCATE
TRUNCATE removes all rows from a table much faster than DELETE because it doesn't log individual row deletions.
Question 3: In the context of databases, what is a 'view'?
- A physical copy of a table
- A virtual table based on a stored query (Correct answer)
- A type of index on multiple columns
- A stored procedure that returns data
Correct answer: A virtual table based on a stored query
A view is a virtual table defined by a stored SQL query; it doesn't store data itself but presents data from underlying tables.
Question 4: What does ACID stand for in database transactions?
- Atomicity, Consistency, Isolation, Durability (Correct answer)
- Accuracy, Concurrency, Integrity, Distribution
- Atomicity, Concurrency, Isolation, Durability
- Accuracy, Consistency, Integrity, Durability
Correct answer: Atomicity, Consistency, Isolation, Durability
ACID stands for Atomicity, Consistency, Isolation, and Durability — the four properties that guarantee reliable database transactions.
Question 5: Which SQL aggregate function returns the number of rows that match a condition?
- SUM()
- AVG()
- COUNT() (Correct answer)
- MAX()
Correct answer: COUNT()
COUNT() returns the number of rows that match the specified criteria in a query.
Question 6: What is referential integrity in a relational database?
- Ensuring column data types match
- Ensuring foreign key values match existing primary key values (Correct answer)
- Ensuring all columns have non-null values
- Ensuring indexes are up to date
Correct answer: Ensuring foreign key values match existing primary key values
Referential integrity ensures that a foreign key value in one table always corresponds to an existing primary key value in the referenced table.
Question 7: Which type of database relationship requires an intermediary (junction) table?
- One-to-one
- One-to-many
- Many-to-many (Correct answer)
- Self-referencing
Correct answer: Many-to-many
A many-to-many relationship requires a junction (bridge) table with foreign keys pointing to both related tables.
Web Development Database Basics 3
What is an index in a database used for?