Sample solutions to Assignment 2 - SQL

1.	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));

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 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);

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 e1.cid, count(*) award_num
             FROM Enrolled_in e1
            WHERE e1.grade IN (
                                 SELECT lettergrade
                                FROM Gradeconversion
                               WHERE gradepoint <3
                              )
            GROUP BY e1.cid
            )
           union
           (
           SELECT x.cid, x.award_num
           FROM (
                  SELECT cid, 0 award_num
                  FROM Course
                  GROUP BY cid
                ) x
           WHERE x.cid IN (
                            (
                              SELECT distinct cid
                              FROM Enrolled_in
                            )
                            MINUS
                            (
                              SELECT cid
                              FROM Enrolled_in
                              WHERE grade IN (
                                               SELECT lettergrade
                                              FROM Gradeconversion
                                               WHERE gradepoint <3
                                             )
                            )
                          )
          )
        ) t1,
        (
          (
            SELECT ee1.cid, count(*) award_num
            FROM Enrolled_in ee1
            WHERE ee1.grade IN (
                                 SELECT lettergrade
                                 FROM Gradeconversion
                                 WHERE gradepoint <=3
                               )
            GROUP BY ee1.cid
          )
          union
          (
            SELECT x1.cid, x1.award_num
            FROM (
                   SELECT cid, 0 award_num
                   FROM Course
                   GROUP BY cid
                 ) x1
            WHERE x1.cid IN (
                              (
                               SELECT distinct cid
                               FROM Enrolled_in
                              )
                              MINUS
                              (
                               SELECT cid
                              FROM Enrolled_in
                               WHERE grade IN (
                                                SELECT lettergrade
                                                FROM Gradeconversion
                                                WHERE gradepoint <=3
                                              )
                               )
                             )
          )
        ) t2,
        ( SELECT e2.cid cid, count(*) total_num
          FROM Enrolled_in e2 GROUP BY e2.cid
        ) t3,
          Course c, Faculty f
   	WHERE t1.cid = t3.cid
     	AND t2.cid = t3.cid
  	   AND c.cid = t1.cid
  	   AND c.cid = t2.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 S1.fname AS fname1, S1.lname AS lname1, S2.fname AS fname2, S2.lname AS l
	name2
	FROM (SELECT L1.stuid AS stuid1, L2.stuid AS stuid2
	FROM Lives_in L1,Lives_in L2
	WHERE L1.dormid=L2.dormid
	AND L1.room_number=L2.room_number
	AND NOT L1.stuid=L2.stuid
	AND EXISTS (SELECT *FROM Loves LO1,Loves LO2
                         WHERE LO1.wholoves=LO2.wholoves
	AND LO1.whoisloved=L1.stuid
	AND LO2.whoisloved=L2.stuid))T1,
                Student S1,Student S2
	WHERE S1.stuid=T1.stuid1 AND S2.stuid=T1.stuid2 AND S1.stuid-S2.stuid>0;

15.	SELECT distinct fname||' '||lname Name, sex Gender
  	FROM Student
  	WHERE stuid IN (
                  SELECT wholikes
                  FROM Likes
                  WHERE whoisliked IN (
                                        SELECT stuid
                                        FROM Student
                                        WHERE fname = 'Linda'
                                          AND lname = 'Smith'
                                       )
                    AND wholikes not IN (
                                          SELECT wholoves
                                          FROM Loves
                                          WHERE whoisloved IN (
                                                               SELECT stuid
                                                               FROM Student
                                                               WHERE fname = 'Linda'
                                                                 AND lname = 'Smith'
                                                               )
                                         )
                 );

16.	SELECT s.fname,s.lname
   	 FROM student s
   	 WHERE s.stuid
  	  IN
   	     (
                SELECT p1.stuid
                FROM preferences p1,preferences p2
                WHERE  p1.stuid != p2.stuid and ( p1.smoking != p2.smoking or 				p1.sleephabits != p2.sleephabits) and (p1.stuid,p2.stuid)
                        IN
                                (
                                        SELECT a.wholoves ,a.whoisloved
                                        FROM loves a , loves b
                                        WHERE a.wholoves = b.whoisloved and b.wholoves = 	a.whoisloved and a.wholoves != b.wholoves
                                )
        );


17.	SELECT T.fname, T.lname,T.sex, K.liked_by
	FROM  Student T,
        (SELECT S.stuid,COUNT(wholikes) liked_by
        FROM   Student S, Likes
        WHERE whoisliked=S.stuid AND wholikes<>S.stuid
        GROUP BY S.stuid) K,
        (SELECT MAX(K.liked_by) maxlike
         FROM (SELECT S.stuid,COUNT(wholikes) liked_by
                FROM   Student S, Likes
                WHERE whoisliked=S.stuid AND wholikes<>S.stuid
                GROUP BY S.stuid) K
         ) P
 	WHERE T.stuid=K.stuid AND K.liked_by=P.maxlike;
	

 
18.	SELECT  stud.fname,stud.lname,stud.age,stud.sex,stud.major,likespeoplecount,
	likecount,(likecount/(temp3-likecount))ratio FROM student stud,
                (
                SELECT wholikes,likecount,likespeoplecount
                FROM
                (
                        (
                                SELECT whoisliked,COUNT(*) likecount
                                FROM likes
                                GROUP BY whoisliked
                        )
                        UNION
                        (
                                select stuid whoisliked,0 likecount from student
                        WHERE stuid not in (select whoisliked from likes)
                )
                ),
                (
                        (SELECT wholikes,COUNT(*) likespeoplecount
                        FROM likes
                        GROUP BY wholikes) UNION (select stuid wholikes,0 	likes 			peoplecount from student
                   WHERE stuid NOT IN (select wholikes from likes))
                )
                WHERE wholikes=whoisliked
                ),
                (
                        SELECT COUNT(*) temp3
                        FROM student
                )
         WHERE stuid = wholikes;




19.	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));



20.	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;


21.	SELECT MAX(lover) Max ,MIN(lover) Min ,AVG(lover) Avg
  	  FROM
        (
                SELECT COUNT(*) lover
                FROM loves
                GROUP BY wholoves
        );





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



23.	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;



24.	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
                             );




25.	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'))));


26.	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'
                  )
                  )
            )
        )
    );