Practice: single-table queries

Optional

50 min · Beginner

A review of this module. Skip it if you feel confident; the next module does not need it.

Fix the mistakes

The slips that cost marks in this module. Spot each one, then check the fix.

Every single-table question has the same shape: which columns, which rows, in what order, and how many. Most wrong answers come from a short list of habits: a missing ORDER BY, = NULL, AND mixed with OR, and sorting formatted text.

Filter, sort and page results

Every single-table question has four parts: the columns, the rows, the order and how many. The example takes one question apart and writes the clauses in exactly that order.

Example 1 · Page 2 of in-stock products under 3000, cheapest first

SQL

1-- Columns: name, price, stock2-- Rows:    stock > 0 and price < 30003-- Order:   cheapest first, ties by id4-- How many: page 2, 3 per page5SELECT name, price, stock6FROM products7WHERE stock > 0 AND price < 30008ORDER BY price, product_id9LIMIT 3 OFFSET 3;

Output

+-------------------+--------+-------+
| name              | price  | stock |
+-------------------+--------+-------+
| Gift Card Rs. 500 | 500.00 |   999 |
| Hands-On Python   | 650.00 |    22 |
| Cotton Kurta      | 899.00 |    35 |
+-------------------+--------+-------+

What this code does

The four comment lines are the question taken apart; each becomes one clause, in the same order. The out-of-stock mouse is removed by WHERE before any page is cut.
Page 2 skips the 3 cheapest matches (299, 399 and 499) and shows the next 3. product_id would decide the order if two prices were equal.

The classic mistakes are about the last two parts. LIMIT without ORDER BY returns some rows, not the top ones, and an ORDER BY with ties gives pages that overlap.

"Cheapest first" → ORDER BY price; "latest" → ORDER BY order_date DESC.
"Top 3" → ORDER BY ... DESC LIMIT 3; page p of size n → LIMIT n OFFSET (p - 1) * n.
Add the id as the last sort column whenever ties are possible.

Handle NULL in real data

Real data has NULLs where you least expect them: a customer with no phone, an employee with no department. The example keeps the NULL rows in a result and gives them a label.

Example 2 · Employees outside Engineering, NULL included and labelled

SQL

1SELECT name,2       IFNULL(dept_id, 'unassigned') AS dept,3       salary4FROM employees5WHERE dept_id <> 1 OR dept_id IS NULL6ORDER BY dept_id IS NULL, dept_id, name;

Output

+----------------+------------+-----------+
| name           | dept       | salary    |
+----------------+------------+-----------+
| Amit Shah      | 2          |  85000.00 |
| Farah Ali      | 2          |  85000.00 |
| Neha Joshi     | 2          | 150000.00 |
| Lakshmi Menon  | 3          |  90000.00 |
| Rohan Das      | 3          |  60000.00 |
| Gita Reddy     | 4          | 140000.00 |
| Manoj Pillai   | 4          |  75000.00 |
| Tanvi Kulkarni | unassigned |  70000.00 |
+----------------+------------+-----------+

What this code does

dept_id <> 1 alone would drop Tanvi Kulkarni, whose department is NULL. OR dept_id IS NULL brings her back.
IFNULL labels the gap instead of showing a bare NULL, and ORDER BY dept_id IS NULL moves the unassigned row to the end, where a reader expects it.

= NULL matches nothing; use IS NULL. dept_id <> 1 silently drops NULL rows, so add OR dept_id IS NULL. Arithmetic or CONCAT with a NULL part gives NULL; wrap the part in COALESCE or IFNULL.

Predict the output

Keep the NULL rows?

Read the code and pick what it prints.

Round 1 of 2

Tanvi Kulkarni has no department (NULL).

SELECT COUNT(*)

FROM employees

WHERE dept_id <> 1;

Ask "what happens to the NULL rows?" for every condition on a column that allows NULL. It is the question interviewers ask too.

Format results with functions

Formatting functions belong in the SELECT list, at the very end. The example prints a customer directory where every column is formatted, while the filter and the sort use the raw columns.

Example 3 · A customer directory: show formatted, sort raw

SQL

