0%

[SQL] 超經典SQL練習題

最近找工作才發現對於SQL的使用很陌生,因此找了網路上一篇多人引用的經典文章–超经典SQL练习题,做完这些你的SQL就过关了。本篇結合幾位大神的解法,加上自己的想法彙整而成。比較不同的是,原作者們都是使用MSSQL或MySQL,這裡則是使用SQLite來實作。

建立資料表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
-- 學生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
-- 課程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
-- 教師表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
-- 成績表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);

-- 插入學生表測試資料
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 Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

題目

  1. 查詢”01”課程比”02”課程成績高的學生的資訊及課程分數
答案
1
2
3
4
SELECT a.*, b.s_score as score_01, c.s_score as score_02 FROM Student a 
JOIN Score b ON a.s_id = b.s_id AND b.c_id = '01'
JOIN Score c ON a.s_id = c.s_id AND c.c_id = '02'
WHERE b.s_score > c.s_score
結果
1
2
3
4
5
6
+------+--------+------------+-------+----------+----------+
| s_id | s_name | s_birth | s_sex | score_01 | score_02 |
+------+--------+------------+-------+----------+----------+
| 02 | 錢電 | 1990-12-21 | 男 | 70 | 60 |
| 04 | 李雲 | 1990-08-06 | 男 | 50 | 30 |
+------+--------+------------+-------+----------+----------+
  1. 查詢”01”課程比”02”課程成績低的學生的資訊及課程分數
答案
1
2
3
4
SELECT a.*, b.s_score as score_01, c.s_score as score_02 FROM Student a 
JOIN Score b ON a.s_id = b.s_id AND b.c_id = '01'
JOIN Score c ON a.s_id = c.s_id AND c.c_id = '02'
WHERE b.s_score < c.s_score
結果
1
2
3
4
5
6
+------+--------+------------+-------+----------+----------+
| s_id | s_name | s_birth | s_sex | score_01 | score_02 |
+------+--------+------------+-------+----------+----------+
| 01 | 趙雷 | 1990-01-01 | 男 | 80 | 90 |
| 05 | 周梅 | 1991-12-01 | 女 | 76 | 87 |
+------+--------+------------+-------+----------+----------+
  1. 查詢平均成績大於等於60分的同學的學生編號和學生姓名和平均成績
答案
1
2
3
4
SELECT a.s_id, s_name, ROUND(AVG(s_score), 2) AS avg_score FROM Student a
JOIN Score b ON a.s_id = b.s_id
GROUP BY a.s_id
HAVING avg_score >= 60
結果
1
2
3
4
5
6
7
8
9
+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
| 01 | 趙雷 | 89.67 |
| 02 | 錢電 | 70.0 |
| 03 | 孫風 | 80.0 |
| 05 | 周梅 | 81.5 |
| 07 | 鄭竹 | 93.5 |
+------+--------+-----------+
  1. 查詢平均成績小於60分的同學的學生編號和學生姓名和平均成績(包括有成績的和無成績的)
答案 使用ifnull()函式
1
2
3
4
SELECT a.s_id, s_name, ROUND(ifnull(AVG(s_score), 0), 2) AS avg_score FROM Student a
LEFT JOIN Score b ON a.s_id = b.s_id
GROUP BY a.s_id
HAVING avg_score < 60
結果
1
2
3
4
5
6
7
+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
| 04 | 李雲 | 33.33 |
| 06 | 吳蘭 | 32.5 |
| 08 | 王菊 | 0.0 |
+------+--------+-----------+
  1. 查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績(包含沒有選課的)
答案
1
2
3
SELECT a.s_id, s_name, COUNT(c_id) as course_count, ifnull(SUM(s_score), 0) as total_score FROM Student a
LEFT JOIN Score b ON a.s_id = b.s_id
GROUP BY a.s_id
結果
1
2
3
4
5
6
7
8
9
10
11
12
+------+--------+--------------+-------------+
| s_id | s_name | course_count | total_score |
+------+--------+--------------+-------------+
| 01 | 趙雷 | 3 | 269 |
| 02 | 錢電 | 3 | 210 |
| 03 | 孫風 | 3 | 240 |
| 04 | 李雲 | 3 | 100 |
| 05 | 周梅 | 2 | 163 |
| 06 | 吳蘭 | 2 | 65 |
| 07 | 鄭竹 | 2 | 187 |
| 08 | 王菊 | 0 | 0 |
+------+--------+--------------+-------------+
  1. 查詢”李”姓老師的數量
答案
1
2
SELECT COUNT() FROM Teacher
WHERE t_name LIKE '李%'
結果
1
2
3
4
5
+---------+
| COUNT() |
+---------+
| 1 |
+---------+
  1. 查詢學過”張三”老師授課的學生資訊
