Sample solutions to Assignment 2 - SQL

1.	SELECT S.fname, S.lname,S.age,S.sex
	FROM Student S, Lives_in L
	WHERE L.stuid=S.stuid AND NOT  EXISTS
                                (SELECT S2.stuid
                                FROM Student S2,Lives_in L2
                                WHERE L2.stuid=S2.stuid
                                        AND S2.stuid<>S.stuid
                                        AND L2.dormid=L.dormid
                                        AND L2.room_number=L.room_number);

2.	SELECT  P.fname, P.lname, P.sex
        	FROM   ((SELECT  S.fname, S.lname, S.sex
                FROM   Student S, Enrolled_in E, Course C, Department D
                WHERE  S.stuid=E.stuid AND C.cid=E.cid
                       AND C.dno=D.dno AND D.dname= 'Computer Science' )
		 UNION
                (SELECT F.fname, F.lname, F.sex
                FROM    Faculty F, Course R, Department T
                WHERE   R.instructor=F.facid
                        AND R.dno=T.dno AND T.dname='Computer Science' )) P;


3.	SELECT DISTINCT S.fname, S.lname, S.sex, S.major
 	 FROM Student S
 	 WHERE S.stuid = (SELECT L.wholikes
                   FROM Likes L
                   GROUP BY L.wholikes
                   HAVING count(L.whoisliked) = (SELECT count(stuid)
                                                 FROM Student));

4.	SELECT S.fname, S.lname, S.age, S.major, sum(C.credits) as numbercredits
     	 FROM Student S, Enrolled_in E, Course C
   	 WHERE S.stuid = E.stuid and C.cid = E.cid
    	GROUP BY S.fname, S.lname, S.major, S.age;

5.	SELECT S.fname, S.lname, S.major
  	 FROM Student S, Enrolled_in E, Course C
   	WHERE S.stuid = E.stuid AND C.cid = E.cid
   	GROUP BY S.fname, S.lname, S.major
   	HAVING sum(C.credits) > 20;


6.	SELECT  distinct f.fname||' '||f.lname Faculty_Name
  	FROM Student s1, Student s2, Faculty f, Enrolled_in ei
 	WHERE s1.advisor = f.facid
    	AND s1.stuid = ei.stuid
      	AND s2.stuid IN (
                       SELECT stuid
                       FROM Enrolled_in
                       WHERE cid = ei.cid
                         AND stuid != s1.stuid
                     )
     	AND s2.stuid IN (
                      SELECT l1.stuid
                      FROM lives_in l1, lives_in l2,
                           Student s, Minor_in mi, member_of mo
                      WHERE l1.stuid < l2.stuid
                        AND l1.dormid = l2.dormid
                        AND l1.room_number = l2.room_number
                        AND s.stuid = l2.stuid
                        AND mi.stuid = l2.stuid
                        AND ( s.major IN (
                                           SELECT dno
                                           FROM member_of
                                           WHERE facid = f.facid
                                          )
                              or
                              mi.dno IN  (
                                           SELECT dno
                                           FROM member_of
                                           WHERE facid = f.facid
                                         )
                            )
                     );

7.	SELECT temp1.fname,temp1.lname
 	 FROM
       	 (
       	     SELECT s1.fname,s1.lname,s1.major,COUNT(s2.stuid) ans
            FROM student s1,student s2
            WHERE (s1.stuid,s2.stuid)
            IN
                (
                        SELECT wholikes,whoisliked
                        FROM likes
                )
                and s1.major = s2.major and s1.stuid <> s2.stuid
            GROUP BY s1.stuid,s1.fname,s1.lname,s1.major
        )temp1,
        (
                SELECT s3.major,COUNT(s3.stuid) ans
                FROM student s3
                GROUP BY s3.major
        )temp2
 	 WHERE temp1.major=temp2.major and temp1.ans=temp2.ans-1;



8.	SELECT dname,total
  	FROM department d,
                (
                 SELECT dep,SUM(cnt) total
                 FROM
                 (
                        SELECT c.dno dep,(k.ans * c.credits) cnt
                        FROM course c,
                        (
                                SELECT cid,COUNT(stuid) ans
                                FROM enrolled_in GROUP BY cid
                        )k
                        WHERE c.cid = k.cid
                )GROUP BY dep
	)res WHERE d.dno=res.dep;