1SELECT CONCAT('C-', LPAD(customer_id, 3, '0'))               AS code,2       UPPER(name)                                             AS customer,3       IFNULL(CONCAT('XXXXXX', RIGHT(phone, 4)), 'not given')  AS phone,4       DATE_FORMAT(joined_on, '%d %b %Y')                      AS member_since5FROM customers6WHERE joined_on < '2025-03-01'7ORDER BY joined_on;

Output

+-------+--------------+------------+--------------+
| code  | customer     | phone      | member_since |
+-------+--------------+------------+--------------+
| C-001 | ASHA RAO     | XXXXXX0001 | 03 Nov 2024  |
| C-002 | RAVI KUMAR   | XXXXXX0002 | 15 Dec 2024  |
| C-003 | MEENA IYER   | not given  | 08 Jan 2025  |
| C-004 | KABIR SINGH  | XXXXXX0004 | 20 Jan 2025  |
| C-005 | PRIYA SHARMA | XXXXXX0005 | 02 Feb 2025  |
| C-006 | ARJUN MEHTA  | not given  | 14 Feb 2025  |
+-------+--------------+------------+--------------+

What this code does

Every column is formatted text, but WHERE and ORDER BY use the raw joined_on date, so the filter and the order are both correct.
Sorting by member_since instead would put 02 Feb 2025 before 03 Nov 2024, because text is compared character by character.

Sorting by the formatted text is the mistake: ORDER BY DATE_FORMAT(joined_on, '%d-%m-%Y') sorts by day number, and ORDER BY FORMAT(price, 2) puts 9,999.00 after 10,000.00. Keep the raw columns for WHERE and ORDER BY.

Show: DATE_FORMAT(d, '%d %b %Y'); sort: ORDER BY d.
Show: FORMAT(amount, 2, 'en_IN'); sort: ORDER BY amount.
Show: CONCAT_WS(' | ', name, phone); NULL parts are skipped.

Mixed review: choosing the right filter

A longer question is several filters joined with AND, with brackets around any OR. The example answers one question with a list, a date range and a number range; the list after it matches common wording to the filter.

Example 4 · One question, several filters

SQL

1-- Open or delivered orders from February to April 20252-- for customers 1 to 5, newest first, with a label.3SELECT order_id,4       customer_id,5       DATE_FORMAT(order_date, '%d-%m-%Y') AS placed_on,6       CASE status7         WHEN 'DELIVERED' THEN 'Done'8         ELSE 'Open'9       END AS stage10FROM orders11WHERE status IN ('PLACED', 'SHIPPED', 'DELIVERED')12  AND order_date BETWEEN '2025-02-01' AND '2025-04-30'13  AND customer_id BETWEEN 1 AND 514ORDER BY order_date DESC;

Output

+----------+-------------+------------+-------+
| order_id | customer_id | placed_on  | stage |
+----------+-------------+------------+-------+
|       10 |           1 | 12-04-2025 | Open  |
|        7 |           2 | 15-03-2025 | Open  |
|        6 |           5 | 03-03-2025 | Done  |
|        5 |           4 | 21-02-2025 | Done  |
|        3 |           1 | 02-02-2025 | Done  |
+----------+-------------+------------+-------+

What this code does

Each part of the question picked its own filter: a list (IN), a date range on a DATE column (BETWEEN) and a number range. All three are joined with AND, so no brackets are needed.
The CASE label and the formatted date are only for display; the sort uses the real order_date.

One exact value → =; any value except one → <>.
One of a few values → IN (...); none of them → NOT IN (...) (no NULL in the list).
A range → BETWEEN a AND b for numbers and DATE; >= start AND < next for DATETIME.
Text that starts with, ends with or contains → LIKE; an exact shape → REGEXP '^...$'; missing → IS NULL.

Module quiz

Questions from the whole module. Your first answer is your score.

Problems

Graded easy to hard, using everything from this module.

Try each one yourself first. Use the hint if you get stuck. Mark it as done when it works.

Exercise 1· Easy

Show name and city of the customers from Chennai or Kochi, A to Z by name.

Expected output

+------------+---------+
| name       | city    |
+------------+---------+
| Divya Nair | Kochi   |
| Meena Iyer | Chennai |
| Ravi Kumar | Chennai |
+------------+---------+

Exercise 2· Easy

Show the 3 newest employees: name and hire_date.

Expected output

