path: root/assignments/final/run.sql

\echo '--- Module 2 ---'
\set ON_ERROR_STOP on

-- DROP TABLE IF EXISTS accounts CASCADE;
-- DROP TABLE IF EXISTS beds CASCADE;
-- DROP TABLE IF EXISTS care_centres CASCADE;
-- DROP TABLE IF EXISTS consumptions CASCADE;
-- DROP TABLE IF EXISTS items CASCADE;
-- DROP TABLE IF EXISTS laboratories CASCADE;
-- DROP TABLE IF EXISTS patients CASCADE;
-- DROP TABLE IF EXISTS shifts CASCADE;
-- DROP TABLE IF EXISTS staff CASCADE;
-- DROP TABLE IF EXISTS treatments CASCADE;
-- DROP TABLE IF EXISTS visits CASCADE;
-- DROP TABLE IF EXISTS volunteers CASCADE;

-- CREATE TABLE accounts (
  -- id bigint NOT NULL,
  -- name varchar NOT NULL,
  -- address varchar NOT NULL,
  -- birth_date timestamp NOT NULL,
  -- phone_number varchar,
  -- pager_number varchar,
  -- created_at timestamp NOT NULL,
  -- updated_at timestamp NOT NULL
-- );
-- CREATE SEQUENCE accounts_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-- ALTER SEQUENCE accounts_id_seq OWNED BY accounts.id;

-- CREATE TABLE beds (
  -- id bigint NOT NULL,
  -- bed_number varchar,
  -- room_number varchar,
  -- care_centre_id bigint NOT NULL,
  -- patient_id bigint,
  -- created_at timestamp NOT NULL,
  -- updated_at timestamp NOT NULL
-- );
-- CREATE SEQUENCE beds_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-- ALTER SEQUENCE beds_id_seq OWNED BY beds.id;

-- CREATE TABLE care_centres (
  -- id bigint NOT NULL,
  -- nurse_id bigint NOT NULL,
  -- name varchar,
  -- location varchar,
  -- created_at timestamp NOT NULL,
  -- updated_at timestamp NOT NULL
-- );
-- CREATE SEQUENCE care_centres_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-- ALTER SEQUENCE care_centres_id_seq OWNED BY care_centres.id;

-- CREATE TABLE consumptions (
  -- id bigint NOT NULL,
  -- patient_id bigint NOT NULL,
  -- item_id bigint NOT NULL,
  -- consumed_at timestamp NOT NULL,
  -- quantity integer NOT NULL,
  -- total_cost numeric NOT NULL,
  -- created_at timestamp NOT NULL,
  -- updated_at timestamp NOT NULL
-- );
-- CREATE SEQUENCE consumptions_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-- ALTER SEQUENCE consumptions_id_seq OWNED BY consumptions.id;

-- CREATE TABLE items (
  -- id bigint NOT NULL,
  -- sku varchar NOT NULL,
  -- description text,
  -- unit_cost numeric NOT NULL,
  -- created_at timestamp NOT NULL,
  -- updated_at timestamp NOT NULL
-- );
-- CREATE SEQUENCE items_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-- ALTER SEQUENCE items_id_seq OWNED BY items.id;

-- CREATE TABLE laboratories (
  -- id bigint NOT NULL,
  -- name varchar,
  -- location varchar,
  -- created_at timestamp NOT NULL,
  -- updated_at timestamp NOT NULL
-- );
-- CREATE SEQUENCE laboratories_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-- ALTER SEQUENCE laboratories_id_seq OWNED BY laboratories.id;

-- CREATE TABLE patients (
  -- id bigint NOT NULL,
  -- account_id bigint NOT NULL,
  -- physician_id bigint NOT NULL,
  -- referring_physician_id bigint NOT NULL,
  -- contacted_at timestamp NOT NULL,
  -- created_at timestamp NOT NULL,
  -- updated_at timestamp NOT NULL
-- );
-- CREATE SEQUENCE patients_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-- ALTER SEQUENCE patients_id_seq OWNED BY patients.id;

-- CREATE TABLE shifts (
  -- id bigint NOT NULL,
  -- care_centre_id bigint NOT NULL,
  -- employee_id bigint NOT NULL,
  -- started_at timestamp NOT NULL,
  -- ended_at timestamp without time zone,
  -- created_at timestamp NOT NULL,
  -- updated_at timestamp NOT NULL
-- );
-- CREATE SEQUENCE shifts_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-- ALTER SEQUENCE shifts_id_seq OWNED BY shifts.id;

