50道SQL面試題

来源:https://www.cnblogs.com/MessiXiaoMo3334/archive/2020/06/27/13198935.html
-Advertisement-
Play Games

本博客SQL腳本地址：gitee 準備工作 已知有如下4張表： 學生表：student(學號,學生姓名,出生年月,性別) 成績表：score(學號,課程號,成績) 課程表：course(課程號,課程名稱,教師號) 教師表：teacher(教師號,教師姓名) 一、創建資料庫和表 為了演示題目的運行過程 ...


本博客SQL腳本地址：gitee

準備工作

已知有如下4張表：

學生表：student(學號,學生姓名,出生年月,性別)

成績表：score(學號,課程號,成績)

課程表：course(課程號,課程名稱,教師號)

教師表：teacher(教師號,教師姓名)

一、創建資料庫和表

為了演示題目的運行過程，我們先按下麵語句在客戶端HeidiSQL中創建資料庫和表。

1、創建表

1.1、創建學生表（student）

1.2、創建成績表（score）

創建"成績表“。“課程表的“學號”和“課程號”一起設置為主鍵約束（聯合主鍵），“成績”這一列設置為數值類型（float，浮點數值）

1.3、創建課程表（course）

課程表的“課程號”設置為主鍵約束

1.4、教師表（teacher）

教師表的“教師號”列設置為主鍵約束，

教師姓名這一列設置約束為“null”（紅框的地方不勾選），表示這一列允許包含空值（null）

2、向表中添加數據

2.1、學生表

insert into student(學號,姓名,出生日期,性別) 
values('0001' , '猴子' , '1989-01-01' , '男');

insert into student(學號,姓名,出生日期,性別) 
values('0002' , '猴子' , '1990-12-21' , '女');

insert into student(學號,姓名,出生日期,性別) 
values('0003' , '馬雲' , '1991-12-21' , '男');

insert into student(學號,姓名,出生日期,性別) 
values('0004' , '王思聰' , '1990-05-20' , '男');

2.2、成績表（score）

insert into score(學號,課程號,成績) 
values('0001' , '0001' , 80);

insert into score(學號,課程號,成績) 
values('0001' , '0002' , 90);

insert into score(學號,課程號,成績) 
values('0001' , '0003' , 99);

insert into score(學號,課程號,成績) 
values('0002' , '0002' , 60);

insert into score(學號,課程號,成績) 
values('0002' , '0003' , 80);

insert into score(學號,課程號,成績) 
values('0003' , '0001' , 80);

insert into score(學號,課程號,成績) 
values('0003' , '0002' , 80);

insert into score(學號,課程號,成績) 
values('0003' , '0003' , 80);

2.3、課程表

insert into course(課程號,課程名稱,教師號)
values('0001' , '語文' , '0002');

insert into course(課程號,課程名稱,教師號)
values('0002' , '數學' , '0001');

insert into course(課程號,課程名稱,教師號)
values('0003' , '英語' , '0003');

2.4、教師表

insert into teacher(教師號,教師姓名) 
values('0001' , '孟扎扎');

insert into teacher(教師號,教師姓名) 
values('0002' , '馬化騰');

-- 這裡的教師姓名是空值（null）
insert into teacher(教師號,教師姓名) 
values('0003' , null);

-- 這裡的教師姓名是空字元串（''）
insert into teacher(教師號,教師姓名) 
values('0004' , '');

50道面試題

為了方便學習，將50道面試題進行了分類

一、簡單查詢

查詢姓“猴”的學生名單

1.查詢姓“孟”老師的個數

select count(教師號)
from teacher
where 教師姓名 like '孟%';

二、彙總分析

2、查詢課程編號為“0002”的總成績

select sum(成績)
from score
where 課程號 = '0002';

3、查詢選了課程的學生人數

select count(distinct 學號) as 學生人數 
from score;

4、查詢各科成績最高和最低的分， 以如下的形式顯示：課程號，最高分，最低分

select 課程號,max(成績) as 最高分,min(成績) as 最低分
from score
group by 課程號;

5、查詢每門課程被選修的學生數

select 課程號, count(學號) as 人數
from score
group by 課程號;

6、查詢男生、女生人數

select 性別,count(*) as 人數
from student
group by 性別;

7、查詢平均成績大於60分學生的學號和平均成績

select 學號, avg(成績) as 平均成績
from score
group by 學號
having avg(成績)>60;

8、查詢至少選修兩門課程的學生學號

select 學號, count(課程號) as 選修課程數目
from score
group by 學號
having count(課程號)>=2;

