Fundamental Database Concepts Flashcards

Which SQL clause filters rows AFTER a GROUP BY aggregation has been performed?

Answer: HAVING

HAVING filters groups produced by GROUP BY, whereas WHERE filters individual rows before grouping occurs.

What does DCL (Data Control Language) consist of in SQL?

Answer: GRANT and REVOKE statements

DCL includes GRANT (to give privileges) and REVOKE (to remove privileges), controlling user access to database objects.

Which type of JOIN returns all rows from the left table and matching rows from the right table, with NULLs for non-matching right rows?

Answer: LEFT OUTER JOIN

A LEFT OUTER JOIN returns all rows from the left table plus matching rows from the right table; unmatched right-table columns appear as NULL.

What is data redundancy in a database, and why is it problematic?

Answer: Storing the same data in multiple places; it leads to inconsistency and update anomalies

Data redundancy means the same data is stored in multiple locations, causing update anomalies where changing one copy may leave other copies out of sync.

In a relational database, what is a candidate key?

Answer: Any minimal set of attributes that can uniquely identify a row

A candidate key is any minimal superkey — a set of attributes that uniquely identifies rows, from which the primary key is chosen.

What is the difference between DELETE and TRUNCATE in Oracle SQL?

Answer: DELETE can be rolled back and fires triggers; TRUNCATE is DDL and cannot be easily rolled back

DELETE is a DML statement that can be rolled back and fires row-level triggers, while TRUNCATE is DDL that bypasses rollback segments and is much faster.

Which constraint ensures that a column value in one table matches a value in a specified column of another table?

Answer: FOREIGN KEY constraint

A FOREIGN KEY constraint enforces referential integrity by requiring that column values match existing values in the referenced parent table's column.