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

建立資料表

-- 學生表
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);

題目

查詢”01”課程比”02”課程成績高的學生的資訊及課程分數

答案

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

結果

+------+--------+------------+-------+----------+----------+
| s_id | s_name | s_birth    | s_sex | score_01 | score_02 |
+------+--------+------------+-------+----------+----------+
| 02   | 錢電   | 1990-12-21 | 男    | 70       | 60       |
| 04   | 李雲   | 1990-08-06 | 男    | 50       | 30       |
+------+--------+------------+-------+----------+----------+

查詢”01”課程比”02”課程成績低的學生的資訊及課程分數

答案

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

結果

+------+--------+------------+-------+----------+----------+
| s_id | s_name | s_birth    | s_sex | score_01 | score_02 |
+------+--------+------------+-------+----------+----------+
| 01   | 趙雷   | 1990-01-01 | 男    | 80       | 90       |
| 05   | 周梅   | 1991-12-01 | 女    | 76       | 87       |
+------+--------+------------+-------+----------+----------+

查詢平均成績大於等於60分的同學的學生編號和學生姓名和平均成績

答案

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

結果

+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
| 01   | 趙雷   | 89.67     |
| 02   | 錢電   | 70.0      |
| 03   | 孫風   | 80.0      |
| 05   | 周梅   | 81.5      |
| 07   | 鄭竹   | 93.5      |
+------+--------+-----------+

查詢平均成績小於60分的同學的學生編號和學生姓名和平均成績（包括有成績的和無成績的）

答案

使用ifnull()函式

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

結果

+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
| 04   | 李雲   | 33.33     |
| 06   | 吳蘭   | 32.5      |
| 08   | 王菊   | 0.0       |
+------+--------+-----------+

查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績（包含沒有選課的）

答案

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

結果

+------+--------+--------------+-------------+
| 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 2	SELECT COUNT() FROM Teacher WHERE t_name LIKE '李%'

結果

+---------+
| COUNT() |
+---------+
| 1       |
+---------+

查詢學過”張三”老師授課的學生資訊

答案

方法一

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 = '張三'
    )
)

方法二

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 = '張三'
    )
)

結果

+------+--------+------------+-------+
| 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 | 女    |
+------+--------+------------+-------+

查詢沒學過”張三”老師授課的學生資訊

答案

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 = '張三'
    )
)

結果

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 06   | 吳蘭   | 1992-03-01 | 女    |
| 08   | 王菊   | 1990-01-20 | 女    |
+------+--------+------------+-------+

查詢學過編號為01，並且也學過編號為02的課程的學生資訊

答案

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'
)

結果

+------+--------+------------+-------+
| 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 | 女    |
+------+--------+------------+-------+

查詢學過編號為01，但是沒有學過編號為02的課程的學生資訊

答案

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課程的學生
    )    
)

結果

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 06   | 吳蘭   | 1992-03-01 | 女    |
+------+--------+------------+-------+

查詢沒有學全所有課程的學生資訊

答案

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
)

結果

+------+--------+------------+-------+--------------+
| 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            |
+------+--------+------------+-------+--------------+

查詢至少有一門課與學號為”01”的同學所學相同的學生資訊

答案

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

結果

+------+--------+------------+-------+
| 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 | 女    |
+------+--------+------------+-------+

查詢和”01”號的同學學習的課程完全相同的其他學生資訊

答案

使用group_concat()函式

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'

結果

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 02   | 錢電   | 1990-12-21 | 男    |
| 03   | 孫風   | 1990-05-20 | 男    |
| 04   | 李雲   | 1990-08-06 | 男    |
+------+--------+------------+-------+

查詢沒學過”張三”老師講授的任一門課程的學生姓名
PS: 與第8題相似

答案

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 = '張三'
    )
)

結果

+--------+
| s_name |
+--------+
| 吳蘭   |
| 王菊   |
+--------+

查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績

答案

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

結果

+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
| 04   | 李雲   | 33.33     |
| 06   | 吳蘭   | 32.5      |
+------+--------+-----------+

檢索”01”課程分數小於60，按分數降序排列的學生資訊

答案

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

結果

+------+--------+------------+-------+---------+
| s_id | s_name | s_birth    | s_sex | s_score |
+------+--------+------------+-------+---------+
| 04   | 李雲   | 1990-08-06 | 男    | 50      |
| 06   | 吳蘭   | 1992-03-01 | 女    | 31      |
+------+--------+------------+-------+---------+

按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績

答案

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

結果

