sql vb xml 换行_SQL-持续更新语法&实战&面试题

写在前面：在互联网公司业务部门做分析师，一般会要求用SQL进行提数，这是必备的技能。这是一个我从入门到日常积累的帖子，内容循序渐进。第一篇是【入门】，写了我当时学习的步骤，建议控制在十天内搞定（PS:我当时基本就是上下班回家地铁上看的）；第二篇是【刷题&面试题】，一定要保证每天都要刷，即使一道也行！保持手感！第三篇是【工作中遇到的语法记录】，会不定期更新，欢迎长期交流~

---

第一篇：入门-总耗时十天

关卡1-了解什么是数据库和SQL，并成功安装MYSQL-耗时一天

数据库（Database)是将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。用来管理数据库的计算机系统称为数据库管理系统（DBMS）。DBMS能够实现多个用户同时安全简单地操作大量数据。DBMS的分类：层次数据库、关系数据库、面向对象数据库、XML数据库、键值存储系统。这里需要重点了解的是关系数据库（RDBMS），它是采用行和列组成的二维表来管理数据库，并使用专门的SQL（Structured Query Language，结构化查询语言)对数据进行操作。比较具有代表性的RDBMS有：

• Oracle Database
• SQL Server
• DB2
• PostgreSQL
• MySQL

RDBMS最常见的系统结构就是客户端/服务器类型，简而言之就是数据库将数据保存到硬盘等设备上，通过客户端向服务器发送SQL语句来实现数据库的读写操作。关系数据库通过表来管理数据，数据库中可以同时存储多个表，而表是由行和列来组成的，根据SQL语句的内容返回的数据同样必须是二维表的形式。表的列称为字段，表的行称为记录。关系数据库必须以行为单位进行数据读写，行和列交汇的为单元格，一个单元格只能输入一个数据。

SQL是用来操作关系数据库的语言，SQL语言可以分为三类：

• 数据定义语言DDL：用来创建或者删除存储数据用的数据库以及数据库中的表等对象。

CREATE:创建数据库和表等对象。

DROP:删除数据库和表等对象。

ALTER:修改数据库和表等对象。

• 数据操纵语言DML：用来查询或者变更表中的记录。

SELECT：查询。

INSERT：向表中插入新数据。

UPDATE: 更新表中的数据。

DELETE：删除表中的数据。

• 数据控制语言DCL：用来确认或者取消对数据库中的数据进行的变更。

COMMIT: 确认对数据库中的数据进行变更。

ROLLBACK: 取消对数据库中的数据进行变更。

GRANT：赋予用户操作权限。

REVOKE：取消用户操作权限。

作为商业分析师，如果是在业务部门，那实际工作中99%都集中在DML语言中的SELECT。

之后的关卡我们会用到MYSQL,请按照下面的链接一步一步操作，安装好服务器和客户端。

猴子：超级详细的mysql安装指南

猴子：Mysql客户端：Navicat安装教程及问题汇总

安装过程中遇到问题请利用搜索引擎解决，网上也有很多安装教程，请在一天内完成此任务。

---

关卡2-看完《SQL基础教程》（作者：Mick）-耗时五天

这本书非常适合初学者，简单易懂，请保证每天看1~2章，书上的例题及课后题目都可以在Navicat上练习，勤做笔记，入门SQL仅此一本书读透彻即可。以下为我看完书后按照章节选取的重要知识点，供参考。强烈建议边看书边自己梳理知识框架，特别是语法运行的逻辑及先后顺序，这对以后编写复杂代码很有用，一定要打好基础。看完一遍后根据框架再快速过一遍来巩固。（第八-九章可以略过，选择性看）

---

关卡3-上w3school网站查漏补缺-耗时一天

关于学习SQL的网站，我刷过SQLZOO和w3school，但网站上的题目答案不一定是最优的，需要自己去判断和辨别，可以根据个人需要在网站进行学习，不懂的知识点返回《SQL基础教程》去深入理解，会豁然开朗。

SQL 简介

关卡4-入门基础题-耗时三天

网络上流传的入门50题（两个版本都有），先自己做，主要是对之前学的基础知识的巩固和查漏补缺。

• 第一个版本

学生表 Student(SId,Sname,Sage,Ssex) ：

SId 学⽣生编号,Sname 学⽣生姓名,Sage 出⽣生年年⽉月,Ssex 学⽣生性别

课程表 Course(CId,Cname,TId) ：

CId 课程编号,Cname 课程名称,TId 教师编号

