sql查询语句.doc

查询课堂练习21.查询学生总人数Select count(*) 总人数from student2.查询选修了课程的学生人数 提示：用distinctselect count(distinct sno) from sc3.计算C01课程的平均成绩Select avg(grade) 平均成绩from sc where cno=c014.查询所有的课程号及相应的选课人数 提示：按课程号分组select cno,count(cno) from scgroup by cno5.查询选修了门以上课程的学生学号 提示：按学号分组+havingselect cno,count(cno) from scgroup by cnohaving count(cno)=26.查询先修了门以上课程学生学号及课程数 提示：按学号分组+havingselect cno,count(cno) from scgroup by cnohaving count(cno)=37.查询男女生各自的总数。 提示：按性别分组select ssex,count(ssex) from studentgroup by ssex8.查询每门课程选修的人数及考试最低分和最高分。提示：按课程号分组select cno,max(grade),min(grade) from scgroup by cno9.查询选修了“VB”的学生学号，成绩Select sno,grade from scWhere cno=(select cno from course where cname=vb)10.查询年龄在20岁之间的学生所学的课程名称方法：子查询select cname from coursewhere cno in(select cno from sc where sno in (select sno from student where (year(getdate()-year(sbrithday)=18 and (year(getdate()-year(sbrithday)=18 and (year(getdate()-year(sbrithday)=2011.查询计算机系学生的学号，姓名，课程名，成绩select s.sno,sname,cname,gradefrom student s join sc on s.sno=sc.sno join course c on o=o where sdept=计算机科学与技术系12.查询“数据库原理与应用”这门课程的平均成绩方法：子查询select avg(grade) from scwhere cno=(select cno from course where cname=数据库原理与应用)方法：多表连接select avg(grade)from course c join sc on o=owhere cname=数据库原理与应用13.查询张三所学的课程名称方法：子查询select cname from coursewhere cno in(select cno from sc where sno in (select sno from student where sname=张三)方法：多表连接select cname from course c join sc on o=o join student s on s.sno=sc.snowhere sname=张三14.查询计算机系学生平均成绩方法：子查询select avg(grade) from scwhere sno in(select sno from student where sdept=计算机科学与技术系)方法：多表连接select avg(grade)from course c join sc on o=o join student s on s.sno=sc.snowhere sdept=计算机科学与技术系15.查询所有学习了“JAVA”的学生姓名方法：子查询select sname from student where sno in(select sno from sc where cno in( select cno from course where cname=java)方法：多表连接select snamefrom course c join sc on o=o join student s on s.sno=sc.snowhere cname=java16.查询“java”成绩最高的学生姓名（思考题）方法：子查询select snamefrom studentwhere sno=(select sno from sc where grade= (select max(grade) from sc where cno= (select cno from course where cname=java)方法：多表连接+topselect top 1 sn