Sample Solutions for HW2.
------------------------

SQL Queries::
=============

prompt **********************
prompt solution to question 7.
SELECT DISTINCT S.lname, S.fname, count(S.stuid) AS cnt 
FROM Student S, Allergy_Type A, Has_Allergy H 
WHERE S.stuid=H.stuid And H.allergy=A.allergy AND A.allergytype='Food' 
GROUP BY S.lname, S.fname HAVING count(S.stuid)>1;

+----------+---------+-----+
| lname    | fname   | cnt |
+----------+---------+-----+
| Apap     | Lisa    |   4 |
| Han      | Jun     |   3 |
| Lee      | Derek   |   4 |
| Lee      | Susan   |   2 |
| Leighton | Michael |   2 |
| Schwartz | Mark    |   2 |
| Smith    | Sarah   |   3 |
| Woods    | Michael |   2 |
+----------+---------+-----+
8 rows in set (0.04 sec)

prompt **********************
prompt solution to question 8

SELECT Student.fname, Student.lname
FROM Student, Has_Allergy, Allergy_Type
WHERE Student.StuID = Has_Allergy.StuID AND Allergy_Type.Allergy = Has_Allergy.Allergy 
GROUP BY Student.StuID 
HAVING COUNT(DISTINCT Allergy_Type.AllergyType) = (SELECT COUNT(DISTINCT AllergyType) FROM Allergy_Type);

+-------+-------+
| fname | lname |
+-------+-------+
| Lisa  | Apap  |
| Derek | Lee   |
+-------+-------+
2 rows in set (0.07 sec)

prompt **********************
prompt solution to question 9
SELECT Result1.allergy, A.allergytype
FROM
     (SELECT allergy,count(DISTINCT H.stuid) cnt
      FROM Student S, Has_Allergy H
      WHERE S.age>25 and H.stuid=S.stuid
      GROUP BY H.allergy)Result1,
      Allergy_Type A
WHERE A.allergy=Result1.allergy and
      Result1.cnt>=ALL(SELECT count(DISTINCT H.stuid)
      FROM Student S, Has_Allergy H
      WHERE S.age>25 and H.stuid=S.stuid
      GROUP BY H.allergy);

+-------------+---------------+
| allergy     | allergytype   |
+-------------+---------------+
| Tree Pollen | environmental |
+-------------+---------------+
1 row in set (0.00 sec)

prompt **********************
prompt solution to question 10
SELECT Result2.fname1, Result2.lname1, Result2.fname2, Result2.lname2
FROM
    (SELECT S1.fname fname1, S1.lname lname1, S1.stuid stuid1, S2.fname 
fname2, S2.lname lname2, S2.stuid stuid2
     FROM  (SELECT L1.stuid stu1, L2.stuid stu2
            FROM  Lives_in L1, Lives_in L2
            WHERE L1.dormid=L2.dormid AND 
L1.room_number=L2.room_number AND L1.stuid > L2.stuid) Result,
            Student S1,
            Student S2
            WHERE Result.stu1=S1.stuid AND Result.stu2=S2.stuid) Result2,
       Preferences P1,
       Preferences P2
WHERE (Result2.stuid1=P1.stuid AND Result2.stuid2 =P2.stuid) AND 
((P1.musictype<>P2.musictype) OR (P1.sleephabits<>P2.sleephabits) OR 
(P1.smoking='no' AND P2.smoking='Yes') OR (P1.smoking='Yes' AND 
P2.smoking='no'));

+--------+---------+--------+----------+
| fname1 | lname1  | fname2 | lname2   |
+--------+---------+--------+----------+
| Eric   | Brown   | Linda  | Smith    |
| Sarah  | Smith   | Tracy  | Kim      |
| Sarah  | Schmidt | Shiela | Jones    |
| Steven | Davis   | Paul   | Gompers  |
| George | Andreou | Ian    | Thornton |
| Mark   | Goldman | David  | Shieber  |
+--------+---------+--------+----------+
6 rows in set (0.00 sec)