答案 方法一
1
2
3
4
5
6
7
SELECT * FROM Student
WHERE s_id IN (
SELECT s.s_id FROM Score s -- 透過課程找出對應的學號
JOIN Course c ON s.c_id = c.c_id AND c.t_id = (
SELECT t_id FROM Teacher WHERE t_name = '張三'
)
)
方法二
1
2
3
4
5
6
SELECT a.* FROM Student a
JOIN Score b ON a.s_id = b.s_id AND b.c_id = (
SELECT c_id FROM Course WHERE t_id = (
SELECT t_id FROM Teacher WHERE t_name = '張三'
)
)
結果
1
2
3
4
5
6
7
8
9
10
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 01 | 趙雷 | 1990-01-01 | 男 |
| 02 | 錢電 | 1990-12-21 | 男 |
| 03 | 孫風 | 1990-05-20 | 男 |
| 04 | 李雲 | 1990-08-06 | 男 |
| 05 | 周梅 | 1991-12-01 | 女 |
| 07 | 鄭竹 | 1989-07-01 | 女 |
+------+--------+------------+-------+
  1. 查詢沒學過”張三”老師授課的學生資訊
答案
1
2
3
4
5
6
7
SELECT * FROM Student
WHERE s_id NOT IN ( -- 透過學號取反
SELECT s.s_id FROM Score s
JOIN Course c ON s.c_id = c.c_id AND c.t_id = (
SELECT t_id FROM Teacher WHERE t_name = '張三'
)
)
結果
1
2
3
4
5
6
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 06 | 吳蘭 | 1992-03-01 | 女 |
| 08 | 王菊 | 1990-01-20 | 女 |
+------+--------+------------+-------+
  1. 查詢學過編號為01,並且也學過編號為02的課程的學生資訊
答案
1
2
3
4
5
6
SELECT * FROM Student
WHERE s_id IN (
SELECT s1.s_id FROM Score s1
JOIN Score s2 ON s1.s_id = s2.s_id -- 透過自連線實現
WHERE s1.c_id = '01' AND s2.c_id = '02'
)
結果
1
2
3
4
5
6
7
8
9
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 01 | 趙雷 | 1990-01-01 | 男 |
| 02 | 錢電 | 1990-12-21 | 男 |
| 03 | 孫風 | 1990-05-20 | 男 |
| 04 | 李雲 | 1990-08-06 | 男 |
| 05 | 周梅 | 1991-12-01 | 女 |
+------+--------+------------+-------+
  1. 查詢學過編號為01,但是沒有學過編號為02的課程的學生資訊
答案
1
2
3
4
5
6
7
SELECT * FROM Student
WHERE s_id IN (
SELECT s_id FROM Score
WHERE c_id = '01' AND s_id NOT IN (
SELECT s_id FROM Score WHERE c_id = '02' -- 學過02課程的學生
)
)
結果
1
2
3
4
5
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 06 | 吳蘭 | 1992-03-01 | 女 |
+------+--------+------------+-------+
  1. 查詢沒有學全所有課程的學生資訊
答案
1
2
3
4
5
6
SELECT a.*, COUNT(b.c_id) as course_count FROM Student a
LEFT JOIN Score b ON a.s_id = b.s_id
GROUP BY b.s_id
HAVING course_count < (
SELECT COUNT() FROM Course
)
結果
1
2
3
4
5
6
7
8
+------+--------+------------+-------+--------------+
| s_id | s_name | s_birth | s_sex | course_count |
+------+--------+------------+-------+--------------+
| 08 | 王菊 | 1990-01-20 | 女 | 0 |
| 05 | 周梅 | 1991-12-01 | 女 | 2 |
| 06 | 吳蘭 | 1992-03-01 | 女 | 2 |
| 07 | 鄭竹 | 1989-07-01 | 女 | 2 |
+------+--------+------------+-------+--------------+
  1. 查詢至少有一門課與學號為”01”的同學所學相同的學生資訊
答案
1
2
3
4
5
6
SELECT a.* FROM Student a
JOIN Score b ON a.s_id = b.s_id
WHERE b.c_id IN (
SELECT c_id FROM Score WHERE s_id = '01'
) AND b.s_id != '01'
GROUP BY b.s_id
結果
1
2
3
4
5
6
7
8
9
10
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 02 | 錢電 | 1990-12-21 | 男 |
| 03 | 孫風 | 1990-05-20 | 男 |
| 04 | 李雲 | 1990-08-06 | 男 |
| 05 | 周梅 | 1991-12-01 | 女 |
| 06 | 吳蘭 | 1992-03-01 | 女 |
| 07 | 鄭竹 | 1989-07-01 | 女 |
+------+--------+------------+-------+
  1. 查詢和”01”號的同學學習的課程完全相同的其他學生資訊
答案 使用group_concat()函式
1
2
3
4
5
6
SELECT a.* FROM Student a
JOIN Score b ON a.s_id = b.s_id
GROUP BY b.s_id
HAVING group_concat(b.c_id) = (
SELECT group_concat(c_id) FROM Score WHERE s_id = '01'
) AND b.s_id != '01'
結果
1
2
3
4
5
6
7
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 02 | 錢電 | 1990-12-21 | 男 |
| 03 | 孫風 | 1990-05-20 | 男 |
| 04 | 李雲 | 1990-08-06 | 男 |
+------+--------+------------+-------+
  1. 查詢沒學過”張三”老師講授的任一門課程的學生姓名
    PS: 與第8題相似
