MySQL講義第 47 講——select 查詢之查詢練習（五）

睿思達DBA發表於2020-11-08

MySQL講義第 47 講——select 查詢之查詢練習（五）

以下查詢使用到四張表，分別是：student、teacher、course 和 electives，表結構如下：

CREATE TABLE student(
    s_id char(5) primary key,
    s_name char(20),
    birth datetime,
    phone char(20),
    addr varchar(100)
);

INSERT INTO student
VALUES('S2011','張曉剛','1999-12-3','13163735775','信陽市'),
('S2012','劉小青','1999-10-11','13603732255','新鄉市'),
('S2013','曹夢德','1998-2-13','13853735522','鄭州市'),
('S2014','劉豔','1998-6-24','13623735335','鄭州市'),
('S2015','劉巖','1999-7-6','13813735225','信陽市'),
('S2016','劉若非','2000-8-31','13683735533','開封市'),
('S2021','董雯花','2000-7-30','13533735564','開封市'),
('S2022','周華建','1999-5-25','13243735578','鄭州市'),
('S2023','川普','1999-6-21','13343735588','新鄉市'),
('S2024','奧巴馬','2000-10-17','13843735885','信陽市'),
('S2025','周健華','2000-8-22','13788736655','開封市'),
('S2026','張學有','1998-7-6','13743735566','鄭州市'),
('S2031','李明博','1999-10-26','13643732222','鄭州市'),
('S2032','達芬奇','1999-12-31','13043731234','鄭州市'),
('S2033','李明博','1999-10-26','13643732222','鄭州市'),
('S2034','周健華','1999-12-31','13043731234','鄭州市'),
('S2035','張學有','1999-10-26','13643732222','鄭州市'),
('S2036','周健華','1999-12-31','13043731234','鄭州市');

CREATE TABLE teacher(
    t_id char(5) primary key,
    t_name char(20),
    job_title char(20),
    phone char(20)
);

INSERT INTO teacher
VALUES('T8001','歐陽修','教授','13703735666'),
('T8002','華羅庚','教授','13703735888'),
('T8003','鍾南山','教授','13703735675'),
('T8004','錢學森','教授','13703735638'),
('T8005','李白','副教授','13703735828'),
('T8006','孔子','教授','13703735457'),
('T8007','王安石','副教授','13703735369');

CREATE TABLE course(
    c_id char(4) primary key,
    c_name char(20),
    t_id char(5)
);

INSERT INTO course
VALUES('C101','古代文學','T8001'),
('C102','高等數學','T8002'),
('C103','線性代數','T8002'),
('C104','臨床醫學','T8003'),
('C105','傳染病學','T8003'),
('C106','大學物理','T8004'),
('C107','詩歌欣賞','T8005'),
('C108','教育學','T8006'),
('C109','刑事訴訟法','T8007'),
('C110','經濟法','T8007');

CREATE TABLE score(
    s_id char(5),
    c_id char(4),
    score int,
    primary key(s_id, c_id)
);

INSERT INTO score
VALUES('S2011','C102',84),('S2011','C105',90),('S2011','C106',79),('S2011','C109',65),
('S2012','C101',67),('S2012','C102',52),('S2012','C103',55),('S2012','C104',86),
('S2012','C105',87),('S2012','C106',64),('S2012','C107',62),
('S2012','C108',73),('S2012','C109',78),('S2012','C110',89),
('S2013','C102',97),('S2013','C103',68),('S2013','C104',66),('S2013','C105',68),
('S2014','C102',90),('S2014','C103',85),('S2014','C104',77),('S2014','C105',96),
('S2015','C101',69),('S2015','C102',66),('S2015','C103',88),('S2015','C104',69),
('S2015','C105',66),('S2015','C106',88),('S2015','C107',69),
('S2015','C108',66),('S2015','C109',88),('S2015','C110',69),
('S2016','C101',65),('S2016','C102',69),('S2016','C107',82),('S2016','C108',56),
('S2021','C102',72),('S2021','C103',90),('S2021','C104',90),('S2021','C105',57),
('S2022','C102',88),('S2022','C103',93),('S2022','C109',47),('S2022','C110',62),
('S2023','C102',68),('S2023','C103',86),('S2023','C109',56),('S2023','C110',91),
('S2024','C102',87),('S2024','C103',97),('S2024','C109',80),('S2024','C110',81),
('S2025','C102',61),('S2025','C105',62),('S2025','C106',87),('S2025','C109',82),
('S2026','C102',59),('S2026','C105',48),('S2026','C106',90),('S2026','C109',73);

基於以上資料，完成如下查詢：

34、查詢每門課程選修的學生人數

SQL 語句如下：

SELECT
    c.c_id, 
    c.c_name,
    COUNT(*) AS cnt_stu
FROM
    course c JOIN score sc
    ON c.c_id = sc.c_id
GROUP BY
    c.c_id
