MySQL string, number and date functions, COALESCE and CASE

Answers — Functions and CASE

1. Easy. Show every customer name in capital letters. Target: 6 rows: ASHA, BILAL, CHARU, DEV, ESHA, FARAH

   SELECT UPPER(customer_name) AS name_upper FROM customers ORDER BY customer_id;

2. Easy. Show each product name and how many characters it has. Target: 6 rows: Notebook 8, Pen 3, Desk lamp 9, Mug 3, Keyboard 8, Planner 7

   SELECT product_name, CHAR_LENGTH(product_name) AS name_length FROM products ORDER BY product_id;

3. Easy. Make a label like Asha (Pune) for every customer. Target: 6 rows: Asha (Pune) … Farah (Jaipur)

   SELECT CONCAT(customer_name, ' (', city, ')') AS label FROM customers ORDER BY customer_id;

4. Easy. Show each product's price including 18% GST, rounded to 2 decimals. Target: 6 rows: 118.00, 23.60, 944.00, 295.00, 1416.00, 354.00

   SELECT product_name, ROUND(price * 1.18, 2) AS price_with_gst FROM products ORDER BY product_id;

5. Easy. Show the year and month number of every order. Target: 8 rows; all 2026; months 1, 1, 2, 2, 2, 3, 3, 3

   SELECT order_id, YEAR(order_date) AS yr, MONTH(order_date) AS mon FROM orders ORDER BY order_id;

6. Medium. Show every customer's email, or 'not given' when it is missing, using IFNULL. Target: 6 rows; Charu shows not given

   SELECT customer_name, IFNULL(email, 'not given') AS email FROM customers ORDER BY customer_id;

7. Medium. Label products budget (under 100), mid (100 to 799) or premium (800 and above). Target: Notebook mid, Pen budget, Desk lamp premium, Mug mid, Keyboard premium, Planner mid

   SELECT product_name, CASE WHEN price >= 800 THEN 'premium' WHEN price >= 100 THEN 'mid' ELSE 'budget' END AS band FROM products ORDER BY product_id;

8. Medium. Show the email domain (the part after @) for every customer. Target: 6 rows; five show example.test, Charu shows NULL

   SELECT customer_name, SUBSTRING_INDEX(email, '@', -1) AS domain FROM customers ORDER BY customer_id;

9. Medium. Show how many days each order was placed before 2026-03-31. Target: 8 rows; 101 is 85 days, 108 is 20 days

   SELECT order_id, DATEDIFF('2026-03-31', order_date) AS days_before FROM orders ORDER BY order_id;

10. Medium. Show customer-friendly status text: completed → Delivered, cancelled → Cancelled, pending → Awaiting payment. Target: 8 rows; 104 Cancelled, 107 Awaiting payment, the rest Delivered

    SELECT order_id, CASE status WHEN 'completed' THEN 'Delivered' WHEN 'cancelled' THEN 'Cancelled' WHEN 'pending' THEN 'Awaiting payment' ELSE status END AS status_text FROM orders ORDER BY order_id;

11. Medium. Show the weekday name of every order date. Target: 8 rows; 101 (2026-01-05) is Monday

    SELECT order_id, order_date, DAYNAME(order_date) AS weekday FROM orders ORDER BY order_id;

12. Hard. Line totals with a bulk discount: lines with quantity 5 or more get 10% off. Round to 2 decimals. Target: 14 rows; order 101 Pen 100 → 90.00, order 103 Pen 200 → 180.00, every other line full price

    SELECT order_id, product_id, quantity * unit_price AS gross, ROUND(quantity * unit_price * CASE WHEN quantity >= 5 THEN 0.9 ELSE 1 END, 2) AS net FROM order_items ORDER BY order_id, product_id;

13. Hard. Show 'Contact: ' followed by the email for every customer, so that no row is NULL. Target: 6 rows; Charu shows Contact: none

    SELECT customer_name, CONCAT('Contact: ', COALESCE(email, 'none')) AS contact FROM customers ORDER BY customer_id;

14. Hard. List products with Electronics first, then all other products by price, cheapest first. Target: Desk lamp, Keyboard, then Pen, Notebook, Mug, Planner

    SELECT product_name, category, price FROM products ORDER BY CASE WHEN category = 'Electronics' THEN 0 ELSE 1 END, price;

15. Hard. Mask emails for a screenshot: first letter, then ***, then @ and the domain. Target: 6 rows; Asha → a***@example.test; Charu → NULL

    SELECT customer_name, CONCAT(LEFT(email, 1), '***@', SUBSTRING_INDEX(email, '@', -1)) AS masked_email FROM customers ORDER BY customer_id;

16. Professional. Customer contact sheet: name in capitals, city, email or 'no email', and action = 'ask for email' when missing else 'ok'. Missing emails first, then by name. Target: 6 rows; CHARU first with no email / ask for email, then ASHA, BILAL, DEV, ESHA, FARAH

    SELECT UPPER(customer_name) AS customer, city, COALESCE(email, 'no email') AS email, CASE WHEN email IS NULL THEN 'ask for email' ELSE 'ok' END AS action FROM customers ORDER BY CASE WHEN email IS NULL THEN 0 ELSE 1 END, customer_name;

17. Professional. Invoice text for order 101: one line per item like 'Product 1 x2 = 200.00'. Target: 2 rows: Product 1 x2 = 200.00; Product 2 x5 = 100.00

    SELECT CONCAT('Product ', product_id, ' x', quantity, ' = ', FORMAT(quantity * unit_price, 2)) AS invoice_line FROM order_items WHERE order_id = 101 ORDER BY product_id;