mysql不同职称的教师_sql 练习(三)

环境是mysql

练习数据见SQL：练习的前期准备

sql 练习(一)

sql 练习(二)

21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

SELECT *

FROM score

WHERE degree>(SELECT degree

FROM score

WHERE sno>='109' AND cno='3-105')

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

SELECT sno,sname,sbirthday

FROM student

WHERE YEAR(sbirthday)=(SELECT YEAR(sbirthday)

FROM student

WHERE sno='108')

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

SELECT degree

FROM score

WHERE cno IN(SELECT cno

FROM course b

JOIN teacher a ON a.tno=b.tno

WHERE a.tname='张旭')

SELECT degree

FROM score

WHERE cno IN( SELECT Cno

FROM course

WHERE Tno=(SELECT Tno FROM teacher WHERE Tname='张旭'))

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

SELECT tname

FROM teacher

WHERE tno=(SELECT tno

FROM course a

JOIN (SELECT COUNT(cno),cno

FROM score

GROUP BY cno

HAVING COUNT(cno)>5) b

ON a.cno=b.cno)

SELECT Tname

FROM teacher

WHERE Tno IN( SELECT Tno

FROM course

WHERE Cno IN( SELECT Cno

FROM score

GROUP BY Cno

HAVING COUNT(Cno)>5))

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

SELECT *

FROM student

LEFT JOIN score

ON student.sno = score.sno

WHERE student.class IN ('95033','95031')

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

SELECT DISTINCT cno

FROM score

WHERE degree>'85'

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

SELECT sno,cno,degree

FROM score

WHERE cno IN(SELECT cno

FROM course a

JOIN (SELECT tno FROM teacher WHERE depart = '计算机系')b

ON a.tno=b.tno)

SELECT degree,cno,Sno

FROM score

WHERE cno IN(SELECT Cno

FROM Course

WHERE Tno IN ( SELECT Tno FROM teacher WHERE depart='计算机系'))

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

SELECT Tname,prof

FROM teacher

WHERE depart='计算机系' OR depart='电子工程系'

GROUP BY prof

HAVING COUNT(prof)=1

SELECT Tname,prof

FROM teacher

WHERE prof NOT IN(SELECT prof FROM teacher WHERE Depart='计算机系'

AND prof IN(SELECT prof FROM teacher WHERE Depart='电子工程系'))

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

SELECT sno,cno,degree

FROM score

WHERE cno='3-105'

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

30、查询所有教师和同学的name、sex和birthday.

SELECT tname,tsex,tbirthday FROM teacher

UNION

SELECT sname,ssex,sbirthday FROM student;