Just giving credit where credit is due: these are my notes from A udemy course I am taking

CASE

Alot like the if/else: Only execute if conditions are met. CASE evaluates before WHEN

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result3
END
Example:
SELECT a, CASE WHEN a=1 THEN ‘one’
WHEN a=2 THEN ‘two’
ELSE ‘other
END
FROM TEST
Gets you

a	CASE
1	one
2	two

SELECT customer_id, CASE customer_id WHEN 2 THEN “slot 2” END AS extra_column_name FROM customer
This says when the customer_id == 2 then in the new column “extra_column_name” that column’s value should be “slot 2”

See how many items cost .99 to rent
SELECT SUM(CASE rental_rate WHEN .99 THEN 1 ELSE 0 END) FROM film

COALESCE

Accpets however many arguments, it returns the 1st argument that isn’t NULL. If everything is NULL it returns NULL. These are often column names

So for the table below:
SELECT item, (price -COALESCE(discount,0)) as final FROM table
So this if discount has a value of NULL like in the table below it will treat that NULL as a 0

CAST

Convert one data type into another (within reason)
SELECT CAST(‘5’ AS INTEGER)
SELECT ‘5’::INTGER

NULLIF

Takes 2 inputs and returns NULL if both are equal: else it returns the 1st argument passed
NULLIF(arg1,arg2)
NULLIF(10,10): Returns NULL