prompt **********************
prompt solution to question 11
SELECT D.dno, D.dname, (Result2.cnt2/Result1.cnt1)*100 percentage, Result2.cnt2 AS Non_Smokers,
Result1.cnt1 AS Number_of_Majors
FROM   (SELECT dno, count(dno) AS cnt1
        FROM Student S, Department D
        WHERE S.Major=D.dno
        GROUP BY dno
        HAVING count(dno)>=1) Result1,
       (SELECT dno, count(dno) AS cnt2
        FROM Student S, Preferences P, Department D
        WHERE S.stuid=P.stuid AND (P.smoking='no' OR 
P.smoking='no-accept') AND S.major=D.dno
        GROUP BY dno) Result2,
        Department D
WHERE D.dno=Result1.dno AND Result1.dno=Result2.dno;

+------+-----------------------+------------+-------------+------------------+
| dno  | dname                 | percentage | Non_Smokers | Number_of_Majors |
+------+-----------------------+------------+-------------+------------------+
|   50 | Cognitive Science     |   100.0000 |           2 |                2 |
|  100 | History               |   100.0000 |           1 |                1 |
|  520 | ECE                   |    16.6667 |           1 |                6 |
|  540 | Chemical Engineering  |    50.0000 |           1 |                2 |
|  550 | Mathematical Sciences |    80.0000 |           4 |                5 |
|  600 | Computer Science      |    50.0000 |           9 |               18 |
+------+-----------------------+------------+-------------+------------------+
6 rows in set (0.00 sec)

prompt **********************
prompt solution to question 12

SELECT Major_Name, Major_Number, Percentage
FROM ( SELECT DName AS Major_Name, Result1.Major AS Major_Number, (Result2.Smokers/Result1.MajorCount * 100) AS Percentage
	FROM (SELECT Major, COUNT(Major) AS MajorCount
		FROM Student GROUP BY Major HAVING COUNT(Major) >=3 ) AS Result1,
			(SELECT Major, COUNT(Major) AS Smokers
			FROM Student, Preferences
			WHERE Preferences.StuID = Student.StuID AND Preferences.Smoking = "yes"
			GROUP BY Major) AS Result2, Department
		WHERE Result1.Major = Result2.Major AND (Result2.Smokers/Result1.MajorCount * 100) >=
			ALL (SELECT (Result2.Smokers/Result1.MajorCount * 100) AS Percentage
				FROM    (SELECT Major, COUNT(Major) AS MajorCount
					FROM Student GROUP BY Major HAVING COUNT(Major) >=3 ) AS Result1,
					(SELECT Major, COUNT(Major) As Smokers FROM Student, Preferences
					WHERE Preferences.StuID = Student.StuID AND Preferences.Smoking = "yes"
					GROUP BY Major) AS Result2 
				WHERE Result1.Major = Result2.Major)
			AND Department.DNO = Result1.Major) AS Result;

+------------+--------------+------------+
| Major_Name | Major_Number | Percentage |
+------------+--------------+------------+
| ECE        |          520 |    83.3333 |
+------------+--------------+------------+
1 row in set (0.01 sec)

prompt **********************
prompt solution to question 13
SELECT A.allergytype, U.allergy
FROM   (SELECT R1.state, H.allergy, count(H.allergy) cnt
        FROM (SELECT state, count(state)
              FROM Student S, City C
              WHERE S.city_code=C.city_code
              GROUP BY C.state
              HAVING count(state)>3) R1,
              Student S,
              Has_Allergy H,
              City C
         WHERE S.stuid=H.stuid AND S.city_code=C.city_code and C.state=R1.state
         GROUP BY R1.state, H.allergy) U,
         (SELECT R2.state, max(cnt) cnt
          FROM   (SELECT R1.state, H.allergy, count(H.allergy) cnt
                 FROM (SELECT state, count(state)
                       FROM Student S, City C
                       WHERE S.city_code=C.city_code
                       GROUP BY C.state
                       HAVING count(state)>3) R1,
                       Student S,
                       Has_Allergy H,
                       City C
                 WHERE S.stuid=H.stuid AND S.city_code=C.city_code and C.state=R1.state
                 GROUP BY R1.state, H.allergy) R2
         GROUP BY R2.state) W,
         Allergy_Type A
