SQL Query part 3

-- DISPLAY DATA OF ELDER STUDENT

--select top 1 * from student order by age desc

select * from student where age =

(select MAX(age) from student)

-- DISPLAY DATA OF WEAK STUDENT

select * from student where cgpa =

( select min(cgpa) from student)

-- DISPLAY DATA OF THOSE STUDENT WHO HAVE NOT ENROLLED ANY

--COURSE YET

select * from student where sid not in(

select sid from enrollment)

--DISPLAY ALL THOSE TEACHER WHO HAVE NOT ALLOCATE

-- ANY COURSE YET.

select * from teacher where tid not in(

select tid from allocation)

-- DISPLAY ALL THOSE TEACHER WHO ARE TEACHING 'CS'

--CATEGORY COURSE

select * from teacher where tid in(

select tid from allocation where cid in(

select cid from course where category='cs'))

--DISPLAY ALL THOSE TEACHER WHO HAVE ALLOCATE

-- ANY COURSE YET.

select * from teacher where tid in(

select tid from allocation)

-- DISPLAY ALL DATA OF STUDENT WHOSE CGPA IS MAX

select * from student where cgpa=

( select max(cgpa) from student)

-- DISPLAY ALL THOSE TEACHER WHO are TEACING TOPPER STUDENT

select sid from student union all select sid from enrollment

select sid from student intersect select sid from enrollment

select sid from student except select sid from enrollment

select sname from biit_student union

select sname from NUST_STUDENT

select sname from biit_student except

select sname from NUST_STUDENT

-- display name of students who only enrolled in biit

--neither nust nor uaar

select sname from biit_student except

select sname from NUST_STUDENT except

select sname from UAAR_STUDENT

--display name of students who enrolled in nust and biit

--but not in uaar

select sname from biit_student union

select sname from Nust_student except

select sname from Uaar_student

--- display name of those student who enrolled

-- in biit , nust and uaar at same time

select sname from biit_student

intersect select sname from nust_student

intersect select sname from uaar_student

insert into UAAR_STUDENT(sname) values ('hamid')

select * from teacher where tid in(

select tid from allocation where cid in(

select cid from enrollment where sid in(

select sid from student where cgpa in(

select MAX(cgpa) from student))))

-- DISPLAY DATA OF WEAK STUDENT

SELECT * FROM Student WHERE cgpa =(

SELECT MAX(CGPA) FROM Student )

-- DISPLAY DATA OF THOSE STUDENT WHO HAVE NOT ENROLLED ANY

--COURSE YET

SELECT * FROM Student WHERE sid IN (

SELECT sid FROM enrollment )ORDER BY SID DESC

--DISPLAY ALL THOSE TEACHER WHO HAVE NOT ALLOCATE

-- ANY COURSE YET

-- DISPLAY DATA OF STUDENT WHOSE CGPA IS MAXIMUM

-- DISPLAY DATA OF YOUNGER STUDENT

SELECT * FROM teacher WHERE tid NOT IN(

SELECT tid FROM allocation)

SELECT * FROM Student WHERE sid IN(

SELECT SID FROM enrollment WHERE cid IN(

SELECT cid FROM course WHERE title ='DATABASE SYSTEM'))

-- DISPLAY ALL DATA OF STUDENT WHOSE CGPA IS MAX

--SELECT TOP 1 * FROM Student ORDER BY cgpa DESC

SELECT * FROM Student WHERE cgpa =(

SELECT MAX(CGPA) FROM Student )

-- DISPLAY ALL DATA OF YOUNGER STUDENT

SELECT * FROM Student WHERE age=(

SELECT MIN(AGE) FROM STUDENT)

-- DISPLAY DATA OF STUDENT WHO ENROLLED IN 'CS' CATEGORY

--COURSE

SELECT * FROM Student WHERE sid IN(

SELECT sid FROM enrollment WHERE cid IN(

SELECT cid FROM course WHERE category='CS'))

-- DISPLAY ALL THOSE COURSES THAT ARE ENROLLED BY

-- ELDER STUDENTS

SELECT * FROM course WHERE cid IN(

SELECT cid FROM enrollment WHERE sid IN(

SELECT sid FROM Student WHERE age IN(

SELECT MAX(AGE) FROM STUDENT)))

-- DISPLAY ALL THOSE COURSES THAT ARE ENROLLED BY THOSE

-- STUDENT WHOSE NAME START WITH VOWEL ALPHABET

SELECT * FROM course WHERE cid IN(

SELECT cid FROM enrollment WHERE sid IN(

SELECT sid FROM Student WHERE fname LIKE '[AEIOU]%'))

-- DISPLAY ALL THOSE TEACHER WHO ARE TEACHING 'CS'

--CATEGORY COURSE

SELECT * FROM teacher WHERE tid IN(

SELECT tid FROM allocation WHERE cid IN(

SELECT cid FROM course WHERE category='CS'))

-- DISPLAY ALL THOSE STUDENT WHO ARE TOUGHT BY YOUNGER

--TEACHER

SELECT * FROM Student WHERE sid IN(

SELECT sid FROM enrollment WHERE cid IN(

SELECT cid FROM allocation WHERE tid IN(

SELECT tid FROM teacher WHERE age IN(

SELECT MIN(AGE) FROM teacher))))

-- DISPLAY ALL THOSE TEACHER WHO TEACING TOPPER STUDENT