教师表 Teacher(TId,Tname) ：

TId 教师编号,Tname 教师姓名

成绩表 SC(SId,CId,score) ：

SId 学⽣生编号,CId 课程编号,score 分数

-- 1、查询01课程比02课程成绩高的学生的信息及课程分数

-- 书写规范：关键词全部大写、括号要缩进并换行

-- 考点1：where 一定要跟select

-- 考点2：匹配join开始运行都是先笛卡尔集，然后根据on后的条件（可多个）重新组合后一定要用select才能取出结果

SELECT e.*,d.cid,d.score FROM

(

SELECT a.sid FROM

(

SELECT sid,cid,score FROM sc

WHERE cid='01'

)AS a

INNER JOIN

(

SELECT sid,cid,score FROM sc

WHERE cid='02'

)AS b

ON a.sid=b.sid

WHERE a.score>b.score

)AS c

LEFT JOIN

sc AS d

ON c.sid=d.sid

LEFT JOIN

student AS e

ON d.sid=e.sid

-- 1.1 查询同时存在课程01和02课程的情况

SELECT d.*,c.cid,c.score FROM

(

SELECT sid,cid,score FROM sc

WHERE cid='01'

)AS a

INNER JOIN

(

SELECT sid,cid,score From sc

WHERE cid='02'

)AS b

ON a.sid=b.sid

LEFT JOIN sc AS c

ON a.sid=c.sid

LEFT JOIN student d

ON c.sid=d.sid

-- 1.2 查询存在01课程但可能不存在02课程的情况（不存在时显示为null)

SELECT d.*,c.cid,c.score FROM

(

SELECT sid,cid,score FROM sc

WHERE cid='01'

)AS a

LEFT JOIN

(

SELECT sid,cid,score From sc

WHERE cid='02'

)AS b

ON a.sid=b.sid

LEFT JOIN sc AS c

ON a.sid=c.sid

LEFT JOIN student d

ON c.sid=d.sid

-- 1.3 查询不存在01课程但存在02课程的情况

SELECT e.*,d.cid,d.score FROM

(

SELECT a.sid FROM

(

SELECT sid,cid,score FROM sc

WHERE cid='02'

)AS a

LEFT JOIN

(

SELECT sid,cid,score From sc

WHERE cid='01'

)AS b

ON a.sid=b.sid

WHERE b.cid is NULL

)AS c

LEFT JOIN sc AS d

ON c.sid=d.sid

LEFT JOIN student AS e

ON d.sid=e.sid

-- 2.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

-- 考点：子查询和group by

SELECT a.sid,b.sname,a.score_avg FROM

(

SELECT sid,AVG(score)AS score_avg FROM

sc

GROUP BY sid

) AS a

INNER JOIN

student AS b

ON a.sid=b.sid

WHERE a.score_avg>=60

-- 3、查询在SC表存在成绩的学生信息

SELECT DISTINCT a.* FROM student AS a

INNER JOIN

sc AS b

ON a.sid=b.sid

WHERE b.score is not null

-- 4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

SELECT a.sid,a.sname,

COUNT(DISTINCT b.cid)AS '选课总数',

SUM(b.score)AS '课程总成绩'

FROM student AS a

LEFT JOIN

sc AS b

ON a.sid=b.sid

GROUP BY a.sid,a.sname

-- 另外一种是先求分数表先group by 再取对应的学生信息，用子查询

SELECT b.sid,b.sname,a.cid,a.score

FROM

student as b

LEFT JOIN

(

SELECT sid,

COUNT(DISTINCT cid)AS cid,

SUM(score) AS score

FROM sc

GROUP BY sid

)AS a

ON a.sid=b.sid

-- 4.1查有成绩的学生信息

SELECT DISTINCT a.sid,b.sname,b.sage,b.ssex FROM sc AS a

INNER JOIN student AS b

ON a.sid=b.sid

-- 5、查询李姓老师的数量

SELECT COUNT(DISTINCT tid) FROM teacher

WHERE tname LIKE '李%'

-- 6、查询学过张三老师授课的同学信息

SELECT d.sid,c.cid,c.tname,e.sname,e.ssex,e.sage FROM

(

SELECT a.cid,a.tid,b.tname FROM course AS a

INNER JOIN teacher AS b

ON a.tid=b.tid

WHERE tname='张三'

)AS c

LEFT JOIN sc AS d

ON c.cid=d.cid

LEFT JOIN student AS e

ON d.sid=e.sid

