hw2-solutions

COMS 4111, Spring 2013
HW2 Solutions

Q1. Graded by Abhas.

SELECT s.sid, s.sname, s.gpa
FROM student s
WHERE s.gpa > ALL (
    SELECT s1.gpa
    FROM student s1, major m
    WHERE m.sid = s1.sid AND m.dname='Computer Sciences')

SID	SNAME	GPA
48	Natividad A.	4
64	Fred Edwin B.	4
67	Altenhaus Gloria	4
73	Quarnty G.	4
89	Longlastname A.	4
90	Zappa F.	4

Q2. Graded by Ningning.

The sid, name and gpa of Civil Engineering students enrolled in at least one course offered by Chemical Engineering:

SELECT s.sid, s.sname, s.gpa
FROM student s, enroll e, major m
WHERE s.sid = e.sid AND s.sid = m.sid AND 
    m.dname = 'Civil Engineering' AND 
    e.dname = 'Chemical Engineering'

Students enrolled in a course offered by a department other than Civil Engineering and Chemical Engineering:

SELECT sid
FROM enroll
WHERE dname <> 'Civil Engineering' AND dname <> 'Chemical Engineering'

Students majoring in something other than Civil Engineering:

SELECT sid FROM major WHERE dname <> 'Civil Engineering'

The final query should include students returned by the first query above but not the second and third.

SELECT s.sid, s.sname, s.gpa
FROM student s, enroll e, major m
WHERE s.sid = e.sid AND s.sid = m.sid AND 
    m.dname = 'Civil Engineering' AND 
    e.dname = 'Chemical Engineering' AND 
    s.sid NOT IN (
        SELECT sid
        FROM enroll
        WHERE dname <> 'Civil Engineering' AND dname <> 'Chemical Engineering') AND
    s.sid NOT IN (SELECT sid FROM major WHERE dname <> 'Civil Engineering')

SID	SNAME	GPA
45	June Granson	3.1
56	Starry J.	3.3

Q3. Graded by Enrique.

SELECT d.dname, d.numphds, COUNT(p.pname) AS num_of_profs, d.numphds/count(p.pname) AS st_to_prof_ratio
FROM dept d, prof p
WHERE d.dname = p.dname
GROUP BY d.dname, d.numphds
HAVING COUNT(p.pname) >= 2

DNAME	NUMPHDS	NUM_OF_PROFS	ST_TO_PROF_RATIO
Industrial Engineering	41	2	20.5
Computer Sciences	47	2	23.5
Civil Engineering	88	3	29.33333333333333333333333333333333333333

Note: We renamed the last two columns just for illustration purposes; we don't really care if you did not rename the columns or, if you did, the names you used .

Q4. Graded by Abhas.

Let's attack the first part of the query: "For each department with at least 3 students majoring in the department, print the department name, the total number of students majoring in the department and the average age and average GPA of all students majoring in this department". This information is in the major and student tables.

Subquery Q1 :

SELECT m.dname, COUNT(m.sid) AS cnt, AVG(s.age) AS avg_age, AVG(s.gpa) AS avg_gpa
FROM major m, student s
WHERE m.sid = s.sid
GROUP BY m.dname
HAVING COUNT(s.sid) >= 3

Now, for the second part of the query: " ... followed by the same information for the rising stars of this department (i.e., the number of rising stars in the department as well as their average age and average GPA)", we compute the stats as above but this time for the rsing stars only.

Subquery Q2:

SELECT m1.dname, COUNT(m1.sid) AS cnt, AVG(s1.age) as avg_age, AVG(s1.gpa) as avg_gpa 
FROM major m1, student s1
WHERE s1.sid = m1.sid AND 
    s1.gpa >= 1.20*(
        SELECT AVG(s2.gpa) 
        FROM student s2, major m2
        WHERE s2.sid = m2.sid AND m2.dname = m1.dname) AND 
    s1.age < (
        SELECT AVG(s2.age) 
        FROM student s2, major m2
        WHERE s2.sid = m2.sid AND m2.dname = m1.dname) 
