mysql查询未讲课教师_mysql练习

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

select * from score where degree>(select degree from score where cno=3-105 and sno=109)

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

select sno,sname,sbirthday

from student

where year(sbirthday)=(select year(sbirthday) from student where sno=108)

select sno,sname,sbirthday

from student

where year(sbirthday)=(select year(sbirthday) from student where sno=101)

3.查询“张旭“教师任课的学生成绩。

SELECT sno,degree from score

where cno=(select cno from course where tno =(select tno from teacher where tname="张旭") )

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

select tname from teacher

where tno in (select tno from course where cno in (select cno from score GROUP BY cno having count(*)>5))