WHERE U.cnt=W.cnt AND U.state=W.state AND 
U.allergy=A.allergy;

+---------------+--------------+
| allergytype   | allergy      |
+---------------+--------------+
| animal        | Dog          |
| environmental | Tree Pollen  |
| environmental | Grass Pollen |
| food          | Nuts         |
| food          | Shellfish    |
| food          | Soy          |
| environmental | Tree Pollen  |
| animal        | Cat          |
| food          | Nuts         |
| environmental | Ragweed      |
| environmental | Tree Pollen  |
| environmental | Tree Pollen  |
| food          | Nuts         |
| environmental | Ragweed      |
+---------------+--------------+
14 rows in set (0.02 sec)


prompt **********************
prompt solution to question 14
SELECT DISTINCT S.lname, S.fname
FROM   Student S, Preferences P, Has_Allergy H, Allergy_Type A
WHERE  S.stuid=H.stuid AND H.allergy=A.allergy And 
A.allergytype='Environmental' AND S.stuid=P.stuid AND 
P.smoking='Yes';

+----------+--------+
| lname    | fname  |
+----------+--------+
| Kim      | Tracy  |
| Apap     | Lisa   |
| Tai      | Eric   |
| Lee      | Derek  |
| Thornton | Ian    |
| Andreou  | George |
| Prater   | Stacy  |
| Goldman  | Mark   |
| Brody    | Paul   |
| Cheng    | Lisa   |
+----------+--------+
10 rows in set (0.01 sec)

prompt **********************
prompt solution to question 15
SELECT S.lname, S.fname, S.age, S.major, F.lname, F.fname
FROM   Student S, Course C, Enrolled_in E, Faculty F
WHERE  S.stuid=E.stuid AND E.cid=C.cid AND C.instructor=S.advisor 
AND S.advisor=F.facid;

+----------+--------+------+-------+----------+---------+
| lname    | fname  | age  | major | lname    | fname   |
+----------+--------+------+-------+----------+---------+
| Kim      | Tracy  |   19 |   600 | Awerbuch | Baruch  |
| Jones    | Shiela |   21 |   600 | Brill    | Eric    |
| Gompers  | Paul   |   26 |   600 | Goodrich | Michael |
| Apap     | Lisa   |   18 |   600 | Amir     | Yair    |
| Schwartz | Mark   |   17 |   600 | Yarowsky | David   |
| Schmidt  | Sarah  |   26 |    50 | Frank    | Robert  |
+----------+--------+------+-------+----------+---------+
6 rows in set (0.04 sec)

prompt **********************
prompt solution to question 16
SELECT D.division, count(D.division)
FROM Student S, Enrolled_in E, Course C, Department D
WHERE S.stuid=E.stuid AND E.cid=C.cid AND C.dno=D.dno
GROUP BY division;

+----------+-------------------+
| division | count(D.division) |
+----------+-------------------+
| AS       |                18 |
| EN       |               192 |
+----------+-------------------+
2 rows in set (1.10 sec)

prompt ***********************
prompt solution to question 17
SELECT fname, count(fname) AS frequency
FROM Faculty
GROUP BY fname
HAVING count(fname)>1
ORDER BY count(fname) DESC;

+---------+-----------+
| fname   | frequency |
+---------+-----------+
| Michael |         3 |
| William |         2 |
| Robert  |         2 |
| John    |         2 |
+---------+-----------+
4 rows in set (0.00 sec)

prompt ***********************
prompt solution to question 18
SELECT T.fname, T.total
FROM  (SELECT R1.fname fname, R1.cnt+R2.cnt total
       FROM (SELECT fname, count(fname) AS cnt
             FROM Faculty
             GROUP BY fname) R1,
            (SELECT fname, count(fname) AS cnt
             FROM Student
             GROUP BY fname) R2
       WHERE R1.fname=R2.fname) T
WHERE T.total >= ALL
      (SELECT R1.cnt+R2.cnt total
       FROM (SELECT fname, count(fname) AS cnt
             FROM Faculty
             GROUP BY fname) R1,
            (SELECT fname, count(fname) AS cnt
             FROM Student
             GROUP BY fname) R2
       WHERE R1.fname=R2.fname) ;