答案
1
2
3
4
5
6
7
SELECT s_name FROM Student
WHERE s_id NOT IN ( -- 透過學號取反
SELECT s.s_id FROM Score s
JOIN Course c ON s.c_id = c.c_id AND c.t_id = (
SELECT t_id FROM Teacher WHERE t_name = '張三'
)
)
結果
1
2
3
4
5
6
+--------+
| s_name |
+--------+
| 吳蘭 |
| 王菊 |
+--------+
  1. 查詢兩門及其以上不及格課程的同學的學號,姓名及其平均成績
答案
1
2
3
4
5
SELECT a.s_id, s_name, ROUND(AVG(s_score), 2) as avg_score FROM Student a
JOIN Score b ON a.s_id = b.s_id
WHERE s_score < 60
GROUP BY b.s_id
HAVING COUNT(c_id) >= 2
結果
1
2
3
4
5
6
+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
| 04 | 李雲 | 33.33 |
| 06 | 吳蘭 | 32.5 |
+------+--------+-----------+
  1. 檢索”01”課程分數小於60,按分數降序排列的學生資訊
答案
1
2
3
4
SELECT a.*, s_score FROM Student a
JOIN Score b ON a.s_id = b.s_id
WHERE c_id = '01' AND s_score < 60
ORDER BY s_score DESC
結果
1
2
3
4
5
6
+------+--------+------------+-------+---------+
| s_id | s_name | s_birth | s_sex | s_score |
+------+--------+------------+-------+---------+
| 04 | 李雲 | 1990-08-06 | 男 | 50 |
| 06 | 吳蘭 | 1992-03-01 | 女 | 31 |
+------+--------+------------+-------+---------+
  1. 按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
答案
1
2
3
4
5
6
7
8
SELECT s_id,
SUM(CASE WHEN c_id='01' THEN s_score ELSE NULL END) as score_01,
SUM(CASE WHEN c_id='02' THEN s_score ELSE NULL END) as score_02,
SUM(CASE WHEN c_id='03' THEN s_score ELSE NULL END) as score_03,
ROUND(AVG(s_score), 2) as avg_score
FROM Score
GROUP BY s_id
ORDER BY avg_score DESC
結果
1
2
3
4
5
6
7
8
9
10
11
+------+----------+----------+----------+-----------+
| s_id | score_01 | score_02 | score_03 | avg_score |
+------+----------+----------+----------+-----------+
| 07 | | 89 | 98 | 93.5 |
| 01 | 80 | 90 | 99 | 89.67 |
| 05 | 76 | 87 | | 81.5 |
| 03 | 80 | 80 | 80 | 80.0 |
| 02 | 70 | 60 | 80 | 70.0 |
| 04 | 50 | 30 | 20 | 33.33 |
| 06 | 31 | | 34 | 32.5 |
+------+----------+----------+----------+-----------+
  1. 查詢各科成績最高分、最低分和平均分,以如下形式顯示:課程ID,課程name,最高分,最低分,平均分,及格率,中等率,優良率,優秀率(及格為>=60,中等為:70-80,優良為:80-90,優秀為:>=90)。
    要求輸出課程號碼和選修人數,查詢結果依人數降序排列,若人數相同,依課程號碼升序排列
答案
1
2
3
4
5
6
7
8
9
10
SELECT b.c_id as '課程ID', c_name as '課程name', COUNT() as '選修人數',
MAX(s_score) as '最高分', MIN(s_score) as '最低分', ROUND(AVG(s_score), 2) as '平均分',
ROUND(SUM(CASE WHEN s_score >= 60 THEN 1 ELSE 0 END)*1.00/COUNT()*100, 2) as '及格率',
ROUND(SUM(CASE WHEN s_score >= 70 AND s_score < 80 THEN 1 ELSE 0 END)*1.00/COUNT()*100, 2) as '中等率',
ROUND(SUM(CASE WHEN s_score >= 80 AND s_score < 90 THEN 1 ELSE 0 END)*1.00/COUNT()*100, 2) as '優良率',
ROUND(SUM(CASE WHEN s_score >= 90 THEN 1 ELSE 0 END)*1.00/COUNT()*100, 2) as '優秀率'
FROM Score a
JOIN Course b ON a.c_id = b.c_id
GROUP BY b.c_id
ORDER BY '選修人數' DESC, b.c_id ASC
結果
1
2
3
4
5
6
7
+--------+----------+---------+-------+--------+---------+---------+---------+---------+----------+
| 課程ID | 課程name | 選修人數 | 最高分 | 最低分 | 平均分 | 及格率 | 中等率 | 優良率 | 優秀率 |
+--------+----------+---------+-------+--------+---------+---------+---------+---------+----------+
| 01 | 語文 | 6 | 80 | 31 | 64.5 | 66.67 | 33.33 | 33.33 | 0.0 |
| 02 | 數學 | 6 | 90 | 30 | 72.67 | 83.33 | 0.0 | 50.0 | 16.67 |
| 03 | 英語 | 6 | 99 | 20 | 68.5 | 66.67 | 0.0 | 33.33 | 33.33 |
+--------+----------+---------+-------+--------+---------+---------+---------+---------+----------+
  1. 按各科成績進行排序,並顯示排名,分數重複時保留名次空缺
