SQL> @studtables; SQL> select * from students; SQL> select * from professor; SQL> select * from schedule; SQL> select * from transcripts; SQL> create view deptSchedule as (select s.crscode, s.semester, p.department from schedule s, professor p where s.teacher=p.profid); the deptSchedule looks as follows: CRSCODE SEMESTER DEPAR ---------- ------------ ----- CS110 Fall2005 CS CS370 Fall2005 CS SQL> create view deptTrans as (select t.crscode, t.semester, t.grade, t.studid, d.department from transcripts t, deptSchedule d where t.crscode=d.crscode and t.semester=d.semester); SQL> select * from deptTrans; CRSCODE SEMESTER G STUDID DEPAR ---------- ------------ - ---------- ----- CS110 Fall2005 C 222222222 CS ... SQL> create view csStuds as (select * from deptTrans where department='CS'); View created. SQL> create view eeStuds as (select * from deptTrans where department='EE'); View created. Query 6: SQL> select cs.studid, s.name, s.address from csStuds cs, students s where cs.studid=s.id and s.status='Senior' and (cs.studid, cs.Semester) in (select ee.studid, ee.semester from eeStuds ee); SQL> select studid, semester, count(*) from (select distinct studid, semester, department from deptTrans) group by studid, semester having count(*)>=2; SQL> select studid from ( select studid, semester, count(*) from (select distinct studid, semester, department from deptTrans) group by studid, semester having count(*)>=2); Query 7: SQL> select s.id, s.name, s.address from students s where s.status='Freshman' and s.id in (select studid from ( select studid, semester, count(*) from (select distinct studid, semester, department from deptTrans) group by studid, semester having count(*)>=2)); Query 8: -- finding students who do not have a EE class and have more than 4 classes SQL> select distinct studid from deptTrans st where st.studid not in (select t.studid from deptTrans t where t.department='EE') group by st.studid having count(*)>=4; -- adding the name, address to the output SQL> select s.id, s.name, s.address from students s where s.id in ( select distinct studid from deptTrans st where st.studid not in (select t.studid from deptTrans t where t.department='EE') group by st.studid having count(*)>=4); -- answer to query 8 SQL> select s.id, s.name, s.address from students s where s.id in ( select distinct studid from deptTrans st where st.studid not in (select t.studid from deptTrans t where t.department='EE') group by st.studid having count(*)>=30); no rows selected Query 9: SQL> select s.crscode, s.semester, c.title from courses c, schedule s where s.crscode=c.crscode; -- answer to query 9 SQL> select s.crscode, s.semester, c.title from courses c, schedule s where s.crscode=c.crscode and not exists (select * from transcripts t where t.crscode=s.crscode and t.semester=s.semester); Query 10: Oops, I forgot:-) Query 11: toying with some ideas --- SQL> create view profStud as (select t.semester, t.studid, p.profid from transcripts t, professor p, schedule s where t.crscode=s.crscode and t.semester=s.semester and s.teacher=p.profid); -- get people in the top rank SQL> select p.profid, p.name, 'TOP' as rank from professor p where p.department='CS' and p.profid in (select t.profid from profStud t where t.semester='Fall2005' group by t.profid having count(*)>=2); PROFID NAME RAN ---------- ------------------------------ --- 4 Desh TOP -- add people in the second rank SQL> select p.profid, p.name, 'TOP' as rank from professor p where p.department='CS' and p.profid in (select t.profid from profStud t where t.semester='Fall2005' group by t.profid having count(*)>=2) 2 union 3 select p.profid, p.name, 'TOP' as rank from professor p where p.department='CS' and p.profid in (select t.profid from profStud t where t.semester='Fall2005' group by t.profid having count(*)<2 and count(*)>=1); PROFID NAME RAN ---------- ------------------------------ --- 2 Enrico TOP 4 Desh TOP --- try to get the rest SQL> select p.profid, p.name, 'TOP' as rank from professor p where p.department='CS' and p.profid in (select t.profid from profStud t where t.semester='Fall2005' group by t.profid having count(*)>=2) 2 union select p.profid, p.name, 'SECOND' as rank from professor p where p.department='CS' and p.profid in (select t.profid from profStud t where t.semester='Fall2005' group by t.profid having count(*)<2 and count(*)>=1) 3 union select p.profid, p.name, 'THIRD' as rank from professor p where p.department='CS' and p.profid not in (select t.profid from profStud t where t.semester='Fall2005' group by t.profid having count(*)>=1); PROFID NAME RANK ---------- ------------------------------ ------ 1 Son THIRD 2 Enrico SECOND 3 Roger THIRD 4 Desh TOP Query 12: --- create a view assosicate student and professor SQL> create view profStudSem as (select t.crscode, t.semester, t.studid, p.profid from transcripts t, professor p, schedule s where t.crscode=s.crscode and t.semester=s.semester and s.teacher=p.profid); View created. SQL> select * from profStudSem; CRSCODE SEMESTER STUDID PROFID ---------- ------------ ---------- ---------- CS110 Fall2005 222222222 4 CS110 Fall2005 111111111 4 CS370 Fall2005 555555555 4 CS110 Fall2004 555555555 4 CS110 Fall2004 444444444 4 CS110 Spring2006 666666666 4 CS482 Fall2004 222222222 1 CS272 Fall2005 444444444 2 CS272 Fall2002 222222222 2 CS370 Spring2004 222222222 4 CS370 Spring2004 111111111 4 CRSCODE SEMESTER STUDID PROFID ---------- ------------ ---------- ---------- EE110 Spring2006 666666666 5 EE445 Fall2005 111111111 7 13 rows selected. --- a student takes all classes from a professor if there exists --- no course that were taught by the professor whose list does --- not contain the student SQL> select p.studid, p.profid from profStudSem p where not exists (select t.crscode, t.semester from profStudSem t where p.studid not in (select a.studid from profStudSem a where a.crscode=t.crscode and a.semester=t.semester and a.profid=p.profid)); no rows selected SQL> quit;