-- CREATE TABLE staff (
  -- id bigint NOT NULL,
  -- account_id bigint NOT NULL,
  -- laboratory_id bigint,
  -- type varchar,
  -- hired_at timestamp without time zone,
  -- qualifications text[],
  -- created_at timestamp NOT NULL,
  -- updated_at timestamp NOT NULL
-- );
-- CREATE SEQUENCE staff_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-- ALTER SEQUENCE staff_id_seq OWNED BY staff.id;

-- CREATE TABLE treatments (
  -- id bigint NOT NULL,
  -- patient_id bigint NOT NULL,
  -- name varchar NOT NULL,
  -- number varchar NOT NULL,
  -- occurred_at timestamp NOT NULL,
  -- results text,
  -- created_at timestamp NOT NULL,
  -- updated_at timestamp NOT NULL
-- );
-- CREATE SEQUENCE treatments_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-- ALTER SEQUENCE treatments_id_seq OWNED BY treatments.id;

-- CREATE TABLE visits (
  -- id bigint NOT NULL,
  -- patient_id bigint NOT NULL,
  -- scheduled_at timestamp NOT NULL,
  -- comments text,
  -- created_at timestamp NOT NULL,
  -- updated_at timestamp NOT NULL
-- );
-- CREATE SEQUENCE visits_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-- ALTER SEQUENCE visits_id_seq OWNED BY visits.id;

-- CREATE TABLE volunteers (
  -- id bigint NOT NULL,
  -- account_id bigint NOT NULL,
  -- skill text NOT NULL,
  -- created_at timestamp NOT NULL,
  -- updated_at timestamp NOT NULL
-- );
-- CREATE SEQUENCE volunteers_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-- ALTER SEQUENCE volunteers_id_seq OWNED BY volunteers.id;

