jBcPWB93

1
2
3create table teachers (
4	tno varchar(10) primary key,
5	tname varchar(10),
6	title varchar(10),
7	city varchar(10),
8	supno varchar(10) references teachers
9);
10
11create table students (
12	sno varchar(10) primary key,
13	sname varchar(10),
14	syear varchar(10),
15	city varchar(10)
16);
17
18create table ts (
19	tno varchar(10) references teachers,
20	sno varchar(10) references students,
21	hrs int,
22	primary key (tno, sno)
23);
24
25insert into teachers (tno, tname, title, city, supno)
26values ('T1','Blake','Prof.','London',null), ('T2','Smith','PhD','Glasgow','T1'), ('T3','Jones','Prof.','London','T1'),
27       ('T4','Clark','PhD','Liverpool','T3'), ('T5','Adams','MSc','Bristol','T4')
28
29insert into students (sno, sname, syear, city)
30values ('S1','Henry','1975','London'), ('S2','Jones','1980','Davos'), ('S3','Johnson','1983','Dublin'),
31       ('S4','Higgins','1984','London'), ('S5','Ford','1990','Bristol'), ('S6','Hopkins','1990','Adelaide')
32
33insert into ts (tno, sno, hrs)
34values ('T1','S1',64), ('T1','S2',64), ('T2','S2',100), ('T2','S3',120), ('T2','S4',120), ('T3','S1',32), ('T3','S2',36),
35       ('T3','S3',60), ('T3','S5',72), ('T4','S1',96), ('T4','S3',96), ('T4','S5',96), ('T5','S6',32)
36
37/*
38drop table if exists ts
39drop table if exists students
40drop table if exists teachers
41*/
42select * from teachers
43--1.
44select count(*) from students where sname like 'A%'
45--2.
46select city, count(*) as totalstudents from students group by city
47--3.
48select tno from teachers where not exists (select * from ts where teachers.tno = ts.tno)
49--4.
50select count(*) from teachers where exists (select * from ts where teachers.tno = ts.tno)
51--5.
52select count(*) from teachers where not exists (select * from ts where teachers.tno = ts.tno)
53--6.
54select tname, sum(hrs) as totalhrs from teachers join ts on ts.tno = teachers.tno group by tname order by tname
55--7.
56select students.sno, sname, sum(hrs) as totalhrs from students join ts on ts.sno = students.sno group by students.sno, students.sname order by totalhrs desc
57--8.
58select tno, title, tname from teachers where tno = (select top 1 supno from teachers group by supno order by count(*) desc)
59--9.
60select tno, title, tname from teachers where supno is null
61--10.
62select distinct teachers.tno, tname from teachers join ts on ts.tno = teachers.tno where ((select count(*) from ts where ts.tno = teachers.tno) > 3)
63--11.
64--create view musi być w oddzielnym pliku żeby działał
65create view Teachings as
66	select ts.tno, ts.sno, ts.hrs, students.sname, teachers.title, teachers.tname
67		from ts join teachers on ts.tno = teachers.tno join students on ts.sno = students.sno
68
69--select * from Teachings
70--drop view Teachings
71--12.
72select city, count(*) from (select city from students union all select city from teachers) as cities group by cities.city
73--13.
74select * into ts_archive from ts
75delete from ts
76
77-- select * from ts_archive
78-- select * from ts
79-- drop table if exists ts_archive
80
81--14.
82select teachers.tno, title, tname from teachers join students on teachers.city = students.city and exists (select * from ts where teachers.tno = ts.tno and students.sno = ts.sno)
83--15.
84select syear, count(*) as totalstudents from students group by syear order by totalstudents desc