-- 7、查询没有学全所有课程的同学信息

SELECT b.sid,c.sname,c.sage,c.ssex FROM

(

SELECT a.sid FROM

(

SELECT sid,COUNT(DISTINCT cid)AS count_cid FROM sc

GROUP BY sid

)AS a

WHERE a.count_cid <3

)AS b

LEFT JOIN student c

ON b.sid=c.sid

-- 所有课程如果不用3直接表示

SELECT b.sid,c.sname,c.sage,c.ssex FROM

(

SELECT a.sid FROM

(

SELECT sid,COUNT(DISTINCT cid)AS count_cid FROM sc

GROUP BY sid

)AS a

WHERE a.count_cid <

(

SELECT COUNT(DISTINCT cid) FROM course

)

)AS b

LEFT JOIN student c

ON b.sid=c.sid

-- 8、查询至少有一门课与学号为01的同学所学相同的同学信息

SELECT DISTINCT a.sid,b.sname,b.ssex,b.sage FROM sc AS a

LEFT JOIN student b

ON a.sid=b.sid

WHERE a.cid IN

(

SELECT cid FROM sc

WHERE sid='01'

)

-- 9查询和01号同学学习的课程完全相同的其他同学的信息

-- concat函数可以连接多个字段

SELECT concat(sid,',',cid,',',score) FROM sc

-- concat_ws()函数可以一次性指定分隔符

SELECT concat_ws(',',sid,cid,score) FROM sc

-- group_concat() 函数让名字出现一次，相同名字对应的id分别出现:语法

SELECT sid,group_concat(cid order by cid DESC separator '_') from sc

GROUP BY sid

-- 先取出01同学学习的课程，并放在一个单元格里作为一个concat_cid,取出其他sid对应的concat_cid让其相等

SELECT a.sid,b.sname,b.sage,b.ssex,group_concat(distinct a.cid order by a.sid,a.cid separator '_')AS concat_cid1

FROM sc AS a

INNER JOIN student AS b

ON a.sid=b.sid

GROUP BY a.sid,b.sname,b.sage,b.ssex

HAVING concat_cid1=

(

SELECT concat_cid FROM

(

SELECT sid,

group_concat(DISTINCT cid order by sid,cid separator '_')AS concat_cid

FROM sc

WHERE sid='01'

GROUP BY sid

)AS score_01

)

--

SELECT a.sid,b.sname,b.sage,b.ssex,group_concat(distinct a.cid order by a.cid separator '_')AS concat_cid1

FROM sc AS a

INNER JOIN student AS b

ON a.sid=b.sid

GROUP BY a.sid,b.sname,b.sage,b.ssex

-- 10、查询没有学过'张三'老师讲授的任一门课程的学生姓名

-- 需要筛选出学过张三老师课程的学生，再进行排除

SELECT sname FROM student

WHERE sid NOT IN

(

SELECT DISTINCT b.sid FROM student AS b

INNER JOIN sc AS c

ON b.sid=c.sid

INNER JOIN course d

ON c.cid=d.cid

INNER JOIN teacher e

ON d.tid=e.tid

WHERE tname='张三'

)

-- 11、查询两门及其以上不及格课程的同学的学号，姓名和平均成绩

SELECT b.sid,d.sname,AVG(c.score) FROM

(

SELECT a.sid FROM

(

SELECT sid,COUNT(DISTINCT cid)as count_cid FROM sc

WHERE score<60

GROUP BY sid

)AS a

WHERE a.count_cid>=2

)AS b

INNER JOIN sc AS c

ON b.sid=c.sid

INNER JOIN student AS d

ON c.sid=d.sid

GROUP BY b.sid

-- 12、取出01课程分数小于60，按分数降序排列的学生信息

SELECT a.* FROM student AS a

INNER JOIN sc AS b

ON a.sid=b.sid

WHERE b.cid='01'

AND b.score<60

ORDER BY b.score DESC

-- 13、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT a.*,b.cid,b.score,c.avg_score FROM student AS a

INNER JOIN sc AS b

ON a.sid=b.sid

INNER JOIN

(

SELECT sid,AVG(score)AS avg_score

FROM sc

GROUP BY sid

)AS c

ON b.sid=c.sid

ORDER BY avg_score DESC

-- 14、查询各科成绩最高分、最低分、平均分：

-- 以如下形式显示：课程 ID，课程 name，最⾼高分，最低分，平均分，及格率，中等率，优良率，优秀率

-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

