sql查询语句练习.docx

《sql查询语句练习.docx》由会员分享，可在线阅读，更多相关《sql查询语句练习.docx（13页珍藏版）》请在三一文库上搜索。

1、sql查询语句练习(解析版)BY DD表情况Student(S#,Sname,Sage,Ssex)学生表Course(C#,Cname,T#)课程表SC(S#,C#,score)成绩表Teacher(T#,Tname)教师表createtable Student(S#varchar2 (20),Sname varchar2 (10),Sageint ,Ssex varchar2 (2)createtable Course(C#varchar2 (20),Cname varchar2 (10),scorevarchar2 (4);createtable SC(S# varchar2 (20),C

2、# varchar2 (20),score varchar2! (4);createtable Teacher(T#varchar2 (20),Tname varchar2 (10);insertintoStudent(S#,Sname,Sage,Ssex)values (1001,李五,15,男)insertintoStudent(S#,Sname,Sage,Ssex)values (1002,张三,16,女,)insertintoStudent(S#,Sname,Sage,Ssex)values (1003,李四,15,女,)insertintoStudent(S#,Sname,Sage,

3、Ssex)values (1004,陈二,14,男)insertintoStudent(S#,Sname,Sage,Ssex)values (1005,小四,15,男)问题：1、查询001”课程比002”课程成绩高的所有学生的学号；select a.S#from (select s#,scorefrom SC where C#=001) a,(select s#,scorefrom SC where C#=002) bwhere a.scorb.scoreand a.s#=b.s#;解析：(select s#,score from SC where C#=001 ) a/ 从 SC 中查询 C

4、#=001 的学生学号和分数，并定义为a表。(select s#,score from SC where C#=002 ) b / 从 SC中查询 C#=002的学生 学号和分数，并定义为b表。where a.score b.score and a.s# =b.s# 条件，a 表与 b 表中学号相同且分数 比b中的高。2、查询平均成绩大于60分的同学的学号和平均成绩；select S#,avg(score) rom scgroup by S# having avg(score) 60;解析：group by S# having avg(score) 60这里是指平均分数大于 60分的学号。使用

5、HAVING子句原因是，WHER庆键字无法与合计函数一起使用 合计函数(比如SUM)常常需要添加GROUFBY语句，根据一个或多个列对结果 集进行分组。3、查询所有同学的学号、姓名、选课数、总成绩；select Student.S#,Student.Snamcpunt(SC.C#),sum(score)from Studentleft Outer join SC on Student.S#=SC.S#group by Student.S#,Sname解析：Student left Outer join SC on Student.S#=SC.S#左外连接，从student表返回所有的行放于左表

6、中，从SC中返回的与左表匹配的行放于右表。 Student表中有但SC表中没有的在右表中对应行留空。group by Student.S#,Sname 以 Student.S#,Sname 分组。4、查询姓 季”的老师的个数；selectcount(distinct(Tname)from Teacherwhere Tnamelike 李 ;解析：distinct(Tname)在表中，可能会包含重复值。DISTINCT用于返回唯一不同的值，这里的作用就是防止返回的Tname出现重复值。where Tname like 李模糊查询,%”代表一或多个字符 。5、查询没学过 叶平”老师课的同学的学号、

7、姓名；Select Student.S#,Student.Snamefrom Studentwhere S# not in (select distinc(SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=叶平 );解析：not in 题目是查询没学过“叶平”老师课的同学的学号、姓名，这里使 用not in 就表示所要查询的S#在后面的结果中。(select distinct(SC.S#) from SC,Course,Teacher whereSC.C#

8、=Course.C#and Teacher.T#=Course.T# and Teacher.Tname= 叶平)；这段语句是查询学过“叶平”老师课的同学的学号，这里其实查询的SC表，但可看到查的表是SG Course, Teacher表，根据后面的条件语句，用来关联查询 的。三个条件要同时满足，从而查询出“叶平”老师课的同学的学号SC.C#=Course.C# , Teacher.T#=Course.T# 由于 SC与 Teacher 并无直接联系，这里将SC与Teacher表联系起来， Teacher.Tname=叶平：教师名为叶平, 从而对应到SC表。6、查询学过001”并且也学过编号0

9、02”课程的同学的学号、姓名；select Student.S#,Student.Snamefrom Student,SC where Student.S#=SC.S# and SC.C#=001and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C抖002);解析：select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#=001 这一句查询到的是学过“ 001”课程的同 学的学号、姓名；and exists(

10、Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#=002);使用exists关键字，用来判断是否存在的，当exists (查询)中的查询结果存 在时则返回真，否则返回假。这里指查询学过 002课程的同学，使用and则将 得到的结果与上一句匹配得上就都为真，返回结果。若不匹配，则不返回结果。7、查询学过叶平”老师所教的所有课的同学的学号、姓名；selectS#,Snamefrom Studentwhere S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C#

11、and Teacher.T#=Course.T# and Teacher.Tnam=叶 平 group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname=叶平)；解析：这一道题与第5题接近，只是答案后面多了：group by S# havingcount(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname= 叶平)。Group byh

12、aving. 的句式我们已经知道是因为 WHER庆键字无法与合计函数 一起使用。合计函数(比如SUM)常常需要添加GROUFBY语句，根据一个或多 个列对结果集进行分组。接下来就是理解 count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname=叶平)的含义了， count(SC.C#)是基于前面条件叶平老师在SC表中课程号的数量，selectcount(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname= 叶

13、平，是从Course中查询叶平老师的所有课程号数量。这里条件是说course中叶平老师的课程数与成绩表中的课程数相等。(SB我也不知为什么这么写)8、查询课程编号002”的成绩比课程编号001”课程低的所有同学的学号、姓名； Select S#,Sname from (select Student.S#Student.Snamescore,(selectscore fromSC SC_2where SC_2.S#=Student.S#and SC_2.C#=002) score2from Student,SCwhere Student.S#=SC.S# and C#=001) S_2 wher

14、e score2score;解析：1.select score as score2 from SCSC_2where SC_2.S#=Student.S# and SC_2.C#=002;2.select Student.S#,Student.Sname,score , score2as S_2 fromStudent,SC where Student.S#=SC.S# and C#=001;3.Select S#,Sname from S_2 where score2 60);不解析！10、查询没有学全所有课的同学的学号、姓名；select Student.S#,Student.Sname

15、from Student,SCwhere Student.S#=SC.S# group by Student.S#,Student.Snameiaving count(C#) (selectcount(C#) from Course);解析：having count(C#) =60 THEN 1 ELSE 0 END)/COUNT(*) AS及格百分数FROM SCCourse where t.C#=course.C# GROUP BY t.C#ORDER BY 100 * SUM(CASE WHEN isnull(score0)=60 THEN 1 ELSE 0 END)/COUNT(*)

16、DESC解析：isnull(AVG(score),0)使用指定的替换值替换 NULL,即是说当返回的值为空时使用0代替输出，当不为空时，则输出 AVG(score)的值。100 * SUM(CASE WHEN isnull(score,0)=60 THEN 1 ELSE 0 END)/COUNT(*)CASE WHEN isnull(score,0)=60 THEN 1 ELSE 0 END: 当 isnull(score,0) 大 于等于60时，输出1,否则为0,计算 CASE WHEN isnull(score,0)=60 THEN 1 ELSE 0 END输出的总和，再除于Count (

17、*)总个数，就是百分数了。至于乘于100,神马？20、查询如下课程平均成绩和及格率的百分数 （用1行显示）：企业管理（001）, 马克思（002）, OO&UML （003）,数据库（004）SELECT SUM（CASE WHEN C# =001THEN score ELSE 0 END）/SUM（CASEWHENC# = 001THEN 1 ELSE 0 END) AS 企业管理平均分,100 * SUM(CASE WHEN C# = 001 AND score = 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = 001THEN 1 ELSE 0 END)

18、AS企业管理及格百分数,SUM(CASE WHEN C# = 002 THEN score ELSE 0 END)/SUM(CASE C# WHEN 002THEN 1 ELSE 0 END) AS 马克思平均分,100 * SUM(CASE WHEN C# = 002 AND score = 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = 002 THEN 1 ELSE 0 END) AS 马克思及格百分 数,SUM(CASE WHEN C# = 003THEN scoreELSE 0 END)/SUM(CASE C# WHEN 003THEN 1 ELSE

19、 0 END) AS UML 平均分,100 * SUM(CASE WHEN C# = 003 AND score = 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = 003THEN 1 ELSE 0 END) AS UML 及格百分数,SUM(CASE WHEN C# = 004 THEN score ELSE 0 END)/SUM(CASE C# WHEN 004THEN 1 ELSE 0 END) AS 数据库平均分,100* SUM(CASE WHEN C# = 004 AND score = 60 THEN 1 ELSE 0 END)/SUM(CAS

20、E WHEN C# = 004 THEN 1 ELSE 0 END) AS 数据库及格百分 数FROM SC解析：不就是计算平均分和百分比么，case whenthenelse .then平均分：SUM(CASE WHEN C# =001 THEN score ELSE 0 END)/SUM(CASE WHENC# =001 THEN 1 ELSE 0 END) 课程为001的总分数/课程为001的总个数=平均分百分数：100 * SUM(CASE WHEN C# = 001 AND score = 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = 001 TH

21、EN 1 ELSE 0 END程为 001 的大于等于 60分的个数/课程为001的总个数21、查询不同老师所教不同课程平均分从高到低显示SELECT max(Z.T#) AS 教师 ID,MAX (Z.Tname) AS 教师姓名,C.C# AS 课程ID,MAX (C.Cname)AS 课程名称,AVG(Score) AS 平均成绩FROM SC AS T,CourseAS C ,TeacherAS Zwhere T.C#=C.C# and C.T#=Z.T#GROUP BY C.C#ORDER BY AVG (Score) DESC不解析：max(Z.T#) , MAX(Z.Tname)

22、22、查询如下课程成绩第3名到第6名的学生成绩单：企业管理(001),马克 思(002), UML (003),数据库(004)。学生ID,学生姓名,企业管理，马克思,UML,数据库，平均成绩SELECT DISTINCT top 3SC.S#As学生学号，Student.SnameAS 学生姓名,Tl.scoreAS企业管理,T2.scoreAS 马克思，T3.scoreAS UML,T4.scoreAS 数据库, ISNULL(T1.score,0) + ISNULL (T2.score,0) + ISNULL (T3.score0) + ISNULL (T4.score0) as 总分F

23、ROM Student,SCLEFT JOIN SC AS T1 ON SC.S# = T1.S# AND T1.C# = 001LEFT JOIN SC AS T2 ON SC.S# = 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# and ISNULL (T1.score0) + ISNULL (T2.score0) +ISNULL (T3.scor

24、e0) + ISNULL (T4.score,0)NOT IN(SELECT DISTINCT TOP 15 WITH TIESISNULL (T1.score,0) + ISNULL (T2.score0) + ISNULL (T3.score0) +ISNULL (T4.score0)FROM SCLEFT JOINsc AS T1 ONsc.S#=T1.S#ANDT1.C#=k1LEFT JOINsc AS T2 ONsc.S#=T2.S#ANDT2.C#=k2LEFT JOINsc AS T3 ONsc.S#=T3.S#ANDT3.C#=k3LEFT JOINsc AS T4 ONsc

25、.S#=T4.S#ANDT4.C#=k4ORDER BYISNULL (T1.score0)+ISNULL (T2.score0)+ ISNULL (T3.score0) +ISNULL (T4.score0) DESC);23、统计列印各科成绩，各分数段人数：课程ID,课程名 称,100-85 ,85-70 ,70-60, 60SELECTSC.C# as 课程 ID,Cname as课程名称，SUM(CASEWHEN score BETWEEN 85 AND 100THEN 1 ELSE 0 END) AS 100-85,SUM(CASE WHEN score BETWEEN 70 AND

26、 85 THEN 1 ELSE 0 END) AS 85 - 70,SUM(CASEWHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS 70 - 60,SUM(CASEWHEN score T2.平均成绩)as名次，S# as学生学号，平均成绩FROM(SELECT S#,AVG(score)平均成绩 FROM SC GROUP BY S# ) AS T2ORDER BY平均成绩desc;解析：select s#,avg(score) as平均成绩 from SC group by s#-查询学号和平均成1+ (select count(dis

27、tinct平均成绩)from T1。)计算出名次，随着循环加1。(SELECT S#,AVG(score)平均成绩 FROM SC GROUP BY S# ) AS T2T2在不断缩小范围由平均成绩 T2.平均成绩看出。25、查询各科成绩前三名的记录：(不考虑成绩并列情况)SELECT t1.S# as 学生 ID, t1.C# as 课程 ID,Score as 分数FROM SC t1WHERE score IN (SELECT TOP 3 scoreFROM SCWHERE t1.C#= C# ORDER BY score DESC)ORDER BY tl.C#;解析：1.SELECT

28、TOP 3 score as score3 FROM SC ORDER BY score DESC2 .SELECTt1.S# as 学生 ID, t1.C# as 课程 ID,Score as 分数 FROMSCt1 WHERE score in score3 ORDER BY t1.C#;26、查询每门课程被选修的学生数select c#,count(S#) from sc group by C#;不解析！27、查询出只选修了一门课程的全部学生的学号和姓名select SC.S#,Student.Snameount(C#) AS 选课数from SC ,Studentwhere SC.S#

29、=Student.S#groupby SC.S# ,Student.Snameiaving count(C#)=1;不解析！28、查询男生、女生人数Selectcount(Ssex) as 男生人数 from Student group by Ssexhaving Ssex=男；Selectcount(Ssex) as 女生人数 from Student group by Ssexhaving Ssex=女；不解析！29、查询姓张”的学生名单SELECT SnameFROM StudentWHERE Snamelike 张;不解析！30、查询同名同性学生名单，并统计同名人数select Sna

30、mecount(*) from Studentgroup by Snamehaving count(*)1;不解析！31、1981年出生的学生名单(注：Studen徐中Sage列的类型是datetime) selectSname, CONVERT (char (11), DATEPART(yeai;Sage) as age from studentwhere CONVERT(char(11)；DATEPART(yeai;Sage)尸1981;解析：CONVERT(char (11),DATEPART(year,Sage),DATEPART()函数用于返回日期/时间的单独部分，比如年、月、日、小

31、时、分钟 等等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,SCwhere Student.S#=SC.S# group by SC.S#,Snamehaving avg(score)85;不解析！34、查询课程名称为 数据库”，且分数低于60的学生姓名和分数Select

32、 Snamejsnull (score0)from Student,SC,Coursewhere SC.S#=Student.S# and SC.C#=Course.C#and Course.CnameF数据库and score=70 AND SC.S#=student.S#;解析：当某一同学有一门课程成绩大于 70,则distinct 可无，但当某一同学有 多门课程成绩大于70,返回的结果将是有多少个上70分就多少条记录，并不理 是否同一个人，这是就要是用 distinct 函数。37、查询不及格的课程，并按课程号从大到小排列select c# from sc where scor e80

33、and C#=003;不解析！39、求选了课程的学生人数selectcount(*) from sc;不解析!40、查询选修叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩select Student.Sname,scorfrom Student,SC,Course ,Teacherwhere Student.S#=SC.S# and SC.C#=Course.C#and Course.T#=Teacher.T#and Teacher.Tnam=叶平and SC.score=(selectmax(scorefrom SC where C#=Course.C# );解析：and SC.sc

34、ore=(select max(score)from SC where C#=Course.C# ) 课 程分数最高的。41、查询各个课程及相应的选修人数selectcount(*) from sc group by C#;不解析！42、查询不同课程成绩相同的学生的学号、课程号、学生成绩select distinct A.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 分

35、数FROM SC t1WHERE score IN (SELECT TOP 2 scoreFROM SCWHERE t1.C#= C#ORDER BY score DESC )ORDER BY t1.C#;解析： 分解-1.select top 2 score as score2 from sc where t1.c#=c# order by score desc;2.selectt1.s# as 学生 ID,t1.c#as 课程 ID,Score as 分数 fromSC t1Where score in score order by t1.c#;44、统计每门课程的学生选修人数(超过10人

36、的课程才统计)。要求输出课程号 和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列select C# as 课程号,count(*) as 人数from scgroup by C#order by count(*) descc#不解析！45、检索至少选修两门课程的学生学号select S# from scgroup by s# having count(*) = 2 不解析！46、查询全部学生都选修的课程的课程号和课程名select C#,Cnamefrom Coursewhere C# in (select c# from sc group by c#) 解析：有成绩便是有选修！47、查询没学过叶平”老师讲授的任一门课程的学生姓名select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T#and SC.C#=course.C#and Tname=叶平)； 不解析！48、查询两门以上不及格课程的同学的学号及其平均成绩select S#,avg(isnull(score0) from SC where S# in (selectS# from SC where score60 group by S# havi