9.	SELECT R.fname, R.lname, R.age
	FROM Student R
	WHERE R.stuid NOT IN ( SELECT S.stuid
                       FROM  Student S, Student T
                       WHERE S.age<T.age AND S.major=T.major);


10.
    select EI.CID,CName,BelowB,EnrolledCount,
           BOrBelow*1.0/EnrolledCount*100 as PercentBOrBelow,
           Fname,Lname
    from (select CID,count(if(gradepoint < 3,1,NULL)) BelowB,
                     count(if(gradepoint <= 3,1,NULL)) BOrBelow,
                     count(*) EnrolledCount
          from (select CID,CourseGrades.Grade,gradepoint
                from (select CID,Grade from Enrolled_in) as CourseGrades
                join Gradeconversion GC
                on CourseGrades.Grade=GC.lettergrade) as G
          group by CID) EI,Course C,Faculty F
    where EI.CID=C.CID and C.Instructor=F.FacID;


11.	SELECT s1.stuid, s1.fname , s1.lname , s2.stuid, s2.fname , s2.lname
 	  FROM student s1 , student s2
 	  WHERE (s1.stuid,s2.stuid)
 	  IN
      	  (
                SELECT a.wholoves,a.whoisloved
                FROM loves a,loves b
                WHERE a.wholoves=b.whoisloved and a.whoisloved=b.wholoves and a.wholoves 	!= a.whoisloved and a.wholoves < a.whoisloved );



12.	SELECT S1.fname, S1.lname, S1.sex, S2.fname, S2.lname, S2.sex
	FROM   Student S1, Student S2, Loves L1, Loves L2, Lives_in V1,Lives_in V2
	WHERE  L1.wholoves=S1.stuid AND L1.whoisloved=S2.stuid
   	    AND L2.wholoves=S2.stuid AND L2.whoisloved=S1.stuid
    	   AND S1.stuid<S2.stuid
     	  AND V1.stuid=S1.stuid AND V2.stuid=S2.stuid
     	  AND V1.dormid=V2.dormid
    	    AND V1.room_number=V2.room_number;

13.	SELECT S1.fname, S1.lname
	FROM Student S1
	WHERE S1.stuid IN
                           (SELECT L1.wholoves
                                FROM Loves L1
                                WHERE NOT EXISTS
	 (SELECT *FROM Loves L2
           WHERE L2.wholoves=L1.whoisloved AND L2.whoisloved=L1.wholoves));


14.	

    SELECT S3.Fname as LoverFname, S3.Lname as LoverLname,
           S1.Fname as Roomie1Fname, S1.Lname as Roomie1Lname,
           S2.Fname as Roomie2Fname, S2.Lname as Roomie2Lname
    FROM (select *
          from (select distinct L1.StuID Roomie1,L2.StuID Roomie2
                from Lives_in L1,Lives_in L2
                where L1.dormid=L2.dormid
                      and L1.room_number=L2.room_number
                      and L1.StuID < L2.StuID) as Roomies
          join (select Lov1.WhoLoves,Lov1.WhoIsLoved Love1,Lov2.WhoIsLoved Love2
                from Loves as Lov1,Loves as Lov2
                where Lov1.WhoLoves=Lov2.WhoLoves
                      and Lov1.WhoIsLoved < Lov2.WhoIsLoved) as LovePairs
          on Roomies.Roomie1=LovePairs.Love1
             and Roomies.Roomie2=LovePairs.Love2) as ThirdPartyLove,
        Student S1,Student S2,Student S3
    WHERE S1.StuID=Roomie1 and S2.StuID=Roomie2 and S3.StuID=WhoLoves;
    

15.    SELECT T4.fname,T4.lname,T4.advisor,T4.credits
        FROM
        (SELECT S1.major AS major, AVG(T1.credits) AS avgcredits
        FROM (SELECT E1.stuid AS stuid, SUM (C1.credits) AS credits
        FROM Enrolled_in E1,Course C1
        WHERE E1.Cid=C1.cid
        GROUP BY E1.stuid)T1,
        Student S1
        WHERE S1.stuid=T1.stuid
        GROUP BY S1.major) T3,
        (SELECT S2.stuid As stuid,
        S2.lname As lname,
        S2.fname AS fname,
        S2.advisor AS advisor,
        S2.major AS major,
        T2.credits AS credits
        FROM (SELECT E1.stuid AS stuid, SUM (C1.credits) AS credits
        FROM Enrolled_in E1,Course C1
        WHERE E1.Cid=C1.cid
        GROUP BY E1.stuid)T2,
        Student S2
        WHERE S2.stuid=T2.stuid)T4
        WHERE T3.major=T4.major AND T4.credits>T3.avgcredits;