+------+----------+----------+----------+-----------+
| 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      |
+------+----------+----------+----------+-----------+

查詢各科成績最高分、最低分和平均分，以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率(及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90)。
要求輸出課程號碼和選修人數，查詢結果依人數降序排列，若人數相同，依課程號碼升序排列

答案

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

結果

+--------+----------+---------+-------+--------+---------+---------+---------+---------+----------+
| 課程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    |
+--------+----------+---------+-------+--------+---------+---------+---------+---------+----------+

按各科成績進行排序，並顯示排名，分數重複時保留名次空缺

答案

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

結果

+------+------+---------+------+
| 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 按各科成績進行行排序，並顯示排名，分數重複時合併名次

答案

方法一

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

結果

+------+------+---------+------+
| 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    |
+------+------+---------+------+

查詢學生的總成績，並進行排名

答案

使用row_number()函式

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

結果

+------+--------+-------------+------+
| 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    |
+------+--------+-------------+------+

查詢不同老師所教不同課程平均分從高到低顯示

答案

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

結果

+------+--------+------+--------+-----------+
| t_id | t_name | c_id | c_name | avg_score |
+------+--------+------+--------+-----------+
| 01   | 張三   | 02   | 數學   | 72.67     |
| 03   | 王五   | 03   | 英語   | 68.5      |
| 02   | 李四   | 01   | 語文   | 64.5      |
+------+--------+------+--------+-----------+

查詢所有課程的成績第2名到第3名的學生資訊及該課程成績

答案

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名

+------+--------+------------+-------+------+---------+------+
| 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    |
+------+--------+------------+-------+------+---------+------+

統計各科成績各分數段人數：課程編號，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所佔百分比

答案

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

結果

+---------+----------+----------+---------------+---------+--------------+---------+---------------+--------+-------------+
| 課程編號 | 課程名稱 | [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        |
+---------+----------+----------+---------------+---------+--------------+---------+---------------+--------+-------------+

查詢學生平均成績及其名次
PS: 與第20題相似

答案

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

結果

+------+--------+-----------+------+
| 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    |
+------+--------+-----------+------+

查詢各科成績前三名的記錄

答案

使用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

結果

+------+------+---------+------+
| 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 2	SELECT c_id, COUNT(s_id) as num FROM Score GROUP BY c_id

結果

+------+-----+
| c_id | num |
+------+-----+
| 01   | 6   |
| 02   | 6   |
| 03   | 6   |
+------+-----+

查詢出只選修兩門課程的學生學號和姓名

答案

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

結果

+------+--------+
| s_id | s_name |
+------+--------+
| 05   | 周梅   |
| 06   | 吳蘭   |
| 07   | 鄭竹   |
+------+--------+

查詢男生、女生人數

答案

1 2	SELECT s_sex, COUNT(s_id) as num FROM Student GROUP BY s_sex

結果

+-------+-----+
| s_sex | num |
+-------+-----+
| 女    | 4   |
| 男    | 4   |
+-------+-----+

查詢名字中含有”風”字的學生資訊

答案

1 2	SELECT * FROM Student WHERE s_name LIKE '%風%'

結果

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 03   | 孫風   | 1990-05-20 | 男    |
+------+--------+------------+-------+

查詢同名同性學生名單，並統計同名人數

答案

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

查無結果

查詢1990年出生的學生資訊

答案

1 2	SELECT * FROM Student WHERE strftime('%Y', s_birth) = '1990'

結果

+------+--------+------------+-------+
| 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 | 女    |
+------+--------+------------+-------+

查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列

答案

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

結果

+------+-----------+
| c_id | avg_score |
+------+-----------+
| 02   | 72.67     |
| 03   | 68.5      |
| 01   | 64.5      |
+------+-----------+

查詢平均成績大於等於85的所有學生的學號、姓名和平均成績
PS: 與第3題相似

答案

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

結果

+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
| 01   | 趙雷   | 89.67     |
| 07   | 鄭竹   | 93.5      |
+------+--------+-----------+

查詢課程名稱為”數學”，且分數低於60的學生姓名和分數

答案

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

結果

+--------+---------+
| s_name | s_score |
+--------+---------+
| 李雲   | 30      |
+--------+---------+

查詢所有學生的課程及分數狀況（有學生沒成績，沒選課的狀況）

答案

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

結果

+------+--------+------+------+------+
| 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   |
+------+--------+------+------+------+

查詢任何一門課程成績在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

結果

+--------+--------+---------+
| s_name | c_name | s_score |
+--------+--------+---------+
| 趙雷   | 語文   | 80      |
| 趙雷   | 數學   | 90      |
| 趙雷   | 英語   | 99      |
| 錢電   | 英語   | 80      |
| 孫風   | 語文   | 80      |
| 孫風   | 數學   | 80      |
| 孫風   | 英語   | 80      |
| 周梅   | 語文   | 76      |
| 周梅   | 數學   | 87      |
| 鄭竹   | 數學   | 89      |
| 鄭竹   | 英語   | 98      |
+--------+--------+---------+

查詢不及格的課程

答案

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

結果

+--------+--------+---------+
| s_name | c_name | s_score |
+--------+--------+---------+
| 李雲   | 語文   | 50      |
| 李雲   | 數學   | 30      |
| 李雲   | 英語   | 20      |
| 吳蘭   | 語文   | 31      |
| 吳蘭   | 英語   | 34      |
+--------+--------+---------+

查詢課程編號為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

查無結果

求每門課程的學生人數
PS: 與第26題相同

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

答案

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 = '張三'
)

