1、-1、查询课程 1 的成绩 比 课程 2 的成绩 高 的所有学生的学号 .select a.sno from(select sno,score from sc where cno=1) a,(select sno,score from sc where cno=2) bwhere a.scoreb.score and a.sno=b.sno-2、查询平均成绩大于 60 分的同学的学号和平均成绩；select sno,avg(score) as sscore from sc group by sno having avg(score) 60-3、查询所有同学的学号、姓名、选课数、总成绩selec
2、t a.sno as 学号, b.sname as 姓名,count(o) as 选课数, sum(a.score) as 总成绩from sc a, student bwhere a.sno = b.snogroup by a.sno, b.snamego-3、查询所有同学的学号、姓名、选课数、总成绩select student.sno as 学号, student.sname as 姓名,count(o) as 选课数, sum(score) as 总成绩from student left Outer join sc on student.sno = sc.snogroup by stud
3、ent.sno, sname-4、查询姓“李”的老师的个数；select count(distinct(tname) from teacher where tname like 李%-5、查询没学过“叶平”老师课的同学的学号、姓名；select student.sno,student.sname from studentwhere sno not in (select distinct(sc.sno) from sc,course,teacherwhere o=o and teacher.tno=course.tno and teacher.tname=叶平)-6、查询同时学过课程 1 和课程
4、 2 的同学的学号、姓名select sno, sname from studentwhere sno in (select sno from sc where o = 1)and sno in (select sno from sc where o = 2)goselect c.sno, c.sname from(select sno from sc where o = 1) a,(select sno from sc where o = 2) b,student cwhere a.sno = b.sno and a.sno = c.snogoselect student.sno,stude
5、nt.sname from student,sc where student.sno=sc.sno and o=1and exists( Select * from sc as sc_2 where sc_2.sno=sc.sno and sc_o=2)go-7、查询学过“叶平”老师所教所有课程的所有同学的学号、姓名select a.sno, a.sname from student a, sc bwhere a.sno = b.sno and o in(select o from course c, teacher d where c.tno = d.tno and d.tname = 叶平
6、)select a.sno, a.sname from student a, sc b,(select o from course c, teacher d where c.tno = d.tno and d.tname = 叶平) ewhere a.sno = b.sno and o = o-8、查询 课程编号 1 的成绩 比 课程编号 2 的成绩 高的所有同学的学号、姓名select a.sno, a.sname from student a,(select sno, score from sc where cno = 1) b,(select sno, score from sc whe
7、re cno = 2) cwhere b.score c.score and b.sno = c.sno and a.sno = b.sno-9、查询所有课程成绩小于 60 分的同学的学号、姓名select sno,sname from studentwhere sno not in (select distinct sno from sc where score 60)-10、查询所有课程成绩大于 60 分的同学的学号、姓名select sno,sname from studentwhere sno not in (select distinct sno from sc where scor
8、e 1 and a.sno=b.sno o in (select cno from sc where sno = 1)-13、把“sc ”表中“刘老师 ”所教课的成绩都更改为此课程的平均成绩update sc set score = (select avg(sc_2.score) from sc sc_2 where sc_o=o)from course,teacher where o=o and course.tno=teacher.tno and teacher.tname=叶平-14、查询和 2 号同学学习的课程完全相同的其他同学学号和姓名/* -Do first :select sno
9、from scwhere sno 2 and a.sno = b.snogroup by b.sno, b.snamehaving sum(cno) = (select sum(cno) from sc where sno = 2)-15、删除学习“叶平”老师课的 sc 表记录delete sc from course, teacherwhere o = o and course.tno = teacher.tno and tname = 叶平-16、向 sc 表中插入一些记录，这些记录要求符合以下条件：-将没有课程 3 成绩同学的该成绩补齐, 其成绩取所有学生的课程 2 的平均成绩INSER
10、T sc select sno, 3, (select avg(score) from sc where cno = 2)from studentwhere sno not in (select sno from sc where cno = 3)-17、按平平均分从高到低显示所有学生的如下统计报表：- 学号,企业管理,马克思,UML, 数据库,物理,课程数,平均分SELECT sno as 学号,max(case when cno = 1 then score end) AS 企业管理,max(case when cno = 2 then score end) AS 马克思,max(case
11、 when cno = 3 then score end) AS UML,max(case when cno = 4 then score end) AS 数据库,max(case when cno = 5 then score end) AS 物理,count(cno) AS 课程数,avg(score) AS 平均分FROM scGROUP by snoORDER by avg(score) DESC-18、查询各科成绩最高分和最低分：以如下形式显示：课程号，最高分，最低分select cno as 课程号, max(score) as 最高分, min(score) 最低分from sc
12、 group by cno-19、按各科平均成绩从低到高和及格率的百分数从高到低顺序SELECT o AS 课程号,max(ame)AS 课程名,isnull(AVG(score),0) AS 平均成绩 ,100 * SUM(CASE WHEN isnull(score,0)=60 THEN 1 ELSE 0 END)/count(1) AS 及格率FROM sc t, coursewhere o = oGROUP BY oORDER BY 及格率 desc-20、查询如下课程平均成绩和及格率的百分数( 用“1 行“显示): 企业管理（001） ，马克思（002） ，UML （003） ，数据
13、库（ 004） select avg(case when cno = 1 then score end) as 平均分 1,avg(case when cno = 2 then score end) as 平均分 2,avg(case when cno = 3 then score end) as 平均分 3,avg(case when cno = 4 then score end) as 平均分 4,100 * sum(case when cno = 1 and score 60 then 1 else 0 end) / sum(case when cno = 1 then 1 else 0
14、 end) as 及格率 1,100 * sum(case when cno = 2 and score 60 then 1 else 0 end) / sum(case when cno = 2 then 1 else 0 end) as 及格率 2,100 * sum(case when cno = 3 and score 60 then 1 else 0 end) / sum(case when cno = 3 then 1 else 0 end) as 及格率 3,100 * sum(case when cno = 4 and score 60 then 1 else 0 end) /
15、 sum(case when cno = 4 then 1 else 0 end) as 及格率 4from sc-21、查询不同老师所教不同课程平均分, 从高到低显示- 张老师 数据库 88select max(c.tname) as 教师, max(ame) 课程, avg(a.score) 平均分from sc a, course b, teacher cwhere o = o and b.tno = c.tnogroup by oorder by 平均分 desc-22、查询如下课程成绩均在第 3 名到第 6 名之间的学生的成绩：- 学生 ID,学生姓名, 企业管理,马克思,UML,数
16、据库,平均成绩select top 6 max(a.sno) 学号, max(b.sname) 姓名,max(case when cno = 1 then score end) as 企业管理,max(case when cno = 2 then score end) as 马克思,max(case when cno = 3 then score end) as UML,max(case when cno = 4 then score end) as 数据库,avg(score) as 平均分from sc a, student bwhere a.sno not in (select top
17、2 sno from sc where cno = 1 order by score desc)and a.sno not in (select top 2 sno from sc where cno = 2 order by score desc)and a.sno not in (select top 2 sno from sc where cno = 3 order by score desc)and a.sno not in (select top 2 sno from sc where cno = 4 order by score desc)and a.sno = b.snogrou
18、p by a.sno-23、统计打印各科成绩,各分数段人数:课程 ID,课程名称,100-85,85-70,70-60, = 85 then 1 else 0 end) as 100-85,sum(case when score = 70 then 1 else 0 end) as 85-70,sum(case when score = 60 then 1 else 0 end) as 70-60,sum(case when score = t1.pjf) as 名次,- sno as 学号,- pjf as 平均分-from t1-order by pjf desc-goselect(sel
19、ect count(1)from (select distinct avg(score) as pjf from sc group by sno) as t2where pjf = t1.pjf) as 名次,sno as 学号,pjf as 平均分from (select sno, avg(score) as pjf from sc group by sno) as t1order by pjf descgo-25、查询各科成绩前三名的记录:(不考虑成绩并列情况) -drop table aa-select sno, cno, score into aa from sc order by c
20、no, score desc-drop table bb-select distinct cno, score into bb from sc order by cno, score desc-select aa.* from aa-where aa.score in (select top 3 score from bb where o = o)select * from (select top 9999 sno, cno, score from sc order by cno, score desc) as aawhere aa.score in (select top 3 scorefr
21、om (select distinct top 9999 cno, score from sc order by cno, score desc) as bbwhere o = o)-26、查询每门课程被选修的学生数 select cno,count(sno) from sc group by cno-27、查询出只选修了一门课程的全部学生的学号和姓名 SELECT sc.sno, student.sname, count(cno) AS 选课数 FROM sc, student WHERE sc.sno = student.snoGROUP BY sc.sno, student.snameH
22、AVING count(cno) = 3-28、查询男生、女生人数 select (select count(1) from student where ssex = 男) 男生人数,(select count(1) from student where ssex = 女) 女生人数-29、查询姓“张”的学生名单 SELECT sname FROM student WHERE sname like 张%-30、查询同名同性学生名单，并统计同名人数-select sname, count(1) from student group by sname having count(1) 1-31、19
23、81 年出生的学生名单( 注：student 表中 sage 列的类型是 datetime) select sname, CONVERT(char(4), DATEPART(year,sage) as age from student where DATEPART(year,sage)=1981-32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列 select cno 课程号, avg(score) 平均分from sc group by cno order by 平均分 asc, cno desc-33、查询平均成绩大于 80 的所有学生的学号、姓名和平均
24、成绩select sno, avg(score)from scgroup by snohaving avg(score) 80-34、查询 数据库 分数 低于 60 的学生姓名和分数select c.sname, a.scorefrom sc a, course b, student cwhere o = o and a.sno = c.snoand ame = 数据库 and score =70 AND sc.sno=student.sno; -37、查询不及格的课程，并按课程号从大到小排列select cno, score from sc where score 80 and cno=3-
25、39、求选了课程的学生人数 select count(distinct sno) from sc-40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩 select student.sname,cname, score from student,sc,course C,teacher where student.sno=sc.sno and o=C.cno and C.tno=teacher.tnoand teacher.tname =叶平and sc.score=(select max(score)from sc where cno = C.cno)-41、查询各个课程及相
26、应的选修人数 select cno 课程号, count(1) 选修人数 from sc group by cno-42、查询不同课程成绩相同的学生的学号、课程号、学生成绩 select distinct A.sno, A.cno,B.scorefrom sc A ,sc Bwhere A.Score=B.Score and A.cno 1order by count(1) desc,cno -45、检索至少选修了 5 门课程的学生学号select sno from sc group by sno having count(1) = 5-46、查询全部学生都选修的课程的课程号和课程名-(思路:
27、查询最受欢迎的课程是啥)-select cno 课程 ID, count(1) 选修人数 from sc group by cnoselect o, cnamefrom sc, coursewhere o = ogroup by o, cnamehaving count(o) = (select count(1) from student)-查询最受欢迎的课程select cno 课程 ID, count(cno) 选修人数from sc group by cnohaving count(cno) in (select top 1 count(cno) from sc group by cno
28、 order by count(cno) desc)order by 选修人数 desc-47、查询没学过“叶平”老师讲授的任一门课程的学生姓名-思路: 先得到学过“叶平”老师讲授的所有课程清单-select o from course a, teacher b where a.tno = b.tno and b.tname = 叶平)-然后: 从 sc 表中 得到 学过上述课程的 所有学生清单-select sno from sc where cno in-(select o from course a, teacher b where a.tno = b.tno and b.tname =
29、 叶平)-最后: 从 student 表中 得到不在上数学生清单中的其他学生，即为最后的查询结果select sno, sname from studentwhere sno not in(select sno from sc where cno in(select o from course a, teacher b where a.tno = b.tno and b.tname = 叶平)select sno, sname from studentwhere sno not in(select sno from course,teacher,scwhere course.tno=teach
30、er.tno and o=o and tname=叶平)-48、查询两门以上不及格课程的同学的学号及其平均成绩-思路: 先查询出所有不及格的 sc 中的记录-select sno, score from sc where score 2-49、检索 4 号课程分数大于 60 的同学学号，按分数降序排列select sno, score from sc where cno = 4 and score 60 order by score desc-50、删除 2 号同学的课程 1 的成绩-delete sc where sno = 2 and cno = 1-select * from sc where sno = 2 and cno = 1delete from sc where sno = 2 and cno = 1作业：-43.查询各单科状元-46.查询最受欢迎的课程(选修学生最多的课程 )-xx.查询成绩最好的课程-xx.查询最受欢迎的老师(选修学生最多的老师 )-xx.查询教学质量最好的老师-xx.查询需要补考的各科学生清单
