· 4 years ago · Mar 30, 2021, 07:36 PM
1-- 1. For each instructor, compute
2-- a) the total number of courses he/she has taught.
3SELECT name, COUNT(*)
4FROM instructor
5JOIN teaches ON teaches.ID = instructor.ID
6GROUP BY name;
7-- b) the total number of credits based on the courses he/she has taught.
8SELECT name, SUM(credits)
9FROM instructor
10JOIN teaches ON teaches.ID = instructor.ID
11JOIN course ON teaches.course_id = course.course_id
12GROUP BY name;
13
14-- 2. Create tuples in the section relation for Spring 2021 by duplicating all
15-- of the fields for the class information for the courses in the Spring 2020,
16-- changing just the semester and year fields.
17INSERT INTO section(course_id, sec_id, semester, year, building, room_number, time_slot_id)
18SELECT course_id,
19 sec_id,
20 semester,
21 2021,
22 building,
23 room_number,
24 time_slot_id
25FROM section
26WHERE semester = 'Spring' AND year = 2020;
27
28-- 3. In teaches relation, assign Dr. Katz to teach all of the Computer Science
29-- classes in Spring 2021.
30INSERT INTO teaches(id, course_id, sec_id, semester, year)
31SELECT (SELECT id FROM instructor WHERE name = 'Katz'),
32 course_id,
33 sec_id,
34 semester,
35 year
36FROM section
37WHERE semester = 'Spring' AND year = 2021;
38
39-- 4. In takes relation, add all of the CS students who have not taken CS-347
40-- to the class CS-347 of the new semester Spring 2021.
41-- Let the grade field be null.
42
43-- NB: We have to first insert a row for CS-347 Spring 2021 into the sections table
44-- (It wasn't added in stage 2 because there was no CS-347 in Spring 2020.)
45INSERT INTO section values ('CS-347', 1, 'Spring', 2021, 'Taylor', 3128, 'A');
46
47INSERT INTO takes(id, course_id, sec_id, semester, year, grade)
48SELECT student.id,
49 'CS-347',
50 1,
51 'Spring',
52 2021,
53 null
54FROM student
55LEFT JOIN takes ON student.id = takes.id AND takes.course_id = 'CS-347'
56WHERE takes.id IS NULL;
57
58-- 5. Remove all classes that have neither students enrolled in them nor an
59-- instructor assigned to them.
60
61DELETE FROM section s
62WHERE NOT EXISTS (
63 SELECT te.course_id, te.sec_id, te.semester, te.year
64 FROM teaches te
65 WHERE te.course_id = s.course_id
66 AND te.sec_id = s.sec_id
67 AND te.semester = s.semester
68 AND te.year = s.year
69) AND NOT EXISTS (
70 SELECT ta.course_id, ta.sec_id, ta.semester, ta.year
71 FROM takes ta
72 WHERE ta.course_id = s.course_id
73 AND ta.sec_id = s.sec_id
74 AND ta.semester = s.semester
75 AND ta.year = s.year
76);
77-- (NB: There are no such classes in the example data)
78
79-- 6. We want to adjust the sec_id for classes, as follows. If the class was held in 2019, we
80-- want to add 1000 to it, if it was held in 2020, we want to add 2000 to it, and if it was held in
81-- 2021, we want to add 3000 to it. You must change the sec_id in all relations. You may use
82-- several statements to accomplish this task.
83
84-- Temporarily disable all constraints on `teaches` & `takes`:
85DECLARE
86cursor r1 is select * from user_constraints;
87BEGIN
88 FOR c1 IN r1
89 LOOP
90 IF c1.table_name in ('TEACHES', 'TAKES') THEN
91 dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' disable constraint ' || c1.constraint_name);
92 END IF;
93 END LOOP;
94END;
95/
96
97-- Update sec_id in each table:
98UPDATE section
99SET sec_id = sec_id + (year-2018)*1000;
100
101UPDATE teaches
102SET sec_id = sec_id + (year-2018)*1000;
103
104UPDATE takes
105SET sec_id = sec_id + (year-2018)*1000;
106
107-- Re-enable the constraints:
108DECLARE
109cursor r1 is select * from user_constraints;
110BEGIN
111 FOR c1 IN r1
112 LOOP
113 IF c1.table_name in ('TEACHES', 'TAKES') THEN
114 dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' enable constraint ' || c1.constraint_name);
115 END IF;
116 END LOOP;
117END;
118/
119