Exercice 3 chapitre 8 sql pour Postgres

CREATE SCHEMA IF NOT EXISTS chapter8;

SET SCHEMA 'chapter8';
BEGIN;

DROP TABLE IF EXISTS EMPLOYEE;
DROP TABLE IF EXISTS CUSTOMER;
DROP TABLE IF EXISTS PERSON;
DROP TABLE IF EXISTS ID_GENERATION;

CREATE TABLE PERSON
   (
      ID serial NOT NULL PRIMARY KEY,
      FIRSTNAME VARCHAR(50) NOT NULL,
      LASTNAME VARCHAR(50)
   );


CREATE TABLE CUSTOMER
   (
      PERSON_ID BIGINT NOT NULL PRIMARY KEY REFERENCES PERSON(ID),
      STREET VARCHAR(50),
      CITY VARCHAR(50),
      STATE VARCHAR(50),
      ZIP VARCHAR(50)
   );


CREATE TABLE EMPLOYEE
   (
      CUSTOMER_ID BIGINT NOT NULL PRIMARY KEY REFERENCES CUSTOMER(PERSON_ID),
      EMPLOYEEID BIGINT
   );

DELETE FROM EMPLOYEE;
DELETE FROM CUSTOMER;
DELETE FROM PERSON;

INSERT INTO PERSON(ID,LASTNAME,FIRSTNAME) VALUES(-1,'Smith','John');
INSERT INTO PERSON(ID,LASTNAME,FIRSTNAME) VALUES(-2,'Duck','Donald');
INSERT INTO PERSON(ID,LASTNAME,FIRSTNAME) VALUES(-3,'Mouse','Mickey');
INSERT INTO PERSON(ID,LASTNAME,FIRSTNAME) VALUES(-4,'Lynch','David');
INSERT INTO PERSON(ID,LASTNAME,FIRSTNAME) VALUES(-5,'The Builder','Bob');
INSERT INTO CUSTOMER(PERSON_ID,STREET,CITY,STATE,ZIP) VALUES(-4,'Mulholland Drive','Hollywood','California','90210');
INSERT INTO CUSTOMER(PERSON_ID,STREET,CITY,STATE,ZIP) VALUES(-5,'Cartoon road','Disneyland','Florida','88800');
INSERT INTO EMPLOYEE(CUSTOMER_ID,EMPLOYEEID) VALUES(-5,999);

COMMIT;