SQL2



CREATE TABLE STUDENTS(SNO NUMBER PRIMARY KEY,SNAME VARCHAR2(10),
MAJOR VARCHAR2(50) , LEVELS VARCHAR2(50) , AGE NUMBER)

CREATE TABLE FACULTYS(FID NUMBER PRIMARY KEY , FNAME VARCHAR2(50) ,
DEPTID NUMBER)

CREATE TABLE CLASSS (CNAME VARCHAR2(50) PRIMARY KEY , ROOM
VARCHAR2(50) ,FID NUMBER REFERENCES FACULTYS(FID))

CREATE TABLE ENROLLED(SNO NUMBER REFERENCES STUDENTS(SNO) , CNAME
VARCHAR2(50) REFERENCES CLASSS(CNAME))

INSERT INTO STUDENTS VALUES(1,'AA','MATHS','SR',18)
INSERT INTO STUDENTS VALUES(2,'BB','BIO','JR',17)
INSERT INTO STUDENTS VALUES(3,'CC','HIS','JR',17)
INSERT INTO STUDENTS VALUES(4,'DD','MATHS','SR',20)
INSERT INTO STUDENTS VALUES(5,'EE','PHY','SR',21)
INSERT INTO FACULTYS VALUES(11,'ZZ',101)
INSERT INTO FACULTYS VALUES(12,'YY',102)
INSERT INTO FACULTYS VALUES(13,'I.TEACH',103)
INSERT INTO FACULTYS VALUES(14,'XX',105)

INSERT INTO CLASSS VALUES('ONE','R125',12)
INSERT INTO CLASSS VALUES('TWO','R126',11)
INSERT INTO CLASSS VALUES('THREE','R127',13)
INSERT INTO CLASSS VALUES('FOUR','R128',11)

INSERT INTO ENROLLED VALUES(1,'FOUR')
INSERT INTO ENROLLED VALUES(2,'ONE')
INSERT INTO ENROLLED VALUES(2,'TWO')
INSERT INTO ENROLLED VALUES(2,'THREE')
INSERT INTO ENROLLED VALUES(2,'FOUR')
INSERT INTO ENROLLED VALUES(3,'TWO')

-- 1. Find the names of all Juniors (Level = JR) who are enrolled in a class taught by I. Teach.
SELECT S.SNAME FROM STUDENTS S WHERE S.SNO IN(SELECT E.SNO FROM
ENROLLED E WHERE E.CNAME IN(SELECT C.CNAME FROM CLASSS C WHERE
C.FID IN (SELECT F.FID FROM FACULTYS F WHERE F.FNAME = 'I.TEACH')))

-- 2. Find the age of the oldest student who has History major
SELECT MAX(S.AGE) FROM STUDENTS S WHERE S.MAJOR='HIS'

-- 3. Find the age of the oldest student who is enrolled in a coursetaught by I. Teach.
SELECT MAX(S.AGE) FROM STUDENTS S WHERE S.SNO IN (SELECT E.SNO
FROM ENROLLED E WHERE E.CNAME IN(SELECT C.CNAME FROM CLASSS C
WHERE C.FID IN (SELECT F.FID FROM FACULTYS F WHERE F.FNAME='I.TEACH')))

-- 4. Find the names of all classes that meet in room R128
SELECT C.CNAME FROM CLASSS C WHERE C.ROOM ='R128'

-- 5. Find the names of all classes that have five or more studentsenrolled.
SELECT E.CNAME FROM ENROLLED E WHERE E.SNO IN (SELECT E.SNO FROM
ENROLLED E GROUP BY E.SNO HAVING COUNT(E.CNAME)>3)

-- 6. Find the names of all students who are enrolled in two classes
SELECT S.SNAME FROM STUDENTS S WHERE S.SNO IN(SELECT E.SNO FROM
ENROLLED E GROUP BY E.SNO HAVING COUNT(E.CNAME)=4)

-- 7. Find the names of faculty members who teach in every room in which some class istaught.
SELECT F.FNAME FROM FACULTYS F WHERE F.FID IN(SELECT C.FID FROM
CLASSS C WHERE C.ROOM IN(SELECT C.ROOM FROM CLASSS C GROUP BY
C.ROOM HAVING COUNT(C.CNAME)>=1))

-- 8. Find the names of faculty members for whom the combined enrollment of the courses that they
-- teach is less than five.
SELECT F.FNAME FROM FACULTYS F WHERE F.FID IN(SELECT C.FID FROM
CLASSS C GROUP BY C.FID HAVING COUNT(C.CNAME)<2)

-- 9. Print the Level and the average age of students for that Level, for each Level.
SELECT S.LEVELS,AVG(S.AGE) FROM STUDENTS S GROUP BY S.LEVELS

-- 10. Print the Level and the average age of students for that Level, for all Levels except JR.
SELECT S.LEVELS,AVG(S.AGE) FROM STUDENTS S WHERE S.LEVELS<>'JR'
GROUP BY S.LEVELS


-- 11. Find the names of students who are enrolled in the maximum number of classes.
SORRY

-- 12. Find the names of students who are not enrolled in any class.
SELECT S.SNAME FROM STUDENTS S WHERE S.SNO NOT IN (SELECT E.SNO
FROM ENROLLED E GROUP BY E.SNO HAVING COUNT(E.CNAME)>0)


 

Post a Comment

0 Comments