数据分析-SQL练习
图片、部分试题来源网络，侵删，谢谢。
1.SQL 45题
create database school; use school; #学生表 create table `Student` ( `Sno` varchar(20) not null COMMENT \'人名\', `Sname` varchar(20) not null COMMENT \'姓名\', `Ssex` varchar(20) not null COMMENT \'性别\', `Sbirthday` datetime COMMENT \'出生日期\', `Class` varchar(20) COMMENT \'班级\' ); #课程表 create table `Course`( `Cno` varchar(20) not null COMMENT \'课程号\', `Cname` varchar(20) not null COMMENT \'课程名称\', `Tno` varchar(20) not null COMMENT \'教工编号\' ); #成绩表 Create table `Score` ( `Sno` varchar(20) not null COMMENT \'学号\', `Cno` varchar(20) not NULL comment \'课程号\', `Degree` DECIMAL(4,1) null COMMENT \'成绩\' ); #教师表 create table `Teacher` ( `Tno` varchar(20) not null COMMENT \'教工编号\', `Tname` varchar(20) not null COMMENT \'教工姓名\', `Tsex` varchar(20) not null COMMENT \'教工性别\', `Tbirthday` datetime null COMMENT \'教工出生日期\', `Prof` varchar(20) null COMMENT \'职称\', `Depart` varchar(20) null COMMENT \'教工所在部门\' ); insert into `Student` value (108,\'曾华\',\'男\',\'1977-09-01\',95033), (105,\'匡明\',\'男\',\'1975-10-02\',95031), (107,\'王丽\',\'女\',\'1976-01-23\',95033), (101,\'李军\',\'男\',\'1976-02-20\',95033), (109,\'王芳\',\'女\',\'1975-02-10\',95031), (103,\'陆军\',\'男\',\'1974-06-03\',95031); insert into `Course` value (\'3-105\',\'计算机导论\',825), (\'3-245\',\'操作系统\',804), (\'6-166\',\'数字电路\',856), (\'9-888\',\'高等数学\',831); insert into `Score` value (103,\'3-245\',86), (105,\'3-245\',75), (109,\'3-245\',68), (103,\'3-105\',92), (105,\'3-105\',88), (109,\'3-105\',76), (101,\'2-105\',64), (107,\'3-105\',91), (108,\'3-105\',78), (101,\'6-166\',85), (107,\'6-166\',79), (108,\'6-166\',81); insert into `Teacher` value (804,\'李城\',\'男\',\'1958-12-02\',\'副教授\',\'计算机系\'), (856,\'张旭\',\'男\',\'1969-03-12\',\'讲师\',\'电子工程系\'), (825,\'王萍\',\'女\',\'1972-05-05\',\'助教\',\'计算机系\'), (831,\'刘冰\',\'女\',\'1977-08-14\',\'助教\',\'电子工程系\');
1 查询Student表中的所有记录的Sname、Ssex和Class列
select Sname, Ssex, Class from student;
2 查询教师所有的单位即不重复的Depart列。（多练）
select distinct depart from teacher;
3 查询Student表的所有记录
select * from student;
4 查询Score表中成绩在60到80之间的所有记录
select Sno, Cno, degree from score where degree >= 60 and degree < 80;
5 查询Score表中成绩为85，86或88的记录
select Sno, Cno, degree from score where degree = 85 or degree = 86 or degree = 88;
#或另一种写法
select * from score where degree in (85, 86, 88)
6 查询Student表中“95031”班或性别为“女”的同学记录
select * from student where Class = \'95031\' or Ssex = \'女\';
7 以Class降序查询Student表的所有记录
select * from student order by Class desc;
8 以Cno升序、Degree降序查询Score表的所有记录
select * from score order by Cno asc, degree desc; # asc, ascending order(升序排列）, desc, descending order(降序排列）, 默认按升序排列
9 查询“95031”班的学生人数
select count(Class) as \'95031班学生数\' from student where Class = \'95031\';
10 查询Score表中的最高分的学生学号和课程号。（子查询或者排序）
select * from score where degree = (select max(degree) from score); # 直接写 select * from score where degree = max(degree)不行，会报错
11 查询每门课的平均成绩。(group by:根据Cno进行分组) *
select Cno, avg(degree) as \'课程平均分\' from score group by Cno ;
12 查询Score表中至少有4名学生选修的并以3开头的课程的平均分数(*需要细究*）
select cno, avg(degree) from score where cno like \'3%\' group by cno having count(sno) > 4; #参考网站是5名学生，则查询不到，应为4名学生 #like \'3%\' 以3开头，或like \'%3\' 以3结尾, 或包含\'%12%\'\ #HAVING增加的原因是WHERE关键字无法与合计函数一起使用
13 查询分数大于70，小于90的Sno列
SELECT SNO FROM SCORE WHERE DEGREE BETWEEN 70 AND 90;
14 查询所有学生的Sname、Cno和Degree列
select student.Sname, score.Cno, score.Degree from student, score where student.Sno = score.Sno;
#另一种inner join 写法
select student.Sname, score.Cno, score.Degree from student inner join score on student.Sno = score.Sno
15 查询所有学生的Sno、Cname和Degree列
select score.Sno, course.Cname, score.Degree from score, course where course.Cno = score.Cno;
#或另一种inner join写法
select score.Sno, course.Cname, score.Degree from score INNER JOIN course on course.Cno = score.Cno;
16 查询所有学生的Sname、Cname和Degree列
select student.Sname, course.Cname, score.Degree from student, course, score WHERE student.Sno = score.Sno and score.Cno = course.Cno;
17 查询“95033”班学生的平均分。(子查询or条件查询)
select avg(degree) as \'95033班平均分\' from score where sno in (select sno from student where Class = \'95033\');
18 假设使用如下命令建立了一个grade表
create table `grade`(
`low` int COMMENT \'人名\',
`upp` int not null COMMENT \'课长\',
`weight` varchar(20) not null COMMENT \'等级\'
);
insert into `grade` values(90,100,\'A\');
insert into `grade` values(80,89,\'B\');
insert into `grade` values(70,79,\'C\');
insert into `grade` values(60,69,\'D\');
insert into `grade` values(0,59,\'E\');
#网络上的grede，错误, rank关键字不能用，sql存在rank函数,经测试更改为weight可以
19 现查询所有同学的Sno、Cno和weight列。(between选取两个值之间的数据范围)
#select Sno,Cno,Degree,rank from grade join Score on Score.Degree between low and upp; #select Sno,Cno,Degree,rank from Score, grade where Degree between low and upp;
以上为网络上的答案，在Navicat 11.1.13测试错误，实际也是因为rank关键字的原因
select Sno, Cno, weight from Score, grade where (score.Degree > grade.low and score.Degree < grade.upp);
或者是
select Sno, Cno, weight from Score, grade where Degree between low and upp;
或者是
select Sno, Cno, weight from Score inner join grade where Degree between low and upp
20 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录
select sno from score where score.cno = \'3-105\' and score.degree>(select Degree from Score where Cno = \'3-105\' and Sno = \'109\');
#上面是我的答案，题目要求是查询到所有同学的记录，个人是把所有的同学找出来了，下面是网络上的答案，把满足条件的同学的特征又补全了。
select * from Student,Score where Score.Cno = \'3-105\' and Student.Sno = Score.Sno
and Score.Degree>(select Degree from Score where Cno = \'3-105\' and Sno = \'109\');
21 查询score中选学多门课程的同学中分数为非最高分成绩的记录(***挺难***）
select * from Score a where Degree<(select MAX(Degree)from Score b where a.Cno = b.Cno) and Sno in(select Sno from Score group by Sno having count(*) > 1);
22 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录
select * from Student,Score where Student.Sno = Score.Sno and Score.Degree> (select Degree from Score where Cno=\'3-105\' and Sno = \'109\');
#此为网络上答案，个人感觉不对，第一要求只查询课程号3-105的课程，实际查询少限制条件，第二信息不全
select * from student, course, score where score.sno = student.sno and score.cno = course.cno and score.cno = \'3-105\' and score.degree >
(select degree from score where cno = \'3-105\' and sno = \'109\')
23 查询和学号为107的同学同年出生的所有学生的Sno、Sname和Sbirthday列
select Sno, Sname, Sbirthday from student where YEAR(Sbirthday) = (select year(Sbirthday) from student where sno = \'107\');
#用到了year()函数
24 查询“张旭“教师任课的学生成绩
select * from student, course, score where student.sno =score.sno and score.cno = course.cno and course.tno = (select tno from teacher where tname = \'张旭\');
#以上为我的答案，将学生的信息补充的较为完整，以下是网络答案，两个where in 跟score.cno = course.cno student.sno = score.sno 等价，也是另一种写法
select Sno,Cno,Degree from Score where Cno in(select Cno from Course where Tno in(select Tno from Teacher where Tname = \'张旭\'));
25 查询选修某课程的同学人数多于5人的教师姓名
select DISTINCT tname from teacher where tno in (select tno from course where cno in (select cno from score group by cno having count(sno) > 5));
#之前的联等的写法行不通，因为没想到group by 与having count和where之间的连接方式
26 查询95033班和95031班全体学生的记录
select * from student where Class=\'95033\' or Class=\'95031\'
27 查询存在有85分以上成绩的课程Cno
select cno from score where degree >= 85;
28 查询出“计算机系“教师所教课程的成绩表
select * from teacher, student, course, score where score. sno = student.sno and score.cno = course.cno and teacher.tno =course.tno and teacher.depart = \'计算机系\';
#此处有歧义的是成绩表到底包含哪些内容，通常学生姓名、性别、学号包含，所以这里我把信息特征补的较为齐全
select sno,Cno ,Degree from Score where Cno in (select Cno from Course where Tno in (select tno from Teacher where Depart=\'计算机系\'))
#以上为网络答案
29 查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。使用相关子查询
select Tname,Prof from Teacher a where Prof not in(select Prof from Teacher b where a.Depart!=b.Depart);
#此处感觉略奇怪，不同职称的应该有遗漏
30 查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”课程的同学的Cno、Sno和Degree,并按Degree从高到低次序排序
select Cno,Sno,Degree from Score a where (select Degree from Score b where Cno=\'3-105\' and b.Sno = a.Sno)>=(select Degree from Score c where Cno=\'3-245\' and c.Sno = a.Sno)order by Degree desc ;
31 查询选修编号为“3-105”课程且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
select cno, sno, degree from score a where (select degree from score b where cno = \'3-105\' and a.sno = b.sno) > (select degree from score c where cno = \'3-245\' and a.sno = c.sno);
32 查询所有教师和同学的name、sex和birthday
select distinct sname as name, ssex as sex, sbirthday as birthday from student union select distinct tname as name, tsex as sex, tbirthday as birthday from teacher （ORDER BY birthday） ;
#这里order by birthday 是自己加的，按生日升序排列，实际不用
33 查询所有“女”教师和“女”同学的name、sex和birthday.
select distinct sname as name, ssex as sex, sbirthday as birthday from student where ssex = \'女\' union select distinct tname as name, tsex as sex, tbirthday as birthday from teacher where tsex = \'女\';
34 查询成绩比该课程平均成绩低的同学的成绩表
SELECT sno, cno, degree from score a where a.degree < (select avg(Degree) from score b where a.sno = b.sno);
35 查询所有任课教师的Tname和Depart
select tname, depart from teacher where tname in (select distinct tname from teacher, course, score, student where course.tno = teacher.tno and course.cno = score.cno and score.sno =student.sno);
36 查询所有未讲课的教师的Tname和Depart
select tname, depart from teacher where tname not in (select distinct tname from teacher, course, score, student where course.tno = teacher.tno and course.cno = score.cno and score.sno =student.sno);
37 查询至少有2名男生的班号
select Class from student where Ssex=\'男\' group by Class having count(*)>1;
38 查询Student表中不姓“王”的同学记录
select * from Student where Sname not like (\'王%\');
39 查询Student表中每个学生的姓名和年龄（暂有问题）
select Sname,YEAR(GETDATE())-year(Sbirthday) from student;
#getdate函数不可用
40 查询Student表中最大和最小的Sbirthday日期值
select MAX(Sbirthday) as 最大,MIN(Sbirthday) as 最小 from student;
41 以班号和年龄从大到小的顺序查询Student表中的全部记录
select * from Student order by Class desc ,Sbirthday asc;
#注意生日数字越大，年龄越小
42 查询“男”教师及其所上的课程
select Tname,Cname from Teacher,Course where teacher.Tsex=\'男\' and Teacher.Tno = Course.Tno;
43 查询最高分同学的Sno、Cno和Degree列
select sno, cno, degree from score where degree = (select max(Degree) from score);
44 查询和“李军”同性别的所有同学的Sname.
select sname from student where ssex = (select ssex from student where sname = \'李军\') and sname not in (\'李军\');
45 查询和“李军”同性别并同班的同学Sname
select Sname from Student where Ssex=(select Ssex from Student where Sname=\'李军\')and Sname not in (\'李军\')and Class=(select Class from Student where Sname=\'李军\');
46 查询所有选修“计算机导论”课程的“男”同学的成绩表
select Sno,Degree from Score where Sno in(select Sno from Student where Ssex=\'男\')and Cno in (select Cno from Course where Cname=\'计算机导论\')；
2. SQL 面试题
2.1 row_number
2.2 行列转换
id 学生姓名 课程名称 课程成绩 1 张三 Linux 85 2 张三 MySQL 92 3 张三 Java 87 4 李四 Linux 96 5 李四 MySQL 100 7 王五 Linux 91 8 王五 MySQL 83 9 王五 Java 98
学生姓名 Linux MySQL Java 张三 85 92 87 李四 96 89 100 王五 91 83 98
#创建数据表
CREATE TABLE tb_lemon_grade (
id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
student_name VARCHAR(20) DEFAULT NULL,
course VARCHAR(20) DEFAULT NULL,
score FLOAT DEFAULT \'0\');
#更改显示形式
SELECT student_name, MAX(IF(COURSE = \'Linux\',SCORE,0)) \'Linux\', MAX(IF(COURSE = \'MySQL\',SCORE,0)) \'MySQL\', MAX(IF(COURSE = \'Java\',SCORE,0)) \'Java\' FROM lemon_grade group by student_name;
year month amount 1991 1 1.1 1991 2 1.2 1991 3 1.3 1991 4 1.4 1992 1 2.1 1992 2 2.2 1992 3 2.3 1992 4 2.4 year m1 m2 m3 m4 1991 1.1 1.2 1.3 1.4 1992 2.1 2.2 2.3 2.4 select year, (select amount from aaa m where month=1 and m.year=aaa.year) as m1, (select amount from aaa m where month=2 and m.year=aaa.year) as m2, (select amount from aaa m where month=3 and m.year=aaa.year) as m3, (select amount from aaa m where month=4 and m.year=aaa.year) as m4 from aaa group by year
姓名 类别 花费 李 看电影 30 李 吃饭 100 李 旅游 500 王 吃饭 500 王 看电影 100 王 买衣服 700 姓名 TOP1类别 TOP1花费 TOP2类别 TOP2花费 TOP3类别 TOP3花费 李 旅游 500 吃饭 100 看电影 30 王 买衣服 700 吃饭 500 看电影 100
#decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
2.3 索引
索引就一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。
2.4 union all 与 union 的区别
union 去重且排序， union all 不去重且不排序
2.5 where 和 having 的区别
WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。GROUP BY 子句用来分组 WHERE 子句的输出。HAVING 子句用来从分组的结果中筛选行
2.6 常用的时间函数
常用的日期提取函数包括 year()/month()/day()/hour()/minute()/second()
日期运算函数包括datediff(enddate,startdate) 计算两个时间的时间差（day)；
date_sub(startdate,days) 返回开始日期startdate减少days天后的日期。
date_add(startdate,days) 返回开始日期startdate增加days天后的日期。
2.7 存储过程
存储过程是一个预编译的 SQL 语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次 SQL ，使用存储过程比单纯 SQL 语句执行要快。可以用一个命令对象来调用存储过程。
create proc StuProc @sname varchar(100) as begin select S#,Sname,Sage,Ssex from student where sname=@sname end go
2.8 要求给定数据展示效果
2.9 分组函数使用
2.10 LEFT JOIN、RIGHT JOIN、INNER JOIN、FULL JOIN
2.11 查询某时间段例如0:00-9:00用户登录次数。
给定user_id, login_time(时间戳)，表A
3. SQL练习题
4. 参考链接
3.1 https://bbs.csdn.net/topics/392337114
3.2 https://blog.csdn.net/qq_41568597/article/details/84309503
3.3 https://blog.csdn.net/weederss/article/details/78034364
版权声明：本文为Jacon-hunt原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。