答案
1
2
3
4
5
SELECT *, (
SELECT COUNT(DISTINCT s_score) + 1 FROM Score b
WHERE a.c_id = b.c_id AND b.s_score > a.s_score) as rank
FROM Score a
ORDER BY c_id, rank
結果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
+------+------+---------+------+
| s_id | c_id | s_score | rank |
+------+------+---------+------+
| 01 | 01 | 80 | 1 |
| 03 | 01 | 80 | 1 |
| 05 | 01 | 76 | 2 |
| 02 | 01 | 70 | 3 |
| 04 | 01 | 50 | 4 |
| 06 | 01 | 31 | 5 |
| 01 | 02 | 90 | 1 |
| 07 | 02 | 89 | 2 |
| 05 | 02 | 87 | 3 |
| 03 | 02 | 80 | 4 |
| 02 | 02 | 60 | 5 |
| 04 | 02 | 30 | 6 |
| 01 | 03 | 99 | 1 |
| 07 | 03 | 98 | 2 |
| 02 | 03 | 80 | 3 |
| 03 | 03 | 80 | 3 |
| 06 | 03 | 34 | 4 |
| 04 | 03 | 20 | 5 |
+------+------+---------+------+
  • 19.1 按各科成績進行行排序,並顯示排名,分數重複時合併名次
答案 方法一
1
2
3
4
5
SELECT *, (
SELECT COUNT(s_score) + 1 FROM Score b
WHERE a.c_id = b.c_id AND b.s_score > a.s_score) as rank
FROM Score a
ORDER BY c_id, rank
方法二:使用rank()函式
1
2
3
SELECT *
FROM (SELECT *, rank() OVER (PARTITION BY c_id ORDER BY s_score DESC) as rank FROM Score)
ORDER BY c_id
結果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
+------+------+---------+------+
| s_id | c_id | s_score | rank |
+------+------+---------+------+
| 01 | 01 | 80 | 1 |
| 03 | 01 | 80 | 1 |
| 05 | 01 | 76 | 3 |
| 02 | 01 | 70 | 4 |
| 04 | 01 | 50 | 5 |
| 06 | 01 | 31 | 6 |
| 01 | 02 | 90 | 1 |
| 07 | 02 | 89 | 2 |
| 05 | 02 | 87 | 3 |
| 03 | 02 | 80 | 4 |
| 02 | 02 | 60 | 5 |
| 04 | 02 | 30 | 6 |
| 01 | 03 | 99 | 1 |
| 07 | 03 | 98 | 2 |
| 02 | 03 | 80 | 3 |
| 03 | 03 | 80 | 3 |
| 06 | 03 | 34 | 5 |
| 04 | 03 | 20 | 6 |
+------+------+---------+------+
  1. 查詢學生的總成績,並進行排名
答案 使用row_number()函式
1
2
3
4
5
SELECT a.s_id, s_name, ifnull(SUM(s_score), 0) as total_score,
row_number() OVER (ORDER BY SUM(s_score) DESC) as rank
FROM Student a
LEFT JOIN Score b ON a.s_id = b.s_id
GROUP BY a.s_id
結果
1
2
3
4
5
6
7
8
9
10
11
12
+------+--------+-------------+------+
| s_id | s_name | total_score | rank |
+------+--------+-------------+------+
| 01 | 趙雷 | 269 | 1 |
| 03 | 孫風 | 240 | 2 |
| 02 | 錢電 | 210 | 3 |
| 07 | 鄭竹 | 187 | 4 |
| 05 | 周梅 | 163 | 5 |
| 04 | 李雲 | 100 | 6 |
| 06 | 吳蘭 | 65 | 7 |
| 08 | 王菊 | 0 | 8 |
+------+--------+-------------+------+
  1. 查詢不同老師所教不同課程平均分從高到低顯示
答案
1
2
3
4
5
SELECT c.t_id, t_name, c.c_id, c_name, ROUND(AVG(s_score), 2) as avg_score FROM Score s
JOIN Course c ON s.c_id = c.c_id
JOIN Teacher t ON c.t_id = t.t_id
GROUP BY c.c_id
ORDER BY avg_score DESC
結果
1
2
3
4
5
6
7
+------+--------+------+--------+-----------+
| t_id | t_name | c_id | c_name | avg_score |
+------+--------+------+--------+-----------+
| 01 | 張三 | 02 | 數學 | 72.67 |
| 03 | 王五 | 03 | 英語 | 68.5 |
| 02 | 李四 | 01 | 語文 | 64.5 |
+------+--------+------+--------+-----------+
  1. 查詢所有課程的成績第2名到第3名的學生資訊及該課程成績