-- 注意count和case when如果取的是0 ，count对于空值也是会计数的

SELECT a.cid,b.cname,

MAX(a.score),

MIN(a.score),

AVG(a.score),

COUNT(a.sid)AS '选修人数',

SUM(case when a.score>=60 then 1 else 0 end)/COUNT(a.sid)AS '及格率',

SUM(case when a.score>=70 and a.score<80 then 1 else 0 end)/COUNT(a.sid) AS '中等率',

SUM(case when a.score>=80 and a.score>=90 then 1 else 0 end)/COUNT(a.sid)AS '优良率',

SUM(case when a.score>80 then 1 else 0 end)/COUNT(a.sid)AS '优秀率'

FROM sc AS a

INNER JOIN course AS b

ON a.cid=b.cid

GROUP BY a.cid,b.cname

ORDER BY '选修人数' DESC ,a.cid

-- 15按各科的成绩进行排序，并显示排名，分数如果重复保留名次空缺

-- 保留名次空缺是指2个80，1个79分，79分排名是第三名。

-- 第一种解法：两个表自连接，通过cid相连，left join 选出A表比B表分数小的，计算比自己大的B表的个数，count+1

SELECT a.sid,a.cid,a.score,count(b.score)+1 AS ranking

FROM sc AS a

LEFT JOIN sc AS b

ON a.cid=b.cid

AND a.score<b.score

GROUP BY a.cid,a.sid,a.score

ORDER BY a.cid,ranking

-- 第二种解法

-- rank() over是跳跃排序的，row_number()over排序唯一值，dense_rank连续排名。,group by 是分组，partition by 是分区

SELECT cid,sid,score,

rank () over (PARTITION BY cid ORDER BY score DESC)AS '排名'

FROM sc

-- 16查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

SELECT a.sid,a.cid,a.score,count(a.score) AS ranking

FROM sc AS a

LEFT JOIN sc AS b

ON a.cid=b.cid

AND a.score<b.score

GROUP BY a.cid,a.sid,a.score

ORDER BY a.cid,ranking

-- 第二种解法

SELECT a.sid,a.sum_score,dense_rank over(ORDER BY a.sum_score) FROM

(

SELECT sid,sum(score)as sum_score

FROM sc GROUP BY sid

)AS a

-- 17、统计各科成绩各分数段人数，课程编号、课程名称，100-85、85-70、70-60、60-0以及所占百分比

SELECT a.cid,b.cname,COUNT(a.sid),

SUM(case when a.score>=85 and a.score<=100 then 1 else 0 end)/COUNT(a.sid)AS '100-85占比',

SUM(case when a.score>=70 and a.score<85 then 1 else 0 end)/COUNT(a.sid)AS '85-70占比',

SUM(case when a.score>=60 and a.score<70 then 1 else 0 end)/COUNT(a.sid)AS '70-60占比',

SUM(case when a.score>=0 and a.score<60 then 1 else 0 end)/COUNT(a.sid)AS '0-60占比'

FROM sc AS a

INNER JOIN course AS b

ON a.cid=b.cid

GROUP BY a.cid,b.cname

-- 18、查询各科成绩前三名的记录

SELECT a.sid,a.cid,a.score,count(b.score)+1 AS ranking

FROM sc AS a

LEFT JOIN sc AS b

ON a.cid=b.cid

AND a.score<b.score

GROUP BY a.cid,a.sid,a.score

HAVING ranking<=3

ORDER BY a.cid,ranking

-- 19、查询每门课被选修的学生数

SELECT cid,COUNT(sid) FROM sc

GROUP BY cid

-- 20、查询出只选修两门课程的学生学号和姓名

SELECT a.*,b.sname FROM

(

SELECT sid,COUNT(cid)as count_cid FROM sc

GROUP BY sid

)AS a

LEFT join student b

ON a.sid=b.sid

WHERE a.count_cid=2

-- 21、查询男生女生人数

SELECT

ssex,COUNT(sid)

FROM

student

GROUP BY ssex

-- 22、查询名字中含有风字的学生信息

SELECT * FROM student

WHERE sname LIKE '%风%'

-- 23、查询同名同姓学生名单，并统计同名人数

SELECT c.sname,COUNT(c.sid)

FROM

(

SELECT a.sname,a.sid from student AS a

INNER JOIN student AS b

ON a.sname=b.sname

AND a.sid<>b.sid

)AS c

GROUP BY c.sname

-- 另一种解法

SELECT sname,COUNT(sid)AS count_sid FROM student