;
+------+-----------------+---------+
| c_id | c_name          | cnt_stu |
+------+-----------------+---------+
| C101 | 古代文學        |       3 |
| C102 | 高等數學        |      12 |
| C103 | 線性代數        |       8 |
| C104 | 臨床醫學        |       5 |
| C105 | 傳染病學        |       8 |
| C106 | 大學物理        |       5 |
| C107 | 詩歌欣賞        |       3 |
| C108 | 教育學          |       3 |
| C109 | 刑事訴訟法      |       8 |
| C110 | 經濟法          |       5 |
+------+-----------------+---------+
10 rows in set (0.04 sec)

35、查詢選修了 4 門課程的學生的學號、姓名

SQL 語句如下：

SELECT
    s.s_id,
    s.s_name
FROM
    student s JOIN score sc
    ON s.s_id = sc.s_id
GROUP BY
    s.s_id
HAVING
    COUNT(*) = 4
;
+-------+-----------+
| s_id  | s_name    |
+-------+-----------+
| S2011 | 張曉剛    |
| S2013 | 曹夢德    |
| S2014 | 劉豔      |
| S2016 | 劉若非    |
| S2021 | 董雯花    |
| S2022 | 周華建    |
| S2023 | 川普    |
| S2024 | 奧巴馬    |
| S2025 | 周健華    |
| S2026 | 張學有    |
+-------+-----------+
10 rows in set (0.00 sec)

36、統計不同地區的學生人數

SQL 語句如下：

SELECT
    addr,
    COUNT(*) AS cnt_stu
FROM 
    student
GROUP BY
    addr
;
+-----------+---------+
| addr      | cnt_stu |
+-----------+---------+
| 信陽市    |       3 |
| 開封市    |       3 |
| 新鄉市    |       2 |
| 鄭州市    |      10 |
+-----------+---------+
4 rows in set (0.01 sec)

37、查詢姓【劉】的學生資訊

SQL 語句如下：

SELECT
    s_id,
    s_name,
    phone,
    addr
FROM
    student
WHERE
    s_name LIKE '劉%'
;
+-------+-----------+-------------+-----------+
| s_id  | s_name    | phone       | addr      |
+-------+-----------+-------------+-----------+
| S2012 | 劉小青    | 13603732255 | 新鄉市    |
| S2014 | 劉豔      | 13623735335 | 鄭州市    |
| S2015 | 劉巖      | 13813735225 | 信陽市    |
| S2016 | 劉若非    | 13683735533 | 開封市    |
+-------+-----------+-------------+-----------+
4 rows in set (0.05 sec)

38、查詢同名的學生資訊，並統計同名人數

SQL 語句如下：

SELECT
    s_name,
    COUNT(*) AS cnt
FROM
    student
GROUP BY
    s_name
HAVING
    cnt > 1
;
+-----------+-----+
| s_name    | cnt |
+-----------+-----+
| 周健華    |   3 |
| 張學有    |   2 |
| 李明博    |   2 |
+-----------+-----+
3 rows in set (0.01 sec)

SELECT
    s1.s_id,
    s1.s_name,
    s1.birth,
    s1.phone,
    s1.addr,
    s2.cnt
FROM
     student s1 JOIN (SELECT s_name, COUNT(*) AS cnt
     FROM student
     GROUP BY s_name
     HAVING cnt > 1) s2
     ON s1.s_name = s2.s_name
ORDER BY
     s_name
;
+-------+-----------+---------------------+-------------+-----------+-----+
| s_id  | s_name    | birth               | phone       | addr      | cnt |
+-------+-----------+---------------------+-------------+-----------+-----+
| S2036 | 周健華    | 1999-12-31 00:00:00 | 13043731234 | 鄭州市    |   3 |
| S2034 | 周健華    | 1999-12-31 00:00:00 | 13043731234 | 鄭州市    |   3 |
| S2025 | 周健華    | 2000-08-22 00:00:00 | 13788736655 | 開封市    |   3 |
| S2026 | 張學有    | 1998-07-06 00:00:00 | 13743735566 | 鄭州市    |   2 |
| S2035 | 張學有    | 1999-10-26 00:00:00 | 13643732222 | 鄭州市    |   2 |
| S2031 | 李明博    | 1999-10-26 00:00:00 | 13643732222 | 鄭州市    |   2 |
| S2033 | 李明博    | 1999-10-26 00:00:00 | 13643732222 | 鄭州市    |   2 |
+-------+-----------+---------------------+-------------+-----------+-----+
7 rows in set (0.00 sec)

39、查詢 1998 年出生的學生資訊

SQL 語句如下：

SELECT
    s_id,
    s_name,
    birth,
    phone,
    addr
FROM
    student
WHERE
    birth BETWEEN '1998-1-1' AND '1998-12-31'
;
+-------+-----------+---------------------+-------------+-----------+
| s_id  | s_name    | birth               | phone       | addr      |
+-------+-----------+---------------------+-------------+-----------+
| S2013 | 曹夢德    | 1998-02-13 00:00:00 | 13853735522 | 鄭州市    |
| S2014 | 劉豔      | 1998-06-24 00:00:00 | 13623735335 | 鄭州市    |
| S2026 | 張學有    | 1998-07-06 00:00:00 | 13743735566 | 鄭州市    |
+-------+-----------+---------------------+-------------+-----------+
3 rows in set (0.01 sec)

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