答案
1
2
3
4
SELECT b.*, c_id, s_score, rank 
FROM (SELECT *, rank() OVER (PARTITION BY c_id ORDER BY s_score DESC) as rank FROM Score) a
JOIN Student b ON a.s_id = b.s_id
WHERE rank BETWEEN 2 AND 3
結果:因有分數相同,占同個名次,所以課程01才只有呈現第3名
1
2
3
4
5
6
7
8
9
10
+------+--------+------------+-------+------+---------+------+
| s_id | s_name | s_birth | s_sex | c_id | s_score | rank |
+------+--------+------------+-------+------+---------+------+
| 05 | 周梅 | 1991-12-01 | 女 | 01 | 76 | 3 |
| 07 | 鄭竹 | 1989-07-01 | 女 | 02 | 89 | 2 |
| 05 | 周梅 | 1991-12-01 | 女 | 02 | 87 | 3 |
| 07 | 鄭竹 | 1989-07-01 | 女 | 03 | 98 | 2 |
| 02 | 錢電 | 1990-12-21 | 男 | 03 | 80 | 3 |
| 03 | 孫風 | 1990-05-20 | 男 | 03 | 80 | 3 |
+------+--------+------------+-------+------+---------+------+
  1. 統計各科成績各分數段人數:課程編號,課程名稱,[100-85],[85-70],[70-60],[60-0] 及所佔百分比
答案
1
2
3
4
5
6
7
8
9
10
11
12
SELECT a.c_id as '課程編號', c_name as '課程名稱',
SUM(CASE WHEN s_score >= 85 THEN 1 ELSE 0 END) as '[100-85]',
ROUND(SUM(CASE WHEN s_score >= 85 THEN 1 ELSE 0 END)*1.00/COUNT(a.s_id)*100, 2) as '[100-85]百分比',
SUM(CASE WHEN s_score >= 70 AND s_score < 85 THEN 1 ELSE 0 END) as '[85-70]',
ROUND(SUM(CASE WHEN s_score >= 70 AND s_score < 85 THEN 1 ELSE 0 END)*1.00/COUNT(a.s_id)*100, 2) as '[85-70]百分比',
SUM(CASE WHEN s_score >= 60 AND s_score < 70 THEN 1 ELSE 0 END) as '[70-60]',
ROUND(SUM(CASE WHEN s_score >= 60 AND s_score < 70 THEN 1 ELSE 0 END)*1.00/COUNT(a.s_id)*100, 2) as '[70-60]百分比',
SUM(CASE WHEN s_score < 60 THEN 1 ELSE 0 END) as '[60-0]',
ROUND(SUM(CASE WHEN s_score < 60 THEN 1 ELSE 0 END)*1.00/COUNT(a.s_id)*100, 2) as '[60-0]百分比'
FROM Score a
JOIN Course b ON a.c_id = b.c_id
GROUP BY a.c_id
結果
1
2
3
4
5
6
7
+---------+----------+----------+---------------+---------+--------------+---------+---------------+--------+-------------+
| 課程編號 | 課程名稱 | [100-85] | [100-85]百分比 | [85-70] | [85-70]百分比 | [70-60] | [70-60]百分比 | [60-0] | [60-0]百分比 |
+---------+----------+----------+---------------+---------+--------------+---------+---------------+--------+-------------+
| 01 | 語文 | 0 | 0.0 | 4 | 66.67 | 0 | 0.0 | 2 | 33.33 |
| 02 | 數學 | 3 | 50.0 | 1 | 16.67 | 1 | 16.67 | 1 | 16.67 |
| 03 | 英語 | 2 | 33.33 | 2 | 33.33 | 0 | 0.0 | 2 | 33.33 |
+---------+----------+----------+---------------+---------+--------------+---------+---------------+--------+-------------+
  1. 查詢學生平均成績及其名次
    PS: 與第20題相似
答案
1
2
3
4
5
SELECT a.s_id, s_name, ROUND(ifnull(AVG(s_score), 0), 2) as avg_score,
row_number() OVER (ORDER BY AVG(s_score) DESC) as rank
FROM Student a
LEFT JOIN Score b ON a.s_id = b.s_id
GROUP BY a.s_id
結果
1
2
3
4
5
6
7
8
9
10
11
12
+------+--------+-----------+------+
| s_id | s_name | avg_score | rank |
+------+--------+-----------+------+
| 07 | 鄭竹 | 93.5 | 1 |
| 01 | 趙雷 | 89.67 | 2 |
| 05 | 周梅 | 81.5 | 3 |
| 03 | 孫風 | 80.0 | 4 |
| 02 | 錢電 | 70.0 | 5 |
| 04 | 李雲 | 33.33 | 6 |
| 06 | 吳蘭 | 32.5 | 7 |
| 08 | 王菊 | 0.0 | 8 |
+------+--------+-----------+------+
  1. 查詢各科成績前三名的記錄
