Guida all'istruzione SQL CASE

CASE
  WHEN condition THEN result
  [WHEN condition THEN result ...]
  [ELSE result]
END

SELECT
  CASE
    WHEN 72 >= 90 THEN 'Hot'
    WHEN 72 >= 60 THEN 'Mild'
    ELSE 'Cold'
  END;
-- Output: Mild

SELECT
  CASE 'amber'
    WHEN 'green' THEN 'Go'
    WHEN 'amber' THEN 'Slow down'
    WHEN 'red' THEN 'Stop'
    ELSE 'Unknown'
  END;
-- Output: Slow down

-- Input: orders
-- order_id | amount
-- ---------+--------
-- 1001     | 45.00
-- 1002     | 100.00
-- 1003     | 349.99
-- 1004     | 1250.00

SELECT
  order_id,
  amount,
  CASE
    WHEN amount < 100 THEN 'Small'
    WHEN amount < 500 THEN 'Medium'
    ELSE 'Large'
  END AS order_size
FROM orders;

-- Output:
-- order_id | amount  | order_size
-- ---------+---------+-----------
-- 1001     | 45.00   | Small
-- 1002     | 100.00  | Medium
-- 1003     | 349.99  | Medium
-- 1004     | 1250.00 | Large

SELECT
  CASE
    WHEN 45 < 500 THEN 'Medium'
    WHEN 45 < 100 THEN 'Small'  -- can never win
    ELSE 'Large'
  END;
-- Output: Medium

SELECT CASE WHEN 72 >= 90 THEN 'Hot' END;
-- Output: NULL

SELECT CASE NULL WHEN NULL THEN 'matched' ELSE 'no match' END;
-- Output: no match

SELECT CASE WHEN NULL IS NULL THEN 'matched' ELSE 'no match' END;
-- Output: matched

-- Input: products
-- name         | stock
-- -------------+------
-- Desk Lamp    | 12
-- Office Chair | 0

SELECT name, CASE WHEN stock > 0 THEN stock ELSE 'Sold out' END AS stock
FROM products;
-- PostgreSQL: ERROR: invalid input syntax for type integer: "Sold out"
-- SQL Server: Conversion failed when converting the varchar value 'Sold out' to data type int.

-- (in MySQL, cast with CAST(stock AS CHAR))
SELECT
  name,
  CASE WHEN stock > 0 THEN CAST(stock AS VARCHAR(10)) ELSE 'Sold out' END AS stock
FROM products;

-- Output:
-- name         | stock
-- -------------+---------
-- Desk Lamp    | 12
-- Office Chair | Sold out

-- MySQL
SELECT IF(72 >= 60, 'Mild', 'Cold');
-- Output: Mild

-- SQL Server and SQLite
SELECT IIF(72 >= 60, 'Mild', 'Cold');
-- Output: Mild

-- Input: products
-- product_name     | category    | price
-- -----------------+-------------+--------
-- Laptop Pro 14    | Electronics | 1499.00
-- Wireless Earbuds | Electronics | 129.00
-- Leather Jacket   | Clothing    | 349.00
-- Cotton T-Shirt   | Clothing    | 19.99
-- Coffee Mug       | Kitchen     | 12.50

SELECT
  product_name,
  CASE category
    WHEN 'Electronics' THEN
      CASE
        WHEN price > 1000 THEN 'Premium Electronics'
        ELSE 'Standard Electronics'
      END
    WHEN 'Clothing' THEN
      CASE
        WHEN price > 200 THEN 'Designer Clothing'
        ELSE 'Regular Clothing'
      END
    ELSE 'Other Products'
  END AS product_class
FROM products;

-- Output:
-- product_name     | product_class
-- -----------------+---------------------
-- Laptop Pro 14    | Premium Electronics
-- Wireless Earbuds | Standard Electronics
-- Leather Jacket   | Designer Clothing
-- Cotton T-Shirt   | Regular Clothing
-- Coffee Mug       | Other Products

-- Input: customers
-- id | customer_name | tier
-- ---+---------------+-------
-- 1  | Maya Brooks   | gold
-- 2  | Leo Martin    | silver
-- 3  | Ava Kim       | bronze
-- 4  | Noah Price    | NULL

-- Input: orders
-- id  | customer_id | order_total
-- ----+-------------+------------
-- 101 | 1           | 200.00
-- 102 | 2           | 150.00
-- 103 | 3           | 80.00
-- 104 | 4           | 60.00

SELECT
  customer_name,
  order_total,
  ROUND(order_total * CASE tier
    WHEN 'gold' THEN 0.15
    WHEN 'silver' THEN 0.10
    WHEN 'bronze' THEN 0.05
    ELSE 0
  END, 2) AS discount
FROM customers c
JOIN orders o ON c.id = o.customer_id
ORDER BY o.id;

-- Output:
-- customer_name | order_total | discount
-- --------------+-------------+---------
-- Maya Brooks   | 200.00      | 30.00
-- Leo Martin    | 150.00      | 15.00
-- Ava Kim       | 80.00       | 4.00
-- Noah Price    | 60.00       | 0.00

-- Input: sales
-- product_category | sale_date  | amount
-- -----------------+------------+-------
-- Books            | 2026-01-12 | 40.00
-- Games            | 2026-01-20 | 60.00
-- Books            | 2026-02-03 | 25.00
-- Books            | 2026-03-15 | 30.00
-- Games            | 2026-03-28 | 75.00

SELECT
  product_category,
  SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 1 THEN amount ELSE 0 END) AS jan_sales,
  SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 2 THEN amount ELSE 0 END) AS feb_sales,
  SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 3 THEN amount ELSE 0 END) AS mar_sales
FROM sales
GROUP BY product_category
ORDER BY product_category;

-- Output:
-- product_category | jan_sales | feb_sales | mar_sales
-- -----------------+-----------+-----------+----------
-- Books            | 40.00     | 25.00     | 30.00
-- Games            | 60.00     | 0         | 75.00

-- Input: users
-- id | middle_name
-- ---+------------
-- 1  | Rose
-- 2  |
-- 3  | NULL

SELECT
  id,
  CASE
    WHEN middle_name IS NULL THEN 'Missing'
    WHEN middle_name = '' THEN 'Empty'
    ELSE middle_name
  END AS middle_name
FROM users;

-- Output:
-- id | middle_name
-- ---+------------
-- 1  | Rose
-- 2  | Empty
-- 3  | Missing

-- Input: products
-- product_name | category
-- -------------+------------
-- Wool Scarf   | Sale
-- Desk Lamp    | Featured
-- Bike Helmet  | Clearance
-- Smart Watch  | New Arrival
-- Air Fryer    | Featured

SELECT product_name, category
FROM products
ORDER BY
  CASE category
    WHEN 'Featured' THEN 1
    WHEN 'New Arrival' THEN 2
    WHEN 'Sale' THEN 3
    ELSE 4
  END,
  product_name;

-- Output:
-- product_name | category
-- -------------+------------
-- Air Fryer    | Featured
-- Desk Lamp    | Featured
-- Smart Watch  | New Arrival
-- Wool Scarf   | Sale
-- Bike Helmet  | Clearance