Advanced SQL

• CASE can return different values depending on conditions
• CASE usually appears in SELECT
• It is comparable to an if/else construct in other programming languages

SELECT CASE
         WHEN (condition_1) THEN 'String_1'
         WHEN (condition_2) THEN 'String_2'
         WHEN (condition_3) THEN 'String_3'
         ELSE 'String_4'
       END
FROM (Table_1)

• Each WHEN tests a condition; the following THEN provides the value if that condition is TRUE
• ELSE provides a value if none of the conditions is TRUE
• A CASE expression ends with END

SELECT CASE
       WHEN vend_state = 'OH' THEN 'Ohio'
       WHEN vend_state = 'MI' THEN 'Michigan'
       WHEN vend_state = 'CA' THEN 'California'
       WHEN vend_state = 'NY' THEN 'New York'
       ELSE 'Onbekend'
       END AS 'State',
       COUNT(*) AS 'AantalLeveranciers'
FROM Vendors
GROUP BY 1
ORDER BY 1;