Select Exercise 4

21, inquires the score to choose to learn many courses of students in the score is not the highest score in all grades record.

SELECT * FROM score where CNO in (select CNO from Score GROUP by CNO have count (1) >1) and degree<> (select Max (d Egree) from score);

24. The name of the teacher who has more than 5 students who have enrolled in a course .

Select T.tname from teacher T joins course c on t.tno = C.tno where C.cno in (select CNO from Score Group by CNO have Cou NT (1) >5);

26, inquires the existence has the above grade achievement the course Cno.

Select CNO from score where degree in (select degree from score Group by degree have degree>85);

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

Select s.* from score s joins course c on s.cno = C.cno join teacher T on t.tno = c.tno where T.depart = ' computer system ';

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

Select T.tname,t.prof from teacher T where t.prof in (select T.prof from teacher T Group by T.prof have count (1) =1)

29, Inquires the elective number is "3-105" the course and the result is at least higher than the elective number is "3-245" schoolmate's cno,Sno and degree, and presses degree Order from high to low.

Select Cno,sno,degree from score where cno= ' 3-105 '
and degree>= (select min (degree) from score where cno= ' 3-245 ') Order by degree Desc;

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 Cno,sno,degree from score where cno= ' 3-105 ' and degree> (select Max (degree) from score where cno= ' 3-245 ');

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

Select S.sname,s.ssex,s.sbirthday from student S Union select t.tname,t.tsex,t.tbirthday from teacher T;

32, query all "female" teacher and "female" classmate's name,sex and birthday.

Select S.sname,s.ssex,s.sbirthday from student s where s.ssex= ' female ' union select T.tname,t.tsex,t.tbirthday from teacher T W Here t.tsex= ' Women ';

Select Exercise 4