1. 求解 sql server 2000 考试试题
一、填空题（4小题、每空1分、共5分）
1、 在SELECT查询语句中用_distinct____关键字来删除重复记录。
2、 DATEDIFF（YEAR,’2004-5-6’,’2008-9-7’）这个表达式的值__4__。
3、在SQL Server中，根据索引对数据表中记录顺序的影响，索引可以分为____asc____和___desc_____。
4、____检查___约束通过检查一个或多个字段的输入值是否符合设定的检查条件来强制数据的完整性。
2. 求帮解这几道SQL的题
1.创建数据表[宿舍表]代码；
宿舍表(宿舍号char(6)，宿舍电话)
要求使用：主键(宿舍号)、宿舍电话：以633开头的7位电话号码
createtable宿舍表
(宿舍号char(6)primarykey,
宿舍电话varchar(7)check(宿舍电话like'633%'))
2.将下列宿舍信息添加到宿舍表的代码
宿舍号宿舍电话
1016331157
1026331777
insertinto宿舍表values('101','6331157')
insertinto宿舍表values('102','6331777')
修改宿舍号为101的宿舍电话：6331158
update宿舍表set宿舍电话='6331158'wherertrim(宿舍号)='101'
删除宿舍号为102的宿舍信息
deletefrom宿舍表wherertrim(宿舍号)='102'
3.创建视图[同学表视图]代码；
同学表视图(学号,姓名,性别,年龄,民族,身份证号,宿舍号,宿舍电话)
createview同学表视图
as
select同学表.学号,同学表.姓名,同学表.性别,同学表.年龄,同学表.民族,同学表.身份证号,同学表.宿舍号,同学表.宿舍电话
from同学表,宿舍表where同学表.宿舍号=宿舍表.宿舍号
4.从同学表视图中查询姓张的女同学的姓名、性别、宿舍电话。
select姓名,性别,宿舍电话
from同学表视图where姓名like'张%'and性别='女'
5.从同学表中查询女同学的最大年龄、最小年龄、平均年龄。
selectmax(年龄)as最大年龄,min(年龄)as最小年龄,avg(年龄)as平均年龄
from同学表where性别='女'
少年，这个其实没什么太难的，多看看书就会，希望你能顺利通过考试
3. SQL的题目，最基础的～等～～
1. 从职工关系中检索所有工资值。
答：select 工资 from 职工表
2. 检索仓库关系中的所有记录
答：select * from 仓库表
3. 检索工资多于1230元的职工号
答：select 职工号 from 职工表 where 工资>1230
4.检索哪些仓库有工资多于1210元的职工。
答：select distinct 仓库号 from 职工表 where 工资>1210
5. 给出在仓库“wh1”或“wh2”工作，并且工资少于1250元的职工号。
答：select 职工号 from 职工表 where 工资<1250 ;
and (仓库号="wh1" or 仓库号="wh2")
注意：逻辑运算符的优先级从高到低依次为not、and、or。运算符的优先级:括号 算术运算 关系运算 逻辑运算.
说明：前面的几个例子在from之后只指定了一张表，也就是说这些检索只基于一张表。如果有where子句，系统首先根据指定的条件依次检验关系中的每条记录，然后选出满足条件的记录（相当于关系的选择操作），并显示select子句中指定属性的值（相当于关系的投影操作）。
6. 找出工资多于1230元的职工号和他们所在的城市。
答：select 职工表.职工号, 仓库表.城市 from 职工表,仓库表 ;
where 职工表.仓库号=仓库表.仓库号 and 工资>1230
7. 找出工作在面积大于400的仓库的职工号以及这些职工工作所在的城市。
答:select 职工表.职工号, 仓库表.城市, 仓库表.面积 ;
from 职工表,仓库表 where 职工表.仓库号=仓库表.仓库号 ;
and 仓库表.面积>400
说明:以上两题为简单的联接查询.
8. 哪些城市至少有一个仓库的职工工资为1250元
答:
方法一:
Select 仓库表.城市 from 职工表,仓库表 where 职工表.仓库号=仓库表.仓库号 and 职工表.工资=1250
方法二:
select 仓库号 from 职工表 where 工资=1250 into dbf abc.dbf
select 仓库表.城市 from 仓库表,abc where 仓库表.仓库号=abc.仓库号
方法三:
select 城市 from 仓库表 where 仓库号 in (select 仓库号 from 职工表 where 工资=1250)
说明: 这属于嵌套查询. 这类查询所要求的结果出自一个关系,但相关的条件却涉及多个关系.
可以看到,方法三的命令中含有两个select-from-where查询块,即内层查询块和外层查询块,内层查询块检索到的仓库值是wh1和wh2,这样就可以写出等价命令:
select 城市 from 仓库表 where 仓库号 in ("wh1","wh2")
或者
select 城市 from 仓库表 where 仓库号="wh1" or 仓库号="wh2"
9. 查询所有职工的工资都多于1210元的仓库的信息。
答:
方法一：
select 仓库号,min(工资) as 工资 from 职工表 group by 仓库号 into dbf 仓库min工资.dbf
select 仓库表.* from 仓库表,仓库min工资 where 仓库表.仓库号=仓库min工资.仓库号 and 仓库min工资.工资>1210
方法二：
select * from 仓库表 where 仓库表.仓库号 not in (select 仓库号 from 职工表 where 工资<=1210 ) and 仓库表.仓库号 in (select 仓库号 from 职工表)
（错误方法）
select * from 仓库表 where 仓库表.仓库号 not in (select 仓库号 from 职工表 where 工资<=1210 )
注意:上述检索结果错误,会将没有职工的仓库检索出来.如果要求排除那些还没有职工的仓库,检索要求可以叙述为:检索所有职工的工资都大于1210元的仓库的信息,并且该仓库至少要有一名职工.
（错误方法）
select * from 仓库表 where 仓库表.仓库号 in (select 仓库号 from 职工表 where 工资>1210 )
注意：上述查询结果错误。它会查出仓库号为wh1的信息，但wh1的职工工资并不都大于1210。
10. 找出和职工e4挣同样工资的所有职工。
答: Select 职工号 from 职工表 where 工资 in (select 工资 from 职工表 where 职工号="e4")
说明:7、9、10题都是基于多个关系的查询,这类查询所要求的结果出自一个关系,但相关的条件却涉及多个关系.我们称之为嵌套查询。嵌套查询优选含有两个select-from-where查询块的查询结构。
11. 检索出工资在1220元到1240元范围内的职工信息。
答:select * from 职工表 where 工资 between 1220 and 1240
说明: "工资 between 1220 and 1240"等价于"工资>=1220 and 工资<=1240"
如果要求查询工资不在1220元到1240元范围内的职工信息
说明: select * from 职工表 where 工资 not between 1220 and 1240
12. 从供应商关系中检索出全部公司的信息，不要工厂或其他供应商的信息。
Select * from 供应商表 where "公司" $ 供应商名
13. 找出不在北京的全部供应商信息。
Select * from 供应商表 where 地址!="北京"
或者
Select * from 供应商表 where not(地址="北京")
14. 按职工的工资值升序检索出全部职工信息。
答:select * from 职工表 order by 工资
如果需要将结果按降序排列,只要加上desc
select * from 职工表 order by 工资 desc
说明:使用SQL SELECT可以将查询结果排序,排序的短语是order by ,具体格式如下:
order by order_item [ASC|DESC] [,order_item [ASC|DESC]……]
15. 先按仓库号排序，再按工资排序并输出全部职工信息。
答：Select * from 职工表 order by 仓库号,工资
16. 找出供应商所在地的数目。
答:select count(distinct 地址) from 供应商表
注意:除非对表中的记录数进行计数,一般count函数应该使用distinct
比如: select count(*) from 供应商表
查询结果是供应商表中的记录数.
说明:可用于计算检索的函数有:count——计数 sum——求和
avg——计算平均值 max——求最大值 min——求最小值
17. 求支付的工资总数
答：select sum(工资) from 职工表
18. 求北京和上海的仓库职工的工资总和
答: select sum(工资) from 职工表,仓库表 where 职工表.仓库号=仓库表.仓库号 and (城市="北京" or 城市="上海")
方法二:
select sum(工资) from 职工表 where 仓库号 in (select 仓库号 from 仓库表 where 城市="北京" or 城市="上海")
19. 求所有职工的工资都多于1210元的仓库的平均面积
答：Select avg(面积) from 仓库表 where 仓库号 not in(select 仓库号 from 职工表 where 工资<=1210) and 仓库号 in(select 仓库号 from 职工表)
20. 求在wh2仓库工作的职工的最高工资值
答:select max(工资) from 职工表 where 仓库号="wh2"
21. 求每个仓库的职工的平均工资
答:select 仓库号,avg(工资) from 职工表 group by 仓库号
说明:可以利用group by 子句进行分组计算查询.group by短语的格式如下:group by groupcolumn[,groupcolumn……][having filtercondition]
可以按一列或多列分组,还可以用having 进一步限定分组的条件.
注意：where /group by等子句都不能放在from子句之前.
22. 求至少有两个职工的每个仓库的平均工资。
答: select 仓库号,count(*),avg(工资) from 职工表 group by 仓库号 having count(*)>=2
说明:having子句总是跟在group by 子句之后,不可以单独使用.having子句用于限定分组.
23. 找出尚未确定供应商的订购单
答：select * from 订购单表 where 供应商号 is null
24. 列出已经确定了供应商的订购单信息
答：select * from 订购单表 where 供应商号 is not null
4. sql 期末考试题
1、建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、 所在系Sdept五个属性组成，其中学号属性不能为空，并且其值是唯一的。
其值是唯一的，并不是说是主键，应该用关键字UNIQUE
CREATE TABLE Student(
Sno SMALLINT NOT NULL UNIQUE,
Sname CHAR(8),
Ssex CHAR(1),
Sage SMALLINT,
Sdept CHAR(20))
2、查询全体学生的详细记录
SELECT * FROM Student
3、查询年龄在20至23岁之间的学生的姓名、系别、和年龄
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23
4、计算1号课程的学生平均成绩
SELECT AVG(Grade) FROM SC WHERE Cno = 1
5、将计算机科学系全体学生的成绩置零
UPDATE SC
SET grade = 0
WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept = '计算机科学系')
5. SQL50题及答案
习题来源于网络，sql语句是自己的答案，部分有参考。欢迎指正及探讨。
1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
1.1 查询同时存在" 01 "课程和" 02 "课程的情况
1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null)
null提示：使用left join
1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
3. 查询在 SC 表存在成绩的学生信息
4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null)
4.1 查有成绩的学生信息
5. 查询“李”姓老师的数量
6. 查询学过“张三”老师授课的同学的信息
7. 查询没有学全所有课程的同学的信息
8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
9. 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
14. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率（及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
14.1要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
18. 查询各科成绩前三名的记录
19. 查询每门课程被选修的学生数
20. 查询出只选修两门课程的学生学号和姓名
21. 查询男生、女生人数
22. 查询名字中含有“风”字的学生信息
23. 查询同名同性学生名单，并统计同名人数
24. 查询 1990 年出生的学生名单
25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
27. 查询课程名称为“数学”，且分数低于 60 的学生姓名和分数
28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
30. 查询不及格的课程
31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
32. 求每门课程的学生人数
33. 成绩不重复，查询选修“张三”老师所授课程的学生中，成绩最高的学生信息及其成绩
34. 成绩有重复的情况下，查询选修“张三”老师所授课程的学生中，成绩最高的学生信息及其成绩
35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
36. 查询每门功成绩最好的前两名
37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）
38. 检索至少选修两门课程的学生学号
39. 查询选修了全部课程的学生信息
40. 查询各学生的年龄，只按年份来算
41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
42. 查询本周过生日的学生
43. 查询下周过生日的学生
44. 查询本月过生日的学生
45. 查询下月过生日的学生
1.1 查询同时存在" 01 "课程和" 02 "课程的情况
1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
4.1 查有成绩的学生信息
15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
6. sql基础选择题
d;
d;
d;
a;
b;
7. SQL经典50题题解
本篇文章主要是对SQL经典50题进行详细解析。
解析包含：1、解题思路，2、考核知识点，3、答案；
首先，表结构用脑图输出出来，如下所示：
先进行数据准备，建表以及插入数据。
1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数
解题思路：
第一步：关键词有“课程编号”、“课程成绩”、“学生信息”，锁定使用表：学生表、成绩表。
第二步：给出学生信息及课程分数，通过主键sid关联学生表和课程表。
第三步：比较同一个学生不同课程的成绩，再关联一次课程表，利用sid、cid进行关联。
第四步：根据题目，用where比较分数筛选结果。
考核知识点： join，where
答案：
1.1 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
解题思路：
第一步：关键词有“课程编号”、“学生编号”，锁定使用表：成绩表。
第二步：分别查询出存在" 01 "课程的学生和存在" 02 "课程的学生。
第三步：对两个子查询进行关联，用sid进行左联接。
考核知识点： where, 子查询，left join
答案：
1.2 查询同时存在01和02课程的情况
解题思路： 同1.1，把left join改为join
考核知识点： where, 子查询，join
答案：
1.3 查询选择了02课程但没有01课程的情况
解题思路： 类似1.1，把left join改为right join
考核知识点： where, 子查询，right join
答案：
小结： 上面的题主要考察join、left join、right join。
2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
解题思路：
第一步：关键词有“平均成绩”、“学生编号”、“学生姓名”，锁定使用表：成绩表、学生表。
第二步：给出学生信息及课程分数，通过主键sid关联学生表和课程表。。
第三步：根据题目，用group by聚合计算出平均成绩，然后筛选出大于等于60分的学生。
考核知识点： join、group by、avg()
答案：
3.查询在 SC 表存在成绩的学生信息
解题思路：
第一步：关键词有“SC”、“学生信息”，锁定使用表：成绩表、学生表。
第二步：用EXISTS判断在SC表存在成绩的学生信息
考核知识点： EXISTS语句
答案：
4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
解题思路：
第一步：关键词有“学生编号”、“学生姓名”、“选课总数”、“课程成绩”，锁定使用表：成绩表、学生表。
第二步：通过主键sid关联学生表成绩表，得到学生信息、学生成绩的宽表
第三步：根据题目，用group by聚合计算选课总数和总成绩
考核知识点： left join、group by、count()、sum()、ifnull()
答案：
5.查询“李”姓老师的数量
解题思路：
第一步：关键词有“老师的数量”，锁定使用表：教师表。
第二步：先筛选出“李”姓老师，再汇总统计“李”姓老师的数量
考核知识点： like、where、%、count()
答案：
6.查询学过“张三”老师授课的同学的信息
解题思路：
第一步：关键词有“老师”、“学生信息”，锁定使用表：教师表、学生表、成绩表、课程表。
第二步：通过sid关联学生表、成绩表，再通过cid关联课程表，最后通过tid关联教师表。
第三步：用where筛选出“张三”老师授课的同学的信息。
考核知识点： 多重连接join
答案：
7.查询没有学全所有课程的同学的信息。
解题思路：
第一步：关键词有“课程”、“学生信息”，锁定使用表：学生表、成绩表、课程表。
第二步：先统计学生的课程数量，再筛选出小于所有课程数量的学生。
考核知识点： left join、group by、count()
答案：
8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息。
解题思路：
第一步：关键词有“课程”、“学生信息”，锁定使用表：学生表、成绩表。
第二步：先查询学号“01”的同学学习的课程。
第三步：通过sid关联学生表和成绩表，获取所有学生信息、课程信息。
第四步：用EXISTS筛选出至少1门课相同的同学信息。
考核知识点： left join、子查询、EXISTS
答案：
9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
解题思路：
第一步：关键词有“课程”、“学生信息”，锁定使用表：学生表、成绩表。
第二步：用“01”号的同学学习的课程左关联学生课程表，筛选出关链课程数一致的其他同学的sid
第三步：通过sid关联学生表和成绩表，获取完整的学生信息。
考核知识点： left join、子查询、group by
答案：
10.查询没学过"张三"老师讲授的任一门课程的学生姓名
解题思路：
第一步：关键词有“老师”、“学生姓名”，锁定使用表：学生表、成绩表、课程表、教师表。
第二步：反向求解。先查询至少学过“张三”老师讲授的课程的学生sid
第三步：用NOT EXISTS筛选出不在第二步查询结果的学生信息,。
考核知识点： 多重连接join、NOT EXISTS、子查询
答案：
11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
解题思路：
第一步：关键词有“姓名”、“平均成绩”，锁定使用表：学生表、成绩表。
第二步：通过sid关联学生表和成绩表，得到学生成绩信息宽表
第三步：用group by聚合统计，having对聚合的结果进行筛选。
考核知识点： join、group by、having、case when语句、avg()
答案：
12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
解题思路：
第一步：关键词有“课程分数”、“学生信息”，锁定使用表：学生表、成绩表。
第二步：通过sid关联学生表和成绩表，得到学生成绩信息宽表
第三步：用where筛选" 01 "课程分数小于 60的记录，并按照分数降序排列。
考核知识点： join、where、order by
答案：
13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
解题思路：
第一步：关键词有“平均成绩”，锁定使用表：成绩表。
第二步：用group by将学生课程的成绩由行转换为列并计算平均成绩。
第三步：按照平均成绩降序显示学生的所有课程的成绩以及平均成绩。
考核知识点： join
答案：
14.查询各科成绩最高分、最低分和平均分,以如下形式显示：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，
优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
解题思路：
第一步：关键词有“课程name”、“最高分”，锁定使用表：课程表、成绩表。
第二步：用group by聚合计算课程最高分、最低分、平均分。
第三步：用case语句判断及格、中等、优良、优秀，并结合group by计算。
考核知识点： join、group by、max()、min()、avg()、sum()、case when语句
答案：
15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
解题思路：
第一步：关键词有“各科成绩”，锁定使用表：成绩表。
第二步：用rank()排名。
考核知识点： rank() over(partition by)
答案：
15.1 按各科成绩进行行排序，并显示排名， Score 重复时合并名次
解题思路：
第一步：关键词有“各科成绩”，锁定使用表：成绩表。
第二步：用dense_rank()排名。
考核知识点： dense_rank() over(partition by)
答案：
16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
解题思路：
第一步：关键词有“总成绩”，锁定使用表：成绩表。
第二步：用group by统计学生的总成绩。
第三步：用left join自关联进行排名。
考核知识点： group by、 left join
答案：
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
解题思路：
第一步：关键词有“总成绩”，锁定使用表：成绩表。
第二步：用group by统计学生的总成绩。
第三步：用变量进行排名。
考核知识点： group by、变量
答案：
17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
解题思路：
第一步：关键词有“各科成绩”、“课程名称”，锁定使用表：成绩表、课程表。
第二步：通过cid关联课程表和成绩表，得到课程、成绩信息宽表。
第三步：用group by聚合统计各分段的人数及百分比。
考核知识点： join、group by、case when条件语句
答案：
18.查询各科成绩前三名的记录
解题思路：
第一步：关键词有“各科成绩”，锁定使用表：成绩表。
第二步：筛选出各科比当前成绩高的人数小于3的学生记为各科的前三名。
考核知识点： 子查询
答案：
19.查询每门课程被选修的学生数
解题思路：
第一步：关键词有“每门课程”、“学生数”，锁定使用表：课程表、成绩表。
第二步：用left join关联课程表和成绩表，再用group by分组汇总各科的学生数。
考核知识点： left join、group by
答案：
20.查询出只选修两门课程的学生学号和姓名
解题思路：
第一步：关键词有“选修课程”、“学生姓名”，锁定使用表：学生表、成绩表。
第二步：用join关联学生表和成绩表，再用group by分组汇总每个学生的选修课程数，最后用having对分组汇总结果筛选出选修两门课程的学生。
考核知识点： join、group by、having
答案：
21. 查询男生、女生人数
解题思路：
第一步：关键词有“男生、女生”，锁定使用表：学生表。
第二步：通过ssex学生表用group by分组汇总男生、女生人数。
考核知识点： group by
答案：
22. 查询名字中含有“风”字的学生信息
解题思路：
第一步：关键词有“学生信息”，锁定使用表：学生表。
第二步：用like匹配姓名中含有风”字的学生。
考核知识点： like、%
答案：
23查询同名同性学生名单，并统计同名人数
解题思路：
第一步：关键词有“学生名单”，锁定使用表：学生表。
第二步：使用group by，汇总同名同性人数，再用having筛选出大于1的记录
考核知识点： group by、having
答案：
24.查询 1990 年出生的学生名单
解题思路：
第一步：关键词有“学生名单”，锁定使用表：学生表。
第二步：用where筛选出1990年出生的学生名单
考核知识点： where、year
答案：
25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编
号升序排列。
解题思路：
第一步：关键词有“平均成绩”，锁定使用表：成绩表。
第二步：用group by分组计算各科平均成绩，再用order by完成多列排序
考核知识点： group by、order by
答案：
26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
解题思路：
第一步：关键词有“平均成绩”、“学生姓名”，锁定使用表：成绩表、学生表。
第二步：用join关联学生表和成绩表
第三步：用group by分组汇总计算每个学生的平均成绩，再用having筛选平均成绩>=85的记录
考核知识点： join、group by、having
答案：
27.查询课程名称为“数学”，且分数低于 60 的学生姓名和分数
解题思路：
第一步：关键词有“课程名称”、“分数”、“学生姓名”，锁定使用表：课程表、成绩表、学生表。
第二步：用join关联学生表、成绩表、课程表，再用where筛选
考核知识点： 多重join、where
答案：
28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
解题思路：
第一步：关键词有“所有学生”、“分数”，锁定使用表：学生表、成绩表
第二步：用left join关联学生表、成绩表
考核知识点： left join
答案：
29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
解题思路：
第一步：关键词有“课程成绩”、“姓名”、“课程名称”，锁定使用表：学生表、成绩表、课程表
第二步：用join关联学生表、成绩表、课程表，再筛选出课程成绩在70分以上的。
考核知识点： 多重join
答案：
30.查询不及格的课程
解题思路：
第一步：关键词有“不及格的课程”，锁定使用表：成绩表、课程表
第二步：关联课程表和成绩表，再条件筛选出不及格的课程信息。
考核知识点： join、where、去重
答案：
31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
解题思路：
第一步：关键词有“课程编号”、“课程成绩”、“姓名”，锁定使用表：成绩表、学生表
第二步：关联成绩表和学生表，再条件筛选出结果。
考核知识点： join、where
答案：
32.求每门课程的学生人数
解题思路：
第一步：关键词有“课程”、“学生人数”，锁定使用表：成绩表
第二步：用group by分组汇总各科的学生人数。
考核知识点： group by
答案：
33.成绩不重复，查询选修“张三”老师所授课程的学生中，成绩最高的学生信息及其成绩
解题思路：
第一步：关键词有“成绩”、““张三”老师”、“学生信息”，锁定使用表：成绩表、课程表、学生表、教师表
第二步：关联所有表，筛选出选修“张三”老师所授课程的学生。
第三步：因为成绩不重复，对学生成绩由高到低排序，筛选出第一行记录。
考核知识点： 多重join、order by、limit
答案：
34.成绩有重复的情况下，查询选修“张三”老师所授课程的学生中，成绩最高的学生
信息及其成绩
解题思路：
第一步：关键词有“成绩”、““张三”老师”、“学生信息”，锁定使用表：成绩表、课程表、学生表、教师表
第二步：关联所有表，筛选出选修“张三”老师所授课程的学生。
第三步：因为成绩有重复，先求出最高成绩，再匹配最高成绩对应的学生信息。
考核知识点： 多重join、max()
答案：
35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
解题思路：
第一步：关键词有“成绩”，锁定使用表：成绩表
第二步：自联接，筛选出不同课程成绩相同的记录
考核知识点： 自联接join
答案：
36. 查询每门成绩最好的前两名
解题思路：
第一步：关键词有“成绩”，锁定使用表：成绩表
第二步：自联接，筛选出各科低于自身成绩的人数为2的。
考核知识点： left join
答案：
37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。
解题思路：
第一步：关键词有“选修人数”，锁定使用表：成绩表
第二步：先用group by分组汇总各科的选修人数，再条件筛选出超过5人的课程。
考核知识点： group by、having
答案：
38.检索至少选修两门课程的学生学号
解题思路：
第一步：关键词有“两门课程”，锁定使用表：成绩表
第二步：先用group by分组汇总每个学生的选修课程数，再用having筛选出至少2门课程的学生学号
考核知识点： group by、having
答案：
39.查询选修了全部课程的学生信息
解题思路：
第一步：关键词有“全部课程”、“学生信息”，锁定使用表：成绩表、课程表、学生表
第二步：关联学生表和成绩表，再用group by分组统计每个学生的选修课程数
第三步：最后用having筛选出等于全部课程数的学生信息。
考核知识点： join、 group by、having、子查询
答案：
40.查询各学生的年龄，只按年份来算
解题思路：
第一步：关键词有“学生的年龄”，锁定使用表：学生表
第二步：用year和now来统计
考核知识点： year、now
答案：
41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
解题思路：
第一步：关键词有“出生日期”，锁定使用表：学生表
第二步：用timestampdiff()统计年龄
考核知识点： timestampdiff()
答案：
42.查询本周过生日的学生
解题思路：
第一步：关键词有“过生日”，锁定使用表：学生表
第二步：用week函数
考核知识点： week()
答案：
43. 查询下周过生日的学生
解题思路：
第一步：关键词有“过生日”，锁定使用表：学生表
第二步：用week函数
考核知识点： week()
答案：
44.查询本月过生日的学生
解题思路：
第一步：关键词有“过生日”，锁定使用表：学生表
第二步：用month函数
考核知识点： month()
答案：
45.查询下月过生日的学生
解题思路：
第一步：关键词有“过生日”，锁定使用表：学生表
第二步：用month函数
考核知识点： month()
答案：
8. SQL 查询语句期中考试题2
--1.分别查询学生表和学生修课表中的全部数据。
SELECT * FROM student--查询学生表
SELECT * FROM course--查询课程表
--2.查询成绩在70到80分之间的学生的学号、课程号和成绩。
SELECT Sno 学号,Cno 课程号,Grade 成绩
FROM SC
WHERE Grade BETWEEN 70 AND 80
--3．查询C01号课程成绩最高的分数
SELECT TOP 1 Grade
FROM SC
WHERE Cno='C01'
ORDER BY Grade DESC--降序DESC，升序ASC
--4.查询学生都选修了哪些课程,要求列出课程号。
SELECT Cname AS 学生选修的课程,Cno AS 课程号
FROM course
WHERE Cno IN(SELECT DISTINCT Cno FROM SC)--DISTINCT用来去除重复
--5.查询Northwind数据库中orders表的OrderID、CustomerID和OrderDate,
--并将最新的定购日期(OrderDate)列在前边。
USE Northwind
GO
SELECT OrderID,CustomerID,OrderDate
FROM orders
ORDER BY OrderDate DESC--降序DESC，升序ASC
--6.查询Northwind数据库中orders表的ShipCountry列以B,C,D,F --//ShipCountry BCDF
--开始且第三个字符为"a"的OrderID、CustomerID和ShipCountry的信息。//OrderID、CustomerID第三个字符为"a"
USE Northwind
GO
SELECT OrderID,CustomerID,ShipCountry FROM orders
WHERE ShipCountry LIKE '[BCDF]_a%'--_下划线表示任意一个字符，%表示一个或多个字符
--7.查询Northwind数据库中orders表的ShipCountry列不以A,B,C,D,E,F开始且最后一个字
--母是"a"的OrderID、CustomerID和ShipCountry的信息。
USE Northwind
GO
SELECT OrderID,CustomerID,ShipCountry
FROM orders
WHERE ShipCountry LIKE '[^ABCDEF]%a'
--8.查询修了C02号课程的所有学生的平均成绩、最高成绩和最低成绩。
SELECT AVG(Grade) 平均成绩,MAX(Grade) 最高成绩,MIN(Grade) 最低成绩
FROM SC
WHERE Cno='C02'
--9.统计每个系的学生人数。
SELECT Sdept 系别,COUNT(*) 人数
FROM student
WHERE Sdept IN(SELECT DISTINCT Sdept FROM student)
GROUP BY Sdept
--10.统计每门课程的修课人数和考试最高分。
SELECT Cname 课程名,COUNT(*) 修课人数,MAX(Grade) 考试最高分
FROM SC,course
WHERE SC.Cno IN(SELECT DISTINCT Cno FROM SC ) AND course.Cno=SC.Cno
GROUP BY course.Cname
--11.统计每个学生的选课门数,并按选课门数的递增顺序显示结果。
SELECT student.Sname 学生姓名,student.Sno 学生学号,COUNT(SC.Sno) 选课门数
FROM student
inner join SC ON SC.Sno=student.Sno
GROUP BY student.Sname,student.Sno
ORDER BY COUNT(SC.Sno) ASC
--12.统计选修课的学生总数和考试的平均成绩。
SELECT COUNT(DISTINCT(Sno)) AS 学生总数,AVG(Grade) AS 平均成绩
FROM SC --//用DISTINCT消除重复的行
--13.查询选课门数超过2门的学生的平均成绩和选课门数。
SELECT Student.Sname,AVG(Sc.Grade) 平均成绩,COUNT(SC.Sno) 选课门数
FROM SC
--内联接join或inner join,内联系是比较运算符，只返回符合条件的行
JOIN Student ON (SC.Sno = Student.Sno)
JOIN Course ON (SC.Cno = Course.Cno)
GROUP BY Student.Sname
HAVING COUNT(distinct Course.Cno) >2--分组条件
--14.列出总成绩超过200分的学生,要求列出学号、总成绩。
SELECT Sno 学号,SUM(Grade) 总成绩
FROM SC
GROUP BY Sno
HAVING SUM(Grade)>200
--15.查询pubs数据库的titles表中每类图书的平均价格超过12.0元的书的类型(Type)、
--平均价格和最高价格。
USE pubs
GO
SELECT Type AS 书的类型,AVG(price) AS 平均价格,MAX(price) AS 最高价格
FROM titles
GROUP BY Type
HAVING AVG(price)>12.0
--16.查询pubs数据库的titles表中每类图书中图书的数目超过3本的图书的总价格。
USE pubs
GO
SELECT 图书类型=Type,图书的数目=count(Type),图书的总价格=SUM(price)
FROM titles
GROUP BY Type
HAVING count(Type)>3
--17.查询选修了c02号课程的学生的姓名和所在系。
SELECT Sname 学生姓名,Sdept 所在系,SC.Cno AS 选修课程
FROM student
inner join SC ON student.Sno=SC.Sno
WHERE SC.Cno='C02'
--18.查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果。
SELECT Sname 学生姓名,SC.Cno 课程号,SC.Grade 成绩
FROM student
inner join SC ON student.Sno=SC.Sno--内连接表SC查询
WHERE SC.Grade>80
ORDER BY SC.Grade DESC
--19.查询计算机系男生修了"数据库基础"的学生的姓名、性别、成绩。
SELECT Sname 姓名,Ssex 性别,SC.Grade 成绩
FROM student
inner join SC ON Cno IN(SELECT Cno FROM course WHERE Cname='数据库基础') --显示成绩的条件
AND student.Sno=SC.Sno --显示成绩的学生的学号
WHERE Sdept='计算机系' AND Ssex='男'
--20.查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。
--这题使用到了表的自连接，所以需要给表取两个别名，如A和B
SELECT A.Sname 年龄相同的学生的姓名,A.Sage 年龄
FROM student A
inner join student B ON A.Sage IN(SELECT Sage FROM student WHERE A.Sage=B.Sage AND A.Sname!=B.Sname)
GROUP BY A.Sname,A.Sage
ORDER BY A.Sage
--21.查询哪些课程没有人选,要求列出课程号和课程名。
SELECT Cno AS 课程号,Cname AS 课程名
FROM course
WHERE Cno NOT IN(SELECT DISTINCT SC.Cno FROM SC)
--22.查询有考试成绩的所有学生的姓名、修课名称及考试成绩
--要求将查询结果放在一张新的永久表(假设新表名为new-sc)中。
SELECT student.Sname AS 有考试成绩的学生的姓名,course.Cname AS 修课名称,SC.Grade AS 考试成绩
INTO [new_sc] --将查询结果放入新表new_sc中
FROM student,course,SC
WHERE SC.Grade IS NOT NULL AND student.Sno=SC.Sno AND course.Cno=SC.Cno
--23.分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,
--并要求将这两个查询结果合并成一个结果集,
--并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。
--//此题用到了并union查询
SELECT Sdept 系名,Sname 姓名,Ssex 性别,course.Cname 修课名称,SC.Grade 修课成绩
FROM student
inner join SC ON student.Sno=SC.Sno
inner join course ON course.Cno=SC.Cno
WHERE Sdept='信息系'
UNION
SELECT Sdept 系名,Sname 姓名,Ssex 性别,course.Cname 修课名称,SC.Grade 修课成绩
FROM student
inner join SC ON student.Sno=SC.Sno
inner join course ON course.Cno=SC.Cno
WHERE Sdept='计算机系'
或
select sdept, sname,ssex,cname,grade from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno and (sdept='信息系' or sdept='计算机系')
--24.用子查询实现如下查询:
--(1) 查询选修了C01号课程的学生的姓名和所在系。
SELECT Sname AS 选修了C01号课程的学生的姓名,Sdept AS 所在系
FROM student
WHERE Sno IN(SELECT Sno FROM SC WHERE Cno='C01')
--(2) 查询数学系成绩80分以上的学生的学号、姓名。
SELECT Sno AS 数学系成绩80分以上的学生的学号,Sname AS 姓名
FROM student
WHERE Sno IN(SELECT Sno FROM SC WHERE Grade>80)
AND Sno IN(SELECT Sno FROM student WHERE Sdept='数学系')
--(3) 查询计算机系学生所选的课程名.
SELECT Cname AS 计算机系学生所选的课程名
FROM course
WHERE
course.Cno IN(SELECT DISTINCT Cno FROM SC WHERE SC.Sno IN(SELECT Sno FROM student WHERE Sdept='计算机系'))
--25.将计算机系成绩高于80分的学生的修课情况插入到另一张表中,分两种情况实现：//////////////?????/
--(1) 在插入数据过程中建表。
--使用SELECT INTO插入数据的方法，是在插入数据的过程中建立新表
SELECT student.Sname AS 学生名字,course.Cname AS 选修课程,SC.Cno AS 课程编号
INTO [SC_Info1]--将计算机系成绩高于80分的学生的修课情况插入到表SC_Info1中
FROM student,course,SC
WHERE student.Sdept='计算机系' AND SC.Grade>80 AND course.Cno=SC.Cno AND student.Sno=SC.Sno
--(2) 先建一个新表,然后再插入数据。
--创建表SC_Info2
CREATE TABLE SC_Info2
(
Sname char(7),
Cname char(20),
Cno char(10)
)
--往表SC_info2插入查询得到的结果
INSERT SC_Info2
SELECT student.Sname AS 学生名字,course.Cname AS 选修课程,SC.Cno AS 课程编号
FROM student,course,SC
WHERE student.Sdept='计算机系' AND SC.Grade>80 AND course.Cno=SC.Cno AND student.Sno=SC.Sno
--26.删除修课成绩小于50分的学生的修课记录
DELETE SC WHERE Grade<50 OR Grade IS NULL
--27.将所有选修了"c01"课程的学生的成绩加10分。
UPDATE SC
SET Grade=Grade+10
WHERE Cno='C01'
9. 挑战SQL经典题（一）
建表语句：
表说明：
student(sid,sname,sage,ssex) 学生表
course(cid,cname,tid) 课程表
sc(sid,cid,score) 成绩表
teacher(tid,tname) 教师表
问题：
1、查询“1”课程比“2”课程成绩高的所有学生的学号；（3号-张三）
2、查询平均成绩大于60分的同学的学号和平均成绩；（1、2、3、4号）
3、查询所有同学的学号、姓名、选课数、总成绩；
4、查询姓“李”的老师的个数；（0个）
5、查询没学过“叶平”老师课的同学的学号、姓名； （4号-李四）
6、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
（1号-刘一 2号-钱二 3号-张三 6号-赵六 除了4号和5号）---考查EXISTS
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名； （除了4号）
8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
（3号-张三）
9、查询所有课程有成绩小于60分的同学的学号、姓名；
（1号、3号、5号、6号 除了2号和4号）
10、查询没有学全所有课的同学的学号、姓名； （4号、5号、6号）
11、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名； （All）
12、查询跟学号为“1”的同学学的所有课一样的其他同学学号和姓名； （2号和3号）
13、把“sc”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩； （61.4）
14、删除学习“叶平”老师课的SC表记录；
15、向sc表中插入一条记录，要求符合以下条件：没有上过编号“3”课程的同学学号、2号课的平均成绩；（6号童鞋没有上过3号课程）
16、按平均成绩从高到低显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分（待更正 语句有错）
17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
18、按各科平均成绩从低到高和及格率的百分数从高到低顺序
19、查询如下课程平均成绩和及格率的百分数(用"1行"显示):
语文-1、数学-2、英语-3、物理-4
20、查询不同老师所教不同课程平均分从高到低显示
21、统计打印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
22、查询学生平均成绩及其名次
23、查询各科成绩前三名的记录:(不考虑成绩并列情况)---------SQL不识别TOP
24、查询每门课程被选修的学生数
25、查询出只选修了一门课程的全部学生的学号和姓名
26、查询男生、女生人数
27、查询姓“张”的学生名单
28、查询同名同姓学生名单，并统计同名人数
29、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
30、查询“物理”课分数不及格的学生姓名和分数
31、查询所有学生的选课情况；
32、查询课程的成绩在80分以上的学生姓名、课程名称和分数；
33、查询选修“周磊”老师所授课程的学生中，成绩最高的学生姓名及其成绩（李四-93）
34、查询不同课程成绩相同的学生的学号、课程号、学生成绩
35、查询两门以上课程大于80分的同学的学号及其平均成绩
36、检索“4”课程分数小于60，按分数降序排列的同学学号
10. sql面试题50题（mysql版）
--插入学生表测试数据
insert into Student values(༽' , '赵雷' , -01-01' , '男');
insert into Student values(༾' , '钱电' , -12-21' , '男');
insert into Student values(༿' , '孙风' , -05-20' , '男');
insert into Student values(ཀ' , '李云' , -08-06' , '男');
insert into Student values(ཁ' , '周梅' , -12-01' , '女');
insert into Student values(ག' , '吴兰' , -03-01' , '女');
insert into Student values(གྷ' , '郑竹' , -07-01' , '女');
insert into Student values(ང' , '王菊' , -01-20' , '女');
--课程表测试数据
insert into Course values(༽' , '语文' , ༾');
insert into Course values(༾' , '数学' , ༽');
insert into Course values(༿' , '英语' , ༿');
--教师表测试数据
insert into Teacher values(༽' , '张三');
insert into Teacher values(༾' , '李四');
insert into Teacher values(༿' , '王五');
--成绩表测试数据
insert into Score values(༽' , ༽' , 80);
insert into Score values(༽' , ༾' , 90);
insert into Score values(༽' , ༿' , 99);
insert into Score values(༾' , ༽' , 70);
insert into Score values(༾' , ༾' , 60);
insert into Score values(༾' , ༿' , 80);
insert into Score values(༿' , ༽' , 80);
insert into Score values(༿' , ༾' , 80);
insert into Score values(༿' , ༿' , 80);
insert into Score values(ཀ' , ༽' , 50);
insert into Score values(ཀ' , ༾' , 30);
insert into Score values(ཀ' , ༿' , 20);
insert into Score values(ཁ' , ༽' , 76);
insert into Score values(ཁ' , ༾' , 87);
insert into Score values(ག' , ༽' , 31);
insert into Score values(ག' , ༿' , 34);
insert into Score values(གྷ' , ༾' , 89);
insert into Score values(གྷ' , ༿' , 98);
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select c.*,a.s_score as 01课程score,b.s_score as 02课程score from
score a,score b
left join student c
on b.s_id = c.s_id
where a.s_id = b.s_id and a.c_id = ༽' and b.c_id = ༾' and a.s_score > b.s_score;
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.* ,b.s_score as 01课程,c.s_score as 02课程 from student a
join score b
on a.s_id=b.s_id and b.c_id = ༽'
left join score c
on b.s_id = c.s_id and c.c_id = ༾'
where b.s_score < c.s_score ;
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select a.s_id,a.s_name,round(avg(b.s_score),2) as 平均成绩 from student a
join score b
on a.s_id = b.s_id
group by b.s_id having 平均成绩 >= 60;
备注：round[avg(成绩),1]里,round是四舍五入函数，1代表保留1位小数
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
select b. ,round(avg(a.s_score),2) as 平均成绩 from
student b
left join score a on b.s_id = a.s_id group by a.s_id having 平均成绩 < 60
union
select b. ,0 as 平衡成绩 from student b where b.s_id not in (select s_id from score);
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.s_id,a.s_name,count(b.c_id) as 选课总数 ,sum(b.s_score) as 总分 from student a
left join score b
on a.s_id = b.s_id group by s_id ;
-- 6、查询"李"姓老师的数量
select count(*) as 李姓老师数量 from teacher where t_name like '李%'
-- 7、查询学过"张三"老师授课的同学的信息
select a.* from student a join score b
on a.s_id = b.s_id
where b.c_id in (select c.c_id from course c
join teacher d on c.t_id = d.t_id where d.t_name = '张三');
-- 8、查询没学过"张三"老师授课的同学的信息
select a.* from student a left join score b on a.s_id = b.s_id where a.s_id not in
(select s_id from score where c_id =
(select c_id from course where t_id =
(select t_id from teacher where t_name = '张
三'))) group by a.s_id;
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select * from student where s_id in
(select a.s_id from score a join score b on a.s_id = b.s_id
where a.c_id = ༽' and b.c_id = ༾');
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select * from student where s_id in
(select s_id from score where c_id = ༽' )
and s_id not in (select s_id from score where c_id = ༾' );
-- 11、查询没有学全所有课程的同学的信息
select * from student where s_id not in
(select s_id from score group by s_id having count(c_id) = 3);
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select distinct a.* from student a left join score b
on a.s_id = b.s_id where b.c_id in
(select c_id from score where s_id = ༽') and a.s_id != ༽' ;
注意：distinct是去重的
-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select * from student where s_id in
(select s_id from score group by s_id having count(c_id) =
(select count(c_id) from score where s_id = ༽') and s_id not in
(select s_id from score where c_id not in
(select c_id from score where s_id = ༽')) and s_id != ༽');
-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select s_name from student where s_id not in
(select s_id from score where c_id in
(select c_id from course where t_id in
(select t_id from teacher where t_name ='张三')));
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select a.s_id ,b.s_name,round(avg(a.s_score),2) as 平均成绩 from score a
left join student b on a.s_id = b.s_id
where s_score < 60 group by s_id having count(1) >=2;
或者试试
select a.s_id ,b.s_name,round(avg(a.s_score),2) as 平均成绩 from score a
left join student b on a.s_id = b.s_id
where a.s_score < 60 group by a.s_id having count(*) >=2;
-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
select a.* ,b.c_id ,b.s_score from student a
left join score b on a.s_id = b.s_id
where b.c_id = ༽' and b.s_score < 60
order by b.s_score desc;
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.s_name ,
sum(case when b.c_id = ༽' then s_score else null end ) as 语文,
sum(case when b.c_id = ༾' then s_score else null end ) as 数学,
sum(case when b.c_id = ༿' then s_score else null end ) as 英语,
round(avg(s_score),2) as 平均成绩
from student a left join score b on a.s_id = b.s_id group by a.s_name
order by 平均成绩 desc;
-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select b.c_id,b.c_name,
max(a.s_score) as 最高分,
min(a.s_score) as 最低分,
round(avg(a.s_score),2) as 平均分,
round(sum(case when a.s_score>= 60 then 1 else 0 end)/count(s_id),2) as 及格率 ,
round(sum(case when a.s_score>= 70 and a.s_score <80 then 1 else 0 end)/count(s_id),2) as 中等率,
round(sum(case when a.s_score>= 80 and a.s_score <90 then 1 else 0 end)/count(s_id),2) as 优良率,
round(sum(case when a.s_score>= 90 then 1 else 0 end)/count(s_id),2) as 优秀率
from score a
left join course b
on a.c_id = b.c_id group by b.c_id;
-- 19、按各科成绩进行排序，并显示排名
第一种：
set @pre_c_id:= ༽'
set @rank:=0;
select tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2;
如果看不懂用第二种方法：
SELECT a.c_id,a.s_id,a.s_score,COUNT(b.s_score)+1 AS 排名
FROM score a LEFT JOIN score b ON a.s_score<b.s_score AND a.c_id = b.c_id
GROUP BY a.c_id,a.s_id,a.s_score ORDER BY a.c_id,排名,a.s_id ASC
-- 20、查询学生的总成绩并进行排名
set @rank:=0;
select * ,(@rank:=@rank+1) as rank from
(select s_id ,sum(s_score) as 总成绩 from score
group by s_id order by 总成绩 desc) tb1;
-- 21、查询不同老师所教不同课程平均分从高到低显示
select a.c_id, d.t_name,round(avg(a.s_score)) as 平均分 from score a
left join student b on a.s_id = b.s_id
left join course c on a.c_id = c.c_id
left join teacher d on c.t_id = d.t_id group by a.c_id
order by 平均分 desc;
-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
set @pre_c_id:= ༽'
set @rank:=0;
select b.s_name,tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2.s_id = b.s_id where 排名 = 2 or 排名 =3;
-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],(85-70],(70-60],(0-60]及所占百分比
select b.c_id,b.c_name ,
sum(case when a.s_score >=85 then 1 else 0 end) as 100-85 ,
concat(round(100 sum(case when a.s_score >=85 then 1 else 0 end)/count( ),2), '%') as 百分比,
sum(case when a.s_score <85 and a.s_score >=70 then 1 else 0 end) as 85-70 ,
concat(round(100 sum(case when a.s_score <85 and a.s_score >=70 then 1 else 0 end)/count( ),2),'%') as 百分比,
sum(case when a.s_score <70 and a.s_score >=60 then 1 else 0 end) as 70-60 ,
concat(round(100 sum(case when a.s_score <70 and a.s_score >=60 then 1 else 0 end)/count( ),2) ,'%')as 百分比,
sum(case when a.s_score <60 and a.s_score >=0 then 1 else 0 end) as 60-0 ,
concat(round(100 sum(case when a.s_score <60 and a.s_score >=0 then 1
else 0 end)/count( ),2),'%') as 百分比
from score a left join course b on a.c_id = b.c_id group by b.c_id;
-- 24、查询学生平均成绩及其名次
select tb1.*,(@rank:=@rank +1 ) as rank from
(select s_id ,round(avg(s_score),2) as 平均成绩 from score
group by s_id order by 平均成绩 desc) tb1,(select @rank:=0) b;
-- 25、查询各科成绩前三名的记录
set @pre_c_id:= ༽'
set @rank:=0;
select b.s_name,tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2.s_id = b.s_id where 排名 <4;
-- 26、查询每门课程被选修的学生数
select c_id ,count(s_id) as 选修人数 from score group by c_id;
-- 27、查询出只有两门课程的全部学生的学号和姓名
select a.s_id ,b.s_name from score a left join student b on a.s_id = b.s_id group by s_id having count(*) = 2;
-- 28、查询男生、女生人数
select sum(case s_sex when '男' then 1 else 0 end) as 男生人数,
sum(case s_sex when '女' then 1 else 0 end) as 女生人数 from student;
-- 29、查询名字中含有"风"字的学生信息
select * from student where s_name like '%风%'
-- 30、查询同名同性学生名单，并统计同名人数
--略，不想写
-- 31、查询1990年出生的学生名单
select * from student where s_birth like %'
-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c_id ,round(avg(s_score),2) as 平均成绩 from score group by c_id order by 平均成绩 desc, c_id asc;
-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select a.s_id,b.s_name ,round(avg(s_score),2) as 平均成绩 from score a
left join student b on a.s_id = b.s_id group by a.s_id having 平均成绩>=85;
-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select b.s_name ,a.s_score from score a
left join student b on a.s_id = b.s_id
where a.c_id=(select c_id from course where c_name = '数学')and a.s_score < 60;
-- 35、查询所有学生的课程及分数情况；
select b.s_name,
sum(case when a.c_id = ༽' then a.s_score else null end) as 语文,
sum(case when a.c_id = ༾' then a.s_score else null end) as 数学,
sum(case when a.c_id = ༿' then a.s_score else null end) as 英语
from score a right join student b on a.s_id = b.s_id group by b.s_name
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select b.s_name,
sum(case when a.c_id = ༽' then a.s_score else null end) as 语文,
sum(case when a.c_id = ༾' then a.s_score else null end) as 数学,
sum(case when a.c_id = ༿' then a.s_score else null end) as 英语
from score a right join student b on a.s_id = b.s_id group by b.s_name having 语文>= 70 or 数学>= 70 or 英语>= 70 ;
-- 37、查询不及格的课程
select a.s_id,a.c_id,b.c_name,a.s_score from score a
left join course b on a.c_id = b.c_id where a.s_score<60;
--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select a.s_id,b.s_name from score a left join student b on a.s_id = b.s_id where a.c_id = ༽' and a.s_score>=80;
-- 39、求每门课程的学生人数
select c_id,count(*) as 学生人数 from score group by c_id ;
-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select a.*,b.c_id,max(b.s_score) as 最高成绩 from student a
right join score b on a.s_id = b.s_id
group by b.c_id
having b.c_id = (select c_id from course
where t_id = (select t_id from teacher where t_name = '张三'));
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
--（这题我搞不清题目是什么意思,是指查找学生个体参加了的所有课程的成绩各不相同的那个学生信息呢？还是所有课程之间做对比呢，我更倾向于理解为前者）
--理解为前者的写法
select * from
(select * from score group by s_id,s_score) tb1
group by s_id having count(*) = 1;
--理解为后者的写法
select distinct a.s_id,a.c_id,b.s_score from score a,score b where a.c_id != b.c_id and a.s_score = b.s_score;
-- 42、查询每门课程成绩最好的前两名
set @pre_c_id:= ༽'
set @rank:=0;
select tb2.s_id ,tb2.c_id,tb2.s_score from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2
join student b on tb2.s_id = b.s_id where 排名 <3;
-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人相同，按课程号升序排列
select c_id ,count(*) as 选修人数 from score group by c_id having 选修人数>5 order by 选修人数 desc , c_id asc;
-- 44、检索至少选修两门课程的学生学号
select s_id from score group by s_id having count(*) >= 2;
-- 45、查询选修了全部课程的学生信息
select * from student where s_id in
(select s_id from score group by s_id having count(*) = 3)
--46、查询各学生的年龄
select s_name ,(date_format(now(),'%Y')-date_format(s_birth,'%Y') + (CASE when date_format(now(),'%m%d')>=date_format(s_birth,'%m%d') then 0 else 1 end)) as age
from student
-- 47、查询本周过生日的学生
---(实现得并不完全，因为例如出生月日为‘01-01’在每一年可能会输入不同周）
select * from student where week(date_format(s_birth,'%m%d'))=week(date_format(now(),'%m%d')) ;
-- 48、查询下周过生日的学生
select * from student
where week(date_format(s_birth,'%m%d'))=week(date_format(date_add(now(),interval 7-dayofweek(now())+1 day),'%m%d'));
-- 49、查询本月过生日的学生
select * from student where date_format(s_birth,'%m') = date_format(now(),'%m')
-- 50、查询下月过生日的学生
select * from student where date_format(s_birth,'%m') = date_format(date_add(now(),interval 1 month),'%m')