SQL 語句如下：

SELECT
    c.c_id,
    c.c_name,
    AVG(score) AS avg_score
FROM
    course c JOIN score sc
    ON c.c_id = sc.c_id
GROUP BY
    c.c_id
ORDER BY
    avg_score DESC, c.c_id
;
+------+-----------------+-----------+
| c_id | c_name          | avg_score |
+------+-----------------+-----------+
| C103 | 線性代數        |   82.7500 |
| C106 | 大學物理        |   81.6000 |
| C110 | 經濟法          |   78.4000 |
| C104 | 臨床醫學        |   77.6000 |
| C102 | 高等數學        |   74.4167 |
| C105 | 傳染病學        |   71.7500 |
| C109 | 刑事訴訟法      |   71.1250 |
| C107 | 詩歌欣賞        |   71.0000 |
| C101 | 古代文學        |   67.0000 |
| C108 | 教育學          |   65.0000 |
+------+-----------------+-----------+
10 rows in set (0.03 sec)

41、查詢平均成績 80 分以上（包含 80）的所有學生的學號、姓名和平均成績

SQL 語句如下：

SELECT
    s.s_id,
    s.s_name,
    AVG(score) AS avg_score
FROM
    student s JOIN score sc
    ON s.s_id = sc.s_id
GROUP BY
    s.s_id
HAVING
    avg_score >= 80
;
+-------+-----------+-----------+
| s_id  | s_name    | avg_score |
+-------+-----------+-----------+
| S2014 | 劉豔      |   87.0000 |
| S2024 | 奧巴馬    |   86.2500 |
+-------+-----------+-----------+
2 rows in set (0.00 sec)

MySQL講義第27講——select 查詢之自連線查詢
2020-10-19
MySql
Mysql第六講 select查詢基礎篇
2020-09-17
MySql
Mysql查詢練習
2020-11-14
MySql
oracle心得3--多表查詢@分組查詢@子查詢講解與案例分析@經典練習題
2013-04-06
Oracle
MYSQL練習1: DQL查詢練習
2024-03-09
MySql
select查詢之五：分析函式在查詢的運用
2016-10-08
函式
select查詢之四：連線查詢
2016-10-08
PostgreSQL技術大講堂 - 第33講：並行查詢管理
2023-11-09
SQL並行
Mysql系列第七講玩轉select條件查詢，避免採坑
2020-09-21
MySql
入門MySQL——查詢語法練習
2020-06-23
MySql
mysql三表關聯查詢練習
2019-03-20
MySql
select查詢之三：子查詢
2016-10-08
select查詢之一：普通查詢
2016-10-08
MySQL入門系列：查詢簡介（五）之子查詢
2019-03-13
MySql
MySQL之連線查詢和子查詢
2021-09-04
MySql
MYSQL學習筆記25: 多表查詢(子查詢)[標量子查詢,列子查詢]
2024-03-10
MySql筆記
MySQL講義第8講——資料更新之 DELETE
2020-09-24
MySqldelete
Mysql優化_慢查詢開啟說明及Mysql慢查詢分析工具mysqldumpslow用法講解
2018-01-20
MySql優化
【閃回特性之閃回查詢】使用閃回查詢(select as of)
2015-04-15
多表查詢經典練習
2012-07-02
select子查詢
2011-05-19
查詢之折半查詢
2014-12-09
MYSQL學習筆記26: 多表查詢|子查詢
2024-03-14
MySql筆記
MySQL 學習之索引篇和查詢
2020-10-27
MySql索引
MySQL之連線查詢
2017-05-11
MySql
Mysql之查詢語句
2024-03-09
MySql
mysql-分組查詢-子查詢-連線查詢-組合查詢
2020-12-22
MySql
sql查詢入門練習題
2018-11-06
SQL
Hive -------- hive常見查詢練習
2018-11-13
Hive
Mysql系列第九講分組查詢詳解（group by & having）
2020-09-30
MySql
閃回查詢（基礎例項講解）
2013-08-24
MySQL查詢
2024-04-14
MySql
【SQL查詢】集合查詢之INTERSECT
2016-11-13
SQL
MySQL - 資料查詢 - 簡單查詢
2020-12-27
MySql
MySQL聯結查詢和子查詢
2018-02-24
MySql
[Mysql 查詢語句]——查詢欄位
2017-03-18
MySql
oracle-hr表查詢命令練習（超完整的select命令大全）
2013-04-05
Oracle
select查詢之六：別名與拼接
2016-10-09

MySQL講義第 47 講——select 查詢之查詢練習（五）

MySQL講義第 47 講——select 查詢之查詢練習（五）

34、查詢每門課程選修的學生人數

35、查詢選修了 4 門課程的學生的學號、姓名

36、統計不同地區的學生人數

37、查詢姓【劉】的學生資訊

38、查詢同名的學生資訊，並統計同名人數

39、查詢 1998 年出生的學生資訊

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

41、查詢平均成績 80 分以上（包含 80）的所有學生的學號、姓名和平均成績

相關文章