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

SELECT COALESCE(NULL, 2 , 1);
SELECT COALESCE(NULL, 'ab' , 'xy');
SELECT COALESCE(NULL, 'ab' , 1);

SELECT NULLIF(1, 1)
SELECT NULLIF(1, 0)
SELECT NULLIF('A', 'B')

SELECT '100'::INTEGER, '01-OCT-2015'::DATE
SELECT CAST('100' AS INTEGER), CAST('01-OCT-2015' AS DATE)

SELECT
   CAST('true' AS BOOLEAN),
   CAST('false' as BOOLEAN),
   CAST('T' as BOOLEAN),
   CAST('F' as BOOLEAN)

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

CREATE FUNCTION increment(i integer) RETURNS integer AS $$
  begin
    return i + 1;
  end;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
  begin
    return i + 2;
  end;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text) AS $$
  SELECT $1, CAST($1 AS text) || ' is text'
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION dup(in int, out f1 int, out f2 text) AS $$
  SELECT $1, CAST($1 AS text) || ' is text'
$$ LANGUAGE SQL;

CREATE FUNCTION trigger_function()
  RETURNS trigger AS

CREATE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {event [OR ...]}
   ON table_name
   [FOR [EACH] {ROW | STATEMENT}]
       EXECUTE PROCEDURE trigger_function

CREATE TABLE employees(
   id SERIAL PRIMARY KEY,
   first_name VARCHAR(40) NOT NULL,
   last_name VARCHAR(40) NOT NULL
);

CREATE TABLE employee_audits (
   id SERIAL PRIMARY KEY,
   employee_id INT NOT NULL,
   last_name VARCHAR(40) NOT NULL,
   changed_on TIMESTAMP(6) NOT NULL
)

CREATE OR REPLACE FUNCTION log_last_name_changes()
  RETURNS trigger AS
$BODY$
BEGIN
   IF NEW.last_name <> OLD.last_name THEN
       INSERT INTO employee_audits(employee_id,last_name,changed_on)
       VALUES(OLD.id,OLD.last_name,now());
   END IF;

   RETURN NEW;
END;
$BODY$

CREATE TRIGGER last_name_changes
  BEFORE UPDATE
  ON employees
  FOR EACH ROW
  EXECUTE PROCEDURE log_last_name_changes();

INSERT INTO employees (first_name, last_name)
VALUES ('John', 'Doe');

INSERT INTO employees (first_name, last_name)
VALUES ('Lily', 'Bush');

SELECT * FROM employees;

UPDATE employees SET last_name = 'Brown' WHERE ID = 2;

SELECT * FROM employees;

SELECT * FROM employee_audits;