OR
    select StuID,Student,Advisor,AdvName,CreditsEnrolled,SInfo.Major,AvgCreditsEnrolled
    from (select S.StuID,concat(S.Fname," ",S.Lname) as Student,Major,
                 Advisor,concat(F.Fname," ",F.Lname) as AdvName,
                 sum(Credits) as CreditsEnrolled
          from Enrolled_in E,Course C,Student S,Faculty F
          where E.StuID=S.StuID and E.CID=C.CID and S.Advisor=F.FacID
          group by StuID) as SInfo
    join (select Major,avg(CreditsEnrolled) AvgCreditsEnrolled
          from (select Major,sum(Credits) as CreditsEnrolled
                from Enrolled_in E,Course C,Student S,Faculty F
                where E.StuID=S.StuID and E.CID=C.CID and S.Advisor=F.FacID
                group by S.StuID) as CredByMajor
          group by Major) as AvgCredits
    on SInfo.Major=AvgCredits.Major
    where CreditsEnrolled > AvgCreditsEnrolled;
    



16.    SELECT S.fname,S.lname
        FROM Student S
        WHERE S.stuid IN
       (SELECT T1.stuid
        FROM
        (SELECT L1.whoisliked AS stuid,COUNT (*) AS likednum
        FROM Likes L1
        GROUP BY L1.whoisliked)T1
        WHERE T1.likednum=(SELECT MAX (T2.likednum)
        FROM (SELECT L1.whoisliked AS stuid,COUNT (*) AS likednum
                                                        FROM Likes L1
                GROUP BY L1.whoisliked)T2));

    OR
      select WhoIsLiked,concat(Fname," ",Lname) Name,HowManyLikeThem
      from (select *
            from (select WhoIsLiked,count(*) HowManyLikeThem
                  from Likes
                  group by WhoIsLiked) as Likable
            where HowManyLikeThem in (select max(HowManyLikeThem) mHM
                                      from (select WhoIsLiked,count(*) HowManyLikeThem
                                            from Likes
                                            group by WhoIsLiked) as Likable
                                      )
            ) as MaxLiked,Student S
      where MaxLiked.WhoIsLiked=S.StuID;
      


17.	SELECT S2.fname,S2.lname,T2.likedorlovednum AS likedorlovednum
	FROM
	(SELECT S1.stuid AS stuid,S1.stuid-S1.stuid AS likedorlovednum
	FROM Student S1
	WHERE (NOT EXISTS (SELECT *
                                        FROM Likes LI1
                                        WHERE LI1.whoisliked=S1.stuid))
                AND
                (NOT EXISTS (SELECT *FROM Loves LO1 WHERE LO1.whoisloved=S1.stuid))
	UNION
	SELECT T1.whoislikedorloved AS stuid,COUNT(*) AS likedorlovednum
	FROM
	(SELECT LI2.wholikes AS wholikesorloves, LI2.whoisliked AS whoislikedorloved
	FROM Likes LI2
	UNION
	SELECT LO2.wholoves AS wholikesorloves,LO2.whoisloved AS whoislikedorloved
	FROM Loves LO2)T1
	GROUP BY T1.whoislikedorloved) T2,
	Student S2
	WHERE S2.stuid=T2.stuid
	ORDER BY likedorlovednum DESC;

18.	SELECT S1.fname,S1.lname,S1.age
	FROM Student S1
	WHERE S1.age>(SELECT AVG(S2.age)
         FROM Student S2);

19.	SELECT c.cname,f.fname,f.lname,COUNT(e.cid) "TOTAL ENROLLMENT"
   	 FROM course c , enrolled_in e ,faculty f
    	WHERE f.facid = c.instructor and c.cid = e.cid
    	GROUP BY c.cid,c.cname,f.fname,f.lname
    	HAVING COUNT(e.cid) > ALL(
                                SELECT AVG(COUNT(cid))
                                FROM enrolled_in
                                GROUP BY cid
                             );

