MySQL練習題及答案

来源:https://www.cnblogs.com/changxin7/archive/2019/09/21/11561505.html
-Advertisement-
Play Games

一 題目 二 答案 1、查詢所有的課程的名稱以及對應的任課老師姓名 SELECT course.cname, teacher.tname FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid; 2、查詢學生表中男女生各有多 ...


一 題目

1、查詢所有的課程的名稱以及對應的任課老師姓名

2、查詢學生表中男女生各有多少人

3、查詢物理成績等於100的學生的姓名

4、查詢平均成績大於八十分的同學的姓名和平均成績

5、查詢所有學生的學號，姓名，選課數，總成績

6、 查詢姓李老師的個數

7、 查詢沒有報李平老師課的學生姓名

8、 查詢物理課程比生物課程高的學生的學號

9、 查詢沒有同時選修物理課程和體育課程的學生姓名

10、查詢掛科超過兩門(包括兩門)的學生姓名和班級
、查詢選修了所有課程的學生姓名

12、查詢李平老師教的課程的所有成績記錄
 
13、查詢全部學生都選修了的課程號和課程名

14、查詢每門課程被選修的次數

15、查詢之選修了一門課程的學生姓名和學號

16、查詢所有學生考出的成績並按從高到低排序（成績去重）

17、查詢平均成績大於85的學生姓名和平均成績

18、查詢生物成績不及格的學生姓名和對應生物分數

19、查詢在所有選修了李平老師課程的學生中，這些課程(李平老師的課程，不是所有課程)平均成績最高的學生姓名

20、查詢每門課程成績最好的前兩名學生姓名

21、查詢不同課程但成績相同的學號，課程號，成績

22、查詢沒學過“葉平”老師課程的學生姓名以及選修的課程名稱；

23、查詢所有選修了學號為1的同學選修過的一門或者多門課程的同學學號和姓名；

24、任課最多的老師中學生單科成績最高的學生姓名

二 答案

#1、查詢所有的課程的名稱以及對應的任課老師姓名
SELECT
    course.cname,
    teacher.tname
FROM
    course
INNER JOIN teacher ON course.teacher_id = teacher.tid;




#2、查詢學生表中男女生各有多少人
SELECT
    gender 性別,
    count(1) 人數
FROM
    student
GROUP BY
    gender;




#3、查詢物理成績等於100的學生的姓名
SELECT
    student.sname
FROM
    student
WHERE
    sid IN (
        SELECT
            student_id
        FROM
            score
        INNER JOIN course ON score.course_id = course.cid
        WHERE
            course.cname = '物理'
        AND score.num = 100
    );




#4、查詢平均成績大於八十分的同學的姓名和平均成績
SELECT
    student.sname,
    t1.avg_num
FROM
    student
INNER JOIN (
    SELECT
        student_id,
        avg(num) AS avg_num
    FROM
        score
    GROUP BY
        student_id
    HAVING
        avg(num) > 80
) AS t1 ON student.sid = t1.student_id;




#5、查詢所有學生的學號，姓名，選課數，總成績(註意：對於那些沒有選修任何課程的學生也算在內)
SELECT
    student.sid,
    student.sname,
    t1.course_num,
    t1.total_num
FROM
    student
LEFT JOIN (
    SELECT
        student_id,
        COUNT(course_id) course_num,
        sum(num) total_num
    FROM
        score
    GROUP BY
        student_id
) AS t1 ON student.sid = t1.student_id;




#6、 查詢姓李老師的個數
SELECT
    count(tid)
FROM
    teacher
WHERE
    tname LIKE '李%';




#7、 查詢沒有報李平老師課的學生姓名(找出報名李平老師課程的學生，然後取反就可以)
SELECT
    student.sname
FROM
    student
WHERE
    sid NOT IN (
        SELECT DISTINCT
            student_id
        FROM
            score
        WHERE
            course_id IN (
                SELECT
                    course.cid
                FROM
                    course
                INNER JOIN teacher ON course.teacher_id = teacher.tid
                WHERE
                    teacher.tname = '李平老師'
            )
    );




