50SQL语句题目及答案

1/*Student(S#,Sname,Sage,Ssex)学生表Course(C#,Cname,T#)课程表SC(S#,C#,score)成绩表Teacher(T#,Tname)教师表*/--1、查询“001”课程比“002”课程成绩高的所有学生的学号；selects1.s#fromSCs1,SCs2wheres1.s#=s2.s#ands1.c#='001'ands2.c#='002'ands1.scores2.score;--2、查询平均成绩大于60分的同学的学号和平均成绩；selects.s#,avg(s.score)fromSCsgroupbys.s#havingavg(s.score)60;--3、查询所有同学的学号、姓名、选课数、总成绩；selectd.s#,max(d.sname),count(distincts.c#),sum(s.score)fromStudentd,SCswhered.s#=s.s#groupbyd.s#;--4、查询姓“李”的老师的个数；selectcount(0)fromTeachertwheret.tnamelike'李%';--5、查询没学过“叶平”老师课的同学的学号、姓名；selectd.s#,d.snamefromStudentdwherenotexists(selectdistinct(s.s#)fromSCs,Teachert,Coursecwheret.t#=c.t#ands.c#=c.c#andt.tname='叶平'andd.s#=s.s#);--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；selectd.s#,d.snamefromStudentd,SCswheres.s#=d.s#ands.c#='001'andexists(selects1.s#fromSCs1wheres1.s#=s.s#ands1.c#='002');--7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；selects.s#,max(d.sname)fromSCs,Studentd,Coursec,Teachertwheres.s#=d.s#ands.c#=c.c#andc.t#=t.t#andt.tname='叶平'groupbys.s#havingcount(distincts.c#)=(selectcount(0)fromCoursec,Teachertwherec.t#=t.t#andt.tname='叶平');--8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；selectd.s#,d.snamefromSCs1,SCs2,Studentdwheres1.s#=s2.s#ands1.s#=d.s#ands1.c#='002'ands2.c#='001'ands1.scores2.score;--9、查询所有课程成绩小于60分的同学的学号、姓名；selects.s#,max(d.sname)fromStudentd,SCswhered.s#=s.s#ands.score60groupbys.s#havingcount(0)=(selectcount(0)fromSCs1wheres.s#=s1.s#);--10、查询没有学全所有课的同学的学号、姓名；selects.s#,max(d.sname)fromStudentd,SCswhered.s#=s.s#groupbys.s#havingcount(distincts.c#)(selectcount(0)fromCourse);2--11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；selectdistincts.s#,d.snamefromStudentd,SCswhered.s#=s.s#ands.s#'1001'andexists(selects1.c#fromSCs1wheres1.s#='1001'ands.c#=s1.c#);--12、查询至少学过学号为“1010”同学所有一门课的其他同学学号和姓名；selectdistincts.s#,d.snamefromStudentd,SCswhered.s#=s.s#ands.s#'1010'ands.c#in(selects1.c#fromSCs1wheres1.s#='1010');--13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；updateSCssets.score=(selectavg(s1.score)fromSCs1wheres.c#=s1.c#)whereexists(selects.c#fromCoursec,Teachertwheres.c#=c.c#andc.t#=t.t#andt.tname='叶平');--14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；selects.s#,max(d.sname)fromStudentd,SCswhered.s#=s.s#ands.s#'1002'andexists(selectc#fromSCwheres#='1002'ands.c#=c#)groupbys.s#havingcount(0)=(selectcount(0)fromSCwheres#='1002')andcount((selectcount(0)fromSCwheres#=s.s#))=(selectcount(0)fromSCwheres#='1002');--15、删除学习“叶平”老师课的SC表记录；deleteSCswhereexists(selects.c#fromCoursec,Teachertwheres.c#=c.c#andc.t#=t.t#andt.tname='叶平');--16、向SC表中插入一些记录，这些记录要求符合以下条件：--没有上过编号“003”课程的同学学号、002号课的平均成绩;insertintoSC(selectd.s#,'002',(selectround(avg(score),2)fromSCwherec#='002')fromStudentdwherenotexists(selectdistincts.s#fromSCswheres.s#=d.s#ands.c#='003'));--17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，--按如下形式显示：学生ID,,数据库,企业管理,英语,有效课程数,有效平均分;selects.s#as学生ID,(selectavg(s1.score)fromSCs1,Coursecwheres1.c#=c.c#ands.s#=s1.s#andc.cname='Java编程思想')asJava编程思想,(selectavg(s1.score)fromSCs1,Coursecwheres1.c#=c.c#ands.s#=s1.s#andc.cname='Struts2基础')asStruts2基础,(selectavg(s1.score)fromSCs1,Coursecwheres1.c#=c.c#ands.s#=s1.s#andc.cname='Spring基础')asSpring基础,count(0)as有效课程数,round(avg(s.score),2)as有效平均分fromSCs,Coursecwheres.c#=c.c#andc.cnamein('Java编程思想','Struts2基础','Spring基础')groupbys.s#orderbyround(avg(s.score),2)desc;--18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分selects.c#as课程ID,max(s.score)as最高分,min(s.score)as最低分fromSCsgroupbys.c#;--19、按各科平均成绩从低到高和及格率的百分数从高到低顺序selects.c#as课程ID,round(avg(s.score),2)as平均分,round(100*sum(casewhens.score=60then1else0end)/count(s.score),0)||'%'as及格率fromSCsgroupbys.c#;--20、查询如下课程平均成绩和及格率的百分数(用1行显示):--企业管理（001），马克思（002），OO&UML（003），数据库（004）selectround(sum(casewhenc.cname='Java编程思想'thens.scoreelse0end)/sum(casec.cnamewhen'Java编程思想'then1else0end),2)asJava编程思想,round(100*sum(casewhens.score=60andc.cname='Java编程思想'then1else0end)/sum(casewhenc.cname='Java编程思想'then1else0end),0)||'%'as及格率,round(sum(casewhenc.cname='Struts2基础'thens.scoreelse0end)/sum(casec.cnamewhen'Struts2基础'then1else0end),2)asStruts2基础,round(100*sum(casewhens.score=60andc.cname='Struts2基础'then1else0end)/sum(casewhenc.cname='Struts2基础'then1else0end),0)||'%'as及格率fromSCs,Coursec3wheres.c#=c.c#;--21、查询不同老师所教不同课程平均分从高到低显示--SELECTmax(Z.T#)AS教师ID,MAX(Z.Tname)AS教师姓名,--C.C#AS课程ＩＤ,MAX(C.Cname)AS课程名称,AVG(Score)AS平均成绩selectt.t#as教师ID,max(t.tname)as教师名称,c.cnameas所教课程,round(avg(s.score),2)as平均分fromTeachert,Coursec,SCswheret.t#=c.t#andc.c#=s.c#groupbyt.t#,c.cnameorderbyt.t#,round(avg(s.score),2)desc;--22、查询如下课程成绩第3名到第6名的学生成绩单：--企业管理（001），马克思（002），UML（003），数据库（004）selectd.s#,d.sname,c.cname,s.score,s.rank1as名次fromStudentd,Coursec,(selects#,c#,score,row_number()over(partitionbyc#orderbyscoredesc)rank1fromSC)swheres.c#=c.c#andd.s#=s.s#andc.cnamein('Java编程思想','Struts2基础','Hibernate基础','Spring基础')and(s.rank1between3and6)orderbyc.c#,s.rank1;--23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[60]selects.c#,max(c.cname),sum(casewhens.scorebetween85and100then1else0end)as分数85至100,sum(casewhens.scorebetween70and85then1else0end)as分数70至85,sum(casewhens.scorebetween60and70then1else0end