Answer more question in module 3

2 files changed, 96 insertions, 5 deletions

diff --git a/assignments/final/README.md b/assignments/final/README.md
index c8f9d58..0504404 100644
--- a/assignments/final/README.md
+++ b/assignments/final/README.md
@@ -532,36 +532,93 @@ WHERE nurse_charge_id = nid and certificate_type like ‘RN’
 GROUP BY cid;
 ```
 
-State which of the following queries and updates would be allowed in this view. If a particular query or update would be allowed, show what the corresponding query or update on the base relations would look like, and give its result when applied to the database. (5 marks)
+This is incorrect. It is only including nurses who are in charge of the care centre.
+
+```sql
+CREATE 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;
+```
+
+State which of the following queries and updates would be allowed in this view.
+If a particular query or update would be allowed,
+show what the corresponding query or update on the base relations would look like,
+and give its result when applied to the database.
 
 Q1
 ```sql
 SELECT * FROM NURSE_SUMMARY;
 ```
+
+This query is allowed.
+
+```sql
+ d | c | total_s |     average_s
+---+---+---------+--------------------
+ 1 | 4 |  250050 | 62512.500000000000
+(1 row)
+```
+
 Q2.
 ```sql
 SELECT D, C
 FROM NURSE_SUMMARY
 WHERE TOTAL_S > 100000;
 ```
+
+This query is allowed.
+
+```sql
+ d | c
+ ---+---
+ 1 | 4
+(1 row)
+
+```
+
 Q3.
 ```sql
 SELECT D, AVERAGE_S
 FROM NURSE_SUMMARY
 WHERE C > (SELECT C FROM NURSE_SUMMARY WHERE D=4);
 ```
+
+This query is allowed.
+
+```sql
+ d | average_s
+---+-----------
+(0 rows)
+```
+
 Q4.
 ```sql
 UPDATE  NURSE_SUMMARY
 SET D=3
 WHERE D=4;
 ```
+
+This statement is not allowed. Please use:
+
+```sql
+UPDATE Nurses SET nid = 3 WHERE nid = 4;
+```
+
 Q5.
 ```sql
 DELETE FROM NURSE_SUMMARY
 WHERE C > 4;
 ```
 
+This statement is not allowed. Please use:
+
+```sql
+DELETE FROM Nurses WHERE nid IN (SELECT D FROM NURSE_SUMMARY WHERE C > 4);
+```
+
 **Use Oracle Forms, or write a program (using PL/SQL and/or Java), to display the following report for a given patient (the user will only enter the patient number). (10 marks)**
 
 ```text
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);