#8、 查詢物理課程比生物課程高的學生的學號(分別得到物理成績表與生物成績表，然後連表即可)
SELECT
    t1.student_id
FROM
    (
        SELECT
            student_id,
            num
        FROM
            score
        WHERE
            course_id = (
                SELECT
                    cid
                FROM
                    course
                WHERE
                    cname = '物理'
            )
    ) AS t1
INNER JOIN (
    SELECT
        student_id,
        num
    FROM
        score
    WHERE
        course_id = (
            SELECT
                cid
            FROM
                course
            WHERE
                cname = '生物'
        )
) AS t2 ON t1.student_id = t2.student_id
WHERE
    t1.num > t2.num;




#9、 查詢沒有同時選修物理課程和體育課程的學生姓名(沒有同時選修指的是選修了一門的，思路是得到物理+體育課程的學生信息表，然後基於學生分組，統計count(課程)=1)
SELECT
    student.sname
FROM
    student
WHERE
    sid IN (
        SELECT
            student_id
        FROM
            score
        WHERE
            course_id IN (
                SELECT
                    cid
                FROM
                    course
                WHERE
                    cname = '物理'
                OR cname = '體育'
            )
        GROUP BY
            student_id
        HAVING
            COUNT(course_id) = 1
    );




#10、查詢掛科超過兩門(包括兩門)的學生姓名和班級(求出<60的表，然後對學生進行分組，統計課程數目>=2)
SELECT
    student.sname,
    class.caption
FROM
    student
INNER JOIN (
    SELECT
        student_id
    FROM
        score
    WHERE
        num < 60
    GROUP BY
        student_id
    HAVING
        count(course_id) >= 2
) AS t1
INNER JOIN class ON student.sid = t1.student_id
AND student.class_id = class.cid;




#11、查詢選修了所有課程的學生姓名(先從course表統計課程的總數，然後基於score表按照student_id分組，統計課程數據等於課程總數即可)
SELECT
    student.sname
FROM
    student
WHERE
    sid IN (
        SELECT
            student_id
        FROM
            score
        GROUP BY
            student_id
        HAVING
            COUNT(course_id) = (SELECT count(cid) FROM course)
    );




#12、查詢李平老師教的課程的所有成績記錄
SELECT
    *
FROM
    score
WHERE
    course_id IN (
        SELECT
            cid
        FROM
            course
        INNER JOIN teacher ON course.teacher_id = teacher.tid
        WHERE
            teacher.tname = '李平老師'
    );




#13、查詢全部學生都選修了的課程號和課程名(取所有學生數，然後基於score表的課程分組，找出count(student_id)等於學生數即可)
SELECT
    cid,
    cname
FROM
    course
WHERE
    cid IN (
        SELECT
            course_id
        FROM
            score
        GROUP BY
            course_id
        HAVING
            COUNT(student_id) = (
                SELECT
                    COUNT(sid)
                FROM
                    student
            )
    );




#14、查詢每門課程被選修的次數
SELECT
    course_id,
    COUNT(student_id)
FROM
    score
GROUP BY
    course_id;




#15、查詢之選修了一門課程的學生姓名和學號
SELECT
    sid,
    sname
FROM
    student
WHERE
    sid IN (
        SELECT
            student_id
        FROM
            score
        GROUP BY
            student_id
        HAVING
            COUNT(course_id) = 1
    );




#16、查詢所有學生考出的成績並按從高到低排序（成績去重）
SELECT DISTINCT
    num
FROM
    score
ORDER BY
    num DESC;




#17、查詢平均成績大於85的學生姓名和平均成績
SELECT
    sname,
    t1.avg_num
FROM
    student
INNER JOIN (
    SELECT
        student_id,
        avg(num) avg_num
    FROM
        score
    GROUP BY
        student_id
    HAVING
        AVG(num) > 85
) t1 ON student.sid = t1.student_id;




#18、查詢生物成績不及格的學生姓名和對應生物分數
SELECT
    sname 姓名,
    num 生物成績
FROM
    score
LEFT JOIN course ON score.course_id = course.cid
LEFT JOIN student ON score.student_id = student.sid
WHERE
    course.cname = '生物'
