Cacher is the code snippet organizer for pro developers

\!h Example - creates a column the specifies the 'mood' of the film depending on its genre
SELECT name,
	CASE
  	WHEN genre = 'romance' THEN 'fun'  -- all romance and comedy films are 'fun'
    WHEN genre = 'comedy' THEN 'fun'
    ELSE 'serious' -- all the other genres a are 'serious'
  END AS 'mood' -- the results of the above go into a new column called 'mood'
FROM movies;

\!h Example 2 - column called elevation_tier which returns 'Low', 'Medium' or 'High' depending on the elevation in the table
SELECT
    CASE
        WHEN elevation < 250 THEN 'Low' -- if lower than 250
        WHEN elevation BETWEEN 250 AND 1749 THEN 'Medium' -- if between 250 and 1749
        WHEN elevation >= 1750 THEN 'High' -- if greater than or equal to 1750
        ELSE 'Unknown'
    END AS elevation_tier
    , COUNT(*)
FROM airports
GROUP BY 1;

\!h COUNT(CASE WHEN) - number of low elevation (<1000ft) airports by state
SELECT state, 
    COUNT(CASE WHEN elevation < 1000 THEN 1 ELSE NULL END) 
as count_low_elevation_aiports 
FROM airports 
GROUP BY state;