答案 使用rank()函式
1
2
3
SELECT *
FROM (SELECT *, rank() OVER (PARTITION BY c_id ORDER BY s_score DESC) as rank FROM Score)
WHERE rank <= 3
結果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
+------+------+---------+------+
| s_id | c_id | s_score | rank |
+------+------+---------+------+
| 01 | 01 | 80 | 1 |
| 03 | 01 | 80 | 1 |
| 05 | 01 | 76 | 3 |
| 01 | 02 | 90 | 1 |
| 07 | 02 | 89 | 2 |
| 05 | 02 | 87 | 3 |
| 01 | 03 | 99 | 1 |
| 07 | 03 | 98 | 2 |
| 02 | 03 | 80 | 3 |
| 03 | 03 | 80 | 3 |
+------+------+---------+------+
  1. 查詢每門課程被選修的學生數
答案
1
2
SELECT c_id, COUNT(s_id) as num FROM Score
GROUP BY c_id
結果
1
2
3
4
5
6
7
+------+-----+
| c_id | num |
+------+-----+
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
+------+-----+
  1. 查詢出只選修兩門課程的學生學號和姓名
答案
1
2
3
4
SELECT a.s_id, s_name FROM Score a
JOIN Student b ON a.s_id = b.s_id
GROUP BY a.s_id
HAVING COUNT(c_id) = 2
結果
1
2
3
4
5
6
7
+------+--------+
| s_id | s_name |
+------+--------+
| 05 | 周梅 |
| 06 | 吳蘭 |
| 07 | 鄭竹 |
+------+--------+
  1. 查詢男生、女生人數
答案
1
2
SELECT s_sex, COUNT(s_id) as num FROM Student
GROUP BY s_sex
結果
1
2
3
4
5
6
+-------+-----+
| s_sex | num |
+-------+-----+
| 女 | 4 |
| 男 | 4 |
+-------+-----+
  1. 查詢名字中含有”風”字的學生資訊
答案
1
2
SELECT * FROM Student
WHERE s_name LIKE '%風%'
結果
1
2
3
4
5
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 03 | 孫風 | 1990-05-20 | 男 |
+------+--------+------------+-------+
  1. 查詢同名同性學生名單,並統計同名人數
答案
1
2
3
SELECT s1.s_name, s1.s_sex, COUNT() FROM Student s1
JOIN Student s2 ON s1.s_name = s2.s_name AND s1.s_sex = s2.s_sex AND s1.s_id != s2.s_id
GROUP BY s1.s_name, s1.s_sex
查無結果
  1. 查詢1990年出生的學生資訊
答案
1
2
SELECT * FROM Student
WHERE strftime('%Y', s_birth) = '1990'
結果
1
2
3
4
5
6
7
8
9
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 01 | 趙雷 | 1990-01-01 | 男 |
| 02 | 錢電 | 1990-12-21 | 男 |
| 03 | 孫風 | 1990-05-20 | 男 |
| 04 | 李雲 | 1990-08-06 | 男 |
| 08 | 王菊 | 1990-01-20 | 女 |
+------+--------+------------+-------+
  1. 查詢每門課程的平均成績,結果按平均成績降序排列,平均成績相同時,按課程編號升序排列
答案
1
2
3
4
SELECT c_id, ROUND(AVG(s_score), 2) as avg_score
FROM Score
GROUP BY c_id
ORDER BY avg_score DESC, c_id ASC
結果
1
2
3
4
5
6
7
+------+-----------+
| c_id | avg_score |
+------+-----------+
| 02 | 72.67 |
| 03 | 68.5 |
| 01 | 64.5 |
+------+-----------+
  1. 查詢平均成績大於等於85的所有學生的學號、姓名和平均成績
    PS: 與第3題相似
答案
1
2
3
4
SELECT a.s_id, s_name, ROUND(AVG(s_score), 2) AS avg_score FROM Student a
JOIN Score b ON a.s_id = b.s_id
GROUP BY a.s_id
HAVING avg_score >= 85
結果
1
2
3
4
5
6
+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
| 01 | 趙雷 | 89.67 |
| 07 | 鄭竹 | 93.5 |
+------+--------+-----------+
  1. 查詢課程名稱為”數學”,且分數低於60的學生姓名和分數
答案
1
2
3
4
5
SELECT s_name, s_score FROM Score a
JOIN Student b ON a.s_id = b.s_id
WHERE a.c_id = (
SELECT c_id FROM Course WHERE c_name = '數學'
) AND s_score < 60
結果
1
2
3
4
5
+--------+---------+
| s_name | s_score |
+--------+---------+
| 李雲 | 30 |
+--------+---------+
  1. 查詢所有學生的課程及分數狀況(有學生沒成績,沒選課的狀況)
