Answer more question in module 3

1 files changed, 38 insertions, 4 deletions

diff --git a/assignments/final/run.sql b/assignments/final/run.sql
index acdf15d..131a6b9 100644
--- a/assignments/final/run.sql
+++ b/assignments/final/run.sql
@@ -214,6 +214,7 @@
 \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;
@@ -263,13 +264,25 @@ CREATE TABLE Treatments(
 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', 10000000);
-INSERT INTO Physicians VALUES (2, 'dr. allison', '555-555-0001', 'head aches', 10000000);
-INSERT INTO Physicians VALUES (3, 'dr. didi', '555-555-0002', 'foot pain', 10000000);
-INSERT INTO Physicians VALUES (4, 'dr. nini', '555-555-0003', 'back pain', 10000000);
+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);
@@ -282,3 +295,24 @@ 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);