Quiz · Medium

SQL Practice: SELECT, GROUP BY, JOINs and NULLs

Run queries in your head against a small employees table. Covers COUNT, AVG, GROUP BY with HAVING, NULL handling, ORDER BY with OFFSET, INNER vs LEFT JOIN and query execution order.

by Kill h.15 questionsCreated Nov 8, 2026, 08:04 PM

Data Science & AI· SQL#sql#databases#joins#data-analysis

15 questions

Show answers

What does this query return?

employees
id | name | dept  | salary
---+------+-------+-------
 1 | Ana  | Sales | 50000
 2 | Ben  | Sales | 60000
 3 | Cara | IT    | 70000
 4 | Dev  | IT    | 80000
 5 | Eli  | HR    | NULL

SELECT COUNT(*) FROM employees;

A
4
B
0
5
D
NULL

COUNT(*) counts rows, whatever is in them. There are 5 rows, including Eli's row with a NULL salary.

What does this query return?

employees
id | name | dept  | salary
---+------+-------+-------
 1 | Ana  | Sales | 50000
 2 | Ben  | Sales | 60000
 3 | Cara | IT    | 70000
 4 | Dev  | IT    | 80000
 5 | Eli  | HR    | NULL

SELECT COUNT(salary) FROM employees;

4
B
5
C
1
D
NULL

COUNT(column) counts only the non-NULL values in that column. Eli's salary is NULL, so the answer is 4, not 5.

What does this query return?

employees
id | name | dept  | salary
---+------+-------+-------
 1 | Ana  | Sales | 50000
 2 | Ben  | Sales | 60000
 3 | Cara | IT    | 70000
 4 | Dev  | IT    | 80000
 5 | Eli  | HR    | NULL

SELECT AVG(salary) FROM employees;

A
52000
65000
C
NULL
D
60000

Aggregates skip NULLs: (50000 + 60000 + 70000 + 80000) / 4 = 65000. 52000 would treat NULL as 0 and divide by 5. Use AVG(COALESCE(salary, 0)) if that is what you want.

How many rows does this query return?

employees
id | name | dept  | salary
---+------+-------+-------
 1 | Ana  | Sales | 50000
 2 | Ben  | Sales | 60000
 3 | Cara | IT    | 70000
 4 | Dev  | IT    | 80000
 5 | Eli  | HR    | NULL

SELECT dept, COUNT(*)
FROM employees
GROUP BY dept
HAVING COUNT(*) > 1;

Grouping gives Sales (2), IT (2) and HR (1). HAVING keeps groups with more than one row, so Sales and IT: 2 rows.

What does this query return?

employees
id | name | dept  | salary
---+------+-------+-------
 1 | Ana  | Sales | 50000
 2 | Ben  | Sales | 60000
 3 | Cara | IT    | 70000
 4 | Dev  | IT    | 80000
 5 | Eli  | HR    | NULL

SELECT name FROM employees WHERE salary = NULL;

A
Eli
B
All five names
No rows
D
An error in every database

Any comparison with NULL using = gives UNKNOWN, never TRUE, so no rows match. Write WHERE salary IS NULL to find Eli.

6
What is the difference between WHERE and HAVING?
- A
  They are interchangeable
- WHERE filters rows before grouping; HAVING filters groups after aggregation
- C
  HAVING filters rows before grouping; WHERE filters groups
- D
  WHERE works only with numbers, HAVING only with text
WHERE runs before GROUP BY, so it cannot use aggregates like COUNT(*). HAVING runs after grouping and is where conditions on aggregates go. You can use both in one query.

What does this query return?

employees
id | name | dept  | salary
---+------+-------+-------
 1 | Ana  | Sales | 50000
 2 | Ben  | Sales | 60000
 3 | Cara | IT    | 70000
 4 | Dev  | IT    | 80000
 5 | Eli  | HR    | NULL

SELECT name
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Cara
B
Dev
C
Ben
D
Eli

Sorted by salary, highest first: Dev (80000), Cara (70000), Ben, Ana. OFFSET 1 skips Dev and LIMIT 1 takes the next row, so this is the classic second-highest salary query. Dev would be the answer with no offset.

8
What happens when you run this statement?
employees id | name | dept | salary ---+------+-------+------- 1 | Ana | Sales | 50000 2 | Ben | Sales | 60000 3 | Cara | IT | 70000 4 | Dev | IT | 80000 5 | Eli | HR | NULL
UPDATE employees SET salary = 0;
- A
  Nothing, because there is no WHERE clause
