文档章节

数据分析-SQL练习

o
 osc_g8254g7s
发布于 2019/08/21 11:38
字数 3587
阅读 0
收藏 0

行业解决方案、产品招募中!想赚钱就来传!>>>

图片、部分试题来源网络,侵删,谢谢。

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

o
粉丝 0
博文 499
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。
Flappy Bird(安卓版)逆向分析(一)

更改每过一关的增长分数 反编译的步骤就不介绍了,我们直接来看反编译得到的文件夹 方法1:在smali目录下,我们看到org/andengine/,可以知晓游戏是由andengine引擎开发的。打开/res/raw/at...

enimey
2014/03/04
5.9K
18
SQLServer实现split分割字符串到列

网上已有人实现sqlserver的split函数可将字符串分割成行,但是我们习惯了split返回数组或者列表,因此这里对其做一些改动,最终实现也许不尽如意,但是也能解决一些问题。 先贴上某大牛写的s...

cwalet
2014/05/21
9.6K
0
beego API开发以及自动化文档

beego API开发以及自动化文档 beego1.3版本已经在上个星期发布了,但是还是有很多人不了解如何来进行开发,也是在一步一步的测试中开发,期间QQ群里面很多人都问我如何开发,我的业余时间实在...

astaxie
2014/06/25
2.7W
22
数据库代码辅助工具--MaoCaiJun.Database

MaoCaiJun.DataBase 是一个用于 Microsoft Visual Studio 的数据库代码生成组件。它是基于 xml 文件的代码创建工具,支持sql2000,sql2005,sql2008,access, SQLite MaoCaiJun.Database 数据库...

mccj
2013/02/06
2.2K
1
实时分析系统--istatd

istatd是IMVU公司工程师开发的一款优秀的实时分析系统,能够有效地收集,存储和搜索各种分析指标,类似cacti,Graphite,Zabbix等系统。实际上,istatd修改了Graphite的存储后端,重新实现了...

匿名
2013/02/07
2.8K
1

没有更多内容

加载失败,请刷新页面

加载更多

golang 打印程序的启动流程。runtime 包示例。

package mainimport ( "fmt""runtime")func main() { for skip := 0; ; skip++ { pc, file, line, ok := runtime.Caller(skip) if !ok { break......

osc_wuji6g86
8分钟前
0
0
JAVA互联网架构师专题/分布式/高并发/微服务【第三期】

JAVA互联网架构师专题/分布式/高并发/微服务【第三期】 下载地址:百度云盘 全新打造60W年薪架构师课程,4年磨一剑,10次课程升级改版 课程内容涉及Java互联网一线大厂技术、源码框架 性能调...

1930133570
8分钟前
0
0
网站优化与推广排名受哪些因素的影响?

 现今从事网站关键词优化的企业数量变得越来越多,同时很多行业核心关键词排名竞争也变得十分激烈,因此做网络SEO的时候,要注重诸多的优化细节,这样才能够保证关键词排名可以稳定增长,那...

小苏seo
9分钟前
0
0
通过python调用jenkins-cli实现快速发布

通过python调用jenkins-cli实现快速发布 下载jenkins-cli.jar 登录jenkins页面,进入【系统管理】-【jenkins命令行接口】,点击截图中内容即可下载jar包文件。 编写jks-autodeploy.py文件,内...

osc_l89li1g7
10分钟前
0
0
JVM学习笔记之类装载器-ClassLoader

JVM学习笔记之类装载器-ClassLoader 本文字数:2300,阅读耗时7分钟 JVM体系结构概览 类装载器ClassLoader: 负责加载class文件,class文件在文件开头有特定的文件标识,将class文件字节码内容...

中凯_凯哥java
10分钟前
10
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部