9、查詢同名同姓學生名單並統計同名人數

select 姓名,count(*) as 人數
from student
group by 姓名
having count(*)>=2;

10、查詢不及格的課程並按課程號從大到小排列

select 課程號
from score 
where 成績<60
order by 課程號 desc;

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

select 課程號, avg(成績) as 平均成績
from score
group by 課程號
order by 平均成績 asc,課程號 desc;

12、檢索課程編號為“0004”且分數小於60的學生學號，結果按按分數降序排列

select 學號
from score
where 課程號='0004' and 成績 <60
order by 成績 desc;

13、統計每門課程的學生選修人數(超過2人的課程才統計)

要求輸出課程號和選修人數，查詢結果按人數降序排序，若人數相同，按課程號升序排序

select 課程號, count(學號) as '選修人數'
from score
group by 課程號
having count(學號)>2
order by count(學號) desc,課程號 asc;

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

select 學號, avg(成績) as 平均成績
from score
where 成績 <60
group by 學號
having count(課程號)>2;

.

15、查詢學生的總成績併進行排名

select 學號 ,sum(成績) as 總成績
from score 
group by 學號
order by sum(成績);

16、查詢平均成績大於60分的學生的學號和平均成績

select 學號 ,avg(成績) 
from score 
group by 學號  
having avg(成績 )>60;

三、複雜查詢

17、查詢所有課程成績小於60分學生的學號、姓名

select student.學號,姓名
from student,score
where 成績<60 and student.`學號`=score.`學號`;

18、查詢沒有學全所有課的學生的學號、姓名

select score.學號,姓名
from student,score
group by score.學號
having count(課程號)<(select count(課程號) from course);

19、查詢出只選修了兩門課程的全部學生的學號和姓名

select score.學號,姓名
from student,score
where student.`學號`=score.`學號`
group by score.`學號`
having count(課程號)=2;

20、1990年出生的學生名單

select 學號,姓名 
from student 
where year(出生日期)=1990;

21、查詢各學生的年齡（精確到月份）

select 學號,timestampdiff(month ,出生日期 ,now())/12 as 年齡
from student;

22、查詢本月過生日的學生

select * 
from student 
where month (出生日期 )=month(now());

四、多表查詢

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

selecta.學號,a.姓名,count(b.課程號) as 選課數,sum(b.成績) as 總成績
from student as a left join score as b
on a.學號 = b.學號
group by a.學號;

24、查詢平均成績大於85的所有學生的學號、姓名和平均成績

select a.學號,a.姓名, avg(b.成績) as 平均成績
from student as a left join score as b 
on a.學號 = b.學號
group by a.學號
having avg(b.成績)>85;

25、查詢學生的選課情況：學號，姓名，課程號，課程名稱

select a.學號, a.姓名, c.課程號,c.課程名稱
from student a 
inner join score b on a.學號=b.學號 
inner join course c on b.課程號=c.課程號;

26、查詢出每門課程的及格人數和不及格人數

select 課程號,
sum(case when 成績>=60 then 1 
	 else 0 
    end) as 及格人數,
sum(case when 成績 <  60 then 1 
	 else 0 
    end) as 不及格人數
from score
group by 課程號;

27、使用分段[100-85],[85-70],[70-60],[<60]來統計各科成績，分別統計：各分數段人數，課程號和課程名稱

select a.課程號,b.課程名稱,
sum(case when 成績 between 85 and 100 
	 then 1 else 0 end) as '[100-85]',
sum(case when 成績 >=70 and 成績<85 
	 then 1 else 0 end) as '[85-70]',
sum(case when 成績>=60 and 成績<70  
	 then 1 else 0 end) as '[70-60]',
sum(case when 成績<60 then 1 else 0 end) as '[<60]'
from score as a right join course as b 
on a.課程號=b.課程號
group by a.課程號,b.課程名稱;

28、查詢課程編號為0003且課程成績在80分以上的學生的學號和姓名

select a.學號,a.姓名
from student  as a inner join score as b on a.學號=b.學號
where b.課程號='0003' and b.成績>80;

.

29、檢索"0001"課程分數小於60，按分數降序排列的學生信息

思路如圖：

.

select a.*,b.成績 
from student as a 
inner join score as b 
on a.學號 =b.學號 
where b.成績 <60 and b.課程號 =01
order by b.成績 desc;

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

【知識點】分組+條件+排序+多表連接，思路如圖

.