答案
1
2
3
4
5
6
7
8
SELECT a.s_id, s_name,
MAX(CASE WHEN c_name='語文' THEN s_score ELSE NULL END) as '語文',
MAX(CASE WHEN c_name='數學' THEN s_score ELSE NULL END) as '數學',
MAX(CASE WHEN c_name='英語' THEN s_score ELSE NULL END) as '英語'
FROM Student a
LEFT JOIN Score b ON a.s_id = b.s_id
JOIN Course c ON b.c_id = c.c_id
GROUP BY a.s_id
結果
1
2
3
4
5
6
7
8
9
10
11
+------+--------+------+------+------+
| s_id | s_name | 語文 | 數學 | 英語 |
+------+--------+------+------+------+
| 01 | 趙雷 | 80 | 90 | 99 |
| 02 | 錢電 | 70 | 60 | 80 |
| 03 | 孫風 | 80 | 80 | 80 |
| 04 | 李雲 | 50 | 30 | 20 |
| 05 | 周梅 | 76 | 87 | |
| 06 | 吳蘭 | 31 | | 34 |
| 07 | 鄭竹 | | 89 | 98 |
+------+--------+------+------+------+
  1. 查詢任何一門課程成績在70分以上的姓名、課程名稱和分數
答案
1
2
3
SELECT s_name, c_name, s_score FROM Score a
JOIN Course b ON a.c_id = b.c_id AND s_score > 70
JOIN Student c ON a.s_id = c.s_id
結果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
+--------+--------+---------+
| s_name | c_name | s_score |
+--------+--------+---------+
| 趙雷 | 語文 | 80 |
| 趙雷 | 數學 | 90 |
| 趙雷 | 英語 | 99 |
| 錢電 | 英語 | 80 |
| 孫風 | 語文 | 80 |
| 孫風 | 數學 | 80 |
| 孫風 | 英語 | 80 |
| 周梅 | 語文 | 76 |
| 周梅 | 數學 | 87 |
| 鄭竹 | 數學 | 89 |
| 鄭竹 | 英語 | 98 |
+--------+--------+---------+
  1. 查詢不及格的課程
答案
1
2
3
SELECT s_name, c_name, s_score FROM Score a
JOIN Course b ON a.c_id = b.c_id AND s_score < 60
JOIN Student c ON a.s_id = c.s_id
結果
1
2
3
4
5
6
7
8
9
+--------+--------+---------+
| s_name | c_name | s_score |
+--------+--------+---------+
| 李雲 | 語文 | 50 |
| 李雲 | 數學 | 30 |
| 李雲 | 英語 | 20 |
| 吳蘭 | 語文 | 31 |
| 吳蘭 | 英語 | 34 |
+--------+--------+---------+
  1. 查詢課程編號為01且課程成績在80分以上的學生的學號和姓名
答案
1
2
SELECT a.s_id, s_name FROM Score a
JOIN Student b ON a.s_id = b.s_id AND c_id = '01' AND s_score > 80
查無結果
  1. 求每門課程的學生人數
    PS: 與第26題相同

  2. 成績不重複的情況下,查詢選修”張三”老師所授課程的學生中,成績最高的學生資訊及其成績

答案
1
2
3
4
5
6
SELECT b.*, MAX(s_score) FROM Score a
JOIN Student b ON a.s_id = b.s_id
WHERE c_id = (
SELECT c_id FROM Teacher t
JOIN Course c ON t.t_id = c.t_id AND t_name = '張三'
)
結果
1
2
3
4
5
+------+--------+------------+-------+--------------+
| s_id | s_name | s_birth | s_sex | MAX(s_score) |
+------+--------+------------+-------+--------------+
| 01 | 趙雷 | 1990-01-01 | 男 | 90 |
+------+--------+------------+-------+--------------+
  • 40.1 成績重複的情況下,查詢選修”張三”老師所授課程的學生中,成績最高的學生資訊及其成績
答案
1
2
3
4
5
6
7
SELECT b.*, s_score 
FROM (SELECT *, rank() OVER (PARTITION BY c_id ORDER BY s_score DESC) as rank FROM Score) a
JOIN Student b ON a.s_id = b.s_id
WHERE c_id = (
SELECT c_id FROM Teacher t
JOIN Course c ON t.t_id = c.t_id AND t_name = '張三'
) AND rank = 1
結果
1
2
3
4
5
+------+--------+------------+-------+---------+
| s_id | s_name | s_birth | s_sex | s_score |
+------+--------+------------+-------+---------+
| 01 | 趙雷 | 1990-01-01 | 男 | 90 |
+------+--------+------------+-------+---------+
  1. 查詢不同課程成績相同的學生的學生編號、課程編號、學生成績
答案
1
2
SELECT DISTINCT a.* FROM Score a
JOIN Score b ON a.s_id = b.s_id AND a.s_score = b.s_score AND a.c_id != b.c_id
結果
1
2
3
4
5
6
7
+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
| 03 | 01 | 80 |
| 03 | 02 | 80 |
| 03 | 03 | 80 |
+------+------+---------+
  1. 查詢每門成績最好的前兩名
    PS: 與第25題相似
