Review Session
---------------------


TOPICS:


DML - Data Manipulation Language
DDL - Data Definition Language

    SELECT
      Basic -> Advanced


DML INSERT

    UPDATE

    DELETE

     CREATE
DDL  ALTER
     DROP


Transactions

PL/pgSQL



Transactions:

levels of isolation
begin/commit/rollback



-- T1



-- T2



----------

isolation


SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

------------



SELECT
FROM
WHERE
GROUP BY   a,b
HAVING 

UNION/UNION ALL/INTERSECT/...

SELECT
FROM
WHERE
GROUP BY
HAVING

ORDER BY
LIMIT


------------

CREATE TABLE employees (
  employee_id serial PRIMARY KEY,
  full_name VARCHAR NOT NULL,
  manager_id INT
);

INSERT INTO employees (
  employee_id,
  full_name,
  manager_id
)
VALUES
  (1, 'Michael North', NULL),
  (2, 'Megan Berry', 1),
  (3, 'Sarah Berry', 1),
  (4, 'Zoe Black', 1),
  (5, 'Tim James', 1),
  (6, 'Bella Tucker', 2),
  (7, 'Ryan Metcalfe', 2),
  (8, 'Max Mills', 2),
  (9, 'Benjamin Glover', 2),
  (10, 'Carolyn Henderson', 3),
  (11, 'Nicola Kelly', 3),
  (12, 'Alexandra Climo', 3),
  (13, 'Dominic King', 3),
  (14, 'Leonard Gray', 4),
  (15, 'Eric Rampling', 4),
  (16, 'Piers Paige', 7),
  (17, 'Ryan Henderson', 7),
  (18, 'Frank Tucker', 8),
  (19, 'Nathan Ferguson', 8),
  (20, 'Kevin Rampling', 8);


------------

DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
    REFERENCES customers(customer_id)
    ON DELETE CASCADE
);


INSERT INTO customers(customer_name)
VALUES('BlueBird Inc'),
      ('Dolphin LLC');     
     
INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1,'John Doe','(408)-111-1234','john.doe@bluebird.dev'),
      (1,'Jane Doe','(408)-111-1235','jane.doe@bluebird.dev'),
      (2,'David Wright','(408)-222-1234','david.wright@dolphin.dev');


DELETE FROM customers
WHERE customer_id = 1;





   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
    REFERENCES customers(customer_id)
    ON DELETE SET NULL


   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
    REFERENCES customers(customer_id)
    ON DELETE CASCADE

------------



CREATE TABLE a1 (id int)



-- For each baker that won either a technical or star baker, find how
--  many times they won star baker or technical

WITH wins AS (
SELECT
  t.baker
FROM
  technicals t
WHERE
  t.rank = 1

UNION ALL

SELECT
  baker
FROM
  results
WHERE
  result = 'star baker'
)

SELECT
  baker
  , count(*) as numwins
 FROM
  wins
GROUP BY
  baker
ORDER BY
  numwins DESC
;


-----------------------