-- ALTER TABLE accounts ALTER COLUMN id SET DEFAULT nextval('accounts_id_seq'::regclass);
-- ALTER TABLE beds ALTER COLUMN id SET DEFAULT nextval('beds_id_seq'::regclass);
-- ALTER TABLE care_centres ALTER COLUMN id SET DEFAULT nextval('care_centres_id_seq'::regclass);
-- ALTER TABLE consumptions ALTER COLUMN id SET DEFAULT nextval('consumptions_id_seq'::regclass);
-- ALTER TABLE items ALTER COLUMN id SET DEFAULT nextval('items_id_seq'::regclass);
-- ALTER TABLE laboratories ALTER COLUMN id SET DEFAULT nextval('laboratories_id_seq'::regclass);
-- ALTER TABLE patients ALTER COLUMN id SET DEFAULT nextval('patients_id_seq'::regclass);
-- ALTER TABLE shifts ALTER COLUMN id SET DEFAULT nextval('shifts_id_seq'::regclass);
-- ALTER TABLE staff ALTER COLUMN id SET DEFAULT nextval('staff_id_seq'::regclass);
-- ALTER TABLE treatments ALTER COLUMN id SET DEFAULT nextval('treatments_id_seq'::regclass);
-- ALTER TABLE visits ALTER COLUMN id SET DEFAULT nextval('visits_id_seq'::regclass);
-- ALTER TABLE volunteers ALTER COLUMN id SET DEFAULT nextval('volunteers_id_seq'::regclass);
-- ALTER TABLE accounts ADD CONSTRAINT accounts_pkey PRIMARY KEY (id);
-- ALTER TABLE beds ADD CONSTRAINT beds_pkey PRIMARY KEY (id);
-- ALTER TABLE care_centres ADD CONSTRAINT care_centres_pkey PRIMARY KEY (id);
-- ALTER TABLE consumptions ADD CONSTRAINT consumptions_pkey PRIMARY KEY (id);
-- ALTER TABLE items ADD CONSTRAINT items_pkey PRIMARY KEY (id);
-- ALTER TABLE laboratories ADD CONSTRAINT laboratories_pkey PRIMARY KEY (id);
-- ALTER TABLE patients ADD CONSTRAINT patients_pkey PRIMARY KEY (id);
-- ALTER TABLE shifts ADD CONSTRAINT shifts_pkey PRIMARY KEY (id);
-- ALTER TABLE staff ADD CONSTRAINT staff_pkey PRIMARY KEY (id);
-- ALTER TABLE treatments ADD CONSTRAINT treatments_pkey PRIMARY KEY (id);
-- ALTER TABLE visits ADD CONSTRAINT visits_pkey PRIMARY KEY (id);
-- ALTER TABLE volunteers ADD CONSTRAINT volunteers_pkey PRIMARY KEY (id);
-- CREATE INDEX index_beds_on_care_centre_id ON beds (care_centre_id);
-- CREATE INDEX index_beds_on_patient_id ON beds (patient_id);
-- CREATE INDEX index_care_centres_on_nurse_id ON care_centres (nurse_id);
-- CREATE INDEX index_consumptions_on_item_id ON consumptions (item_id);
-- CREATE INDEX index_consumptions_on_patient_id ON consumptions (patient_id);
-- CREATE UNIQUE INDEX index_patients_on_account_id ON patients (account_id);
-- CREATE INDEX index_patients_on_physician_id ON patients (physician_id);
-- CREATE INDEX index_patients_on_referring_physician_id ON patients (referring_physician_id);
-- CREATE INDEX index_shifts_on_care_centre_id ON shifts (care_centre_id);
-- CREATE INDEX index_shifts_on_employee_id ON shifts (employee_id);
-- CREATE UNIQUE INDEX index_staff_on_account_id ON staff (account_id);
-- CREATE INDEX index_staff_on_laboratory_id ON staff (laboratory_id);
-- CREATE INDEX index_staff_on_type ON staff (type);
-- CREATE INDEX index_treatments_on_patient_id ON treatments (patient_id);
-- CREATE INDEX index_visits_on_patient_id ON visits (patient_id);
-- CREATE UNIQUE INDEX index_volunteers_on_account_id ON volunteers (account_id);
-- ALTER TABLE beds ADD CONSTRAINT care_centres_fk FOREIGN KEY (care_centre_id) REFERENCES care_centres(id);
-- ALTER TABLE beds ADD CONSTRAINT patients_fk FOREIGN KEY (patient_id) REFERENCES patients(id);
-- ALTER TABLE consumptions ADD CONSTRAINT items_fk FOREIGN KEY (item_id) REFERENCES items(id);
-- ALTER TABLE consumptions ADD CONSTRAINT patients_fk FOREIGN KEY (patient_id) REFERENCES patients(id);
-- ALTER TABLE patients ADD CONSTRAINT accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(id);
-- ALTER TABLE patients ADD CONSTRAINT staff_fk FOREIGN KEY (physician_id) REFERENCES staff(id);
-- ALTER TABLE patients ADD CONSTRAINT referring_staff_fk FOREIGN KEY (referring_physician_id) REFERENCES staff(id);
-- ALTER TABLE shifts ADD CONSTRAINT care_centres_fk FOREIGN KEY (care_centre_id) REFERENCES care_centres(id);
-- ALTER TABLE shifts ADD CONSTRAINT staff_fk FOREIGN KEY (employee_id) REFERENCES staff(id);
-- ALTER TABLE staff ADD CONSTRAINT accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(id);
-- ALTER TABLE staff ADD CONSTRAINT laboratories_fk FOREIGN KEY (laboratory_id) REFERENCES laboratories(id);
-- ALTER TABLE treatments ADD CONSTRAINT patients_fk FOREIGN KEY (patient_id) REFERENCES patients(id);
-- ALTER TABLE visits ADD CONSTRAINT patients_fk FOREIGN KEY (patient_id) REFERENCES patients(id);
-- ALTER TABLE volunteers ADD CONSTRAINT accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(id);


\echo '--- Module 3 ---'

ALTER TABLE IF EXISTS Nurses DROP CONSTRAINT care_centres_fk;
DROP VIEW IF EXISTS NURSE_SUMMARY;
DROP TABLE IF EXISTS Treatments;
DROP TABLE IF EXISTS Physicians;
DROP TABLE IF EXISTS Patients;
DROP TABLE IF EXISTS Care_centres;
DROP TABLE IF EXISTS Nurses;

CREATE TABLE Nurses(
  nid bigint primary key,
  name varchar,
  care_centre_id bigint,
  certificate_type varchar,
  telephone varchar,
  salary decimal
);

