MySQL IN, BETWEEN, LIKE, DISTINCT and LIMIT with examples

Answers — IN, BETWEEN, LIKE, DISTINCT, LIMIT

1. Easy. List the products in the Stationery or Home category using IN. Target: 4 rows: Notebook, Pen, Mug, Planner

   SELECT product_name, category FROM products WHERE category IN ('Stationery', 'Home') ORDER BY product_id;

2. Easy. List each city that has a customer, once, A to Z. Target: 4 rows: Delhi, Jaipur, Mumbai, Pune

   SELECT DISTINCT city FROM customers ORDER BY city;

3. Easy. Find products priced from 100 to 300 inclusive, cheapest first. Target: 3 rows: Notebook 100.00, Mug 250.00, Planner 300.00

   SELECT product_name, price FROM products WHERE price BETWEEN 100 AND 300 ORDER BY price;

4. Easy. Find customers whose name starts with E. Target: 1 row: Esha

   SELECT customer_name FROM customers WHERE customer_name LIKE 'E%';

5. Easy. Show the three cheapest products. Target: 3 rows: Pen 20.00, Notebook 100.00, Mug 250.00

   SELECT product_name, price FROM products ORDER BY price LIMIT 3;

6. Medium. List February 2026 orders using BETWEEN on order_date. Target: 3 rows: 103, 104, 105

   SELECT order_id, order_date FROM orders WHERE order_date BETWEEN '2026-02-01' AND '2026-02-28' ORDER BY order_id;

7. Medium. Find products whose name contains the letter o anywhere. Target: 2 rows: Notebook, Keyboard

   SELECT product_name FROM products WHERE product_name LIKE '%o%' ORDER BY product_id;

8. Medium. List customers who are not in Pune or Mumbai. Target: 2 rows: Dev (Delhi), Farah (Jaipur)

   SELECT customer_name, city FROM customers WHERE city NOT IN ('Pune', 'Mumbai') ORDER BY customer_id;

9. Medium. List each order status once, A to Z. Target: 3 rows: cancelled, completed, pending

   SELECT DISTINCT status FROM orders ORDER BY status;

10. Medium. Show page 2 of the product list, 2 products per page, in product_id order. Target: 2 rows: Desk lamp, Mug

    SELECT product_id, product_name FROM products ORDER BY product_id LIMIT 2 OFFSET 2;

11. Medium. Find product names that are exactly three characters long using LIKE. Target: 2 rows: Pen, Mug

    SELECT product_name FROM products WHERE product_name LIKE '___' ORDER BY product_id;

12. Hard. Find customers whose email does NOT end in .test — including customers with no email. Target: 1 row: Charu (email NULL)

    SELECT customer_name, email FROM customers WHERE email NOT LIKE '%.test' OR email IS NULL;

13. Hard. List each customer_id that appears in orders, once. Target: 5 rows: 1, 2, 3, 4, 5 (customer 6 never ordered)

    SELECT DISTINCT customer_id FROM orders ORDER BY customer_id;

14. Hard. Find the single most expensive Stationery product. Target: 1 row: Planner 300.00

    SELECT product_name, price FROM products WHERE category = 'Stationery' ORDER BY price DESC LIMIT 1;

15. Hard. List orders from 2026-01-08 to 2026-03-02 inclusive that were not cancelled. Target: 4 rows: 102, 103, 105, 106

    SELECT order_id, order_date, status FROM orders WHERE order_date BETWEEN '2026-01-08' AND '2026-03-02' AND status <> 'cancelled' ORDER BY order_id;

16. Professional. A support agent types “sh” into a search box. Return customers whose name or email contains it. Target: 2 rows: Asha, Esha

    SELECT customer_id, customer_name, email FROM customers WHERE customer_name LIKE '%sh%' OR email LIKE '%sh%' ORDER BY customer_id;

17. Professional. Price-list page 1: non-Electronics products priced 100–800, names A to Z, 2 per page. Target: 2 rows: Mug 250.00, Notebook 100.00

    SELECT product_name, price FROM products WHERE category <> 'Electronics' AND price BETWEEN 100 AND 800 ORDER BY product_name LIMIT 2;