結果

+------+--------+------------+-------+--------------+
| s_id | s_name | s_birth    | s_sex | MAX(s_score) |
+------+--------+------------+-------+--------------+
| 01   | 趙雷   | 1990-01-01 | 男    | 90           |
+------+--------+------------+-------+--------------+

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

答案

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

結果

+------+--------+------------+-------+---------+
| s_id | s_name | s_birth    | s_sex | s_score |
+------+--------+------------+-------+---------+
| 01   | 趙雷   | 1990-01-01 | 男    | 90      |
+------+--------+------------+-------+---------+

查詢不同課程成績相同的學生的學生編號、課程編號、學生成績

答案

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

結果

+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
| 03   | 01   | 80      |
| 03   | 02   | 80      |
| 03   | 03   | 80      |
+------+------+---------+

查詢每門成績最好的前兩名
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

結果

+------+------+---------+------+
| 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    |
+------+------+---------+------+

統計每門課程的學生選修人數（超過5人的課程才統計）
PS: 與第26題相似

答案

1
2
3

SELECT c_id, COUNT(s_id) as num FROM Score
GROUP BY c_id
HAVING num > 5

結果

+------+-----+
| c_id | num |
+------+-----+
| 01   | 6   |
| 02   | 6   |
| 03   | 6   |
+------+-----+

檢索至少選修兩門課程的學生學號
PS: 與第27題相似

答案

1
2
3

SELECT s_id FROM Score
GROUP BY s_id
HAVING COUNT(c_id) >= 2

結果

+------+
| s_id |
+------+
| 01   |
| 02   |
| 03   |
| 04   |
| 05   |
| 06   |
| 07   |
+------+

查詢選修了全部課程的學生資訊

答案

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)

結果

+------+--------+------------+-------+
| 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	SELECT s_name, strftime('%Y', 'now') - strftime('%Y', s_birth) as age FROM Student

結果

+--------+------+
| s_name | age  |
+--------+------+
| 趙雷   | 33   |
| 錢電   | 33   |
| 孫風   | 33   |
| 李雲   | 33   |
| 周梅   | 32   |
| 吳蘭   | 31   |
| 鄭竹   | 34   |
| 王菊   | 33   |
+--------+------+

46.1 按照出生日期來算，當前月日 < 出生年月的月日則，年齡減一

答案

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

結果

+--------+-----+
| s_name | age |
+--------+-----+
| 趙雷   | 33  |
| 錢電   | 32  |
| 孫風   | 33  |
| 李雲   | 33  |
| 周梅   | 31  |
| 吳蘭   | 31  |
| 鄭竹   | 34  |
| 王菊   | 33  |
+--------+-----+

查詢本週過生日的學生

答案

1 2	SELECT * FROM Student WHERE strftime('%W', s_birth) = strftime('%W', 'now')

查詢下週過生日的學生
答案
使用strftime()函式會得到字串結果，做加減運算後，會變成數字，要小心兩邊的型態是否一致。
1
2
SELECT * FROM Student
WHERE strftime('%W', s_birth) + 0 = strftime('%W', 'now') + 1

查詢本月過生日的學生

答案

1 2	SELECT * FROM Student WHERE strftime('%m', s_birth) = strftime('%m', 'now')

查詢下月過生日的學生
答案
使用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

結果

+------+--------+------------+-------+
| 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 | 女    |
+------+--------+------------+-------+

參考資料

Mike的筆記

[SQL] 超經典SQL練習題

建立資料表

題目