查询所有修过java这门课_SQL查询学习

四张表 ，学生、课程、分数、教师。

student(学号#,姓名,性别,年龄)

course(课程号#，课程名，教师号#)

score(学号#，课程号#，成绩)

teacher(教师号#，教师名)

查询平均成绩大于60分的同学的学号和平均成绩

select stuNo,avg(score) from score  group by  stuNo  having  avg(score)>60

查询所有同学的学号、姓名、选课数、总成绩

select a.stuNo,a.stuName,count(cNo),sum(score)from student a,score b

where a.stuNo=b.stuNogroup by a.stuNo,a.stuName

查询姓“赵”的老师的个数

select count(tName),tName from teacherwhere tName like '赵%' group by tName

查询没学过“某某”老师课的同学的学号、姓名 select stuNo,stuName from student

where stuNo not in

(select a.stuNo from student a,score b where a.stuNo=b.stuNo and cNo in

(select d.cNo from teacher c,course d where c.tNo=d.tNo and c.tName='钱市保'))

查询学过“001”并且也学过编号“002”课程的同学的学号、姓名select a.stuNo,a.stuName from student a,score b,score c

where a.stuNo=b.stuNo and b.stuNo=c.stuNo and b.cNo='c001' and c.cNo='c002'

查询学过“某某”老师所教的所有课的同学的学号、姓名 select stuNo,stuName from student

where stuNo in (select stuNo from score a,course b,teacher c

where a.cNo=b.cNo and b.tNo=c.tNo and c.tName='钱市保'

group by stuNo

having count(a.cNo)>=(select count(cNo) from course d,teacher e

where d.tNo=e.tNo and e.tNam

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！