GROUP BY sname

HAVING count_sid>1

-- 24、查询1990年出生的学生名单

SELECT * FROM student

WHERE sage LIKE '%1990%'

-- 另一种解法

SELECT * FROM student

WHERE YEAR(sage)='1990'

-- 25、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排序

SELECT cid,AVG(score)AS avg_score FROM sc

GROUP BY cid

ORDER BY avg_score DESC ,cid

-- 26、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT a.sid,b.sname,AVG(a.score)AS avg_score FROM

sc AS a

INNER JOIN student AS b

ON a.sid=b.sid

GROUP BY sid

HAVING avg_score>85

-- 27、查询课程名称为数学，且分数低于60的学生姓名和分数

SELECT a.sname,b.score FROM student AS a

INNER JOIN sc AS b

ON a.sid=b.sid

INNER JOIN course AS c

ON b.cid=c.cid

WHERE c.cname='数学'

AND b.score<60

-- 28、查询所有学生的课程及分数情况(存在学生没成绩，没选课的情况)

SELECT a.sid,b.cid,b.score FROM student AS a

LEFT JOIN sc AS b

ON a.sid=b.sid

-- 29、查询任何一门课程成绩在70分以上的姓名、课程名称和分数

SELECT a.sname,b.cid,b.score FROM sc AS b

LEFT JOIN student AS a

ON a.sid=b.sid

WHERE b.score>70

-- 30、查询存在不及格的课程

SELECT cid,sid,score FROM sc

WHERE score<60

-- 31、查询课程编号为01且课程成绩在80分及以上的学生的学号和姓名

SELECT b.sid,b.sname,a.cid,a.score FROM sc AS a

LEFT JOIN student AS b

ON a.sid=b.sid

WHERE a.cid='01'

AND a.score>80

-- 32、求每门课程的学生人数

SELECT cid,COUNT(sid) FROM sc

GROUP BY cid

-- 33、成绩不重复的情况下，查询选修张三老师所授课程的学生中，成绩最高的学生信息以及成绩

-- limit是从表排序第一条开始取的，就算成绩相同也只取一条

SELECT a.sid,a.score,d.sname,d.ssex,d.sage FROM sc AS a

INNER JOIN student AS d

ON a.sid=d.sid

INNER JOIN course AS b

ON a.cid=b.cid

INNER JOIN teacher c

ON b.tid=c.tid

WHERE tname='张三'

ORDER BY a.score DESC

LIMIT 1

-- 34、成绩有重复的情况下，查询选修张三老师所授课程的学生中，成绩最高的学生信息及其成绩

-- 查出最高的这个分数，然后限制得这个分数得所有学生,但仍然要限制课程是张三交的

SELECT f.sid,q.sname,q.ssex,q.sage,f.cid,f.score FROM sc AS f

INNER JOIN student AS q

ON f.sid=q.sid

INNER JOIN course AS w

ON f.cid=w.cid

INNER JOIN teacher AS y

ON w.tid=y.tid

WHERE y.tname='张三'

AND f.score IN

(

SELECT MAX(a.score)AS max_score FROM sc AS a

INNER JOIN student AS d

ON a.sid=d.sid

INNER JOIN course AS b

ON a.cid=b.cid

INNER JOIN teacher c

ON b.tid=c.tid

WHERE tname='张三'

)

-- 35、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT distinct a.sid,a.cid,a.score FROM sc AS a

INNER JOIN sc AS b

ON a.sid=b.sid

AND a.score=b.score

AND a.cid<>b.cid

-- 36、未完成查询每门功课成绩最好的前两名

SELECT cid,count(sid) FROM sc

GROUP BY cid

