20_ Student Selection Database SQL statement exercises 1

25. Check the records of all students in class 95033 and 95031.

SELECT * from STUDENT t,score s where t.sclass=95033 or t.sclass=95031

26, the inquiry existence has 85 points above the course CNO.

Select S.cno from score s where s.degree>85

27. Find out the results table of the "computer Department" teacher teaching course.

28, query "computer department" and "Electronic Engineering department" different titles of teachers Tname and Prof.

Select Tname,prof from teacher where Prof not in

(

Select A.prof from

(select prof from teacher where depart= ' electronic Engineering Department ') a

Join

(select prof from teacher where depart = ' computer system ') b

On A.prof =b.prof) and depart in (' Computer department ', ' Electronic Engineering Department ')

29, the inquiry elective number is "3-105" course and the result is at least higher than the elective number "3-245" classmate's CNO, Sno and degree, and according to degree from high to low order.

Select T.degree from score t where t.cno= ' 3-105 ' and t.degree> (select min (t.degree) from score T where t.cno= ' 3-245 ')

30, inquires the elective number is "3-105" and the result is higher than the elective number is "3-245" the course schoolmate's CNO, Sno and degree.

Select T.degree from score t where t.cno= ' 3-105 ' and t.degree> any (select T.degree from score t where t.cno= ' 3-245 ') o Rder by t.degree Desc;

31. Check the name, sex and birthday of all teachers and classmates.

Select Tname,tsex,t.tbirthday from teacher T '

Union

Select Sname,ssex,s.sbirthday from student s ';

32, query All "female" teacher and "female" classmate's name, Sex and birthday.

Select Tname,tsex,t.tbirthday from teacher t where t.tsex= ' woman '

Union

Select Sname,ssex,s.sbirthday from student s where s.ssex= ' female ';

33. Check the scores of students who have a lower average score than the course.

34. Check the Tname and depart of all classroom teachers.

Select Tname,depart from teacher T where T.tno in

(select TNO from course C where c.cno in (select CNO from Score))

35. Inquire about the Tname and depart of all teachers who have not lectured.

Select Tname,depart from teacher T where T.tno not in

(select TNO from course C where c.cno in (select CNO from Score))

36. Check the class number of at least 2 boys.

37, inquires the student table the surname "the king" the classmate record.

38. Check the name and age of each student in the student table.

S.sname,to_char Select (sysdate, ' yyyy ')-to_char (s.sbirthday, ' yyyy ') from student s where s.sbirthday are NOT null

39. Query the maximum and minimum sbirthday date values in the student table.

To_char (S.sbirthday, ' Mm/dd '), Min (To_char (s.sbirthday, ' Mm/dd ')) from student s

40. Check all records in the student table in order of class number and age from large to small.

Max Select s.* from student s where s.sbirthday are NOT null ORDER by S.class,to_char (sysdate, ' yyyy ')

41. Query "male" teachers and their courses.

C.TNO,C.CNO Select course C INNER JOIN teacher T on T.tno=c.tno where t.tsex= ' man '

42. Check the SNO, CNO and degree columns of the students with the highest score.

S.sno,s.cno,s.degree Select score s where degree= (select Max (degree) from score)

43, inquiries and "Li June" with the gender of all students sname.

S.sname Select student s where S.ssex in (select Ssex from student where ssex= ' man ')

44, inquiries and "Li June" with the same sex and classmates sname.

S.sname-Select-Student s where S.ssex in (select Ssex from student where ssex= ' man ') and S.class in
(select class from student where Sname= ' Li June ')

45. Check the scores of all the "male" students who have enrolled in the "Introduction to Computer" course.

S.* from score s inner joins course C on c.cno=s.cno inner joins student SS on Ss.sno=s.sno
Where ss.ssex= ' man ' and C.cname= ' computer Introduction '

20_ Student Selection Database SQL statement exercises 1