查询计算机系教师所教课程的成绩表,MySQL数据库基础练习二

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

子查询

SELECT tno FROM teacher WHERE depart='计算机系';

SELECT cno FROM course WHERE tno IN(SELECT tno FROM teacher WHERE depart='计算机系');

SELECT * FROM score WHERE cno IN(SELECT cno FROM course WHERE tno IN(SELECT tno FROM teacher WHERE depart='计算机系'));

sno cno degree

103 3-245 86

105 3-245 75

109 3-245 68

101 3-105 90

102 3-105 91

103 3-105 92

104 3-105 88

105 3-105 88

109 3-105 76

26、查询计算机系与电子工程系不同职称的教师的tname和prof

union求并集，联合在一起

SELECT tname,prof FROM teacher WHERE depart='计算机系' AND prof NOT IN(SELECT prof FROM teacher WHERE depart='电子工程系')

UNION

SELECT tname,prof FROM teacher WHERE depart='电子工程系' AND prof NOT IN(SELECT prof FROM teacher WHERE depart='计算机系');

27、查询选修编号为3-105课程且成绩至少高于选修编号为3-245的同学的cno，sno和degree，并按degree从高到底排序

至少的含义：大于其中至少一个，any

SELECT * FROM score WHERE cno = '3-105';

SELECT * FROM score WHERE cno = '3-245';

SELECT * FROM score WHERE cno = '3-105'

AND degree >ANY(SELECT degree FROM score WHERE cno = '3-245')

ORDER BY degree DESC;

sno cno degree

103 3-105 92

102 3-105 91

101 3-105 90

104 3-105 88

105 3-105 88

109 3-105 76

28、查询选修编号为3-105课程且成绩高于选修编号为3-245的同学的cno，sno和degree

且：所有，all表示所有的关系

SELECT * FROM score WHERE cno = '3-105'

AND degree >ALL(SELECT degree FROM score WHERE cno = '3-245')

sno cno degree

101 3-105 90

102 3-105 9