+-------+-------+
| fname | total |
+-------+-------+
| Eric  |     6 |
+-------+-------+
1 row in set (0.02 sec)

prompt ***********************
prompt solution to question 19
SELECT D.dno, D.dname, result1.cnt
FROM Department D,
    (SELECT D.dno, count(D.dno) cnt
     FROM Department D, Enrolled_in E, Course C
     WHERE D.dno=C.dno AND C.cid=E.cid
     GROUP BY D.dno)result1
WHERE result1.dno=D.dno AND result1.cnt <= ALL
        (SELECT count(D.dno) cnt
         FROM Department D, Enrolled_in E, Course C
         WHERE D.dno=C.dno AND C.cid=E.cid
         GROUP BY D.dno);

+------+-------+-----+
| dno  | dname | cnt |
+------+-------+-----+
|  520 | ECE   |  14 |
+------+-------+-----+
1 row in set (0.06 sec)


prompt ***********************
prompt solution to question 20
SELECT S.fname, S.lname, C.cname, F.fname, F.lname, E.grade
FROM Student S, Course C, Faculty F, Enrolled_in E, Preferences P
WHERE S.stuid=P.stuid AND P.sleephabits='EarlyRiser'AND 
S.stuid=E.stuid
	AND E.cid=C.cid AND F.facid=C.instructor AND S.stuid NOT in
        (SELECT S.stuid
         FROM Student S, Has_Allergy H, Preferences P
         WHERE S.stuid=H.stuid OR (S.stuid=P.stuid AND 
P.smoking='Yes'));

+-------+-------+--------------------------------------+--------+-----------+-------+
| fname | lname | cname                                | fname  | lname     | grade |
+-------+-------+--------------------------------------+--------+-----------+-------+
| Eric  | Epp   | MIND, BRAIN, COMPUTERS               | Paul   | Smolensky | B+    |
| Eric  | Epp   | COGNITIVE NEUROSCIENCE               | Brenda | Rapp      | B     |
| Eric  | Epp   | SOUND STRUCTURES IN NATURAL LANGUAGE | Luigi  | Burzio    | B     |
| Eric  | Epp   | INTRO TO PROGRAMMING IN JAVA         | Stacey | Jones     | B+    |
| Eric  | Epp   | DATABASE SYSTEMS                     | David  | Yarowsky  | B     |
+-------+-------+--------------------------------------+--------+-----------+-------+
5 rows in set (0.19 sec)

prompt ***********************
prompt solution to question 21
SELECT lname, fname, age
FROM Student
WHERE age >= ALL
      (SELECT age
       FROM Student)
UNION
SELECT lname, fname, age
FROM Student
WHERE age <= ALL
      (SELECT age
       FROM Student);

+--------+-------+------+
| lname  | fname | age  |
+--------+-------+------+
| Wilson | Bruce |   27 |
| Lee    | Susan |   16 |
+--------+-------+------+
2 rows in set (0.01 sec)


PROMPT  ******************************************  
prompt Query 22
prompt *******************************************

SELECT DName, R2.Cnt AS "A's", R1.Cnt AS Enrollment, (R2.Cnt/R1.Cnt *100) AS Percentage
FROM (SELECT C.DNO, COUNT(StuID) Cnt
                FROM Course C,Enrolled_in E
                WHERE E.CID=C.CID
                GROUP BY C.DNO
           ) R1,
           (SELECT C.DNO, COUNT(StuID) Cnt
                FROM Course C,Enrolled_in E
                WHERE E.CID=C.CID AND (Grade='A+' OR Grade='A-' OR Grade='A')
                GROUP BY C.DNO
            ) R2, Department D
WHERE D.DNO=R1.DNO AND R1.DNO=R2.DNO;

+-----------------------+-----+------------+------------+
| DName                 | A's | Enrollment | Percentage |
+-----------------------+-----+------------+------------+
| Cognitive Science     |   5 |         18 |    27.7778 |
| ECE                   |   7 |         14 |    50.0000 |
| Mathematical Sciences |  18 |         45 |    40.0000 |
| Computer Science      |  56 |        133 |    42.1053 |
+-----------------------+-----+------------+------------+
4 rows in set (0.04 sec)

