0YKnsEWL

1drop table if exists student;
2create table student (
3  sno integer,
4	sname varchar(10),
5	age integer
6);
7
8drop table if exists courses;
9create table courses (
10	cno varchar(5),
11	title varchar(10),
12	credits integer
13);
14
15drop table if exists professor;
16create table professor (
17	lname varchar(10),
18	debt varchar(10),
19	salary integer,
20	age integer
21);
22
23drop table if exists take;
24create table take (
25	sno integer,
26	cno varchar(5)
27);
28
29drop table if exists teach;
30create table teach (
31	lname varchar(10),
32	cno varchar(5)
33);
34
35insert into student values (1, 'AARON', 20);
36insert into student values (2, 'CHUCK', 21);
37insert into student values (3, 'DOUG', 20);
38insert into student values (4, 'MAGGIE', 19);
39insert into student values (5, 'STEVE', 22);
40insert into student values (6, 'JING', 18);
41insert into student values (7, 'BRIAN', 21);
42insert into student values (8, 'KAY', 20);
43insert into student values (9, 'GILLIAN', 20);
44insert into student values (10, 'CHAD', 21);
45
46insert into courses values ('CS112', 'PHYSICS', 4);
47insert into courses values ('CS113', 'CALCULUS', 4);
48insert into courses values ('CS114', 'HISTORY', 4);
49
50insert into professor values ('CHOI', 'SCIENCE', 400, 45);
51insert into professor values ('GUNN', 'HISTORY', 300, 60);
52insert into professor values ('MAYER', 'MATH', 400, 55);
53insert into professor values ('POMEL', 'SCIENCE', 500, 65);
54insert into professor values ('FEUER', 'MATH', 400, 40);
55
56insert into take values (1, 'CS112');
57insert into take values (1, 'CS113');
58insert into take values (1, 'CS114');
59insert into take values (2, 'CS112');
60insert into take values (3, 'CS112');
61insert into take values (3, 'CS114');
62insert into take values (4, 'CS112');
63insert into take values (4, 'CS113');
64insert into take values (5, 'CS113');
65insert into take values (6, 'CS113');
66insert into take values (6, 'CS114');
67
68insert into teach values ('CHOI', 'CS112');
69insert into teach values ('CHOI', 'CS112');
70insert into teach values ('CHOI', 'CS112');
71insert into teach values ('POMEL', 'CS113');
72insert into teach values ('MAYER', 'CS112');
73insert into teach values ('MAYER', 'CS114');
74
75-- pp. 557-559: Negation: "Students who do not take CS112"
76select *
77  from student
78 where sno not in (select sno
79                    from take
80                  where cno = 'CS112');
81
82-- pp. 566-567: "At most": "Find the students who take at most two courses; students who do not take any courses should be excluded."
83select distinct s.*
84  from student s, take t
85 where s.sno = t.sno
86   and s.sno not in ( select t1.sno
87                       from take t1, take t2, take t3
88                      where t1.sno = t2.sno
89                        and t2.sno = t3.sno
90                        and t1.cno < t2.cno and
91                        t2.cno < t3.cno );