## SQL SELECT examples: exercise 5.1

-- simple examples -- Names of students taking Data Structures select s.sname from student s, enrolled e where s.snum=e.snum and e.cname='Data Structures'; -- students taking both databases and operating sys select sname from student s, enrolled e where s.snum= e.snum and e.cname like 'Datab%' intersect select sname from student s2, enrolled e2 where s2.snum= e2.snum and e2.cname like 'Oper%' -- students taking databases but not op sys select sname from student s, enrolled e where s.snum= e.snum and e.cname like 'Datab%' minus select sname from student s2, enrolled e2 where s2.snum= e2.snum and e2.cname like 'Oper%' -- List Courses taken by Lisa Walker (snum=322654189) select e.cname from student s, enrolled e where s.snum=e.snum and s.snum=322654189; -- Ex 5.1 -- names of all juniors select sname from student where standing='JR' -- 1: names of all juniors enrolled in class by I Teach select sname from student s, class c, enrolled e, faculty f where s.snum=e.snum and e.cname=c.name and c.fid=f.fid and s.standing='JR' and f.fname='I. Teach'; -- Age of oldest student select max(age) from student; -- age of oldest student who is history major select max(age) from student s where s.major='History'; -- 2: age of oldest student who is history major, or in course by I. Teach select max(age) from student s, class c, enrolled e, faculty f where s.snum=e.snum and e.cname=c.name and c.fid=f.fid and ( (f.fname='I. Teach') or (s.major='History')); -- 3: names of all classes that either meet in R128 or have >= 5 students select c1.name from class c1 where c1.room='R128' or 5 <= (SELECT COUNT(*) FROM enrolled e WHERE e.cname= c1.name) -- 4: names of all students who are enrolled in two classes -- that meet at the same time. select distinct s.sname from enrolled e1, class c1, enrolled e2, class c2, student s where e1.cname=c1.name and e2.cname= c2.name and s.snum=e1.snum and e1.snum= e2.snum and c1.name <> c2.name and c1.meets_at= c2.meets_at -- 5: find names of faculty who teach in every room in which some class is -- taught. SQL Division. -- NOTE: added a class for one of the professors to test. SELECT f.fname FROM faculty f WHERE NOT EXISTS (SELECT c1.room FROM class c1 WHERE c1.room is not null MINUS (SELECT c2.room FROM class c2 WHERE c2.fid=f.fid)); -- 7: print the level and average age of students for that level, for each level select standing, avg(age) from student group by standing -- 11: find the names of students not enrolled in any classes select s.sname from student s where s.snum in (select s1.snum from student s1 minus select distinct s2.snum from student s2, enrolled e where s2.snum=e.snum)