prompt ***********************
prompt solution to question 23
prompt in table Enrolled_in, there are two records for one student with 
the same course id
SELECT DISTINCT S1.fname, S1.lname, S2.fname, S2.lname, E1.cid
FROM Student S1,
     Student S2,
     Enrolled_in E1,
     Enrolled_in E2
WHERE S1.stuid>S2.stuid AND S1.fname=S2.fname AND S1.stuid=E1.stuid 
AND S2.stuid=E2.stuid AND E1.cid=E2.cid;
+---------+---------+---------+----------+---------+
| fname   | lname   | fname   | lname    | cid     |
+---------+---------+---------+----------+---------+
| Paul    | Brody   | Paul    | Gompers  | 600.107 |
| Paul    | Brody   | Paul    | Gompers  | 600.227 |
| Paul    | Brody   | Paul    | Gompers  | 600.303 |
| Lisa    | Cheng   | Lisa    | Apap     | 550.420 |
| Mark    | Goldman | Mark    | Schwartz | 550.420 |
| Mark    | Goldman | Mark    | Schwartz | 600.107 |
| Michael | Woods   | Michael | Leighton | 600.303 |
| Eric    | Brown   | Eric    | Pang     | 600.303 |
| Eric    | Brown   | Eric    | Rugh     | 550.420 |
| Eric    | Brown   | Eric    | Rugh     | 600.315 |
| Eric    | Epp     | Eric    | Rugh     | 600.315 |
| Eric    | Epp     | Eric    | Brown    | 600.315 |
+---------+---------+---------+----------+---------+
12 rows in set (0.06 sec)

prompt ***********************
prompt solution to question 24
SELECT Count(Student.StuID) 
FROM Student, Department, Preferences
WHERE Student.StuID = Preferences.StuID AND Department.DNO = Student.Major AND
	Preferences.Smoking = "yes" AND Department.DName = "Computer Science" AND 
	Student.StuID NOT IN (SELECT WhoLikes FROM Likes);

+----------------------+
| Count(Student.StuID) |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.06 sec)

prompt ***********************
prompt solution to question 25
SELECT Faculty.lname, Faculty.fname, Faculty.room, Faculty.building 
FROM Faculty, Member_of 
WHERE Faculty.facID=Member_of.facID AND Member_of.DNO IN 
	(SELECT Department.DNO FROM
	Department WHERE Department.Dname='Computer Science') 
	AND Member_of.appt_type='Secondary' AND Faculty.building != 'NEB';

+---------+-----------+------+----------+
| lname   | fname     | room | building |
+---------+-----------+------+----------+
| Brent   | Michael   | 288  | Krieger  |
| Jelinek | Frederick | 119  | Barton   |
+---------+-----------+------+----------+
2 rows in set (0.00 sec)

prompt ***********************
prompt solution to question 26

SELECT Fname, Lname
FROM Student, Enrolled_in, Minor_in, Department
WHERE Student.StuID = Minor_in.StuID AND Student.StuID = Enrolled_in.StuID AND
	(Minor_in.DNO = Department.DNO AND Dname = "Mathematics" OR Minor_in.DNO = Department.DNO AND
	Dname = "Mathematical Sciences" )AND (Enrolled_in.Grade = "A" OR Enrolled_in.Grade = "A+")
GROUP BY Student.StuID;

+-------+---------+
| Fname | Lname   |
+-------+---------+
| Paul  | Gompers |
| Jandy | Nelson  |
+-------+---------+
2 rows in set (0.00 sec)

prompt ***********************
prompt solution to question 27
SELECT FName, Lname
FROM Student, Lives_in, Has_amenity, Dorm_amenity, Preferences
WHERE Student.StuID = Preferences.StuID AND Student.StuID = Lives_in.StuID AND Lives_in.dormid = Has_amenity.dormid 
AND Has_amenity.amenid = Dorm_amenity.amenid AND amenity_name = "Working Fireplaces" AND Preferences.Smoking = "yes";