+----------------+------------+
| name           | hire_date  |
+----------------+------------+
| Tanvi Kulkarni | 2025-01-06 |
| Rohan Das      | 2024-05-20 |
| Farah Ali      | 2023-02-01 |
+----------------+------------+

Exercise 3· Medium

Show student_id, course_id and marks of every enrollment with marks from 60 to 80, both included, sorted by marks (highest first) and then by student id.

Expected output

+------------+-----------+-------+
| student_id | course_id | marks |
+------------+-----------+-------+
|          4 |         5 |    80 |
|          2 |         1 |    79 |
|          1 |         4 |    75 |
|          6 |         3 |    72 |
|          3 |         3 |    67 |
|          7 |         1 |    64 |
+------------+-----------+-------+

Exercise 4· Medium

Show name and a contact column for every customer who joined in 2025: the phone number, or the email when there is no phone. Sort by customer id.

Expected output

+--------------+-------------------------+
| name         | contact                 |
+--------------+-------------------------+
| Meena Iyer   | meena.iyer@example.com  |
| Kabir Singh  | 9876500004              |
| Priya Sharma | 9876500005              |
| Arjun Mehta  | arjun.mehta@example.com |
| Divya Nair   | 9876500007              |
| Rahul Verma  | 9876500008              |
| Sneha Patil  | 9876500009              |
| Imran Khan   | imran.khan@example.com  |
+--------------+-------------------------+

Exercise 5· Medium

Products are listed 4 per page, most expensive first, ties by product id. Show page 2 with name and the price as price_inr in Indian format with 2 decimals.

Expected output

+------------------------+-----------+
| name                   | price_inr |
+------------------------+-----------+
| Redmi Note 13          | 17,999.00 |
| Running Shoes          | 2,499.00  |
| Mi Power Bank 20000mAh | 2,199.00  |
| boAt Airdopes 141      | 1,299.00  |
+------------------------+-----------+

Exercise 6· Medium

This query should list the products with no category or with fewer than 10 in stock, cheapest first, but it returns only the mouse, the HP laptop and the iPhone, and misses the gift card. Fix it.

SQL

SELECT name, category_id, stockFROM productsWHERE category_id = NULL OR stock < 10ORDER BY price;

Expected output

+---------------------+-------------+-------+
| name                | category_id | stock |
+---------------------+-------------+-------+
| Gift Card Rs. 500   |        NULL |   999 |
| Logitech M331 Mouse |           4 |     0 |
| HP Victus Gaming    |           3 |     5 |
| iPhone 15           |           2 |     8 |
+---------------------+-------------+-------+

Exercise 7· Hard

Make an order list for customers 1 and 2: order_no like SL-00001, placed_on like 05 Jan 2025, the weekday as day, and a stage that says Done for DELIVERED, Cancelled for CANCELLED and Open otherwise. Leave out orders placed on a Sunday. Sort by the real order date.

Expected output

+----------+-------------+----------+-------+
| order_no | placed_on   | day      | stage |
+----------+-------------+----------+-------+
| SL-00002 | 18 Jan 2025 | Saturday | Done  |
| SL-00007 | 15 Mar 2025 | Saturday | Open  |
| SL-00010 | 12 Apr 2025 | Saturday | Open  |
+----------+-------------+----------+-------+

Exercise 8· Hard

HR wants a list of employees whose first name starts with a letter from A to M, who earn from 80000 to 150000 and who have at least 3 full years of service on 2025-06-30. Show name, salary and years (full years), most years first, then by name.

Expected output

+---------------+-----------+-------+
| name          | salary    | years |
+---------------+-----------+-------+
| Gita Reddy    | 140000.00 |     9 |
| Lakshmi Menon |  90000.00 |     6 |
| Karthik Raj   | 120000.00 |     5 |
| Amit Shah     |  85000.00 |     4 |
+---------------+-----------+-------+

Interview questions

Questions interviewers ask on this topic.

Finish up

Mark complete & continue

Next: Mini project: IPL player statistics

1-- Columns: name, price, stock2-- Rows: stock > 0 and price < 30003-- Order: cheapest first, ties by id4-- How many: page 2, 3 per page5SELECT name, price, stock6FROM products7WHERE stock > 0 AND price < 30008ORDER BY price, product_id9LIMIT 3 OFFSET 3;

