Database Basics Flashcards

What is an index in a database used for?

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.

Which SQL command is used to remove all rows from a table quickly without logging individual row deletions?

Answer: TRUNCATE

TRUNCATE removes all rows from a table much faster than DELETE because it doesn't log individual row deletions.

In the context of databases, what is a 'view'?

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.

What does ACID stand for in database transactions?

Answer: Atomicity, Consistency, Isolation, Durability

ACID stands for Atomicity, Consistency, Isolation, and Durability — the four properties that guarantee reliable database transactions.

Which SQL aggregate function returns the number of rows that match a condition?

Answer: COUNT()

COUNT() returns the number of rows that match the specified criteria in a query.

What is referential integrity in a relational database?

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.

Which type of database relationship requires an intermediary (junction) table?

Answer: Many-to-many

A many-to-many relationship requires a junction (bridge) table with foreign keys pointing to both related tables.