+--------+----------+
| FName  | Lname    |
+--------+----------+
| Jandy  | Nelson   |
| Derek  | Lee      |
| Susan  | Lee      |
| Ian    | Thornton |
| George | Andreou  |
| David  | Shieber  |
| Mark   | Goldman  |
+--------+----------+
7 rows in set (0.01 sec)

prompt ***********************
prompt solution to question 28
SELECT R.cid, R.cname, R.credits, G.lettergrade, G.gradepoint
FROM   (SELECT C.cid, C.cname, C.credits, E.grade
        FROM   Student S, Enrolled_in E, Course C
        WHERE  S.fname='Bruce' AND S.lname='Wilson' AND 
S.stuid=E.stuid AND E.cid = C.cid) R,
        Gradeconversion G
WHERE G.lettergrade=R.grade;

+---------+-----------------------------------------+---------+-------------+------------+
| cid     | cname                                   | credits | lettergrade | gradepoint |
+---------+-----------------------------------------+---------+-------------+------------+
| 600.461 | COMPUTER VISION                         |       3 | A           | 4   |
| 600.109 | INTRO TO PROGRAMMING IN C/C++           |       3 | A-          | 3.7 |
| 050.427 | THE HISTORY OF ROMANCE LANGUAGES        |       3 | B           | 3 |
| 050.670 | FORMAL METHODS IN COGNITIVE SCIENCE     |       3 | B           | 3 |
| 550.681 | NUMERICAL ANALYSIS                      |       3 | B           | 3 |
| 050.802 | RESEARCH SEMINAR IN COGNITIVE PROCESSES |       1 | C           | 2 |
| 600.465 | INTRO TO NATURAL LANGUAGE PROCESSING    |       3 | C           | 2 |
+---------+-----------------------------------------+---------+-------------+------------+
7 rows in set (0.05 sec)

prompt ***********************
prompt solution to question 29
SELECT sum(T.credits*T.gradepoint)/sum(T.credits) gpa
FROM  (SELECT R.cid, R.cname, R.credits, G.lettergrade, G.gradepoint
       FROM   (SELECT C.cid, C.cname, C.credits, E.grade
               FROM   Student S, Enrolled_in E, Course C
               WHERE  S.fname='Bruce' AND S.lname='Wilson' AND 
S.stuid=E.stuid AND E.cid = C.cid AND substr(C.cid,1,3)=S.major) R,
               Gradeconversion G
       WHERE G.lettergrade=R.grade) T;

+-----------------+
| gpa             |
+-----------------+
| 3.2333333492279 |
+-----------------+
1 row in set (0.02 sec)

PROMPT  ******************************************  
PROMPT Query 30
PROMPT ********************************************

   SELECT S1.stuid, SUM(C1.credits) AS Total_credits, 
	CAST(SUM( G1.gradepoint * C1.credits ) / SUM(C1.credits) AS decimal(1,1)) as gpa
   FROM Student S1,Course C1,Enrolled_in E1,Gradeconversion G1
   WHERE E1.stuid=S1.stuid
   AND   E1.cid=C1.cid
   AND   E1.grade=G1.lettergrade
   AND   C1.dno=S1.major
   GROUP BY S1.stuid;

+-------+---------------+------+
| stuid | Total_credits | gpa  |
+-------+---------------+------+
|  1001 |            13 |  3.2 |
|  1002 |            13 |  3.0 |
|  1003 |            15 |  3.1 |
|  1004 |            19 |  2.8 |
|  1005 |            11 |  2.9 |
|  1006 |            13 |  3.0 |
|  1007 |            15 |  3.1 |
|  1008 |            15 |  3.2 |
|  1010 |             3 |  3.3 |
|  1011 |             3 |  3.3 |
|  1012 |             6 |  3.5 |
|  1014 |            21 |  3.6 |
|  1015 |            10 |  3.3 |
|  1016 |             6 |  3.0 |
|  1017 |             9 |  3.2 |
|  1018 |            13 |  3.0 |
|  1019 |            11 |  2.9 |
|  1025 |             7 |  4.0 |
|  1026 |             6 |  4.0 |
|  1028 |             4 |  3.3 |
|  1030 |            20 |  2.9 |
|  1032 |             8 |  2.2 |
|  1034 |            10 |  3.1 |
|  1035 |            13 |  3.2 |
+-------+---------------+------+
24 rows in set (4.29 sec)