OR:

      SELECT TotalEnrollment,CName Course,concat(FName," ",LName) as Instructor
      FROM (SELECT *
            FROM (SELECT count(*) TotalEnrollment,CID
                  FROM Enrolled_in
                  GROUP BY CID) as Enrollment
            WHERE TotalEnrollment > ALL (SELECT avg(NumEnrolled) as AvgEnrollment
                                     FROM (SELECT count(*) NumEnrolled,CID
                                           FROM Enrolled_in
                                           GROUP by CID) as Enrollment
                                     )
            ) as CInfo,Course C,Faculty F
      WHERE CInfo.CID=C.CID and C.Instructor=F.FacID;
      

20.	SELECT S1.fname,S1.lname
	FROM Student S1
	WHERE S1.stuid IN
                        (SELECT L3.wholikes
                         FROM Likes L3
                         WHERE L3.whoisliked IN
	(SELECT L1.wholikes
	FROM Likes L1
	WHERE L1.whoisliked IN
       	(SELECT L2.wholikes FROM Likes L2
        WHERE L2.whoisliked IN
         (SELECT S2.stuid
          FROM Student S2 WHERE S2.lname='Smith'
	AND S2.fname='Linda'))));


21.    SELECT stud.fname , stud.lname
   	 FROM student stud
    	WHERE stud.stuid IN
    	(
        (
        SELECT distinct l2.wholikes
        FROM likes l2
        WHERE l2.whoisliked IN
                (
                        (
                        SELECT distinct l1.wholikes
                        FROM likes l1
                        WHERE l1.whoisliked IN
                        (
                                SELECT stuid FROM student WHERE fname = 'Linda' 
				and lname = 'Smith'
                        )
                        )
                        UNION
                        (
                                SELECT distinct e1.stuid
                                FROM enrolled_in e1 , enrolled_in e2
                                WHERE e1.stuid <> e2.stuid and e1.cid = e2.cid 
				and e2.stuid in
                                (
                                        SELECT stuid FROM student WHERE fname =
					'Linda' and lname = 'Smith'
                                )
                        )
                        )
                )
                UNION
                (
                 SELECT distinct e3.stuid
                 FROM enrolled_in e3 , enrolled_in e4
                 WHERE e3.stuid <> e4.stuid and e3.cid = e4.cid and e4.stuid in
                 (
                        (
                        SELECT distinct l1.wholikes
                        FROM likes l1
                        WHERE l1.whoisliked IN
                        (
                                SELECT stuid FROM student WHERE  fname = 'Linda'
				 and lname = 'Smith'
                        )
                 )
                 UNION
                 (
                  SELECT distinct e1.stuid
                  FROM enrolled_in e1 , enrolled_in e2
                  WHERE e1.stuid <> e2.stuid and e1.stuid = e2.stuid and e2.stuid in
                  (
                        SELECT stuid FROM student WHERE  fname = 'Linda' and lname = 			'Smith'
                  )
                  )
            )
        )
    );



22. 

  SELECT D.Division, count(E.CID) as TotalEnrollment
  FROM   Enrolled_in E, Course C, Department D
  WHERE  E.CID = C.CID and C.DNO = D.DNO
  GROUP BY D.Division;


#23

select DName,DeptStuCount
from (select DNO,sum(StuCount) as DeptStuCount
      from (select DNO,CD.CID,count(distinct(StuID)) as StuCount
             from Course as CD
             join Enrolled_in as E
             on CD.CID=E.CID
             group by CD.CID) as CE
        group by DNO) as DE, Department as Dept
       where DE.DNO = Dept.DNO
             and DeptStuCount = (select min(DeptStuCount)
                                 from (select DNO,sum(StuCount) as DeptStuCount
                                       from (select DNO,CD.CID,count(distinct(StuID)) as StuCount
                                             from Course as CD
                                             join Enrolled_in as E on CD.CID=E.CID
                                             group by CD.CID) as CE
                      			     group by DNO) as DE);

#24

