This assignment should be done individually. You may work with other people, get ideas, help each other debug, and so on; but the content that you submit should be your own.
This part of the assignment uses the same college enrollment data that you used for a previous SQL assignment. Even if you have the data set up from last time, drop it and load it up again. Specifically, make sure you use this script in particular for loading up the tables. That way, you're all starting with exactly the same primary key index structure.
Read the documentation about EXPLAIN in the PostgreSQL manual. Look at the examples at the end to see specifically how to use it. The documentation is sorely lacking in details on how to read the output from EXPLAIN. Part of the point of this assignment is to do the best you can, with the knowledge that you have and incomplete documentation, to figure out what's going on.
I've provided below some queries that were submitted by students in the past for questions from a past version of the previous SQL assignment. For some of the questions from the previous assignment, I'm providing two or three alternative choices as solutions that yield the same answer when you run them. For each group of queries, answer the following questions:
The numbers (e.g. "Query 3") on these queries are those from the previous assignment. Feel free to go back and look to remind yourself what each query was supposed to do.
-- Version A -- SELECT E.cno, E.sectno, AVG(S.gpa) FROM enroll E, student S WHERE S.sid = E.sid AND E.dname = 'Computer Sciences' GROUP BY E.cno, E.sectno;
-- Version B -- SELECT section.cno AS Course, section.sectno AS Section, AVG(student.gpa) AS avgGPA FROM section, enroll, student WHERE enroll.cno=section.cno AND enroll.sid = student.sid AND enroll.sectno = section.sectno AND section.dname='Computer Sciences' GROUP BY section.cno, section.sectno;
-- Version A -- SELECT temp.sectno, temp.cname FROM (SELECT E.cno, E.sectno, C.cname FROM enroll E, course C WHERE E.cno = C.cno AND E.dname = C.dname) temp GROUP BY temp.cno, temp.sectno, temp.cname HAVING COUNT(*) > 6;
-- Version B -- SELECT course.cname, thistable.sectno FROM course, (SELECT enroll.cno, enroll.sectno, enroll.dname, COUNT (*) AS count FROM enroll GROUP BY enroll.cno, enroll.sectno, enroll.dname) AS thistable WHERE course.cno=thistable.cno AND course.dname=thistable.dname AND thistable.count > 6;
-- Version A -- SELECT S.sid,S.sname FROM student S, enroll E WHERE S.sid=E.sid GROUP BY S.sid, S.sname HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM student S, enroll E WHERE S.sid=E.sid GROUP BY s.sid);
-- Version B -- SELECT S.sid, S.sname FROM student S WHERE S.sid IN (SELECT E.sid FROM enroll E GROUP BY E.sid HAVING COUNT(*) = (SELECT MAX(count) FROM (SELECT COUNT(*) FROM enroll E GROUP BY E.sid) AS foo));
-- Version C -- SELECT temp.sid, S.sname FROM (SELECT COUNT(*), E2.sid FROM enroll E2 GROUP BY E2.sid) AS temp, student S WHERE temp.count = (SELECT MAX(temp.count) FROM (SELECT COUNT(*), E2.sid FROM enroll E2 GROUP BY E2.sid) AS temp) AND temp.sid = S.sid;
Submit the answers to the questions above for each set of queries. You may use drawing software such as Dia to generate the trees, or you may write them on paper neatly.