Practice: questions about the shop

How to use this page

Connect with psql shop. For each question, write and run your query before opening the answer. If your result matches ours row for row, you are right, even if your query looks different. Many correct queries exist for each question.

New tools used here, in one line each:

Tool	Example	Meaning
BETWEEN	price BETWEEN 1000 AND 2000	Between the two values, including both ends
IN	status IN ('paid', 'shipped')	Equal to any value in the list
LIKE / ILIKE	name ILIKE '%usb%'	Matches a pattern; % means any characters; ILIKE ignores case
DISTINCT	SELECT DISTINCT category	Remove duplicate rows from the result
CASE	CASE WHEN price < 2000 THEN 'budget' ELSE 'other' END	Choose a value by condition

The questions

1. List the products under 2,000, cheapest first, with their prices.

   Show answer

   SELECT name, price FROM products WHERE price < 2000 ORDER BY price;

         name       |  price
   -----------------+---------
    Cable organiser |  499.00
    Wireless mouse  | 1200.00
    Laptop stand    | 1899.00

   I could explain this without looking

2. Which customers joined between 1 April and 30 June 2025, in the order they joined?

   Show answer

   SELECT name, joined_on FROM customers
   WHERE joined_on BETWEEN '2025-04-01' AND '2025-06-30'
   ORDER BY joined_on;

        name      | joined_on
   ---------------+------------
    Elena Petrova | 2025-04-15
    Farhan Ali    | 2025-05-01
    Grace Kim     | 2025-06-22

   I could explain this without looking

3. What product categories does the shop sell? Each category once, alphabetically.

   Show answer

   SELECT DISTINCT category FROM products ORDER BY category;

   Six rows: Accessories, Audio, Displays, Furniture, Storage, Video.

   I could explain this without looking

4. Show the five most recent orders that are paid or shipped.

   Show answer

   SELECT id, customer_id, status, ordered_at FROM orders
   WHERE status IN ('paid', 'shipped')
   ORDER BY ordered_at DESC
   LIMIT 5;

    id  | customer_id | status  |       ordered_at
   -----+-------------+---------+------------------------
    112 |           6 | shipped | 2025-09-09 05:00:00+00
    110 |           3 | shipped | 2025-08-02 05:25:00+00
    109 |           7 | shipped | 2025-07-19 11:10:00+00
    108 |           1 | paid    | 2025-07-03 06:00:00+00
    107 |           6 | paid    | 2025-06-14 11:45:00+00

   Times are shown in UTC; the practice database sets that so everyone sees the same values.

   I could explain this without looking

5. Find the products whose name mentions USB or SSD, in any capitalization.

   Show answer

   SELECT name FROM products WHERE name ILIKE '%usb%' OR name ILIKE '%ssd%';

   Two rows: USB-C hub and Portable SSD 1TB.

   I could explain this without looking

6. Label every product as budget (under 2,000), mid-range (under 10,000) or premium, cheapest first.

   Show answer

   SELECT name, price,
          CASE WHEN price < 2000  THEN 'budget'
               WHEN price < 10000 THEN 'mid-range'
               ELSE 'premium' END AS band
   FROM products
   ORDER BY price;

   Three budget, five mid-range, three premium. CASE takes the first WHEN that is true, so a price of 1,500 is “budget” even though it is also under 10,000.

   I could explain this without looking

7. Which customers do we have no city for?

   Show answer

   SELECT name FROM customers WHERE city IS NULL;

   Elena Petrova and Hugo Martin. WHERE city = NULL returns nothing, because the comparison is unknown for every row.

   I could explain this without looking

8. List every customer who does not live in Pune, including those whose city we do not know.

   Show answer

   SELECT name, city FROM customers WHERE city IS DISTINCT FROM 'Pune' ORDER BY name;

   Six rows. WHERE city <> 'Pune' would return only four, missing Elena and Hugo.

   I could explain this without looking

If you got stuck

Two mistakes cause most wrong answers on this page:

• Forgetting ORDER BY, so the rows are right but the order is not, or LIMIT picks the wrong ones.
• Comparing with NULL using = or <>, which silently drops the rows whose value is unknown.