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)