笔试中经常遇到的数据库代码集合.doc

-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、查询所有同学的学号、姓名、选课数、总成绩select 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 student.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和课程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,student.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_2.cno=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 = 叶平)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 where 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 score 60)-11、查询没有学全所有课的同学的学号、姓名select student.sno, student.snamefrom student, scwhere student.sno = sc.snogroup by student.sno, student.snamehaving count(o) (select count(cno) from course)-12、查询至少有一门课程 与 学号为1的同学所学课程 相同的同学的学号和姓名select distinct a.sno, a.snamefrom student a, sc bwhere a.sno 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_2.cno=o)from course,teacher where o=o and course.tno=teacher.tno and teacher.tname=叶平-14、查询和2号同学学习的课程完全相同的其他同学学号和姓名/* -Do first :select snofrom scwhere sno 2group by snohaving sum(cno) = (select sum(cno) from sc where sno = 2)*/select b.sno, b.snamefrom sc a, student bwhere b.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的平均成绩INSERT 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 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 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），数据库（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 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) / 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,数据库,平均成绩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 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.snogroup 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 60 then 1 else 0 end) as = t1.pjf) as 名次,-sno as 学号,-pjf as 平均分-from t1-order by pjf desc-goselect(select 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 cno, 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 scorefrom (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.snameHAVING 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、1981年出生的学生名单(注：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的所有学生的学号、姓名和平均成绩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.sno and ame = 数据库 and score =70 AND sc.sno=student.sno; -37、查询不及格的课程，并按课程号从大到小排列 select cno, score from sc where score 80 and cno=3-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、查询各个课程及相应的选修人数 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 B.cnoorder by B.score-43、查询每门课程成绩最好的前两名的学生ID-先按照 课程, 成绩 高低 对 sc表 排序-select * from sc order by cno, score descselect * from sc awhere score in (select top 2 score from sc where o = o order by sc.score desc)order by o, a.score desc-查询各单科状元select * from sc awhere score = (select top 1 score from sc where o = o order by sc.score desc)order by o, a.score desc-44、统计每门课程的学生选修人数(至少有2人选修的课程才统计)。要求输出课程号和选修人数，-查询结果按人数降序排列，若人数相同，按课程号升序排列 select cno as 课程号,count(1) as 人数 from sc group by cno having count(1) 1order by count(1) desc,cno -45、检索至少选修了5门课程的学生学号select sno from sc group by sno having count(1) = 5-46、查询全部学生都选修的课程的课程号和课程名-(思路:查询最受欢迎的课程是啥)-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 order by count(cno) desc)order by 选修人数 desc-47、查询没学过“叶平”老师讲授的任一门课程的学生姓名-思路: 先得到学过“叶平”老师讲授的所有课程清单-select o fr