· 4 years ago · Feb 07, 2021, 04:34 PM
1-- except <- set difference
2
3-- 1. Show identifiers and names of courses held on the 1st year of study.
4-- Order them alphabetically according to the names of courses.
5select cno, cname
6from courses
7where studyear = 1
8order by cname;
9
10-- 2. How many students have their names starting from letter J?
11select count(*)
12from students
13where sname like 'J%';
14
15-- 3. For each city, give the numer of students that come from this city.
16select city, count(*)
17from students
18group by city;
19
20-- 4. For each teacher (id, title and name), give courses (ids, names) that he/she conducted.
21select tsc.tno, t.title, t.tname, tsc.cno, c.cname
22from tsc
23inner join teachers as t
24 on t.tno = tsc.tno
25inner join courses as c
26 on c.cno = tsc.cno
27group by (tsc.tno, t.title, t.tname, tsc.cno, c.cname)
28order by (tsc.tno, tsc.cno);
29
30-- 5. Show ids of teachers that didnot have classes on the 1st year of study. (IT'S A TRAP)
31(
32select tno from tsc
33)
34except
35(
36select tno from tsc
37 inner join courses as c
38 on c.cno = tsc.cno and c.studyear = 1
39);
40
41-- 6. On which year of study there were most courses?
42select c.studyear, count(distinct(tsc.cno)) as "courses_count"
43from courses as c
44inner join tsc
45 on tsc.cno = c.cno
46group by c.studyear
47order by "courses_count" desc limit 1;
48
49-- 7. On which year of study there was the greatest average grade?
50select c.studyear, avg(tsc.grade) as "avg_grade"
51from courses as c
52inner join tsc
53 on tsc.cno = c.cno
54group by c.studyear
55order by "avg_grade" desc;
56
57-- 8. How many teachers did not have any classes?
58select teachers.tno
59from teachers
60where not exists (select * from tsc where tsc.tno = teachers.tno);
61
62-- 9. For each year of study, show the sum of teaching hours.
63-- Order the result according to the years of study.
64select c.studyear, sum(tsc.hours) as "total_teaching_hours"
65from courses as c
66inner join tsc
67 on tsc.cno = c.cno
68group by c.studyear
69order by c.studyear;
70
71-- 10. Which students (id, name) have the greatest average grade?
72-- 11. Show the list of all students (ids, names) ordered according to decreasing average grades.
73select s.sno, s.sname, avg(tsc.grade) as "avg_grade"
74from students s, tsc
75where s.sno = tsc.sno
76group by s.sno, s.sname
77order by "avg_grade" desc;
78
79-- 12. Which teachers(id, title, name) have the most numer of subordinates?
80create or replace function subordinates(id varchar(20))
81returns integer as $$
82with recursive subordinates as (
83 select tno, supno
84 from teachers
85 where tno = id
86 union
87 select t.tno, t.supno
88 from teachers t
89 inner join subordinates s
90 on s.tno = t.supno
91 ) select count(*)-1
92 from subordinates
93$$ language SQL;
94select tno, title, tname, subordinates(tno)
95from teachers;
96
97-- 13. Which teachers (id, title, name) have no supervisor?
98select tno, title, tname
99from teachers
100where supno is null;
101
102-- 14. Which teachers (ids, names) conducted more than 3 courses and how many courses they conducted?
103select distinct on (tsc.tno, tname)
104 tsc.tno, tname, count(distinct tsc.cno)
105from tsc
106inner join teachers t
107 on t.tno = tsc.tno
108group by (tsc.tno, tname);
109
110-- 15. Show ids and names of those students who obtained at least one 5.0 from any subject.
111select distinct tsc.sno, s.sname
112from tsc, students s
113where s.sno = tsc.sno and grade = 5.0;
114
115-- 16. Show ids and names of those students who obtained
116-- at least one grade that is maximal from all the grades obtained by anyone.
117-- How many students satisfy this condition?
118select distinct tsc.sno, s.sname
119from tsc
120inner join students s
121 on tsc.sno = s.sno
122where grade = 5.0;
123
124-- 17. How many students had mathematics on the 2nd year of study?
125select count(distinct sno)
126from tsc
127where cno = 'C2';
128
129-- 18. Create a view that contains all the data from TSC
130-- extended by names of students, titles and names of teachers and names of courses.
131create or replace view everything as (
132 select tsc.*, s.sname, t.title, t.tname, c.cname
133 from tsc
134 inner join students s
135 on s.sno = tsc.sno
136 inner join teachers t
137 on t.tno = tsc.tno
138 inner join courses c
139 on c.cno = tsc.cno
140);
141select * from everything;
142
143-- 19. For each city, give the total numer of students and teachers that come from this city.
144with together as (
145 (select city, sno from students union select city, tno from teachers)
146) select city, count(*) from together group by city;
147
148-- 20. Which teacher (id, name) gave the students the worst grades
149-- (i.e. the average grade from all the grades given by this teacher is the lowest).
150select tsc.tno, t.tname, avg(grade) as "avg_grade"
151from tsc
152inner join teachers t
153 on t.tno = tsc.tno
154group by tsc.tno, t.tname order by "avg_grade";
155
156-- 21. Create an archive for table TSC. The archive should contain:
157-- ids and names of students and teachers, ids and names of courses and grades.
158-- Next insert to this archive all the data from TSC and clear TSC.
159
160create table if not exists tsca (
161 tno varchar(20),
162 tname varchar(20),
163 sno varchar(20),
164 sname varchar(20),
165 cno varchar(20),
166 cname varchar(20),
167 grade float,
168 primary key (tno, sno, cno)
169);
170-- insert into tsca (tno, tname, sno, sname, cno, cname, grade)
171-- (select tsc.tno, t.tname, tsc.sno, s.sname, tsc.cno, c.cname, grade
172-- from tsc
173-- inner join students s
174-- on s.sno = tsc.sno
175-- inner join teachers t
176-- on t.tno = tsc.tno
177-- inner join courses c
178-- on c.cno = tsc.cno);
179
180select * from tsca;
181
182-- 22. For each course, show the average grade obtained by the studentsfrom this course.
183-- Next show these courses (ids, names) for which this average exceeds the average of all the averages.
184with grand_avg as (select avg(grade) from tsca
185) select
186 cname,
187 power(avg(grade)-(select * from grand_avg), 2) / count(*) as std
188from tsca group by cname order by std;
189
190-- 23. Show those teachers (ids, titles, names)
191-- who on the 1st year of study taught students that come from the same city as the teacher.
192select distinct(t.tname), t.title, s.sname from tsca
193inner join teachers t on t.tno = tsca.tno
194inner join students s on s.sno = tsca.sno
195inner join courses c on c.cno = tsca.cno
196where c.studyear = 1 and t.city = s.city;
197
198-- 24. Show the course (id, name) that had the least numer of students
199-- (but was conducted on any year of study).
200select cno, cname, count(distinct sno) from tsca group by cno, cname;
201
202-- 25. For each year of entry the studies,
203-- show the average grade of students that entered studies in this year.
204-- The result should be ordered according to decreasing averages.
205with "first" as (
206 select sname, grade, c.studyear from tsca, courses c where c.cno = tsca.cno and c.studyear = 1
207), "second" as (
208 select sname, grade, c.studyear from tsca, courses c where c.cno = tsca.cno and c.studyear = 2
209 except select * from "first"
210), "both" as (
211 select * from "first" union select * from second
212) select studyear, avg(grade) from "both" group by studyear;