DAC DAC Database & SQL Analytics 2 — Questions and Answers
Question 1: Which SQL window function assigns a unique sequential integer to rows within a partition ordered by a specified column?
- RANK()
- COUNT()
- ROW_NUMBER() (Correct answer)
- NTILE()
Correct answer: ROW_NUMBER()
ROW_NUMBER() assigns a unique sequential number to each row within its partition, with no gaps or ties unlike RANK().
Question 2: What is a subquery in SQL?
- A stored procedure for repeated queries
- A query nested inside another query (Correct answer)
- A temporary table created during execution
- A query that spans multiple databases
Correct 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.
Question 3: Which normal form eliminates transitive dependencies in a relational database?
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF) (Correct answer)
- Boyce-Codd Normal Form (BCNF)
Correct 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).
Question 4: What is the purpose of the GROUP BY clause in SQL?
- To sort query results in ascending order
- To combine rows from multiple tables
- To group rows sharing common values for use with aggregate functions (Correct answer)
- To filter rows based on a condition
Correct 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.
Question 5: In SQL, what does a FULL OUTER JOIN return?
- Only rows that have matches in both tables
- All rows from the left table and matching rows from the right table
- All rows from both tables, with NULLs where there is no match on either side (Correct answer)
- Only rows that have no match in either table
Correct 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.
Question 6: What is the difference between DELETE and TRUNCATE in SQL?
- DELETE removes specific rows based on a condition; TRUNCATE removes all rows and cannot be rolled back in most databases (Correct answer)
- TRUNCATE removes specific rows; DELETE removes all rows
- DELETE is faster than TRUNCATE for large tables
- There is no functional difference between DELETE and TRUNCATE
Correct 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.
DAC DAC Database & SQL Analytics 2
Which SQL window function assigns a unique sequential integer to rows within a partition ordered by a specified column?