答案
1
2
3
SELECT *
FROM (SELECT *, rank() OVER (PARTITION BY c_id ORDER BY s_score DESC) as rank FROM Score)
WHERE rank <= 2
結果
1
2
3
4
5
6
7
8
9
10
+------+------+---------+------+
| s_id | c_id | s_score | rank |
+------+------+---------+------+
| 01 | 01 | 80 | 1 |
| 03 | 01 | 80 | 1 |
| 01 | 02 | 90 | 1 |
| 07 | 02 | 89 | 2 |
| 01 | 03 | 99 | 1 |
| 07 | 03 | 98 | 2 |
+------+------+---------+------+
  1. 統計每門課程的學生選修人數(超過5人的課程才統計)
    PS: 與第26題相似
答案
1
2
3
SELECT c_id, COUNT(s_id) as num FROM Score
GROUP BY c_id
HAVING num > 5
結果
1
2
3
4
5
6
7
+------+-----+
| c_id | num |
+------+-----+
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
+------+-----+
  1. 檢索至少選修兩門課程的學生學號
    PS: 與第27題相似
答案
1
2
3
SELECT s_id FROM Score
GROUP BY s_id
HAVING COUNT(c_id) >= 2
結果
1
2
3
4
5
6
7
8
9
10
11
+------+
| s_id |
+------+
| 01 |
| 02 |
| 03 |
| 04 |
| 05 |
| 06 |
| 07 |
+------+
  1. 查詢選修了全部課程的學生資訊
答案
1
2
3
4
SELECT a.* FROM Student a
JOIN Score b ON a.s_id = b.s_id
GROUP BY a.s_id
HAVING COUNT(b.c_id) = (SELECT COUNT() FROM Course)
結果
1
2
3
4
5
6
7
8
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 01 | 趙雷 | 1990-01-01 | 男 |
| 02 | 錢電 | 1990-12-21 | 男 |
| 03 | 孫風 | 1990-05-20 | 男 |
| 04 | 李雲 | 1990-08-06 | 男 |
+------+--------+------------+-------+
  1. 查詢各學生的年齡,只按年份來算
答案
1
SELECT s_name, strftime('%Y', 'now') - strftime('%Y', s_birth) as age FROM Student
結果
1
2
3
4
5
6
7
8
9
10
11
12
+--------+------+
| s_name | age |
+--------+------+
| 趙雷 | 33 |
| 錢電 | 33 |
| 孫風 | 33 |
| 李雲 | 33 |
| 周梅 | 32 |
| 吳蘭 | 31 |
| 鄭竹 | 34 |
| 王菊 | 33 |
+--------+------+
  • 46.1 按照出生日期來算,當前月日 < 出生年月的月日則,年齡減一
答案
1
2
3
4
5
SELECT s_name, 
strftime('%Y', 'now') - strftime('%Y', s_birth) - (
CASE WHEN strftime('%m%d', 'now') < strftime('%m%d', s_birth)
THEN 1 ELSE 0 END) as age
FROM Student
結果
1
2
3
4
5
6
7
8
9
10
11
12
+--------+-----+
| s_name | age |
+--------+-----+
| 趙雷 | 33 |
| 錢電 | 32 |
| 孫風 | 33 |
| 李雲 | 33 |
| 周梅 | 31 |
| 吳蘭 | 31 |
| 鄭竹 | 34 |
| 王菊 | 33 |
+--------+-----+
  1. 查詢本週過生日的學生
答案
1
2
SELECT * FROM Student
WHERE strftime('%W', s_birth) = strftime('%W', 'now')
  1. 查詢下週過生日的學生
答案 使用strftime()函式會得到字串結果,做加減運算後,會變成數字,要小心兩邊的型態是否一致。
1
2
SELECT * FROM Student
WHERE strftime('%W', s_birth) + 0 = strftime('%W', 'now') + 1
  1. 查詢本月過生日的學生
答案
1
2
SELECT * FROM Student
WHERE strftime('%m', s_birth) = strftime('%m', 'now')
  1. 查詢下月過生日的學生
答案 使用strftime()函式會得到字串結果,做加減運算後,會變成數字,要小心兩邊的型態是否一致。
1
2
SELECT * FROM Student
WHERE strftime('%m', s_birth) + 0 = strftime('%m', 'now') + 1

其它

  • 查詢在成績表存在成績的學生資訊
答案
1
2
SELECT DISTINCT b.* FROM Score a, Student b
WHERE a.s_id = b.s_id
結果
1
2
3
4
5
6
7
8
9
10
11
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 01 | 趙雷 | 1990-01-01 | 男 |
| 02 | 錢電 | 1990-12-21 | 男 |
| 03 | 孫風 | 1990-05-20 | 男 |
| 04 | 李雲 | 1990-08-06 | 男 |
| 05 | 周梅 | 1991-12-01 | 女 |
| 06 | 吳蘭 | 1992-03-01 | 女 |
| 07 | 鄭竹 | 1989-07-01 | 女 |
+------+--------+------------+-------+

參考資料

  1. 50道SQL练习题
  2. 超經典MySQL練習50題,做完這些你的SQL就過關了
  3. Mysql 经典50题练习
  4. 經典SQL練習題(MySQL版)