DAC Database & SQL Analytics Flashcards

Which SQL window function assigns a unique sequential integer to rows within a partition ordered by a specified column?

Answer: ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential number to each row within its partition, with no gaps or ties unlike RANK().

What is a subquery in SQL?

Answer: A query nested inside another query

A subquery (or inner query) is a SELECT statement embedded within another SQL statement, used to produce intermediate results.

Which normal form eliminates transitive dependencies in a relational database?

Answer: Third Normal Form (3NF)

Third Normal Form (3NF) requires that all non-key attributes depend only on the primary key and not on other non-key attributes (no transitive dependencies).

What is the purpose of the GROUP BY clause in SQL?

Answer: To group rows sharing common values for use with aggregate functions

GROUP BY groups rows that have the same values in specified columns, enabling aggregate functions like SUM, COUNT, and AVG to be applied per group.

In SQL, what does a FULL OUTER JOIN return?

Answer: All rows from both tables, with NULLs where there is no match on either side

A FULL OUTER JOIN returns all rows from both tables, pairing matching rows and filling in NULLs for rows in each table that have no match in the other.

What is the difference between DELETE and TRUNCATE in SQL?

Answer: DELETE removes specific rows based on a condition; TRUNCATE removes all rows and cannot be rolled back in most databases

DELETE removes rows matching a WHERE condition and is logged row-by-row; TRUNCATE removes all rows at once with minimal logging and typically cannot be rolled back.