prompt ***********************
prompt solution to question 33
SELECT W.dno, W.musictype
FROM   (SELECT dno, P.musictype, count(P.musictype) AS musiccnt
        FROM   (SELECT dno, dname, count(dno) AS cnt
                FROM Department D, Student S
                WHERE D.dno=S.major
                GROUP BY dno, dname
                HAVING count(dno)>3) R,
                Student S,
                Preferences P
        WHERE S.major=R.dno AND S.stuid=P.stuid
        GROUP BY dno, P.musictype) W,
       (SELECT dno, max(T.musiccnt) musiccnt
        FROM   (SELECT dno, P.musictype, count(P.musictype) AS musiccnt
                FROM   (SELECT dno, dname, count(dno) AS cnt
                       FROM Department D, Student S
                       WHERE D.dno=S.major
                       GROUP BY dno, dname
                       HAVING count(dno)>3) R,
                       Student S,
                       Preferences P
               WHERE S.major=R.dno AND S.stuid=P.stuid
               GROUP BY dno, P.musictype ) T
       GROUP BY dno) U
WHERE W.dno=U.dno AND W.musiccnt=U.musiccnt;

+------+----------------+
| dno  | musictype      |
+------+----------------+
|  520 | StudiesWith    |
|  550 | StudiesWithout |
|  600 | StudiesWithout |
+------+----------------+
3 rows in set (0.02 sec)


--------------------------------------------------------------------------

QBE::
=====

7. List the names of all students with more than 1 food allergy
---------------------------------------------------------------------
STUDENT   | StuID     | Lname  | FName | Age | Sex | Major | Advisor |
----------|-----------|--------|-------|-----|-----|-------|---------|
          |  ._x      |  P.    |  P.   |     |     |       |         |

----------------------------------------
ALLERGY    | AllergyName | AllergyType |
-----------|-------------|-------------|
           |   _y        |    Food     |

----------------------------------------
HAS_ALLERGY|   StuID     | AllergyName |
-----------|-------------|-------------|
           |    _x       |     _y      |

---------------
|conditions   |
|-------------|
|CNT.ALL._y>1 |
---------------


15. List the name, age and major of all students enrolled in a class
taught by their advisor (also include the name of the advisor)
------------------------------------------------------------------
STUDENT   | StuID | Lname  | FName | Age | Sex | Major | Advisor |
----------|-------|--------|-------|-----|-----|-------|---------|
          |  _x   |   _a   |   _b  | _c  |     | _d    |  _z     |

--------------------------------------------------------------------------
COURSE   |  CID  | CName  |Credits| Instructor | Days  | Hours |   DNO   |
---------|-------|--------|-------|------------|-------|-------|---------|
         |  _y   |        |       |    _z      |       |       |         |

---------------------------------------
ENROLLED_IN | StuID  |  CID   | Grade |
------------|--------|--------|-------|
            |   _x   | _ y    |       |

--------------------------------------------------------------------------
FACULTY  | FacID | Lname  | Fname | Rank | Sex | Phone | Room | Building |
---------|-------|--------|-------|------|-----|-------|------|----------|
         |  _z   |   _e   |  _f   |      |     |       |      |          |

--------------------------------------------------------------
RESULT    | Lname  | FName | Age | Major | F_Lname | F_Lname |
----------|--------|-------|-----|-------|---------|---------|
   P.     |   -a   |   _b  | _c  |  _d   |  _e     |  _f     |


20.List the student name, course name, instructor name and letter grade
for all classes enrolled in by students who are early risers and have no
allergies and do not smoke
------------------------------------------------------------------
STUDENT   | StuID | Lname  | FName | Age | Sex | Major | Advisor |
----------|-------|--------|-------|-----|-----|-------|---------|
          |   _x  |  _a    |  _b   |     |     |       |   _y    |

