--Q 1
SELECT P.pname FROM prof P,dept D WHERE P.dname=D.dname AND D.numphds<50;

--Q 2
SELECT S1.sname FROM student S1 WHERE S1.gpa <= ALL (SELECT S2.gpa FROM student S2);

--Q 3
SELECT E.cno,E.sectno,AVG(S.gpa) FROM student S, enroll E WHERE S.sid=E.sid AND
E.dname LIKE 'Computer Sciences' GROUP BY E.cno,E.sectno;

--Q 4
SELECT E.cno, E.sectno, C.cname FROM course C, enroll E WHERE C.cno=E.cno AND C.dname=E.dname GROUP BY C.cname, E.cno, E.sectno HAVING COUNT(E.sid)<6);

--Q 5
SELECT S.sname, S.sid FROM student S WHERE S.sid IN (SELECT E.sid FROM enroll E GROUP BY E.sid HAVING COUNT(*)>=ALL(SELECT COUNT(*) FROM enroll E1 GROUP BY E1.sid));

--Q 6
SELECT M.dname FROM major M, student S WHERE M.sid=S.sid AND S.age<18;

--Q 7
SELECT S.sname, M.dname FROM student S, major M, course C, enroll E WHERE E.sid=S.sid AND  S.sid=M.sid AND E.cno= C.cno AND C.dname=E.dname AND C.cname LIKE 'College Geo%';

--Q 8
SELECT S.sname FROM student S, enroll E, enroll E1 WHERE E.sid=S.sid AND E.sid=E1.sid AND E.dname='Computer Sciences' AND E1.dname='Mathematics';

--Q 9
SELECT MAX(S.age)-MIN(S.age) AS Age_Differance from student S, major M WHERE S.sid=M.sid AND M.dname='Computer Sciences';

--Q 10
SELECT M1.dname, AVG(S1.gpa) FROM student S1, major M1 WHERE S1.sid=M1.sid AND M1.dname IN (SELECT M.dname from major M, student S, where M.sid=S.sid and S.gpa<1.0) group by M1.dname;