select Dname as Major,Fname,Lname,GPA
from (select Student.StuID,Fname,Lname,Major,GPA
     from Student 
     join (select StuID,GPA 
     	   from (select StuID,Accrued/Attempted as GPA
	         from (select StuID,
           	              sum(credits*gradepoint) as Accrued,
              		      sum(credits) as Attempted
            	       from (select StuID,C.CID,Credits,Grade
                       	     from Course as C
                  	     join Enrolled_in as E
			     on C.CID=E.CID) as Grades
		       join Gradeconversion as GC
		       on Grades.Grade=GC.lettergrade
            	       group by StuID) as TmpGrades
		 ) as StudentGPA
	  join (select max(GPA) as mGPA,Major 
	       from (select SGPA.StuID,GPA,LName,FName,Major 
	       	    from (select StuID,Accrued/Attempted as GPA
		          from (select StuID,
                   	       	       sum(credits*gradepoint) as Accrued,
                   		       sum(credits) as Attempted
				from (select StuID,C.CID,Credits,Grade
                  		     from Course as C
                  		     join Enrolled_in as E
                  		     on C.CID=E.CID) as Grades
            			join Gradeconversion as GC
            			on Grades.Grade=GC.lettergrade
            			group by StuID) as TmpGrades
       			 ) as SGPA,Student as S 
		    where SGPA.StuID=S.StuID) as SInfo 
	       group by Major) as MMaxGPA 
	  on StudentGPA.GPA=MMaxGPA.mGPA) as StuMaxGPA 
     on Student.StuID=StuMaxGPA.StuID) as BestStudents 
join Department as D 
on BestStudents.Major=D.DNO;

#25

# N/A, all dorms have 0 students not liked by anyone (all students listed are on the WhoIsLiked list.)

#26

select NumStu,Allergy,dorm_name
from (select NumStu,Allergy,NSwA.dormid
     from (select max(NumStu) as mNumStu,dormid
          from (select count(*) as NumStu,Allergy,dormid
               from Student as S,Has_Allergy as A,Lives_in as L
                where S.StuID=A.StuID and A.StuID=L.StuID
                group by dormid,Allergy) as NSwA
          group by dormid) as mNSwA
     join (select count(*) as NumStu,Allergy,dormid
          from Student as S,Has_Allergy as A,Lives_in as L
          where S.StuID=A.StuID and A.StuID=L.StuID
          group by dormid,Allergy) as NSwA
     on mNSwA.mNumStu=NSwA.NumStu and mNSwA.dormid=NSwA.dormid) as maxAllergy
join Dorm on maxAllergy.dormid=Dorm.dormid;

#27

select distinct E.StuID,LName,FName
from (select StuID,LName,FName
     from Student
     where StuId not in (select StuID from Participates_in)) as NoPart
join Enrolled_in as E
on NoPart.StuID=E.StuID

where E.StuID not in (select StuID
                   from (select StuID,LName,FName,max(gradepoint) as mGP
                        from (select S.StuID,LName,FName,Grade
                              from Student as S
                              join Enrolled_in as E
                              on S.StuID=E.StuID) as StuGrades
                        join Gradeconversion as GC
                        on StuGrades.Grade=GC.lettergrade
                        group by StuID) as StuGrades
                   where mGP <= (select gradepoint from Gradeconversion where lettergrade='B+'));

#28

select StuID,LName,FName,max(gradepoint) as mGP
from (select S.StuID,LName,FName,Grade
      from Student as S
      join Enrolled_in as E
      on S.StuID=E.StuID) as StuGrades
join Gradeconversion as GC
on StuGrades.Grade=GC.lettergrade
where StuID in
(select distinct E.StuID as StuID
from (select StuID,LName,FName
     from Student
     where StuId not in (select StuID from Participates_in)) as NoPart
join Enrolled_in as E
on NoPart.StuID=E.StuID
where E.StuID not in (select StuID
                   from (select StuID,LName,FName,max(gradepoint) as mGP
                        from (select S.StuID,LName,FName,Grade
                              from Student as S
                              join Enrolled_in as E
                              on S.StuID=E.StuID) as StuGrades
                        join Gradeconversion as GC
                        on StuGrades.Grade=GC.lettergrade
                        group by StuID) as StuGrades
                   where mGP <= (select gradepoint from Gradeconversion where lettergrade='B+')))
group by StuID;

#29

# female faculty and their students

select CID,CName,
       FTaughtStu.Fname as FacFname,
       FTaughtStu.Lname as FacLname,
       S.Fname as StuFname,
       S.Lname as StuLname 