--------------------------------------------------------------------------
COURSE   |  CID  | CName  |Credits| Instructor | Days  | Hours |   DNO   |
---------|-------|--------|-------|------------|-------|-------|---------|
         |   _z  |  _c    |       |            |       |       |         |

---------------------------------------
ENROLLED_IN | StuID  |  CID   | Grade |
------------|--------|--------|-------|
            |  _x    |   _z   |  _d   |

--------------------------------------------------------------------------
FACULTY  | FacID | Lname  | Fname | Rank | Sex | Phone | Room | Building |
---------|-------|--------|-------|------|-----|-------|------|----------|
         |  _y   |  _e    |  _f   |      |     |       |      |          |

--------------------------------------------------------------
PREFERENCES|  StuID      | SleepHabits | MusicType | Smoking |
-----------|-------------|-------------|-----------|---------|
           |    _x       | EarlyRiser  |           |  _w     |
           |             |             |           |         |
-------------
|conditions |
|-----------|
|_w!=3D Yes   |
-------------
----------------------------------------
HAS_ALLERGY|   StuID     | AllergyName |
-----------|-------------|-------------|
   ~       |    _x       |             |

*** ~ stands for not exit

---------------------------------------------------------------
RESULT    | Lname  | FName | CName | Grade | F_Lname | F_Fname |
----------|--------|-------|-------|-------|---------|---------|
    P.    |   _a   |   _b  |   _c  |   _d  |   _e    |   _f    |



21. List the name and age of both the oldest and the youngest student in
the database (include in the same table)
-------------------------------------------------------------------------
STUDENT   | StuID | Lname  | FName | Age        | Sex | Major | Advisor |
----------|-------|--------|-------|------------|-----|-------|---------|
          |  _x   |  P.    |   P.  |P.MAX.ALL._v|     |       |         |
          |  _y   |        |       |P.MIN.ALL._u|     |       |         |


22. For each department, list the total number of A's awarded in a
course offered by the department, the total combined enrollment for all
courses in the department, the percentage of awarded grades in the
department that are A- or above, the department's name 
--------------------------------------------------------------------------
COURSE   |  CID  | CName  |Credits| Instructor | Days  | Hours |   DNO   |
---------|-------|--------|-------|------------|-------|-------|---------|
         |  -u   |        |       |            |       |       |  G._d   |

------------------------------------------
ENROLLED_IN | StuID     |  CID   | Grade |
------------|-----------|--------|-------|
            |CNT.ALL._x |   -u   |  _y   |

---------------------
|   conditions      |
|-------------------|
|_y=3D(A or A- or A+) |
---------------------

-----------------------------------------
ENROLLED_IN | StuID     |  CID   | Grade |
------------|-----------|--------|-------|
            |CNT.ALL._w |   _c   |       |

--------------------------------------------------------------------------
COURSE   |  CID  | CName  |Credits| Instructor | Days  | Hours |   DNO   |
---------|-------|--------|-------|------------|-------|-------|---------|
         |   _c  |        |       |            |       |       |    _d   |

------------------------------------------------------------------
DEPARMENT |  DNO  |Division| DName | Room   | Building  | DPhone |
----------|-------|--------|-------|--------|-----------|--------|
          |   _d  |        | _e    |        |           |        |

------------------------------
CHAIRED_BY |  DNO  |  FacID  |
-----------|-------|---------|
           |  _d   |   _f    |

--------------------------------------------------------------------------
FACULTY  | FacID | Lname  | Fname | Rank | Sex | Phone | Room | Building |
---------|-------|--------|-------|------|-----|-------|------|----------|
         |   _f  |  _a    |  _b   |      |     |       |      |          |

-------------------------------------------------------------------------
RESULT   | DName  |Chr_Fname|Chr_Lname|  Totl_A  | Totl_en  | Percentage|
---------|--------|---------|---------|----------|----------|-----------|
   P.    |  _e    |   _a    |    _b   |CNT.ALL._x|CNT.ALL._w|    _p     |
         |        |         |         |

---------------------------------
|conditions                     |
|-------------------------------|
|_p=3D(CNT.ALL._x/CNT.ALL._w)*100%|
---------------------------------