Database Benchmarking exercise End (32~45)

--32, query all "female" teacher and "female" classmate's name, Sex and birthday.SelectSname, Ssex, Sbirthday fromStudentwhereSsex= 'female' Union SelectTname, Tsex,tbirthday fromTeacherwhereTsex= 'female';--33. Check the scores of students who have a lower average score than the course. SelectC.cno,c.degree fromscore CwhereC.degree<(Select avg(S.degree) fromScore SwhereS.cno=C.cnoGroup  bys.cno);--34. Check the Tname and depart of all classroom teachers.SelectT.tname, T.depart fromTeacher T, course CwhereT.tno=C.tno;--35, check the Tname and depart of all teachers who have not lectured.Select distinctT.tname, T.depart fromTeacher T, course CwhereT.tno not inch(SelectTno fromcourse);--36. Check the class number of at least 2 boys. Select *  from(SelectS.sclass,Count(S.sclass) C fromStudent SwhereS.ssex= 'male' Group  byS.sclass)whereC>=2 ; --37, inquires the student table the surname "the king" the classmate record. Select *  fromStudent SwhereS.sname not  like 'Wang%'; --38. Check the name and age of each student in the student table. SelectS.sname,to_char (Sysdate,'yyyy')-To_char (S.sbirthday,'yyyy') fromstudent S;--39. Query the maximum and minimum sbirthday date values in the student table. Select min(s.sbirthday) Max birthday,Max(s.sbirthday) Minimum birthday fromstudent S;--40. Check all records in the student table in order of class number and age from large to small. Select *  fromStudent SOrder  byS.sclassdesc, S.sbirthday;--41. Query "male" teachers and their courses. SelectT.tname,c.cname fromTeacher T,course CwhereT.tno=C.tno andT.tsex= 'male';--42. Check the SNO, CNO and degree columns of the students with the highest score. SelectS.sname,c.cname,r.degree fromStudent S, course C,score RwhereR.degree=(Select Max(degree) fromscore) andS.sno=R.sno andC.cno=R.cno;--43, inquiries and "Li June" with the gender of all students sname.SelectT.sname fromStudent TwhereT.ssex=(SelectS.ssex fromStudent SwhereS.sname= 'Li June');--44, inquiries and "Li June" with the same sex and classmates sname.SelectT.sname fromStudent TwhereT.ssex=(SelectS.ssex fromStudent SwhereS.sname= 'Li June') andT.sclass=(SelectS.sclass fromStudent SwhereS.sname= 'Li June');--45. Check the scores of all the "male" students who have enrolled in the "Introduction to Computer" course. SelectT.sname,t.ssex,s.degree fromScore S,course C,student TwhereS.cno=C.cno andS.sno=T.sno andT.ssex= 'male'  andC.cname= 'Introduction to Computers';

Database Benchmarking exercise End (32~45)