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
  • Dapr Outbox 執行流程
    Dapr Outbox 是1.12中的功能。 本文只介紹Dapr Outbox 執行流程，Dapr Outbox基本用法請閱讀官方文檔 。本文中appID=order-processor，topic=orders 本文前提知識：熟悉Dapr狀態管理、Dapr發佈訂閱和Outbox 模式。 Outbo ...
  • Advanced .Net Debugging 7：托管堆與垃圾收集
    引言 在前幾章我們深度講解了單元測試和集成測試的基礎知識，這一章我們來講解一下代碼覆蓋率,代碼覆蓋率是單元測試運行的度量值,覆蓋率通常以百分比表示，用於衡量代碼被測試覆蓋的程度，幫助開發人員評估測試用例的質量和代碼的健壯性。常見的覆蓋率包括語句覆蓋率（Line Coverage）、分支覆蓋率（Bra ...
  • C#S7.NET實現西門子PLCDB塊數據採集的完整步驟
    前言 本文介紹瞭如何使用S7.NET庫實現對西門子PLC DB塊數據的讀寫，記錄了使用電腦模擬，模擬PLC，自至完成測試的詳細流程，並重點介紹了在這個過程中的易錯點，供參考。 用到的軟體： 1.Windows環境下鏈路層網路訪問的行業標準工具（WinPcap_4_1_3.exe）下載鏈接：http ...
  • WPF控制項：密碼框綁定MVVM
    從依賴倒置原則（Dependency Inversion Principle, DIP）到控制反轉（Inversion of Control, IoC）再到依賴註入（Dependency Injection, DI）的演進過程，我們可以理解為一種逐步抽象和解耦的設計思想。這種思想在C#等面向對象的編 ...
  • Python中的私有屬性與私有方法
    關於Python中的私有屬性和私有方法 Python對於類的成員沒有嚴格的訪問控制限制，這與其他面相對對象語言有區別。關於私有屬性和私有方法，有如下要點： 1、通常我們約定，兩個下劃線開頭的屬性是私有的（private）。其他為公共的（public）； 2、類內部可以訪問私有屬性（方法）； 3、類外 ...
  • C++ 訪問說明符詳解：封裝數據，控制訪問，提升安全性
    C++ 訪問說明符 訪問說明符是 C++ 中控制類成員（屬性和方法）可訪問性的關鍵字。它們用於封裝類數據並保護其免受意外修改或濫用。 三種訪問說明符： public：允許從類外部的任何地方訪問成員。 private：僅允許在類內部訪問成員。 protected：允許在類內部及其派生類中訪問成員。 示 ...
  • static_cast與dynamic_cast到底是什麼？
    寫這個隨筆說一下C++的static_cast和dynamic_cast用在子類與父類的指針轉換時的一些事宜。首先，【static_cast，dynamic_cast】【父類指針，子類指針】，兩兩一組，共有4種組合：用 static_cast 父類轉子類、用 static_cast 子類轉父類、使用 ...
  • C語言數據結構：雙向鏈表的增刪操作
    /******************************************************************************************************** * * * 設計雙向鏈表的介面 * * * * Copyright (c) 2023-2 ...
  • @ComponentScan註解的實現，Spring掃描包的過程
    相信接觸過spring做開發的小伙伴們一定使用過@ComponentScan註解 @ComponentScan("com.wangm.lifecycle") public class AppConfig { } @ComponentScan指定basePackage，將包下的類按照一定規則註冊成Be ...
  • opensips開啟python支持
    操作系統 ：CentOS 7.6_x64 opensips版本： 2.4.9 python版本：2.7.5 python作為腳本語言，使用起來很方便，查了下opensips的文檔，支持使用python腳本寫邏輯代碼。今天整理下CentOS7環境下opensips2.4.9的python模塊筆記及使用 ...
所有分類