select a.教師號,a.教師姓名,avg(c.成績) 
from  teacher as a 
inner join course as b 
on a.教師號= b.教師號
inner join score  c on b.課程號= c.課程號
group by a.教師姓名
order by avg(c.成績) desc;

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

【知識點】多表連接，思路如圖

select a.姓名,b.成績 
from student as a 
inner join score as b 
on a.學號 =b.學號 
inner join course c on b.課程號 =c.課程號 
where b.成績  <60 and c.課程名稱 ='數學';

32、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數（與上題類似）

select a.姓名,c.課程名稱,b.成績
from student as a
Inner join score as b
on a.學號=b.學號
Inner join course c on b.課程號=c.課程號
where b.成績>70;

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

【知識點】分組+條件+多表連接

翻譯成大白話:計算每個學號不及格分數個數，篩選出大於2個的學號並找出姓名，平均成績，思路如圖：

img

select b.姓名,avg(a.成績),a.學號
from score as a
Inner join student as b
on a.學號=b.學號
where a.成績<60
group by a.學號
having count(a.學號)>=2;

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

select distinct a.學號,a.成績,a.課程號
from score as a
inner join score as b
on a.學號 = b.學號
where a.成績 = b.成績 and a.課程號 != b.課程號;

35、查詢課程編號為“0001”的課程比“0002”的課程成績高的所有學生的學號

select a.學號  
from 
(select 學號 ,成績 from score where 課程號=01) as a
inner join 
(select 學號 ,成績 from score where 課程號=02) as b
on a.學號 =b.學號 
inner join student c on c.學號 =a.學號 
where a.成績 >b.成績 ;

36、查詢學過編號為“0001”的課程並且也學過編號為“0002”的課程的學生的學號、姓名

select a.學號  
from 
(select 學號 ,成績 from score where 課程號=01) as a
inner join 
(select 學號 ,成績 from score where 課程號=02) as b
on a.學號 =b.學號 
inner join student c on c.學號 =a.學號 
where a.成績 >b.成績 ;

37、查詢學過“孟扎扎”老師所教的所有課的同學的學號、姓名

select s.學號,s.姓名,a.學號,b.課程號,c.教師號,c.教師姓名
from student as s
inner join score as a
on s.`學號`=a.學號
inner join course b on a.課程號=b.課程號
inner join teacher c on b.教師號= c.教師號
where c.教師姓名 ='孟扎扎';

38、查詢沒學過"孟扎扎"老師講授的任一門課程的學生姓名（與上題類似，"沒學過"用not in來實現)

select 姓名,學號
from student
WHERE 學號 NOT IN(
select a.學號
from student as a
inner join score AS b
on a.學號 =b.學號
INNER	JOIN course AS c ON b.課程號=c.課程號
INNER	JOIN teacher AS d ON c.教師號=d.教師號
where d.教師姓名 ='孟扎扎');

39、查詢沒學過“孟扎扎”老師課的學生的學號、姓名（與上題類似）

select 學號, 姓名 
from student
where 學號 not  in
(select 學號 from score where 課程號=
(select 課程號 from course  where 教師號 = 
(select 教師號 from teacher where 教師姓名 ='孟扎扎')
)
);

40、查詢選修“孟扎扎”老師所授課程的學生中成績最高的學生姓名及其成績（與上題類似,用成績排名，用 limit 1得出最高一個）

select a.姓名,b.成績
From student as a
Inner join score as b on a.學號=b.學號
Inner join course as c on b.課程號=c.課程號
Inner join teacher as d on c.教師號=d.教師號
where d.教師姓名='孟扎扎'
order by b.成績 desc limit 1;

41、查詢至少有一門課與學號為“0001”的學生所學課程相同的學生的學號和姓名

select 學號,姓名
from student 
where 學號 in
(select distinct(學號) from score where 課程號 in
(select 課程號 from score where 學號=0001))
and 學號!=0001;

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

【知識點】多表連接 新建欄位 ，思路如圖

select a.學號,avg(a.成績),
max(case when b.課程名稱='數學' then a.成績 else null end) AS 數學,
max(case when b.課程名稱='語文' then a.成績 else null end) AS 語文,
max(case when b.課程名稱='英語' then a.成績 else null end) AS 英語
from score as a
Inner join course as b
on a.課程號=b.課程號
group by a.學號;

五、SQL高級功能：視窗函數

43、查詢學生平均成績及其名次

【知識點】視窗函數排名，思路如圖

select 學號,avg(成績) as 平均成績,row_number() over( order by avg(成績) DESC) AS 名次
from score
group by 學號;