+-------------------+--------+-------+ | name | price | stock | +-------------------+--------+-------+ | Gift Card Rs. 500 | 500.00 | 999 | | Hands-On Python | 650.00 | 22 | | Cotton Kurta | 899.00 | 35 | +-------------------+--------+-------+

+----------------+------------+-----------+ | name | dept | salary | +----------------+------------+-----------+ | Amit Shah | 2 | 85000.00 | | Farah Ali | 2 | 85000.00 | | Neha Joshi | 2 | 150000.00 | | Lakshmi Menon | 3 | 90000.00 | | Rohan Das | 3 | 60000.00 | | Gita Reddy | 4 | 140000.00 | | Manoj Pillai | 4 | 75000.00 | | Tanvi Kulkarni | unassigned | 70000.00 | +----------------+------------+-----------+

1SELECT CONCAT('C-', LPAD(customer_id, 3, '0')) AS code,2 UPPER(name) AS customer,3 IFNULL(CONCAT('XXXXXX', RIGHT(phone, 4)), 'not given') AS phone,4 DATE_FORMAT(joined_on, '%d %b %Y') AS member_since5FROM customers6WHERE joined_on < '2025-03-01'7ORDER BY joined_on;

+-------+--------------+------------+--------------+ | code | customer | phone | member_since | +-------+--------------+------------+--------------+ | C-001 | ASHA RAO | XXXXXX0001 | 03 Nov 2024 | | C-002 | RAVI KUMAR | XXXXXX0002 | 15 Dec 2024 | | C-003 | MEENA IYER | not given | 08 Jan 2025 | | C-004 | KABIR SINGH | XXXXXX0004 | 20 Jan 2025 | | C-005 | PRIYA SHARMA | XXXXXX0005 | 02 Feb 2025 | | C-006 | ARJUN MEHTA | not given | 14 Feb 2025 | +-------+--------------+------------+--------------+

1-- Open or delivered orders from February to April 20252-- for customers 1 to 5, newest first, with a label.3SELECT order_id,4 customer_id,5 DATE_FORMAT(order_date, '%d-%m-%Y') AS placed_on,6 CASE status7 WHEN 'DELIVERED' THEN 'Done'8 ELSE 'Open'9 END AS stage10FROM orders11WHERE status IN ('PLACED', 'SHIPPED', 'DELIVERED')12 AND order_date BETWEEN '2025-02-01' AND '2025-04-30'13 AND customer_id BETWEEN 1 AND 514ORDER BY order_date DESC;

+----------+-------------+------------+-------+ | order_id | customer_id | placed_on | stage | +----------+-------------+------------+-------+ | 10 | 1 | 12-04-2025 | Open | | 7 | 2 | 15-03-2025 | Open | | 6 | 5 | 03-03-2025 | Done | | 5 | 4 | 21-02-2025 | Done | | 3 | 1 | 02-02-2025 | Done | +----------+-------------+------------+-------+

SELECT name -- line 1FROM employees -- line 2WHERE dept_id = 2 OR dept_id = 3 -- line 3 AND hire_date > '2020-12-31' -- line 4ORDER BY name;

+----------------+------------+ | name | hire_date | +----------------+------------+ | Tanvi Kulkarni | 2025-01-06 | | Rohan Das | 2024-05-20 | | Farah Ali | 2023-02-01 | +----------------+------------+

+------------+-----------+-------+ | student_id | course_id | marks | +------------+-----------+-------+ | 4 | 5 | 80 | | 2 | 1 | 79 | | 1 | 4 | 75 | | 6 | 3 | 72 | | 3 | 3 | 67 | | 7 | 1 | 64 | +------------+-----------+-------+

+------------------------+-----------+ | name | price_inr | +------------------------+-----------+ | Redmi Note 13 | 17,999.00 | | Running Shoes | 2,499.00 | | Mi Power Bank 20000mAh | 2,199.00 | | boAt Airdopes 141 | 1,299.00 | +------------------------+-----------+

+---------------------+-------------+-------+ | name | category_id | stock | +---------------------+-------------+-------+ | Gift Card Rs. 500 | NULL | 999 | | Logitech M331 Mouse | 4 | 0 | | HP Victus Gaming | 3 | 5 | | iPhone 15 | 2 | 8 | +---------------------+-------------+-------+

