Database Management & SQL Flashcards

Which SQL JOIN type returns only rows where there is a match in BOTH tables?

Answer: INNER JOIN

INNER JOIN returns only the rows that have matching values in both joined tables.

In SQL, which aggregate function returns the number of non-NULL values in a column?

Answer: COUNT()

COUNT(column_name) counts non-NULL values, while COUNT(*) counts all rows including NULLs.

Which clause is used to filter results AFTER a GROUP BY aggregation?

Answer: HAVING

HAVING filters grouped results after aggregation, whereas WHERE filters rows before grouping.

A talent database query needs to list all employees and their managers, including employees with no manager. Which JOIN is most appropriate?

Answer: LEFT JOIN

LEFT JOIN returns all rows from the left (employees) table even when no matching row exists in the right (managers) table.

What does the SQL keyword DISTINCT do when used in a SELECT statement?

Answer: Removes duplicate rows from results

DISTINCT eliminates duplicate rows so each unique combination of selected column values appears only once.

Which SQL statement is used to retrieve data from a database?

Answer: SELECT

SELECT is the DML statement used to query and retrieve data from one or more database tables.

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?

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.