TOC Database Management & SQL 2 — Questions and Answers
Question 1: Which SQL JOIN type returns only rows where there is a match in BOTH tables?
- LEFT JOIN
- INNER JOIN (Correct answer)
- FULL OUTER JOIN
- CROSS JOIN
Correct answer: INNER JOIN
INNER JOIN returns only the rows that have matching values in both joined tables.
Question 2: In SQL, which aggregate function returns the number of non-NULL values in a column?
- SUM()
- AVG()
- COUNT() (Correct answer)
- MAX()
Correct answer: COUNT()
COUNT(column_name) counts non-NULL values, while COUNT(*) counts all rows including NULLs.
Question 3: Which clause is used to filter results AFTER a GROUP BY aggregation?
- WHERE
- HAVING (Correct answer)
- FILTER
- LIMIT
Correct answer: HAVING
HAVING filters grouped results after aggregation, whereas WHERE filters rows before grouping.
Question 4: A talent database query needs to list all employees and their managers, including employees with no manager. Which JOIN is most appropriate?
- INNER JOIN
- RIGHT JOIN
- LEFT JOIN (Correct answer)
- CROSS JOIN
Correct answer: LEFT JOIN
LEFT JOIN returns all rows from the left (employees) table even when no matching row exists in the right (managers) table.
Question 5: What does the SQL keyword DISTINCT do when used in a SELECT statement?
- Sorts results alphabetically
- Removes duplicate rows from results (Correct answer)
- Filters NULL values
- Limits the result set to 1 row
Correct answer: Removes duplicate rows from results
DISTINCT eliminates duplicate rows so each unique combination of selected column values appears only once.
Question 6: Which SQL statement is used to retrieve data from a database?
- GET
- FETCH
- SELECT (Correct answer)
- PULL
Correct answer: SELECT
SELECT is the DML statement used to query and retrieve data from one or more database tables.
Question 7: In a talent analytics query, you want the average performance score grouped by department, but only for departments with more than 10 employees. Which SQL correctly applies this?
- SELECT dept, AVG(score) FROM staff WHERE COUNT(*)>10 GROUP BY dept
- SELECT dept, AVG(score) FROM staff GROUP BY dept HAVING COUNT(*)>10 (Correct answer)
- SELECT dept, AVG(score) FROM staff GROUP BY dept WHERE COUNT(*)>10
- SELECT dept, AVG(score) FROM staff HAVING COUNT(*)>10
Correct answer: SELECT dept, AVG(score) FROM staff GROUP BY dept HAVING COUNT(*)>10
HAVING COUNT(*)>10 filters aggregated groups after GROUP BY, which WHERE cannot do.
TOC Database Management & SQL 2
Which SQL JOIN type returns only rows where there is a match in BOTH tables?