AND score.num < 60;




#19、查詢在所有選修了李平老師課程的學生中，這些課程(李平老師的課程，不是所有課程)平均成績最高的學生姓名
SELECT
    sname
FROM
    student
WHERE
    sid = (
        SELECT
            student_id
        FROM
            score
        WHERE
            course_id IN (
                SELECT
                    course.cid
                FROM
                    course
                INNER JOIN teacher ON course.teacher_id = teacher.tid
                WHERE
                    teacher.tname = '李平老師'
            )
        GROUP BY
            student_id
        ORDER BY
            AVG(num) DESC
        LIMIT 1
    );




#20、查詢每門課程成績最好的前兩名學生姓名
#查看每門課程按照分數排序的信息，為下列查找正確與否提供依據
SELECT
    *
FROM
    score
ORDER BY
    course_id,
    num DESC;




#表1：求出每門課程的課程course_id,與最高分數first_num
SELECT
    course_id,
    max(num) first_num
FROM
    score
GROUP BY
    course_id;




#表2：去掉最高分，再按照課程分組，取得的最高分，就是第二高的分數second_num
SELECT
    score.course_id,
    max(num) second_num
FROM
    score
INNER JOIN (
    SELECT
        course_id,
        max(num) first_num
    FROM
        score
    GROUP BY
        course_id
) AS t ON score.course_id = t.course_id
WHERE
    score.num < t.first_num
GROUP BY
    course_id;




#將表1和表2聯合到一起，得到一張表t3，包含課程course_id與該們課程的first_num與second_num
SELECT
    t1.course_id,
    t1.first_num,
    t2.second_num
FROM
    (
        SELECT
            course_id,
            max(num) first_num
        FROM
            score
        GROUP BY
            course_id
    ) AS t1
INNER JOIN (
    SELECT
        score.course_id,
        max(num) second_num
    FROM
        score
    INNER JOIN (
        SELECT
            course_id,
            max(num) first_num
        FROM
            score
        GROUP BY
            course_id
    ) AS t ON score.course_id = t.course_id
    WHERE
        score.num < t.first_num
    GROUP BY
        course_id
) AS t2 ON t1.course_id = t2.course_id;




#查詢前兩名的學生（有可能出現併列第一或者併列第二的情況）
SELECT
    score.student_id,
    t3.course_id,
    t3.first_num,
    t3.second_num
FROM
    score
INNER JOIN (
    SELECT
        t1.course_id,
        t1.first_num,
        t2.second_num
    FROM
        (
            SELECT
                course_id,
                max(num) first_num
            FROM
                score
            GROUP BY
                course_id
        ) AS t1
    INNER JOIN (
        SELECT
            score.course_id,
            max(num) second_num
        FROM
            score
        INNER JOIN (
            SELECT
                course_id,
                max(num) first_num
            FROM
                score
            GROUP BY
                course_id
        ) AS t ON score.course_id = t.course_id
        WHERE
            score.num < t.first_num
        GROUP BY
            course_id
    ) AS t2 ON t1.course_id = t2.course_id
) AS t3 ON score.course_id = t3.course_id
WHERE
    score.num >= t3.second_num
AND score.num <= t3.first_num;




#排序後可以看的明顯點
SELECT
    score.student_id,
    t3.course_id,
    t3.first_num,
    t3.second_num
FROM
    score
INNER JOIN (
    SELECT
        t1.course_id,
        t1.first_num,
        t2.second_num
    FROM
        (
            SELECT
                course_id,
                max(num) first_num
            FROM
                score
            GROUP BY
                course_id
        ) AS t1
    INNER JOIN (
        SELECT
            score.course_id,
            max(num) second_num
        FROM
            score
        INNER JOIN (
            SELECT
                course_id,
                max(num) first_num
            FROM
                score
            GROUP BY
                course_id
        ) AS t ON score.course_id = t.course_id
        WHERE
            score.num < t.first_num
        GROUP BY
            course_id
    ) AS t2 ON t1.course_id = t2.course_id
) AS t3 ON score.course_id = t3.course_id
WHERE
    score.num >= t3.second_num
