H21114-课后练习1-121页二1-4

1.李明在哪些课程得到的分数超过80？列出课程名

select cname from C join SC on SC.cno=C.cno join S on S.sno=SC.sno and SC.score>’80’

where S.sname='李明'

2.列出没有选修“人工智能”课程的学生姓名、年龄。

select sname,sage from S where sno not in

(select sno from SC where cno  in(select cno from C where cname='人工智能'))

3.哪些学生“数据库”课程的成绩要高于王红的数据库？列出学号、姓名。

select S.sno,sname from S

join SC on S.sno=SC.sno join C on SC.cno=C.cno and cname='数据库' and SC.score>( select score from SC

join S on S.sno=SC.sno join C on SC.cno=C.cno and cname='数据库'

where sname='王红')

4.列出王风老师教的每门课程的最高分。

select SC.cno,max(score),C.cname from SC join C on SC.cno=C.cno join T on C.tno=T.tno and T.tname='王风'

group by SC.cno,C.cname

4.列出王风老师教的每门课程的最高分。

select sc.cno,max(score),course.cname from sc join course on sc.cno=course.cno join teacher on course.tno=teacher.tno and teacher.tname='王风' group by sc.cno,course.cname