- B
  Only the first row is updated
- Every row's salary becomes 0
- D
  Only rows with NULL salaries are updated
Without WHERE, UPDATE (and DELETE) affect every row in the table. Run the matching SELECT first, or work inside a transaction you can roll back.

How many rows does this query return?

employees
id | name | dept  | salary
---+------+-------+-------
 1 | Ana  | Sales | 50000
 2 | Ben  | Sales | 60000
 3 | Cara | IT    | 70000
 4 | Dev  | IT    | 80000
 5 | Eli  | HR    | NULL

departments
dept      | floor
----------+------
Sales     | 1
IT        | 2
Marketing | 3

SELECT e.name, d.floor
FROM employees e
INNER JOIN departments d ON e.dept = d.dept;

A
5
B
6
4
D
15

An inner join keeps only matching pairs: Ana and Ben (Sales), Cara and Dev (IT). Eli's HR has no match in departments, and Marketing has no employees, so both drop out. 15 would be a cross join (5 × 3).

What does this query return for Eli?

employees
id | name | dept  | salary
---+------+-------+-------
 1 | Ana  | Sales | 50000
 2 | Ben  | Sales | 60000
 3 | Cara | IT    | 70000
 4 | Dev  | IT    | 80000
 5 | Eli  | HR    | NULL

departments
dept      | floor
----------+------
Sales     | 1
IT        | 2
Marketing | 3

SELECT e.name, d.floor
FROM employees e
LEFT JOIN departments d ON e.dept = d.dept;

Eli with a NULL floor
B
Eli is not in the result
C
Eli with floor 0
D
An error, because HR is missing

A LEFT JOIN keeps every row from the left table (employees). When there is no match on the right, the right-side columns are filled with NULL. The result has 5 rows.

11
You want every department listed, including Marketing, which has no employees. Which FROM clause does that?
- A
  employees e LEFT JOIN departments d ON e.dept = d.dept
- departments d LEFT JOIN employees e ON d.dept = e.dept
- C
  employees e INNER JOIN departments d ON e.dept = d.dept
- D
  departments d INNER JOIN employees e ON d.dept = e.dept
The table whose rows you must keep goes on the left of a LEFT JOIN. Putting employees on the left keeps all employees but drops Marketing. An inner join drops it no matter which table comes first.
12
Why does this query fail in most databases?
employees id | name | dept | salary ---+------+-------+------- 1 | Ana | Sales | 50000 2 | Ben | Sales | 60000 3 | Cara | IT | 70000 4 | Dev | IT | 80000 5 | Eli | HR | NULL
SELECT salary * 12 AS annual FROM employees WHERE annual > 700000;
- WHERE is evaluated before SELECT, so the alias does not exist yet
- B
  You cannot multiply a column in SELECT
- C
  AS is not valid SQL
- D
  WHERE cannot compare numbers
Logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Repeat the expression (WHERE salary * 12 > 700000) or use a subquery. ORDER BY comes after SELECT, so it can use the alias.

How many rows does this query return?

employees
id | name | dept  | salary
---+------+-------+-------
 1 | Ana  | Sales | 50000
 2 | Ben  | Sales | 60000
 3 | Cara | IT    | 70000
 4 | Dev  | IT    | 80000
 5 | Eli  | HR    | NULL

SELECT DISTINCT dept FROM employees;

DISTINCT removes duplicate values: Sales, IT and HR, so 3 rows.

14
In the result of this query, what is HR's total?
employees id | name | dept | salary ---+------+-------+------- 1 | Ana | Sales | 50000 2 | Ben | Sales | 60000 3 | Cara | IT | 70000 4 | Dev | IT | 80000 5 | Eli | HR | NULL
SELECT dept, SUM(salary) FROM employees GROUP BY dept;
- NULL
- B
  0
- C
  The row is missing
- D
  An error
HR's only salary is NULL, and SUM ignores NULLs, so there is nothing to add and the result is NULL, not 0. The HR group still appears because it has a row. Wrap it with COALESCE(SUM(salary), 0) to show 0.
15
In a schema, employees.dept_id references departments.id. What is employees.dept_id?
- A
  A primary key
- A foreign key
- C
  An index on departments
- D
  A composite key
A foreign key points to the primary key of another table and enforces that the referenced row exists (referential integrity). departments.id is the primary key in this pair.