CREATE TABLE Care_centres(
  cid bigint primary key,
  name varchar,
  location text,
  nurse_charge_id bigint references Nurses(nid)
);

CREATE TABLE Patients(
  pid bigint primary key,
  name varchar,
  address text,
  telephone varchar,
  care_centre_id bigint references Care_centres(cid)
);

CREATE TABLE Physicians(
  phid bigint primary key,
  name varchar,
  pager_number varchar,
  specialization varchar,
  salary decimal
);

CREATE TABLE Treatments(
  tid bigint primary key,
  patient_id bigint references Patients(pid),
  physician_id bigint references Physicians(phid),
  treatment_name varchar,
  "date" timestamp
);

ALTER TABLE Nurses ADD CONSTRAINT care_centres_fk FOREIGN KEY (care_centre_id) REFERENCES Care_centres (cid);

INSERT INTO Care_centres VALUES(1, 'Intensive Care Unit', 'MT-M');

INSERT INTO Nurses VALUES (1, 'Sharp', 1, 'birth', '111-222-3333', 100000);
INSERT INTO Nurses VALUES (2, 'Barton', 1, 'RN', '111-222-3333', 100000);
INSERT INTO Nurses VALUES (3, 'Nightingale', 1, 'birth', '111-222-3333', 100000);
INSERT INTO Nurses VALUES (4, 'Seacole', 1, 'RN', '111-222-3333', 50000);
INSERT INTO Nurses VALUES (5, 'Dix', 1, 'birth', '111-222-3333', 100000);
INSERT INTO Nurses VALUES (6, 'Mahoney', 1, 'birth', '111-222-3333', 50000);
INSERT INTO Nurses VALUES (7, 'Wald', 1, 'birth', '111-222-3333', 100000);
INSERT INTO Nurses VALUES (8, 'Sanger', 1, 'RN', '111-222-3333', 100000);
INSERT INTO Nurses VALUES (9, 'Breckinridge', 1, 'birth', '111-222-3333', 50000);
INSERT INTO Nurses VALUES (10, 'Teresa', 1, 'birth', '111-222-3333', 0);
INSERT INTO Nurses VALUES (11, 'Bertschinger', 1, 'RN', '111-222-3333', 50);

UPDATE Care_centres SET nurse_charge_id = 1 where cid = 1;

INSERT INTO Physicians VALUES (1, 'dr. mo', '555-555-5555', 'love', 250000);
INSERT INTO Physicians VALUES (2, 'dr. allison', '555-555-0001', 'head aches', 250000);
INSERT INTO Physicians VALUES (3, 'dr. didi', '555-555-0002', 'foot pain', 200000);
INSERT INTO Physicians VALUES (4, 'dr. nini', '555-555-0003', 'back pain', 200000);

INSERT INTO Patients VALUES (1, 'moe sislack', '123 street nw', '777-777-7777', 1);
INSERT INTO Patients VALUES (2, 'mose allison', '234 street nw', '888-888-8888', 1);
INSERT INTO Patients VALUES (3, 'didi kong', '345 street nw', '999-999-9999', 1);

INSERT INTO Treatments VALUES(1, 1, 1, 'Surgery', '2020-03-01');

SELECT * FROM Care_centres;
SELECT * FROM Nurses;
SELECT * FROM Physicians;
SELECT * FROM Patients;
SELECT * FROM Treatments;

SELECT cid, COUNT(*), SUM(salary), AVG(salary)
FROM Care_centres, Nurses
WHERE nurse_charge_id = nid
AND certificate_type LIKE 'RN'
GROUP BY cid;

CREATE OR REPLACE VIEW NURSE_SUMMARY(D, C, TOTAL_S, AVERAGE_S)
AS SELECT c.cid, COUNT(*), SUM(n.salary), AVG(n.salary)
FROM Nurses n
INNER JOIN Care_Centres c ON c.cid = n.care_centre_id
AND n.certificate_type LIKE 'RN'
GROUP BY c.cid;

SELECT * FROM NURSE_SUMMARY;

SELECT D, C FROM NURSE_SUMMARY WHERE TOTAL_S > 100000;

SELECT D, AVERAGE_S
FROM NURSE_SUMMARY
WHERE C > (SELECT C FROM NURSE_SUMMARY WHERE D=4);