GROUP BY m1.dname

Now we need to combine these two subqueries into one so the results of both are included in one table. For a better visualization, we keep the same colors for the subqueries discussed above (Q1 in blue, Q2 in green); the new constructs that glue the two subqueries together are in red.

SELECT dept.dname, dept.cnt, dept.avg_age, dept.avg_gpa, rs.cnt AS rs_cnt, rs.avg_age AS rs_avg_age, rs.avg_gpa AS rs_avg_gpa
FROM (

    SELECT m.dname, COUNT(m.sid) AS cnt, AVG(s.age) AS avg_age, AVG(s.gpa) AS avg_gpa
    FROM major m, student s
    WHERE m.sid = s.sid
    GROUP BY m.dname
    HAVING COUNT(s.sid) >= 3 ) dept, (

    SELECT m1.dname, COUNT(m1.sid) AS cnt, AVG(s1.age) as avg_age, AVG(s1.gpa) as avg_gpa 
    FROM major m1, student s1
    WHERE s1.sid = m1.sid AND 
        s1.gpa >= 1.20*(
            SELECT AVG(s2.gpa) 
            FROM student s2, major m2
            WHERE s2.sid = m2.sid AND m2.dname = m1.dname) AND 
        s1.age < (
            SELECT AVG(s2.age) 
            FROM student s2, major m2
            WHERE s2.sid = m2.sid AND m2.dname = m1.dname) 
    GROUP BY m1.dname ) rs

WHERE dept.dname = rs.dname

DNAME	CNT	AVG_AGE	AVG_GPA	RS_CNT	RS_AVG_AGE	RS_AVG_GPA
Computer Sciences	24	25.04166666666666666666666666666666666667	3.00416666666666666666666666666666666667	2	20.5	3.85
Mathematics	19	30.63157894736842105263157894736842105263	3.24210526315789473684210526315789473684	2	18.5	3.95
Civil Engineering	28	27.25	2.91428571428571428571428571428571428571	4	23.75	3.7
Industrial Engineering	20	27.7	2.77	5	21.6	3.84

Q5. Graded by Hahn.

To compute the enrollment of each course offered by each department, including those courses with zero enrollment, it is not sufficient to look at just the enroll table - a course with zero enrololment will not appear in the enroll table due to the key constraints. Information about courses, regardless of enrollment, is available in the course table. So, we use left outer join of the course and enroll tables to find the enrollment of every course, including those with zero enrollment. (A simple join between these two tables is not good because it will exclude courses with zero enrollment since these courses do not appear in the enroll table.)

The enrollment of each course (including those with zero enrollment):

SELECT c1.dname, c1.cno, count(e1.sid)
FROM course c1 LEFT OUTER JOIN enroll e1 ON (e1.dname = c1.dname AND e1.cno = c1.cno)
GROUP BY c1.dname, c1.cno

The average enrollment of all courses (including those with zero enrollment) offered by a specific department X:

SELECT count(e2.sid)/count(distinct c2.cno)
FROM course c2 LEFT OUTER JOIN enroll e2 ON (e2.dname = c2.dname AND e2.cno = c2.cno)
WHERE c2.dname = X

We are now ready to write our query by combining the above queries:

SELECT c1.dname, c1.cno, count(e1.sid)
FROM course c1 LEFT OUTER JOIN enroll e1 ON (e1.dname = c1.dname AND e1.cno = c1.cno)
GROUP BY c1.dname, c1.cno
HAVING COUNT(e1.sid) >= 1.2*(
    SELECT count(e2.sid)/count(distinct c2.cno)
    FROM course c2 LEFT OUTER JOIN enroll e2 ON (e2.dname = c2.dname AND e2.cno = c2.cno)
    WHERE c2.dname = c1.dname)

DNAME	CNO	COUNT(E.SID)
Computer Sciences	302	18
Civil Engineering	561	12
Sanitary Engineering	561	1