SQLServer2008数据库的单表查询

use Teaching--1>查询学生有哪些专业,过滤掉重复行<span style="white-space:pre"></span>--distince用来过滤掉查询结果中的重复行，as用来给列取别名select  distinct Sdept as 专业from Student--2>统计有学生选修的课程门数<span style="white-space:pre"></span>--首先是要将有学生选修了的课程查询出来，然后去掉重复行后进行统计<span style="white-space:pre"></span>--查询SC表中存在的课程号<span style="white-space:pre"></span>--统计使用聚合函数select distinct Cno from SCselect count( distinct Cno)as 课程数from SC--3>统计选修了课程4(或其他的某一门)的学生的平均年龄<span style="white-space:pre"></span>--统计出选修了课程4的学生<span style="white-space:pre"></span>--这是步骤代码<span style="white-space:pre"></span>select Sno<span style="white-space:pre"></span>from SC<span style="white-space:pre"></span>where Cno = 4<span style="white-space:pre"></span>select * from Studentselect AVG(Sage)from Studentwhere Sno in (select Sno from SC where Cno = 4)<span style="white-space:pre"></span><span style="white-space:pre"></span>--4>求学分为3的每门课程的平均成绩<span style="white-space:pre"></span>--这是步骤代码<span style="white-space:pre"></span>select Cno<span style="white-space:pre"></span>from Course<span style="white-space:pre"></span>where Course.Ccredit = 3<span style="white-space:pre"></span>--求平均值<span style="white-space:pre"></span>select Cno, AVG(Grade) 平均成绩<span style="white-space:pre"></span>from SC<span style="white-space:pre"></span>where Cno in (4,9)<span style="white-space:pre"></span>group by Cno--最终完成版本select Cno, AVG(Grade) 平均成绩<span style="white-space:pre"></span>from SC<span style="white-space:pre"></span>where Cno in (select Cno from Course where Course.Ccredit = 3)<span style="white-space:pre"></span>group by Cno--5>统计每门课程的学生选课人数，超过三门的才被统计，要求输出课程号和选修课程人数<span style="white-space:pre"></span>--查询结果按人数降序排列，若人数相同，按课程号升序排列<span style="white-space:pre"></span>---每门课程被选修的人数,超过三门<span style="white-space:pre"></span>select  Cno 课程号,COUNT(Cno)  选课人数<span style="white-space:pre"></span>from SC<span style="white-space:pre"></span>group by Cno<span style="white-space:pre"></span>having count(Cno) >3<span style="white-space:pre"></span>order by 选课人数 DESC, 课程号 ASC<span style="white-space:pre"></span>--6>查询姓鲁的学生的姓名和年纪select Sname as 姓名 ,Sage as 年纪from<span style="white-space:pre"></span>Studentwhere<span style="white-space:pre"></span>Sname like '鲁%'--7>在选课表中查询成绩为空值的学生学号和课程号select Sno as 学号, Cno as 课程号from SCwhere Grade is NULL--8>查询没有学生选修的课程的课程号和课程名select distinct Course.Cno, Cnamefrom Course, SCwhere not exists(select * from SC where Course.Cno = SC.Cno)select * from Courseselect * from SC<span style="white-space:pre"></span>--9>求年龄大于女同学平均年龄的男同学的年龄<span style="white-space:pre"></span>--这是步骤代码<span style="white-space:pre"></span>select AVG(Sage)<span style="white-space:pre"></span>from Student<span style="white-space:pre"></span>where Ssex = '女'select Sname 姓名,Sage 年龄from Studentwhere Ssex = '男' and Sage > (select AVG(Sage) from Student where Ssex = '女')--10>求年龄大于所有女同学年龄的男同学的姓名和年龄select Sname 姓名,Sage 年龄from Studentwhere Ssex = '男' and Sage > (select Max(Sage) from Student where Ssex = '女')--11>查询所有与鲁清华同专业，但比王华年龄大的学生的姓名，年龄和性别select A.Sname  姓名,A.Sage 年龄,A.Ssex 性别from Student A,Student Bwhere A.Sdept=B.Sdept and B.Sname='鲁清华' and A.Sage > (select Sage from Student where Sname ='鲁清华')--12>查询选修课程1的学生中成绩最高的学生的学号select Sno 学号from  SCwhere  Grade =(select  Max(Grade)from SC where Cno = 1)<span style="white-space:pre"></span>--这是步骤代码<span style="white-space:pre"></span>select  Max(Grade) 最高分<span style="white-space:pre"></span>from SC<span style="white-space:pre"></span>where Cno = 1 --13>查询学生姓名和以及其所选修的课程的课程号和成绩select A.Sname 姓名,B.Cno,B.Gradefrom Student A,SC Bwhere A.Sno = B.Sno--14>查询选修4门课以上的学生总成绩(不统计不及格课程)，并要求按照总成绩降序排列select SUM(Grade) 总成绩  from SC where Sno in(select Sno from SC group by Sno having count(Cno) >4)group by Sno order by 总成绩 DESCselect Sno, SUM(Grade) 总成绩 from SCwhere Sno in(select Sno from SC where Grade >= 60 group by Sno having COUNT(*) > 3)<span style="white-space:pre"></span>and Grade >= 60group by Snoorder by 总成绩 DESC--15>查询选修2门课以上的学生平均成绩，只取前三名select top 3 AVG(Grade),Snofrom SCgroup by Snohaving count(Cno) >1<span style="white-space:pre"></span>--这是步骤代码<span style="white-space:pre"></span>select Sno , COUNT(Cno)<span style="white-space:pre"></span>from SC<span style="white-space:pre"></span>group by Sno--16>查询每个学生的总学分select SUM(Ccredit) 总学分,Sno 学号from Course,SCwhere Course.Cno = SC.Cnogroup by Snoselect * from Courseselect * from SC