+----------+-------------+----------+-------+ | order_no | placed_on | day | stage | +----------+-------------+----------+-------+ | SL-00002 | 18 Jan 2025 | Saturday | Done | | SL-00007 | 15 Mar 2025 | Saturday | Open | | SL-00010 | 12 Apr 2025 | Saturday | Open | +----------+-------------+----------+-------+

+---------------+-----------+-------+ | name | salary | years | +---------------+-----------+-------+ | Gita Reddy | 140000.00 | 9 | | Lakshmi Menon | 90000.00 | 6 | | Karthik Raj | 120000.00 | 5 | | Amit Shah | 85000.00 | 4 | +---------------+-----------+-------+

SELECT name -- line 1FROM employees -- line 2WHERE dept_id = 2 OR dept_id = 3 -- line 3 AND hire_date > '2020-12-31' -- line 4ORDER BY name;

Filter, sort and page results

What this code does

Handle NULL in real data

What this code does

Format results with functions

What this code does

Mixed review: choosing the right filter

What this code does

Exercise 1· Easy

Exercise 2· Easy

Exercise 3· Medium

Exercise 4· Medium

Exercise 5· Medium

Exercise 6· Medium

Exercise 7· Hard

Exercise 8· Hard

Filter, sort and page results

What this code does

Handle NULL in real data

What this code does

Format results with functions

What this code does

Mixed review: choosing the right filter

What this code does

In which order are the clauses written?

Which filter fits "students in year 2 or 3"?

Using the practice database, what does this query return?

What does this query return?

How many rows does this query return? (Products 1–15; product 15 has a NULL category.)

Which line has the bug? The query should list Sales or HR employees hired after 2020.

What does this query print?

Which query returns the 2 most recent orders of customer 1?

What does this query return?

A report shows DATE_FORMAT(order_date, '%d-%m-%Y') AS placed_on and sorts with ORDER BY placed_on. What goes wrong?

Which filter finds payments made on 15 August 2025 in a DATETIME column paid_at?

What does this query return? (Course 5 marks: student 4 has 80, student 5 has NULL.)

Exercise 1· Easy

Exercise 2· Easy

Exercise 3· Medium

Exercise 4· Medium

Exercise 5· Medium

Exercise 6· Medium

Exercise 7· Hard

Exercise 8· Hard

Q1How do you approach writing a query for a new question?

Q2What is wrong with SELECT ... LIMIT 10 without ORDER BY?

Q3A condition like dept_id <> 1 is missing some rows. Why?

Q4Why should you not sort by a formatted column?

Q5When would you choose BETWEEN over >= and <?

Q6How do you search for products whose name contains a word typed by a user?

Q7What would you check before trusting a query's result?

In which order are the clauses written?

Which filter fits "students in year 2 or 3"?

Using the practice database, what does this query return?

What does this query return?

How many rows does this query return? (Products 1–15; product 15 has a NULL category.)

Which line has the bug? The query should list Sales or HR employees hired after 2020.

What does this query print?

Which query returns the 2 most recent orders of customer 1?

What does this query return?

A report shows DATE_FORMAT(order_date, '%d-%m-%Y') AS placed_on and sorts with ORDER BY placed_on. What goes wrong?

Which filter finds payments made on 15 August 2025 in a DATETIME column paid_at?

What does this query return? (Course 5 marks: student 4 has 80, student 5 has NULL.)

Q1How do you approach writing a query for a new question?

Q2What is wrong with SELECT ... LIMIT 10 without ORDER BY?

Q3A condition like dept_id <> 1 is missing some rows. Why?

Q4Why should you not sort by a formatted column?

Q5When would you choose BETWEEN over >= and <?

Q6How do you search for products whose name contains a word typed by a user?

Q7What would you check before trusting a query's result?

A report shows `DATE_FORMAT(order_date, '%d-%m-%Y') AS placed_on` and sorts with `ORDER BY placed_on`. What goes wrong?

Which filter finds payments made on 15 August 2025 in a DATETIME column `paid_at`?

A report shows `DATE_FORMAT(order_date, '%d-%m-%Y') AS placed_on` and sorts with `ORDER BY placed_on`. What goes wrong?

Which filter finds payments made on 15 August 2025 in a DATETIME column `paid_at`?