《2022年最叼sql查询语句练习整理 .pdf》由会员分享，可在线阅读，更多相关《2022年最叼sql查询语句练习整理 .pdf（8页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、最叼的 SQL查询语句练习，学习后不必再学其它。我用的是mySQL，先建表，再练习，效果超级好。Student(S#,Sname,Sage,Ssex)学生表Course(C#,Cname,T#)课程表SC(S#,C#,score)成绩表Teacher(T#,Tname)教师表问题：1、查询“001”课程比“002”课程成绩高的所有学生的学号；select a.S#from (select s#,score from SC where C#=001)a,(select s#,score from SC where C#=002)b where a.scoreb.score and a.s#=b.
2、s#;2、查询平均成绩大于60 分的同学的学号和平均成绩；select S#,avg(score)from sc group by S#having avg(score)60;3、查询所有同学的学号、姓名、选课数、总成绩；select Student.S#,Student.Sname,count(SC.C#),sum(score)from Student left Outer join SC on Student.S#=SC.S#group by Student.S#,Sname 4、查询姓“李”的老师的个数；select count(distinct(Tname)from Teacher w
3、here Tname like 李%;5、查询没学过“叶平”老师课的同学的学号、姓名；Student.S#,Student.Sname from Student where S#not in(select distinct(SC.S#)from SC,Course,Teacher whereSC.C#=Course.C#and Teacher.T#=Course.T#and Teacher.Tname=叶平);6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；selectStudent.S#,Student.Sname fromStudent,SC whereStudent
4、.S#=SC.S#andSC.C#=001and exists(Select*from SC as SC_2 where SC_2.S#=SC.S#and SC_2.C#=002);7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；select S#,Sname from Student whereS#in(selectS#fromSC,Course,Teacher whereSC.C#=Course.C#andTeacher.T#=Course.T#and Teacher.Tname=叶平 group by S#having count(SC.C#)=(selectcount(C#)f
5、rom Course,Teacher where Teacher.T#=Course.T#and Tname=叶平);8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；Select S#,Sname from(select Student.S#,Student.Sname,score,(select score from SC SC_2 where SC_2.S#=Student.S#and SC_2.C#=002)score2 from Student,SC where Student.S#=SC.S#and C#=001)S_2 where score2 6
6、0);10、查询没有学全所有课的同学的学号、姓名；select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S#group byStudent.S#,Student.Sname having count(C#)=60 THEN 1 ELSE 0 END)/COUNT(*)AS 及格百分数FROM SC T,Course where t.C#=course.C#GROUP BY t.C#ORDER BY 100*SUM(CASE WHENisnull(score,0)=60 THEN 1 ELSE 0 END)/COU
7、NT(*)DESC20、查询如下课程平均成绩和及格率的百分数(用1 行显示):企业管理（001），马克思（002），OO&UML（003），数据库（004）SELECT SUM(CASE WHEN C#=001 THEN score ELSE 0 END)/SUM(CASE C#WHEN001 THEN 1 ELSE 0 END)AS 企业管理平均分,100*SUM(CASEWHENC#=001ANDscore=60THEN1ELSE0END)/SUM(CASE WHEN C#=001 THEN 1 ELSE 0 END)AS 企业管理及格百分数,SUM(CASE WHEN C#=002 TH
8、EN score ELSE 0 END)/SUM(CASE C#WHEN 002THEN 1ELSE 0 END)AS 马克思平均分,100*SUM(CASEWHENC#=002ANDscore=60THEN1ELSE0END)/SUM(CASE WHEN C#=002 THEN 1 ELSE 0 END)AS 马克思及格百分数,SUM(CASE WHEN C#=003 THEN score ELSE 0 END)/SUM(CASE C#WHEN 003 THEN 1 ELSE 0 END)AS UML 平均分,100*SUM(CASEWHENC#=003ANDscore=60THEN1ELS
9、E0END)/SUM(CASE WHEN C#=003 THEN 1 ELSE 0 END)AS UML 及格百分数,SUM(CASE WHEN C#=004 THEN score ELSE 0 END)/SUM(CASE C#WHEN 004THEN 1ELSE 0 END)AS 数据库平均分,100*SUM(CASE WHEN C#=004 AND score=60 THEN 1 ELSE 0 END)/SUM(CASE WHEN名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 8 页 -C#=004 THEN 1 ELSE 0 END)AS 数据库及格百分数FROM SC 2
10、1、查询不同老师所教不同课程平均分从高到低显示SELECTmax(Z.T#)AS教 师ID,MAX(Z.Tname)AS教 师 姓 名,C.C#AS课 程,MAX(C.Cname)AS 课程名称,AVG(Score)AS 平均成绩FROM SC AS T,Course AS C,Teacher AS Z where T.C#=C.C#and C.T#=Z.T#GROUP BY C.C#ORDER BY AVG(Score)DESC22、查询如下课程成绩第3 名到第6 名的学生成绩单：企业管理（001），马克思（002），UML（003），数据库（004）学生 ID,学生姓名,企业管理,马克思,
11、UML,数据库,平均成绩SELECTDISTINCT top 3SC.S#As 学生学号,Student.Sname AS 学生姓名,T1.score AS 企业管理,T2.score AS 马克思,T3.score AS UML,T4.score AS 数据库,ISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)as 总分FROM Student,SC LEFT JOIN SC AS T1 ON SC.S#=T1.S#AND T1.C#=001LEFT JOIN SC AS T2 ON SC.S
12、#=T2.S#AND T2.C#=002LEFT JOIN SC AS T3 ON SC.S#=T3.S#AND T3.C#=003LEFT JOIN SC AS T4 ON SC.S#=T4.S#AND T4.C#=004WHERE student.S#=SC.S#andISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)NOT IN(SELECTDISTINCTTOP 15 WITH TIES ISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.sc
13、ore,0)+ISNULL(T4.score,0)FROM sc LEFT JOIN sc AS T1 ON sc.S#=T1.S#AND T1.C#=k1LEFT JOIN sc AS T2 ON sc.S#=T2.S#AND T2.C#=k2LEFT JOIN sc AS T3 ON sc.S#=T3.S#AND T3.C#=k3名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 8 页 -LEFT JOIN sc AS T4 ON sc.S#=T4.S#AND T4.C#=k4ORDERBYISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL
14、(T3.score,0)+ISNULL(T4.score,0)DESC);23、统计列印各科成绩,各分数段人数:课程 ID,课程名称,100-85,85-70,70-60,60SELECT SC.C#as 课程 ID,Cnameas 课程名称,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END)AS 100-85,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END)AS 85-70,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 E
15、LSE 0 END)AS 70-60,SUM(CASE WHEN score T2.平均成绩)as 名次,S#as 学生学号,平均成绩FROM (SELECT S#,AVG(score)平均成绩FROM SC GROUP BY S#)AS T2 ORDER BY 平均成绩desc;25、查询各科成绩前三名的记录:(不考虑成绩并列情况)SELECT t1.S#as 学生 ID,t1.C#as 课程 ID,Score as 分数FROM SC t1 WHERE score IN (SELECT TOP 3 score FROM SC WHERE t1.C#=C#ORDER BY score DES
16、C)ORDER BY t1.C#;26、查询每门课程被选修的学生数select c#,count(S#)from sc group by C#;27、查询出只选修了一门课程的全部学生的学号和姓名select SC.S#,Student.Sname,count(C#)AS 选课数from SC,Student where SC.S#=Student.S#group by SC.S#,Student.Sname having count(C#)=1;28、查询男生、女生人数名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 8 页 -Select count(Ssex)as 男生人数fr
17、om Student group by Ssex having Ssex=男;Select count(Ssex)as 女生人数from Student group by Ssex having Ssex=女；29、查询姓“张”的学生名单SELECT Sname FROM Student WHERE Sname like 张%;30、查询同名同性学生名单，并统计同名人数select Sname,count(*)from Studentgroup by Sname havingcount(*)1;31、1981 年出生的学生名单(注：Student 表中 Sage列的类型是datetime)se
18、lect Sname,CONVERT(char(11),DATEPART(year,Sage)as age from student whereCONVERT(char(11),DATEPART(year,Sage)=1981;32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列Select C#,Avg(score)from SC group by C#order by Avg(score),C#DESC;33、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩select Sname,SC.S#,avg(score)from Student,SC wh
19、ere Student.S#=SC.S#group by SC.S#,Sname havingavg(score)85;34、查询课程名称为“数据库”，且分数低于60 的学生姓名和分数Select Sname,isnull(score,0)from Student,SC,Course where SC.S#=Student.S#and SC.C#=Course.C#and Course.Cname=数据库 and score=70 AND SC.S#=student.S#;37、查询不及格的课程，并按课程号从大到小排列select c#from sc where scor e 80 andC#
20、=003;39、求选了课程的学生人数select count(*)from sc;40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩select Student.Sname,score from Student,SC,Course C,Teacher where Student.S#=SC.S#and SC.C#=C.C#andC.T#=Teacher.T#and Teacher.Tname=叶平 and SC.score=(select max(score)fromSC whereC#=C.C#);41、查询各个课程及相应的选修人数select count(*)from
21、sc group by C#;名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 8 页 -42、查询不同课程成绩相同的学生的学号、课程号、学生成绩select distinctA.S#,B.score from SC A ,SC B where A.Score=B.Score and A.C#B.C#;43、查询每门功成绩最好的前两名SELECT t1.S#as 学生 ID,t1.C#as 课程 ID,Score as 分数FROM SC t1 WHERE score IN (SELECT TOP 2 score FROM SC WHERE t1.C#=C#ORDER BY sco
22、re DESC)ORDER BY t1.C#;44、统计每门课程的学生选修人数（超过 10 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列select C#as 课程号,count(*)as 人数fromsc groupbyC#orderbycount(*)desc,c#45、检索至少选修两门课程的学生学号select S#fromsc groupbys#havingcount(*)=246、查询全部学生都选修的课程的课程号和课程名select C#,Cname fromCourse whereC#in(select c
23、#fromsc groupbyc#)47、查询没学过“叶平”老师讲授的任一门课程的学生姓名select Sname fromStudent where S#not in(select S#from Course,Teacher,SC whereCourse.T#=Teacher.T#and SC.C#=course.C#and Tname=叶平);48、查询两门以上不及格课程的同学的学号及其平均成绩select S#,avg(isnull(score,0)from SC where S#in(select S#from SC wherescore 2)group by S#;49、检索“004”课程分数小于60，按分数降序排列的同学学号select S#from SC where C#=004and score 60 order by score desc;50、删除“002”同学的“001”课程的成绩delete from Sc where S#=001and C#=001;名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 8 页 -本文档由 A5下载 http:/收集整理，版权归原作者所有。A5下载提供海量源码，软件，素材，教程文档下载。如果您恰好喜欢打篮球，请登录购买正品低价的斯伯丁篮球名师资料总结-精品资料欢迎下载-名师精心整理-第 8 页，共 8 页 -