44、按各科成績進行排序，並顯示排名

select 課程號,row_number() over(partition by 課程號 order BY 成績) as 排名
from score;

45、查詢每門功成績最好的前兩名學生姓名

【知識點】視窗函數排名+多表連接+條件

preview

select a.課程號,b.姓名,a.成績,a.ranking from (
select 課程號,學號,成績,row_number() over(partition by 課程號 order by 成績 desc) as ranking
from  score) as a 
inner join student b on a.學號=b.學號 
where a.ranking<3;

45、查詢所有課程的成績第2名到第3名的學生信息及該課程成績（與上一題相似）

select b.姓名,a.課程號,a.成績 
from (
select 課程號,學號,成績,row_number() over( partition by 課程號 order by 成績 desc) as ranking
from  score ) as a 
inner join student as b 
on a.學號 =b.學號 
where a.ranking in(2,3);

46、查詢各科成績前三名的記錄（不考慮成績併列情況）（與上一題相似）

select b.姓名,a.課程號,a.成績 
from(
select 課程號,學號,成績,
row_number() over( partition by 課程號 order by 成績 desc) as 'ranking'
from  score) as a 
inner join student as b 
on a.學號=b.學號 
where a.ranking<4;

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

-Advertisement-
Play Games
更多相關文章
  • C#基本類型的取值範圍與 .NET框架類型的對應
    C#類型名稱 說明 取值 .NET框架類型 預設值 sbyte 8位有符號整數 -128~127 System.SByte 0 byte 8位無符號整數 0~255 System.Byte 0 short 16位有符號整數 -32768~32767 System.Int16 0 ushort 16位 ...
  • 關於C#中靜態的一點認識
    1.靜態直觀的特點 靜態成員最顯著的一個特點就是它的作用域是全局的。只要在調用處引入了對應的命名空間，那麼我們可以在代碼任何地方都可以直接使用。凡是具有全局特征的東西我們就可以考慮使用靜態。在實際的開發中，靜態欄位我們常用實現數據的共用，修飾為靜態的方法當做常用的工具方法來使用。 2.命名上的思考 ...
  • Java筆試面試總結—try、catch、finally語句中有return 的各類情況
    前言 之前在刷筆試題和麵試的時候經常會遇到或者被問到 try-catch-finally 語法塊的執行順序等問題，今天就抽空整理了一下這個知識點，然後記錄下來。 正文 本篇文章主要是通過舉例的方式來闡述各種情況，我這裡根據 try-catch-finally 語法塊分為兩種大情況討論：try-cat ...
  • Mariadb之事務隔離級別
    所謂原子性，就是事務中執行的語句要麼全部執行，要麼全部不執行，如果事務在中途發生錯誤，那麼前面執行過的語句將會回滾到事務前；一致性指的是在執行事務之前和事務執行完成後的資料庫狀態是完整的；也就是說我們執行的語句都按照我們預想的結果執行了；隔離性指資料庫允許多個併發事務同時對其數據進行讀寫和修改的能... ...
  • oracle異常處理
    語句執行過程中，由於各種原因使得語句不能正常執行，可能會造成更大錯誤或整個系統的崩潰，所以PS/SQL提供了異常（exception）著一處理的方法來防止此類情況的發生。在代碼運行的過程中無論何時發生錯誤，PL/SQL都能控製程序自動地轉向執行異常部分。 1.預定義異常 預定義異常是由於系統產生的。 ...
  • Oracel中Decode函數的用法
    第一種形式： decode(條件,值1,返回值1,值2,返回值2,…值n,返回值n,預設值) ​ 實現數據的彙總： 源數據： ​ 彙總後的數據：使用decode函數處理數據後對dname欄位進行彙總。 ​ 第二種形式： decode(欄位或欄位的運算，值1，值2，值3)；當欄位或欄位的運算的值等於值 ...
  • Oracel中coalesce函數的用法
    1、coalesce函數的用法 1.1 取出第一個不為空的列的數據。 ​ 1.2 coalesce函數裡面的數據類型，必須全部都跟第一列的數據類型一致。 ​ 原因為第一個參數為數值，第二個參數為字元串；可通過轉換數據類型來使用，如下圖： ​ ...
  • Oracle中Ipad和Rpad函數的用法
    函數參數：lpad( string, padded_length, [ pad_string ] ) 參數說明： string：源字元串； padded_length: 即最終結果返回的字元串的長度；如果最終返回的字元串的長度比源字元串的小，那麼此函數實際上對源串進行截取處理，與substr(str ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類