sql练习题2---in，any,all的用法

http://blog.csdn.net/mrbcy/article/details/68965271#t33
21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

 select s1.sno,s1.degree from scores as s1 inner join scores as s2 on (s1.degree > s2.degree and s1.cno = s2.cno)where s2.sno = '109' and s1.cno = '3-105';

22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

select s1.sno,s1.sname,s1.sbirthday from students as s1 inner join students as s2 on(year(s1.sbirthday) = year(s2.sbirthday))where s2.sno = '108';

23、查询“张旭“教师任课的学生成绩。

select degree from scores inner join courses on(scores.cno = courses.cno) inner join teachers on(teachers.tno = courses.tno)where teachers.tname ='张旭';

24、查询选修某课程的同学人数多于5人的教师姓名。

 select teachers.tname from teachers inner join courses on(teachers.tno = courses.tno) inner join scores on(scores.cno =courses.cno)group by scores.cno having count(scores.cno) > 5;

25、查询95033班和95031班全体学生的记录。

select * from students where class in ('95033' ,'95031');

只能用in不能用and进行并列查询

26、查询存在有85分以上成绩的课程Cno.

select cno from scores where degree > 85;

27、查询出“计算机系“教师所教课程的成绩表。

select degree from scores inner join courses on(scores.cno = courses.cno)inner join teachers on(teachers.tno = courses.tno)where teachers.depart = '计算机系';

28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

 SELECT Tname,Prof
FROM Teachers
WHERE Depart='计算机系' AND Prof NOT IN(
    SELECT DISTINCT Prof
    FROM Teachers
    WHERE Depart='电子工程系');

29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

SELECT Cno,Sno,Degree
FROM Scores
WHERE Cno='3-105' AND Degree > ANY(
    SELECT Degree
    FROM Scores
    WHERE Cno='3-245')
ORDER BY Degree DESC;

30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

SELECT Cno,Sno,Degree
FROM Scores
WHERE Cno='3-105' AND Degree > ALL(
    SELECT Degree
    FROM Scores
    WHERE Cno='3-245')
ORDER BY Degree DESC;