親手為你建立sql面試題案例數據

親手為你建立sql面試題案例數據

來自專欄猴子聊數據分析11 人贊了文章

題目來源網路，自己在Navicat中寫入數據並練習

表名和欄位

–1.學生表

Student(s_id,s_name,s_birth,s_sex) –學生編號,學生姓名, 出生年月,學生性別

–2.課程表

Course(c_id,c_name,t_id) – –課程編號, 課程名稱, 教師編號

–3.教師表

Teacher(t_id,t_name) –教師編號,教師姓名

–4.成績表

Score(s_id,c_id,s_score) –學生編號,課程編號,分數

測試數據

--建表

--學生表

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

50題：

-- 1、查詢"01"課程比"02"課程成績高的學生的信息及課程分數

思路就先查詢括弧最裡層的內容，慢慢往外嵌套，還有就是 select的結果可以作為一個表 被 JOIN ，被查詢。 表裡有的科目沒有成績。

-- 2、查詢"01"課程比"02"課程成績低的學生的信息及課程分數

把第一題答案改一下，注意裡面要查的是b.s_id ，這次是B集合大

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

-- 4、查詢平均成績小於60分的同學的學生編號和學生姓名和平均成績

-- (包括有成績的和無成績的)

IFNULL 如果第一個值不為NULL 返回第一個值，否則返回第二個值

-- 5、查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績

-- 6、查詢"李"姓老師的數量

-- 7、查詢學過"張三"老師授課的同學的信息

-- 8、查詢沒學過"張三"老師授課的同學的信息

-- 9、查詢學過編號為"01"並且也學過編號為"02"的課程的同學的信息

-- 10、查詢學過編號為"01"但是沒有學過編號為"02"的課程的同學的信息

-- 11、查詢沒有學全所有課程的同學的信息

這裡的count(score.c_id)算的是group以後，每個學生的課程數量

-- 12、查詢至少有一門課與學號為"01"的同學所學相同的同學的信息

-- 13、查詢和"01"號的同學學習的課程完全相同的其他同學的信息

-- 14、查詢沒學過"張三"老師講授的任一門課程的學生姓名

反向思維，求沒上過張三課的學生，可以先求出上過張三課的，除了上過的，就是沒上過的。

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

先查所有不及格的學生學號和各科成績，以此為表

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

;

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

select 後面也可以跟select語句

-- 18.查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率

--及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90

-- 19、按各科成績進行排序，並顯示排名(實現不完全)

自定義變數

-- 20、查詢學生的總成績並進行排名

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

-- 22、查詢所有課程的成績第2名到第3名的學生信息及該課程成績

union all 合併三組，注意每組中的@變數名不能重複。

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

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

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

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

-- 27、查詢出只有兩門課程的全部學生的學號和姓名

-- 28、查詢男生、女生人數

-- 29、查詢名字中含有"風"字的學生信息

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

-- 31、查詢1990年出生的學生名單

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

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

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

-- 35、查詢所有學生的課程及分數情況；

-- 36、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數；

-- 37、查詢不及格的課程

--38、查詢課程編號為01且課程成績在80分以上的學生的學號和姓名；

-- 39、求每門課程的學生人數

-- 40、查詢選修"張三"老師所授課程的學生中，成績最高的學生信息及其成績

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

-- 42、查詢每門功成績最好的前兩名

-- 43、統計每門課程的學生選修人數（超過5人的課程才統計）。要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列

-- 44、檢索至少選修兩門課程的學生學號

-- 45、查詢選修了全部課程的學生信息

--46、查詢各學生的年齡

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

-- 47、查詢本周過生日的學生

WEEK函數的參數1 表示周一為一周第一天

-- 48、查詢下周過生日的學生

-- 49、查詢本月過生日的學生

-- 50、查詢下月過生日的學生

推薦閱讀：