from (select E.CID,CName,Fname,LName,StuID 
     from (select CID,CName,FName,LName 
     	  from Course as C 
	  join (select * 
	       from Faculty 
	       where Sex='F') as FemFac 
	  on C.Instructor=FemFac.FacID) as FTaughtCourse 
     join Enrolled_in as E 
     on FTaughtCourse.CID=E.CID) as FTaughtStu 
join Student as S 
on FTaughtStu.StuID=S.StuID 
where Sex='F';

# just the course names

select distinct(CID),Cname 
from (select CID,CName,
     	     FTaughtStu.Fname as FacFname,
	     FTaughtStu.Lname as FacLname,
	     S.Fname as StuFname,
	     S.Lname as StuLname 
       from (select E.CID,CName,Fname,LName,StuID 
       	    from (select CID,CName,FName,LName 
	    	 from Course as C 
		 join (select * 
		       from Faculty 
		       where Sex='F') as FemFac 
		 on C.Instructor=FemFac.FacID) as FTaughtCourse 
	    join Enrolled_in as E 
	    on FTaughtCourse.CID=E.CID) as FTaughtStu 
       join Student as S 
       on FTaughtStu.StuID=S.StuID 
       where Sex='F') as FF;

#30

# this will get you the right answer, but is technically incorrect because it finds the department of the class rather than the faculty member

select Fname,Lname,DName
from (select distinct FacID,Fname,Lname,DNO
     from Faculty as F
     join Course as C
     on F.FacID=C.Instructor
     where C.Cname like "%Intro%") as FacTeachIntro
join Department as D 
on FacTeachIntro.DNO=D.DNO;

# correct answer

select Fname,Lname,Dname 
from (select Fname,Lname,DNO 
     from (select distinct FacID,Fname,Lname 
     	  from Faculty as F 
	  join Course as C 
	  on F.FacID=C.Instructor 
	  where C.Cname like "%Intro%") as FacTeachIntro 
     join Member_of as Mo
     on FacTeachIntro.FacID=Mo.FacID 
     where Appt_Type="Primary") as PrimApptIntro 
join Department as D on PrimApptIntro.DNO=D.DNO;

#31

select DName,NumIntroFac 
from (select sum(FacTeachIntro) as NumIntroFac,DNO 
     from (select FacID in (select distinct FacID 
     	  	  	   from Faculty as F 
			   join Course as C 
			   on F.FacID=C.Instructor 
			   where C.Cname like "%Intro%") as FacTeachIntro,FacID 
           from Faculty) as TeachIntro 
     join Member_of as Mo 
     on TeachIntro.FacID=Mo.FacID 
     where Appt_Type="Primary" 
     group by DNO) as NIF 
join Department as D 
on NIF.DNO=D.DNO;

#32

select Fname,Lname,AllergyCount 
from (select * 
     from (select count(StuID) as AllergyCount,StuID 
     	  from Has_allergy 
	  group by StuID) as AC 
     where AllergyCount in (select max(AllergyCount) as mAC 
     	   		    from (select count(StuID) as AllergyCount,StuID 
			    	 from Has_allergy 
				 group by StuID) as AC
			    )
     ) as maxACStu 
join Student as S 
on maxACStu.StuID=S.StuID;

#33

select Fname,Lname,NumGradesBelow
from (select StuID,NumGradesBelow
      from (select count(*) as NumGradesBelow,StuID
      	    from Enrolled_in as E
	    join Gradeconversion as GC
	    on E.Grade=GC.lettergrade
	    where GC.gradepoint < (select gradepoint 
	    	  		   from Gradeconversion 
				   where lettergrade="C-") 
	    group by StuID) as GBC
      where NumGradesBelow in (select max(NumGradesBelow) as mNGBC 
      	    		       from (select count(*) as NumGradesBelow,StuID 
			       	     from Enrolled_in as E 
				     join Gradeconversion as GC 
				     on E.Grade=GC.lettergrade 
				     where GC.gradepoint < (select gradepoint 
				     	   		    from Gradeconversion 
							    where lettergrade="C-") 
				     group by StuID) as GBC
			       )
      ) as LowStu 
join Student as S 
on LowStu.StuID=S.StuID;

#34