-- 37、统计每门课程的学生选修人数(超过五人才统计）

SELECT cid,COUNT(sid) FROM sc

GROUP BY cid

HAVING COUNT(sid)>=5

-- 38、取出至少选修两门课的学生学号

SELECT sid,COUNT(cid) FROM sc

GROUP BY sid

HAVING COUNT(cid)>=2

-- 39、查询选修了全部课程的学生信息

-- having这个条件的用法

SELECT a.sid,a.sname,a.ssex,a.sage,COUNT(b.cid)AS count_cid FROM student AS a

INNER JOIN sc AS b

ON a.sid=b.sid

GROUP BY a.sid,a.sname,a.ssex,a.sage

HAVING count_cid =(SELECT count(cid) FROM course)

-- 40、查询学生的年龄，只按年份来

SELECT sid,sname,YEAR(curdate())-YEAR(sage) FROM student

-- 41、按照出生日期来算，当前月<出生年月的月日则年龄减一

-- 时间差函数：

SELECT sid,sname,TIMESTAMPDIFF(YEAR,sage,NOW()) FROM student

-- 42、查出本周过生日的学生

SELECT sid,sname FROM student

WHERE week(sage)=week(curdate())

-- 43、查询下周过生日的学生

SELECT sid,sname FROM student

WHERE week(sage)=week(curdate())+1

-- 44、查询本月过生日的学生

SELECT sid,sname FROM student

WHERE MONTH(sage)=MONTH(curdate())

-- 45、查询下月过生日的学生

SELECT sid,sname FROM student

WHERE MONTH(sage)=MONTH(curdate())+1

• 第二个版本

首先创建数据：四个表

表student2-字段：s_id(K) ，s_name，s_birth ，s_sex

表course2-字段：c_id(k)，c_name，t_id

表teacher2-字段：t_id(k)，t_name

表score2-字段：s_id(k)，c_id，s_score

1 .查询"01"课程比"02"课程成绩高的学生的信息及课程分数

考点：镶嵌表的自连接

select a.*,b.c_id,b.s_score from student2 a,score2 b where a.s_id=b.s_id

and a.s_id in (select a.s_id from score2 a,score2 b where a.s_id=b.s_id and

a.c_id='01' and b.c_id='02' and a.s_score>b.s_score)

2.查询平均成绩大于60分的学生的学号和平均成绩

考点：按照score2表需要对s_id单个学生的各科平均成绩排序

select s_id,avg(s_score) from score2 group by s_id HAVING avg(s_score)>60 order by s_id

3.查询所有学生的学号、姓名、选课数、总成绩

考点：两个表的连接,通过s_id,聚合函数count,sum

select a.s_id,a.s_name,count(b.c_id),sum(b.s_score)

from student2 a,score2 b where a.s_id=b.s_id group by a.s_id

4.查询姓“猴”的老师的个数

考点：模糊查询，用like

select t_name,count(t_id) from teacher2 where t_name like '猴%'

5.查询没学过“猴子”老师课的学生的学号、姓名

考点：子查询not in (查询学过猴子老师的学生的学号、姓名)

select a.s_id,a.s_name from student2 a

where a.s_id not in (select b.s_id from score2 b,course2 c,teacher2 d

where b.c_id=c.c_id and c.t_id=d.t_id and d.t_name='猴子')

6.查询学过“猴子”老师所教的所有课的同学的学号、姓名

考点：学生要学过老师教的所有的课，count(学生学过的课)=count(老师教的课），老师教的c_id=学生学的c_id

select a.s_id,a.s_name from student2 a where a.s_id in

(select b.s_id from score2 b,course2 c,teacher2 d

where b.c_id=c.c_id and c.t_id=d.t_id and d.t_name='猴子' group by a.s_id

HAVING count(c.c_id)=(select count(c.c_id) from course2 c,teacher2 d

where c.t_id=d.t_id and d.t_name='猴子' ))

7.查询学过编号为“001”的课程并且也学过编号为“002”的课程的学生的学号、姓名

考点：exist存在,后面接子查询where exists（select.),第二种更加简单，用in (子查询）

select a.s_id,a.s_name,b.c_id from student2 as a ,score2 as b where a.s_id=b.s_id

and b.c_id='01' and EXISTS (select c.c_id from score2 as c where b.s_id=c.s_id

and c.c_id='02')

select a.s_id,a.s_name,b.c_id,b.s_score from student2 a,score2 b where a.s_id=b.s_id

and a.s_id in (select a.s_id from score2 a,score2 b where a.s_id=b.s_id and

a.c_id='01' and b.c_id='02' )

8.查询课程编号为“02”的总成绩

select c_id,sum(s_score) from score2 where c_id='02'

9.查询所有课程成绩小于60分的学生的学号、姓名

select a.s_id,a.s_name from student2 as a ,score2 as b where a.s_id=b.s_id

and b.s_score < 60

10.查询没有学全所有课的学生的学号、姓名

考点：count（学生）<count(c_id)

select a.s_id,a.s_name from student2 as a ,score2 as b where a.s_id=b.s_id group by a.s_id having(count(c_id))<(select count(c_id) from course2)

11.查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名

考点:c_id in (c_id=01)

select a.s_id,a.s_name from student2 as a,score2 as b where a.s_id=b.s_id

and b.c_id in (select c_id from score2 where s_id='01')

12.查询和“1001”号同学所学课程完全相同的其他同学的学号

考点：要满足in，count(c_id)也要满足

select s_id from score2 where c_id in

(select c_id from score2 where s_id='01') group by s_id

HAVING COUNT(c_id)=(select count(*) from score2 where s_id='01')

13.把“SCORE”表中“猴子”老师教的课的成绩都更改为此课程的平均成绩

考点：更改update表 set 字段=(select) where

update score2 as a set a.s_score=(select avg(b.s_score) from score2

as b where a.c_id=b.c_id ) where a.c_id in (select c_id from course2

as c INNER JOIN teacher2 as d on c.t_id=d.t_id where t_name='猴子')

14.查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名

select a.s_id,a.s_name from student2 as a INNER JOIN score2 as b on

a.s_id=b.s_id where c_id in (select c_id from score2 where s_id='02')

group by s_id having count(c_id) =(select count(*) from score2 where s_id='02')

17.按平均成绩从高到低显示所有学生的“数据库”（c_id='004'）、“企业管理”（c_id='001'）、“英语”（c_id='006'）三门的课程成绩，按如下形式显示：学生ID，数据库，企业管理，英语，有效课程数，有效平均分

考点：select (select a from A) from B,当A和B是相等的也需要区分

select s_id as 学生ID,

(select s_score from score2 as a where a.s_id=b.s_id and c_id='04') as 数据库,

(select s_score from score2 as a where a.s_id=b.s_id and c_id='01') as 企业管理,

(select s_score from score2 as a where a.s_id=b.s_id and c_id='06') as 英语,

count(*) as 有效课程数,avg(s_score) as 有效平均分 from score2 as b group by s_id ORDER BY avg(s_score) desc

18.查询各科成绩最高和最低的分： 以如下的形式显示：课程ID，最高分，最低分

select c_id as 课程ID,max(s_score) as 最高分,min(s_score) as 最低分 from score2

group by c_id

19.按各科平均成绩从低到高和及格率的百分数从高到低排列，以如下形式显示：

考点：sum(case when 条件s_score>60 then 结果 1 else 0 end)/cpunt(*)

select a.c_id as 课程号,a.c_name as 课程名,avg(b.s_score)as 平均成绩,

sum(case when s_score>=60 then 1 else 0 end)/count(*) as 及格百分比

from course2 as a INNER JOIN score2 as b on a.c_id=b.c_id group by a.c_id

order by sum(case when s_score>=60 then 1 else 0 end)/count(*) DESC

21.查询不同老师所教不同课程平均分从高到低显示

select a.t_name,b.c_id,avg(b.s_score)from score2 as b,course2 as c,teacher2

as a where a.t_id=c.t_id and c.c_id=b.c_id group by b.c_id

order by avg(b.s_score) desc

23.使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

考点:分段用case when,前面可以接聚合函数

select course2.c_id as 课程ID,course2.c_name as 课程名称,

sum(case when s_score between 85 and 100 then 1 else 0 end )as '85-100',

sum(case when s_score between 70 and 85 then 1 else 0 end )as '70-85',

sum(case when s_score between 60 and 70 then 1 else 0 end )as '60-70',

sum(case when s_score <60 then 1 else 0 end )as '60-'

from score2,course2 where score2.c_id=course2.c_id

group by score2.c_id

24.查询学生平均成绩及其名次

考点：一、排序有三种函数，rank/dense_rank/row_number三种，具体用法如下：rank()over(partition by order by) 排序为1,1,1,4；dense_rank() over (partition by order by)排序为1,1,1,2;row_number()over (partition by order by)排序为1,2,3,4;二、order by 和where后面不能接聚合函数,from 后面加子查询，先找出学生的学号和对应的平均成绩，并对平均成绩进行命名a，之后才用order by;三、有聚合函数，考虑要用到group by 四、row_number()over 可以不加partition by ,加了的话是对内部再次进行排序。

select a.s_id,a.amt,rownumber()over (order by a.amt desc) from (select s_id,avg(s_score) as amt from score2 group by s_id)as a

25.查询各科成绩前三名的记录（不考虑成绩并列情况）

考点：运行的顺序，where 和select是并列的，先运行from后面的子查询（先写出按照各科成绩排名的结果，没有聚合函数一般不用group by，用order by可以实现；这里需要按各科内排序用partition by；）命名为a ，再在外面写select * from ()as a where a.字段

select * from(

select c_id,s_score,row_number()over(partition by c_id order by s_score desc)as ti

FROM score2) a

where a.ti<=3

26.查询每门课程被选修的学生数

select c_id,count(s_id) from score2 group by c_id

27.查询出只选修了一门课程的全部学生的学号和姓名

select a.s_id,b.s_name,count(a.c_id)from student2 as b,score2 as a where a.s_id=b.s_id

group by a.s_id having count(a.c_id)=1

28.查询男生、女生人数

select count(s_sex) as 男生人数 from student2 where s_sex='男'

group by s_sex

select count(s_sex) as 女生人数 from student2 where s_sex='女'

group by s_sex

29.查询姓“张”的学生名单

select * from student2 where s_name like '张%'

30.查询同名同性学生名单并统计同名人数

select *,count(s_name) from student2 group by s_name

having count(s_name)>1

31.1981年出生的学生名单（注：Student表中s_birth列的类型是datetime）

考点：trunc(字段）=当前日期，只适用于oracle，trunc(字段,'mm')返回当月第一天，trunc(字段,'yy'）返回当年第一天；

select * from student2 where trunc(s_birth,'yyyy')='1981-01-01'

32.查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select a.s_id,a.s_name,avg(b.s_score)from student2 a,score2 b where a.s_id=

b.s_id group by a.s_id having avg(b.s_score)>85

33.查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

select avg(s_score) from score2 group by c_id order by avg(s_score),c_id DESC

34.查询课程名称为“数据库”且分数低于60的学生姓名和分数

select a.s_name,b.s_score from student2 as a,score2 as b,course2 as c

where a.s_id=b.s_id and b.c_id=c.c_id and c.c_name='数据库' and b.s_score<60

35.查询所有学生的选课情况

select a.s_id,a.s_name,c.c_name from student2 a,score2 b,course2 c

where a.s_id=b.s_id and b.c_id=c.c_id

36.查询任何一门课程成绩在70分以上的姓名、课程名称和分数

select a.s_name,c.c_name,b.s_score from student2 a,score2 b,course2 c

where a.s_id=b.s_id and b.c_id=c.c_id and b.s_score>70 group by a.s_id

37.查询不及格的课程并按课程号从大到小排列

select c_id from score2 where s_score<60 order by c_id

38.查询课程编号为003且课程成绩在80分以上的学生的学号和姓名

select a.s_id,a.s_name from student2 a ,score2 b where a.s_id=b.s_id

and b.s_score>80 and b.c_id='03'

39.查询选了课程的学生人数

select count(s_id) from score2

40.查询选修“猴子”老师所授课程的学生中成绩最高的学生姓名及其成绩

select a.s_name,max(b.s_score) from student2 as a,score2 as b,course2

as c,teacher2 as d

where a.s_id=b.s_id and b.c_id=c.c_id and c.t_id=d.t_id

and d.t_name='猴子'

41.查询各个课程及相应的选修人数

select count(s_id) from score2 group by c_id

42.查询有2门不同课程成绩相同的学生的学号、课程号、学生成绩

select a.s_id,a.c_id,b.s_score from score2 as a ,score2 as b

where a.s_id=b.s_id and a.s_score=b.s_score and a.c_id<>b.c_id

43.查询每门课程成绩最好的前两名

select * from (select s_id,c_id,s_score,row_number()over(partition by c_id order by s_score desc)as ti from score2 ) as a where a.ti<=2

44.统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

select c_id,count(s_id) from score2 group by c_id having count(s_id)>10

order by s_id desc,c_id asc

45.查询至少选修两门课程的学生学号

select s_id from score2 group by s_id having count(c_id)>=2

46.查询全部学生都选修的课程的课程号和课程名

select c_id,c_name from course2 where c_id in (select c_id from score2 group by c_id)

47.查询没学过“猴子”老师讲授的任一门课程的学生姓名

select s_name from student2 where s_id not in

(select s_id from course2,teacher2,score2 where course2.t_id=teacher2.t_id

and score2.c_id=course2.c_id and t_name='猴子')

48.查询两门以上不及格课程的同学的学号及其平均成绩

select s_id,avg(s_score) from score2 where s_score< 60 group by s_id

having count(c_id)>2

49.检索课程编号为“004”且分数小于60的学生学号，结果按按分数降序排列

select s_id from score2 where c_id='04' and s_score<60 order by s_score DESC

50.删除学生编号为“002”的课程编号为“001”的成绩

delete from score2 where s_id='02' and c_id='01'