AND score.num <= t3.first_num
ORDER BY
    course_id;




#可以用以下命令驗證上述查詢的正確性
SELECT
    *
FROM
    score
ORDER BY
    course_id,
    num DESC;




-- 21、查詢不同課程但成績相同的學號，課程號，成績
-- 22、查詢沒學過“葉平”老師課程的學生姓名以及選修的課程名稱；
-- 23、查詢所有選修了學號為1的同學選修過的一門或者多門課程的同學學號和姓名；
-- 24、任課最多的老師中學生單科成績最高的學生姓名

更多練習以及參考答案：https://www.cnblogs.com/clschao/articles/9995768.html


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Part_three:Redis持久化存儲
    redis持久化存儲 是一種記憶體型資料庫，一旦伺服器進程退出，資料庫的數據就會丟失，為瞭解決這個問題， 提供了兩種持久化的方案，將記憶體中的數據保存到磁碟中，避免數據的丟失。 1.RDB持久化 redis提供了RDB持久化的功能，這個功能可以將redis在記憶體中的狀態數據保存到磁碟 觸發機制： 手動執 ...
  • SQL Server如何正確的刪除Windows認證用戶
    在SQL Server資料庫中，有時候會建立一些Windows認證的賬號（域賬號）,例如，我們公司習慣給開發人員和Support同事開通NT賬號許可權，如果有離職或負責事宜變更的話，那麼要如何正確的刪除這些Windows認證賬號呢？這篇文章就是來探討一下如何正確的刪除Windows認證賬號。如下所示：... ...
  • Part_one:Redis第一次接觸
    1.redis學習 數據預設寫入到記憶體，如果斷電，伺服器宕機，redis進程掛掉，數據會丟失。 selenium操作瀏覽器時，要註意瀏覽器資源釋放，方式記憶體泄露，崩潰 mysql是文件型資料庫，預設持久化到硬碟上 redis 是記憶體型資料庫 2.redis安裝學習 yum安裝，最簡單，配置yum源， ...
  • MYSQL之查詢篇
    2. 資料庫操作 資料庫在創建以後最常見的操作便是 2.1 查詢 為了便於學習和理解，我們預先準備了兩個表分別是 表和 表兩個表的內容和結構如下所示 表的內容: | id | class\_id | name | gender | score | | | | | | | | 1 | 1 | 小明 | ...
  • Python學習日記(三十三) Mysql資料庫篇 一
    背景 Mysql是一個關係型資料庫,由瑞典Mysql AB開發,目前屬於Oracle旗下的產品。Mysql是目前最流行的關係型資料庫管理系統之一,在WEB方面,Mysql是最好的RDBMS(Relational Database Management System)。Mysql是一種關係資料庫管理系 ...
  • 大數據相關概念和hdfs
    大數據 概述 大數據是新處理模式才能具備更多的決策力，洞察力，流程優化能力，來適應海量高增長率，多樣化的數據資產。 大數據面臨的問題 怎麼存儲海量數據(kb，mb，gb，tb，pb，eb，zb) 怎麼對數據進行降噪處理(對數據進行清洗，使得數據變廢為寶，提取有用的數據，減少不必要的數據資源空間的釋放 ...
  • MySQL設計表規範
    規範總結 所有資料庫對象名稱必須使用小寫字母並用下劃線分割 所有資料庫對象名稱禁止使用 MySQL 保留關鍵字【設計表後逐一排查】 所有表必須使用 Innodb 存儲引擎，資料庫和表的字元集統一使用 UTF8 所有表和欄位都需要添加註釋 禁止在資料庫中存儲圖片,文件等大的二進位數據 【 通常存儲於文 ...
  • MySQL基礎總結
    MySQL是Oracle公司開發、發佈和支持的最流行的開源SQL資料庫管理系統。 ...
一周排行
    -Advertisement-
    Play Games
  • GoF之工廠模式
    GoF之工廠模式 @目錄GoF之工廠模式每博一文案1. 簡單說明“23種設計模式”1.2 介紹工廠模式的三種形態1.3 簡單工廠模式（靜態工廠模式）1.3.1 簡單工廠模式的優缺點:1.4 工廠方法模式1.4.1 工廠方法模式的優缺點:1.5 抽象工廠模式1.6 抽象工廠模式的優缺點：2. 總結:3 ...
  • dotnet C# 簡單的追加文件夾到 ZipArchive 壓縮文件的方法
    新改進提供的Taurus Rpc 功能，可以簡化微服務間的調用，同時可以不用再手動輸出模塊名稱，或調用路徑，包括負載均衡，這一切，由框架實現並提供了。新的Taurus Rpc 功能，將使得服務間的調用，更加輕鬆、簡約、高效。 ...
  • Elasticsearch 系列（六）- ES數據同步和ES集群
    本章將和大家分享ES的數據同步方案和ES集群相關知識。廢話不多說，下麵我們直接進入主題。 一、ES數據同步 1、數據同步問題 Elasticsearch中的酒店數據來自於mysql資料庫，因此mysql數據發生改變時，Elasticsearch也必須跟著改變，這個就是Elasticsearch與my ...
  • 自動化測試數據生成：Asp.Net Core單元測試利器AutoFixture詳解
    引言 在我們之前的文章中介紹過使用Bogus生成模擬測試數據，今天來講解一下功能更加強大自動生成測試數據的工具的庫"AutoFixture"。 什麼是AutoFixture? AutoFixture 是一個針對 .NET 的開源庫，旨在最大程度地減少單元測試中的“安排（Arrange）”階段，以提高 ...
  • .NET Emit 入門教程：第七部分：實戰項目1：將 DbDataReader 轉實體
    經過前面幾個部分學習，相信學過的同學已經能夠掌握 .NET Emit 這種中間語言，並能使得它來編寫一些應用，以提高程式的性能。隨著 IL 指令篇的結束，本系列也已經接近尾聲，在這接近結束的最後，會提供幾個可供直接使用的示例，以供大伙分析或使用在項目中。 ...
  • Windows下綁定線程到指定的CPU核心
    當從不同來源導入Excel數據時，可能存在重覆的記錄。為了確保數據的準確性，通常需要刪除這些重覆的行。手動查找並刪除可能會非常耗費時間，而通過編程腳本則可以實現在短時間內處理大量數據。本文將提供一個使用C# 快速查找並刪除Excel重覆項的免費解決方案。 以下是實現步驟： 1. 首先安裝免費.NET ...
  • mORMot 1.18 第07章 簡單的讀寫操作
    C++ 異常處理 C++ 異常處理機制允許程式在運行時處理錯誤或意外情況。它提供了捕獲和處理錯誤的一種結構化方式，使程式更加健壯和可靠。 異常處理的基本概念： 異常: 程式在運行時發生的錯誤或意外情況。 拋出異常: 使用 throw 關鍵字將異常傳遞給調用堆棧。 捕獲異常: 使用 try-catch ...
  • mORMot 1.18 第06章 概念
    優秀且經驗豐富的Java開發人員的特征之一是對API的廣泛瞭解，包括JDK和第三方庫。 我花了很多時間來學習API，尤其是在閱讀了Effective Java 3rd Edition之後 ，Joshua Bloch建議在Java 3rd Edition中使用現有的API進行開發，而不是為常見的東西編 ...
  • php 開發規範
    框架 · 使用laravel框架，原因：tp的框架路由和orm沒有laravel好用 · 使用強制路由，方便介面多時，分多版本，分文件夾等操作 介面 · 介面開發註意欄位類型，欄位是int，查詢成功失敗都要返回int（對接java等強類型語言方便） · 查詢介面用GET、其他用POST 代碼 · 所 ...
  • 2024 年 4 月 28 日 周日 晴 熱（1253 字）
    正文 下午找企業的人去鎮上做貸後。 車上聽同事跟那個司機對罵，火星子都快出來了。司機跟那同事更熟一些，連我在內一共就三個人，同事那一手指桑罵槐給我都聽愣了。司機也是老社會人了，馬上聽出來了，為那個無辜的企業經辦人辯護，實際上是為自己辯護。 “這個事情你不能怪企業。”“但他們總不能讓銀行的人全權負責， ...
所有分類