select avg(GPA) as AverageSmokerGPA 
from (select StuID,Accrued/Attempted as GPA 
      from (select StuID,
                   sum(credits*gradepoint) as Accrued,
		   sum(credits) as Attempted 
            from (select StuID,C.CID,Credits,Grade 
	          from Course as C 
		  join Enrolled_in as E 
		  on C.CID=E.CID) as Grades 
            join Gradeconversion as GC 
	    on Grades.Grade=GC.lettergrade 
	    group by StuID) as TmpGrades
       ) as StuGPA 
join (select distinct StuID 
      from Preferences 
      where Smoking like "Yes") as Smokers 
on StuGPA.StuID=Smokers.StuID;

#35

select avg(if(StuGPA.StuID in (select distinct StuID 
       			       from Preferences 
			       where Smoking like "Yes"),
	      GPA,
	      NULL)) as AverageSmokerGPA,
       avg(if(StuGPA.StuID in (select distinct StuID 
       			       from Preferences 
			       where Smoking like "no%"),
	      GPA,
	      NULL)) as AverageNonSmokerGPA
from (select StuID,Accrued/Attempted as GPA 
      from (select StuID,
                   sum(credits*gradepoint) as Accrued,
		   sum(credits) as Attempted 
            from (select StuID,C.CID,Credits,Grade 
	          from Course as C 
		  join Enrolled_in as E 
		  on C.CID=E.CID) as Grades 
	    join Gradeconversion as GC 
	    on Grades.Grade=GC.lettergrade 
	    group by StuID) 
      as TmpGrades)
 as StuGPA;

#36

select StuID,FName,LName,Major,DName as Minor,State,Age 
from (select StuID,FName,LName,DName as Major,Minor,State,Age 
      from (select distinct MDOlds.StuID,FName,LName,Major,State,Age,DNO as Minor 
            from (select StuID,FName,LName,Major,State,Age 
	          from Student as S 
		  join City as C 
		  on S.city_code=C.city_code 
		  where Age >= 20 and State="MD") as MDOlds 
            left join Minor_in as MI 
	    on MDOlds.StuID=MI.StuID) as Stu 
      join Department as D 
      on Stu.Major=D.DNO) as PartStu 
join Department as D 
on PartStu.Minor=D.DNO;

#37

select * 
from (select count(*) as NumBoyNames,FName 
      from Student 
      where Sex="M" 
      group by FName) as NBN 
where NumBoyNames in (select max(NumBoyNames) as mNBN 
      		      from (select count(*) as NumBoyNames,FName 
		      	    from Student 
			    where Sex="M" 
			    group by FName) 
		      as NBN);

#38

select * 
from (select count(*) as NumBoyNames,FName 
      from (select * 
      	    from (select FName,Sex from Student 
	    	 union all 
		 select FName,Sex from Faculty) as U 
	    where Sex="M") as SFUnion 
      group by FName) as NBN 
where NumBoyNames in (select max(NumBoyNames) as mNBN 
      		      from (select count(*) as NumBoyNames,FName 
		      	    from (select * 
			    	  from (select FName,Sex from Student 
				        union all 
					select FName,Sex from Faculty) as U 
				  where Sex="M") as SFUnion 
			    group by FName)
		       as NBN);

#39
select FName1,LName1,FName as FName2,LName as LName2 from
(select FName as FName1,LName as LName1,Lover2 from
(select distinct Lover1,Dorm1,Room1,Lover2,Dorm2,Room2 
from (select Lover1,Dorm1,Room1,Lover2,D.dormid as Dorm2,D.room_number as Room2 
      from (select Lover1,Lover2,D.dormid as Dorm1,D.room_number as Room1 
            from (select L1.WhoLoves as Lover1,L1.WhoIsLoved as Lover2 
	    	  from Loves as L1 
		  join Loves as L2 
		  on L1.WhoLoves=L2.WhoIsLoved and L1.WhoIsLoved=L2.WhoLoves 
		  where L1.WhoLoves < L1.WhoIsLoved) as Lp1 
	    join Lives_in as D 
	    on Lp1.Lover1=D.StuID) as Lp2 
      join Lives_in as D 
      on Lp2.Lover2=D.StuID) as LI 
where LI.Dorm1=LI.Dorm2 or LI.Room1=LI.Room2) as LoverPairs 
join Student as S
on LoverPairs.Lover1=S.StuID) as LoverPairs
join Student as S
on LoverPairs.Lover2=S.StuID;