SQL语句经典练习30题：基于Navicat for SQLit
2018-11-09 本文已影响23人
AiFany
sqlite.png
本文介绍利用Navicat for SQLite，省去在本地安装数据库的繁琐环节，轻松在本地实现SQL语句的练习。本文列出的30道题目，基本涵盖全部的sql查询语句，更经典，更实用。
第一部分：环境搭建
官网(https://www2.navicat.com/en/products)下载，安装Navicat for SQLite，安装完成后，点击Connection——New SQLite 3，在Database File选择本地的数据库地址。
image
本地数据库建立完成后
image
第二部分：数据表建立
- SQL语句
--创建学生信息表
CREATE TABLE Student(S varchar(10),Sname varchar(10),Sage datetime,Ssex nvarchar(10));
--创建课程表
CREATE TABLE Course(C varchar(10),Cname varchar(10),T varchar(10));
--创建老师表
CREATE TABLE Teacher(T varchar(10),Tname varchar(10));
--创建成绩表
CREATE TABLE SC(S varchar(10),C varchar(10),Score decimal(18,1));
--添加学生
INSERT INTO Student VALUES('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO Student VALUES('02' , '钱电' , '1990-12-21' , '男');
INSERT INTO Student VALUES('03' , '孙风' , '1990-05-20' , '男');
INSERT INTO Student VALUES('04' , '李云' , '1990-08-06' , '男');
INSERT INTO Student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO Student VALUES('06' , '吴兰' , '1992-03-01' , '女');
INSERT INTO Student VALUES('07' , '郑竹' , '1989-07-01' , '女');
INSERT INTO Student VALUES('08' , '王菊' , '1990-01-20' , '女');
--添加课程
INSERT INTO Course VALUES('01' , '语文' , '02');
INSERT INTO Course VALUES('02' , '数学' , '01');
INSERT INTO Course VALUES('03' , '英语' , '03');
--添加老师信息
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');
--添加成绩INSERT INTO SC VALUES('01' , '01' , 80);
INSERT INTO SC VALUES('01' , '02' , 90);
INSERT INTO SC VALUES('01' , '03' , 99);
INSERT INTO SC VALUES('02' , '01' , 70);
INSERT INTO SC VALUES('02' , '02' , 60);
INSERT INTO SC VALUES('02' , '03' , 80);
INSERT INTO SC VALUES('03' , '01' , 80);
INSERT INTO SC VALUES('03' , '02' , 80);
INSERT INTO SC VALUES('03' , '03' , 80);
INSERT INTO SC VALUES('04' , '01' , 50);
INSERT INTO SC VALUES('04' , '02' , 30);
INSERT INTO SC VALUES('04' , '03' , 20);
INSERT INTO SC VALUES('05' , '01' , 76);
INSERT INTO SC VALUES('05' , '02' , 87);
INSERT INTO SC VALUES('06' , '01' , 31);
INSERT INTO SC VALUES('06' , '03' , 34);
INSERT INTO SC VALUES('07' , '02' , 89);
INSERT INTO SC VALUES('07' , '03' , 98);
-
数据表
image
第三部分：SQL语句经典练习30题
1. 查询"01"课程比"02"课程成绩高的学生的学号；
--查询"01"课程比"02"课程成绩高的学生的学号
--查询有01课程成绩的学生的学号和对应的成绩
--查询有02课程成绩的学生的学号和对应的成绩
--学号相同，成绩对比
SELECT a.S 学号
FROM
(SELECT SC.S, SC.Score FROM SC WHERE SC.C = '01') a,
(SELECT SC.S, SC.Score FROM SC WHERE SC.C = '02') b
WHERE a.S = b.S AND a.Score > b.Score
2. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩；
--查询平均成绩不低于60分的同学的学生编号和学生姓名和平均成绩
--选择平均成绩不低于60分的学生的学号，以及平均成绩
SELECT b.S 学号, a.Sname 姓名, b.avgnum 平均成绩
FROM
(SELECT d.S, round(avg(d.Score), 1) avgnum
FROM SC d
GROUP BY d.s
HAVING avgnum >= 60) b,
Student a
where b.S = a.S
3. 查询所有学生的学号、姓名、选课总数、所有课程的总成绩；
--查询所有学生的学号、姓名、选课总数、所有课程的总成绩
SELECT b.S 学号, d.Sname 姓名, b.zongshu 选课总数, b.chengji 总成绩
FROM
(SELECT a.S, count(*) zongshu, sum(a.Score) chengji
FROM SC a
GROUP BY a.S) b,
Student d
WHERE b.S = d.S
4. 查询"李"姓老师的数量；
--查询李姓老师的数量
-- like的用法
SELECT count(*) 数量
FROM
Teacher t
WHERE t.Tname LIKE '李%'
5. 查询学过张三老师课程的学生的学号、姓名、成绩；
--查询学过张三老师课程的学生的学号、姓名、成绩
--通过表Teacher获得"张三"老师的编号
--结合表Course获得对应的课程号
--结合表SC获得学生的学号，成绩
--根据表Student获得结果
SELECT d.S 学号, f.Sname 姓名, d.Score 成绩
FROM
(SELECT s.S, h.Tname, s.Score FROM SC s LEFT JOIN
(SELECT c.C, t.Tname FROM Course c LEFT JOIN Teacher t
ON c.T = t.T) h ON s.C = h.C WHERE h.Tname = '张三') d,
Student f
WHERE d.S = f.S
6. 查询同时有课程01和课程02的成绩的学生的学号、姓名、2科的成绩和；
--查询同时有课程01和课程02的成绩的学生的学号、姓名、2科的成绩和
--获得课程编号01的学号、成绩
--获得课程编号02的学号、成绩
--union all表
--选择恰好有2条记录的,得到结果
SELECT d.S 学号, t.Sname 姓名, d.he 成绩和
FROM
(SELECT f.S, sum(f.Score) he, count(f.S) cc
FROM (SELECT s.S, s.Score FROM SC s WHERE s.C = '01'
UNION All
SELECT s.S, s.Score FROM SC s WHERE s.C = '02') f
GROUP BY f.S HAVING cc = 2) d,
Student t
WHERE d.S = t.S
7. 查询有成绩的课程比课程表中的课程少的学生的学号、姓名以及缺少成绩的课程名称；
--查询有成绩的课程比课程表中的课程少的学生的学号、姓名
SELECT c.S 学号, t.Sname 姓名
FROM
(SELECT s.S, COUNT(*) shu FROM SC s GROUP BY s.S) c,
Student t
WHERE shu < (SELECT COUNT(*) FROM Course) and c.S = t.S
8. 查询和学号为“01”的学生所学课程相同的学生的学号和姓名；
--查询和学号为“01”的学生所学课程相同的学生的学号和姓名
--首先查询和学生01的选的课程总数是一样的学生
--然后查询每个学生选的课程在01所选的课程中的总数
--上面两个数相等的学生选的课就是和01一样的
SELECT kk.S 学号, t.Sname 姓名
FROM
(SELECT gg.S FROM
(SELECT s.S, COUNT(*) shu FROM SC s
GROUP BY s.S
HAVING shu = (SELECT COUNT(*)
FROM SC
WHERE SC.S = '01')) gg,
(SELECT e.S, COUNT(e.S) liang FROM
(SELECT d.S FROM SC d
WHERE d.C in
(SELECT SC.C FROM SC WHERE SC.S = '01')) e
GROUP BY e.S) hh
WHERE gg.S = hh.S AND gg.S <> '01'AND gg.shu = hh.liang) kk,
Student t
WHERE kk.S = t.S
9. 查询没有"张三"老师讲授的课程的成绩的学生姓名；
--查询没学过"张三"老师讲授的任一门课程的学生姓名
--首先找到张三老师的课程编号
--查询成绩中没有这个课程编号的学生
SELECT stu.Sname 姓名
FROM Student stu
WHERE stu.S IN
(SELECT DISTINCT d.S
FROM Student d
WHERE d.S NOT IN
(SELECT k.S
FROM SC k
WHERE k.C IN
(SELECT s.C
FROM Course s, Teacher t
WHERE s.T = t.T and t.Tname = '张三')))
10. 查询两门及其以上不及格课程的同学的学号，姓名；
--查询两门及其以上不及格课程的同学的学号，姓名
SELECT f.S 学号, f.Sname 姓名
FROM
Student f
WHERE f.S in
(SELECT s.S 学号
FROM
(SELECT c.S, c.score
FROM SC c
WHERE c.score < 60) s
GROUP BY s.S
HAVING COUNT(s.S) >= 2)
11. 检索"01"课程成绩小于60，并按'01'课程成绩降序排列的学生学号、姓名、01课程成绩；
--检索"01"课程成绩小于60，并按'01'课程成绩降序排列的学生学号、姓名、01课程成绩
SELECT s.S 学号, t.Sname 姓名, s.score '01成绩'
FROM SC s,
Student t
WHERE
s.C = '01' AND s.score < 60 AND s.S = t.S
ORDER BY s.score DESC
12. 按平均成绩的升序显示所有学生的学号、姓名、平均成绩；
--按平均成绩的升序显示所有学生的学号、姓名、平均成绩
SELECT d.S 学号, t.Sname 姓名, d.jun 平均成绩
FROM
(SELECT s.S, ROUND(AVG(s.score),1) jun
FROM SC s GROUP BY s.S) d,
Student t
WHERE d.S = t.S
ORDER BY d.jun ASC
13. 查询各科成绩，以如下形式显示：课程编号，课程名称，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。其中及格为>=60，中等为[70,80)，优良为[80-90)，优秀为：>=90 ；
--查询各科成绩，以如下形式显示：课程编号，课程名称，最高分，最低分，平均分，及格率，
--中等率，优良率，优秀率。其中及格为>=60，中等为[70,80)，优良为[80-90)，优秀为：>=90
--课程表和成绩表连接
--计算
SELECT
d.C 课程编号,
d.Cname 课程名称,
MAX(d.score) 最高分,
MIN(d.score) 最低分,
ROUND(AVG(d.score),1) 平均分,
ROUND(SUM(CASE WHEN d.score >=60 THEN 1.0 ELSE 0.0 END)
/ SUM(CASE WHEN d.score >=0.0 THEN 1.0 ELSE 0.0 END), 2) 及格率,
ROUND(SUM(CASE WHEN d.score >=70 AND d.score<80 THEN 1.0 ELSE 0.0 END)
/ SUM(CASE WHEN d.score >=0.0 THEN 1.0 ELSE 0.0 END), 2) 中等率,
ROUND(SUM(CASE WHEN d.score >=80 AND d.score <90 THEN 1.0 ELSE 0.0 END)
/ SUM(CASE WHEN d.score >=0.0 THEN 1.0 ELSE 0.0 END), 2) 优良率,
ROUND(SUM(CASE WHEN d.score >=90 THEN 1.0 ELSE 0.0 END)
/ SUM(CASE WHEN d.score >=0.0 THEN 1.0 ELSE 0.0 END), 2) 优秀率
FROM
(SELECT s.C, s.score, c.Cname FROM
SC s LEFT JOIN Course c
ON s.C = c.C) d
GROUP BY d.C
14. 按学生的平均成绩进行排序，并显示学号、姓名、平均成绩、排名样式（1123345）；
--按学生的平均成绩进行排序，并显示学号、姓名、平均成绩、排名样式（1123345）
SELECT t2.S 学生学号, t2.平均成绩,
1 + (SELECT COUNT(DISTINCT 平均成绩)
FROM
(SELECT s.S, ROUND(AVG(s.score), 1) 平均成绩
FROM SC s GROUP BY s.S) t1
WHERE 平均成绩>t2.平均成绩) 名次
FROM
(SELECT c.S, round(avg(score), 1) 平均成绩
FROM SC c GROUP BY c.S) t2
ORDER BY t2.平均成绩 DESC
15. 按学生的平均成绩进行排序，并显示学号、姓名、平均成绩、排名样式（1134467）；
--按学生的平均成绩进行排序，并显示学号、姓名、平均成绩、排名样式（1134467）
--首先计算每个人的平均成绩
--计算每个人的平均成绩大于其他人的个数，需要添加上平均成绩最低的人
--总分数减去这个数就是名次
SELECT re.学号, re.平均成绩, tt.Sname, re.名次
FROM
(SELECT ty.学号, ty.平均成绩, MIN(ty.名次) 名次
FROM
(SELECT tu.S 学号, tu.fen 平均成绩, (SELECT COUNT(*)
FROM (SELECT DISTINCT(SC.S)
FROM SC GROUP BY SC.S)) - tu.jishu 名次
FROM
(SELECT *
FROM
(SELECT h.S, h.fen, COUNT(h.S) jishu
FROM
(SELECT r.S, r.fen
FROM
(SELECT s.S,
ROUND(AVG(s.score), 1) fen
FROM SC s
GROUP BY s.S) r,
(SELECT s.S,
ROUND(AVG(s.score), 1) shu
FROM SC s
GROUP BY s.S) rr
WHERE r.S <> rr.S AND r.fen >= rr.shu) h
GROUP BY h.S)
UNION ALL
SELECT fu.S, fu.fen,
ROUND(ROUND(fu.fen,0) -
ROUND(fu.fen,0),0) jishu
FROM
(SELECT s.S, ROUND(AVG(s.score), 1) fen
FROM SC s
GROUP BY s.S) fu
WHERE fu.fen =
(SELECT MIN(yu.shu)
FROM
(SELECT s.S, ROUND(AVG(s.score), 1) shu
FROM SC s
GROUP BY s.S) yu)) tu
) ty
GROUP BY ty.学号) re,
Student tt
WHERE tt.S = re.学号
ORDER BY re.名次
16. 查询不同老师所教课程平均分从高到低显示；
--查询不同老师所教不同课程平均分从高到低显示
-- Course、Teacher、SC三表连接
SELECT gu.任课教师, gu.科目, ROUND(AVG(gu.分数), 1) 平均分
FROM
(SELECT s.S 学号, c.C 课程号, t.Tname 任课教师, s.Score 分数, c.Cname 科目
FROM SC s
LEFT JOIN
Course c on s.C = c.C
LEFT JOIN
Teacher t ON c.T = t.T) gu
GROUP BY gu.任课教师
ORDER BY ROUND(AVG(gu.分数)) DESC
17. 统计各科成绩各分数段人数：课程编号,课程名称,[0,60),[60,75),[75,85),[85,100]人数；
--统计各科成绩各分数段人数：课程编号,课程名称,[0,60),[60,75),[75,85),[85,100]人数
--Course、SC连接
SELECT hu.C, hu.Cname,
SUM(CASE WHEN hu.Score < 60 THEN 1 ELSE 0 END) '[0-60)',
SUM(CASE WHEN hu.Score >= 60 AND hu.Score < 75 THEN 1 ELSE 0 END) '[60,75)',
SUM(CASE WHEN hu.Score >= 75 AND hu.Score < 85 THEN 1 ELSE 0 END) '[75,85)',
SUM(CASE WHEN hu.Score >= 85 AND hu.Score <= 100 THEN 1 ELSE 0 END) '[85,100]'
FROM
(SELECT s.S, c.C, s.Score, c.Cname
FROM SC s
LEFT JOIN
Course c ON c.C = s.C) hu
GROUP BY hu.C
18. 查询每门课程被选修的学生数,以及男女学生人数；
--查询每门课程被选修的学生数,以及男女学生人数
--Course、SC、Student连接
SELECT hu.C 课程编号, hu.Cname 科目, COUNT(hu.C) 选课人数,
SUM(CASE WHEN hu.Ssex = '男' THEN 1 ELSE 0 END) 男,
SUM(CASE WHEN hu.Ssex = '女' THEN 1 ELSE 0 END) 女
FROM
(SELECT c.C, c.Cname, t.Ssex
FROM SC s
LEFT JOIN
Course c ON c.C = s.C
LEFT JOIN
Student t ON s.S = t.S) hu
GROUP BY hu.C
19. 查询出只有两门课程成绩的学生的学号和姓名；
--查询出只有两门课程成绩的学生的学号和姓名
SELECT hu.S 学号, t.Sname 姓名
FROM
(SELECT s.S, COUNT(s.S) shu
FROM SC s
GROUP BY s.S
HAVING shu = 2) hu,
Student t
WHERE hu.S = t.S
20. 查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)；
--查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
SELECT t.Sname, t.Sage
FROM
Student t
WHERE
STRFTIME('%Y', t.Sage) = '1990'
21. 每科最高成绩的学生名单、成绩；**
--每科最高成绩的学生名单、成绩
--首选选择满足条件的成绩
--在选择满足条件的姓名
--结合2者
SELECT hu.Cname 科目, hu.Sname 姓名, hu.Score 最高分
FROM
(SELECT Course.Cname, Student.Sname, SC.Score
FROM SC
LEFT JOIN Course on SC.C = Course.C
LEFT JOIN Student on SC.S = Student.S) hu,
(SELECT hu.Cname, MAX(hu.Score) hh
FROM
(SELECT Course.Cname, Student.Sname, SC.Score
FROM SC
LEFT JOIN Course on SC.C = Course.C
LEFT JOIN Student on SC.S = Student.S) hu
GROUP BY hu.Cname) gu
WHERE hu.Cname = gu.Cname AND hu.Score = gu.hh
ORDER BY hu.Cname DESC
22. 查询各学生的姓名，年龄, 按年龄的升序；
-- 查询各学生的姓名，年龄, 按年龄的升序
SELECT Student.Sname 姓名,
STRFTIME('%Y', 'now') - strftime('%Y', Student.Sage) age
FROM Student
ORDER BY age ASC
23. 查询5-8月份过生日的同学姓名、以及生日；
-- 查询5-8月份过生日的同学姓名、以及生日
SELECT Student.Sname 姓名, Student.Sage 月份, STRFTIME('%m-%d', Student.Sage) 生日
FROM Student
WHERE STRFTIME('%m', Student.Sage) BETWEEN '05' AND '08'
24. 查询每门课程后2名（人数为2）的学生姓名、分数、科目；
--Course、Student、SC结合
-- 同一科目的课程分数 大于其他分数的数要小于2
SELECT hu.Cname , hu.Sname, hu.Score
FROM
(SELECT Course.Cname, Student.Sname, SC.Score, SC.C
FROM Course
LEFT JOIN
SC ON Course.C = SC.C
LEFT JOIN
Student ON Student.S = SC.S) hu
WHERE
(SELECT COUNT(SC.S)
FROM SC WHERE
hu.Score > SC.Score AND hu.C = SC.C) < 2
25. 把王五任教课程的成绩全部改为该课程的均值；
-- 把王五任教课程的成绩全部改为该课程的均值
UPDATE SC
SET Score = (SELECT AVG(hu.Score)
FROM
(SELECT Teacher.Tname, SC.Score
FROM
Teacher
LEFT JOIN
Course ON Teacher.T = Course.T
LEFT JOIN
SC ON SC.C = Course.C) hu
WHERE hu.Tname = '王五')
WHERE SC.C IN
(SELECT DISTINCT hu.C
FROM
(SELECT Teacher.Tname, SC.Score, Course.C
FROM
Teacher
LEFT JOIN
Course ON Teacher.T = Course.T
LEFT JOIN
SC ON SC.C = Course.C) hu
WHERE hu.Tname = '王五')
26. 查询所有学生的学生学号、姓名、每一科目的分数、以及总成绩，没有的为0，并按总成绩降序排列；
-- 查询所有学生的学生学号、姓名、每一科目的分数、以及总成绩，没有的为0，并按总成绩降序排列
--SC、Student、Course结合
SELECT hu.Sname 姓名,
SUM(CASE WHEN hu.Cname = '语文' THEN hu.Score ELSE NULL END) 语文,
SUM(CASE WHEN hu.Cname = '数学' THEN hu.Score ELSE NULL END) 数学,
SUM(CASE WHEN hu.Cname = '英语' THEN hu.Score ELSE NULL END) 英语,
SUM(CASE WHEN 1=1 THEN hu.Score ELSE NULL END) 总成绩
FROM
(SELECT Student.Sname, Course.Cname, SC.Score, SC.S
FROM Student
LEFT JOIN SC ON SC.S = Student.S
LEFT JOIN Course ON SC.C = Course.C) hu
GROUP BY hu.Sname ORDER BY 总成绩 DESC
27. 计算男、女生各科的平均成绩 （没有成绩的不算）；
-- 计算男、女生各科的平均成绩 （没有成绩的不算）
--Student、SC、Course结合
SELECT hu.Ssex 性别,
ROUND(SUM(CASE WHEN hu.Cname = '语文' THEN hu.Score ELSE 0 END)
/ SUM(CASE WHEN hu.Cname = '语文' THEN 1 ELSE 0 END)) 语文平均分,
ROUND(SUM(CASE WHEN hu.Cname = '数学' THEN hu.Score ELSE 0 END)
/ SUM(CASE WHEN hu.Cname = '语文' THEN 1 ELSE 0 END)) 数学平均分,
ROUND(SUM(CASE WHEN hu.Cname = '英语' THEN hu.Score ELSE 0 END)
/SUM(CASE WHEN hu.Cname = '语文' THEN 1 ELSE 0 END)) 英语平均分
FROM
(SELECT Course.Cname, SC.Score, Student.Ssex, Course.C
FROM Course
LEFT JOIN SC ON Course.C = SC.C
LEFT JOIN Student ON SC.S = Student.S) hu
GROUP BY hu.Ssex
28. 查询出每门课都大于70分的学生姓名；
--查询出每门课都大于70分的学生姓名
--Course、SC、Student结合
SELECT hu.Sname 姓名
FROM
(SELECT Course.Cname, SC.score, Student.Sname
FROM SC
LEFT JOIN Course ON SC.C = Course.C
LEFT JOIN Student ON Student.S = SC.S) hu
GROUP BY hu.Sname
HAVING MIN(hu.score) > 70
29. 英语成绩不小于92：优秀，不小于78：良好，不小于60及格，低于60：重修；
--英语成绩不小于92：优秀，不小于78：良好，不小于60及格，低于60：重修
--Course、SC、Student结合
SELECT hu.Sname 姓名,hu.score 成绩,
(CASE WHEN hu.score >= 92 THEN '优秀'
WHEN hu.score >= 78 THEN '良好'
WHEN hu.score >=60 THEN '及格'
ELSE '重修' END) 级别
FROM
(SELECT Student.Sname, Course.Cname, SC.score
FROM SC
LEFT JOIN Course ON SC.C = Course.C
LEFT JOIN Student ON Student.S = SC.S) hu
WHERE hu.Cname = '英语'
ORDER BY 成绩 DESC
30. 每一科成绩显示各个级别的人数，成绩不小于92：优秀，不小于78：良好，不小于60及格，低于60：重修；
--每一科成绩显示各个级别的人数，成绩不小于92：优秀，不小于78：良好，不小于60及格，低于60：重修
--Course、SC、Student结合, 28题升级版
SELECT gu.科目,
SUM(CASE WHEN gu.级别='优秀' THEN 1 ELSE 0 END) 优秀,
SUM(CASE WHEN gu.级别='良好' THEN 1 ELSE 0 END) 良好,
SUM(CASE WHEN gu.级别='及格' THEN 1 ELSE 0 END) 及格,
SUM(CASE WHEN gu.级别='重修' THEN 1 ELSE 0 END) 重修
FROM
(SELECT hu.Cname 科目,hu.score 成绩,
(CASE WHEN hu.score >= 92 THEN '优秀'
WHEN hu.score >= 78 THEN '良好'
WHEN hu.score >=60 THEN '及格'
ELSE '重修' END) 级别
FROM
(SELECT Student.Sname, Course.Cname, SC.score
FROM SC
LEFT JOIN Course ON SC.C = Course.C
LEFT JOIN Student ON Student.S = SC.S) hu) gu
GROUP BY gu.科目
ORDER BY 优秀 DESC
image
扫描关注微信公众号pythonfan，获取更多。
pythonfan.jpg
