postgres tutorial 7 case, coalesce, nullif, cast

mass if the rental rate is 0.99
economic if the rental rate is 1.99
luxury if the rental rate is 4.99
the number of films that belongs mass, economic or luxury price

SELECT
 SUM (
 CASE
 WHEN rental_rate = 0.99 THEN
 1
 ELSE
 0
 END
 ) AS "Mass",
 SUM (
 CASE
 WHEN rental_rate = 2.99 THEN
 1
 ELSE
 0
 END
 ) AS "Economic",
 SUM (
 CASE
 WHEN rental_rate = 4.99 THEN
 1
 ELSE
 0
 END
 ) AS "Luxury"
FROM
 film;

 ---------------------------
 COALESCE function returns the first argument that is not null.

 SELECT
 product,
 (price - COALESCE(discount,0)) AS net_price
FROM
 items;

 =

 SELECT
 product,
 (
 price - CASE
 WHEN discount IS NULL THEN
 0
 ELSE
 discount
 END
 ) AS net_price
FROM
 items;

 ------------------------------
 The NULLIF function returns a null value if argument_1 equals to argument_2, otherwise it returns argument_1.

SELECT
 NULLIF (1, 1); -- return NULL

SELECT
 NULLIF (1, 0); -- return 1

SELECT
 NULLIF ('A', 'B'); -- return A

 ---------------------------
SELECT
 CAST ('100' AS INTEGER);

SELECT
 CAST ('2015-01-01' AS DATE);

SELECT
 CAST ('10.2' AS DOUBLE PRECISION);

 SELECT
 '100'::INTEGER;

SELECT
 '01-OCT-2015'::DATE;