1、建表

學生表

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


2、插入數據

學生表測試數據

INSERT INTO Student ( s_id, s_name, s_birth, s_sex )
VALUES
( 01, 趙雷, 1990-01-01, 男 ),
( 02, 錢電, 1990-12-21, 男 ),
( 03, 孫風, 1990-05-20, 男 ),
( 04, 李雲, 1990-08-06, 男 ),
( 05, 周梅, 1991-12-01, 女 ),
( 06, 吳蘭, 1992-03-01, 女 ),
( 07, 鄭竹, 1989-07-01, 女 ),
( 08, 王菊, 1990-01-20, 女 );


課程表測試數據

INSERT INTO Course ( c_id, c_name, t_id )
VALUES
( 01, 語文, 02 ),
( 02, 數學, 01 ),
( 03, 英語, 03 );


教師表測試數據

INSERT INTO Teacher ( t_id, t_name )
VALUES
( 01, 張三 ),
( 02, 李四 ),
( 03, 王五 );


成績表測試數據

INSERT INTO Score ( s_id, c_id, s_score )
VALUES
( 01, 01, 80 ),
( 01, 02, 90 ),
( 01, 03, 99 ),
( 02, 01, 70 ),
( 02, 02, 60 ),
( 02, 03, 80 ),
( 03, 01, 80 ),
( 03, 02, 80 ),
( 03, 03, 80 ),
( 04, 01, 50 ),
( 04, 02, 30 ),
( 04, 03, 20 ),
( 05, 01, 76 ),
( 05, 02, 87 ),
( 06, 01, 31 ),
( 06, 03, 34 ),
( 07, 02, 89 ),
( 07, 03, 98 );


筆試題

1.查詢課程編號為「01」的課程比「02」的課程成績高的所有學生的學號

SELECT
a.s_id
FROM
Score AS a
JOIN Score AS b ON a.s_id = b.s_id
AND a.s_score > b.s_score
WHERE
a.c_id = 01
AND b.c_id = 02;


2.查詢平均成績大於60分的學生的學號和平均成績（難點：用錯group by having跟where group by）

SELECT
s_id,
AVG( s_score ) AS score
FROM
Score
GROUP BY
s_id
HAVING
score > 60;


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

SELECT
Student.s_id AS 學號,
s_name AS 姓名,
COUNT( c_id ) AS 選課數,
SUM( s_score ) AS 總成績
FROM
Student
JOIN Score ON Student.s_id = Score.s_id
GROUP BY
Student.s_id,
s_name;


4.查詢姓「張」的老師的個數 （難點：通過t_id查詢t_name數量）

SELECT
COUNT( t_id ) AS num
FROM
Teacher
WHERE
t_name LIKE %張%;


5.查詢沒學過「張三」老師課的學生的學號、姓名

SELECT
s_id,
s_name
FROM
Student
WHERE
s_id NOT IN (
SELECT
s_id
FROM
Score
JOIN Course ON Course.c_id = Score.c_id
JOIN Teacher ON Course.t_id = Teacher.t_id
WHERE
t_name = 張三
);


6.查詢學過「張三」老師課的學生的學號、姓名（同上）

SELECT
s_id,
s_name
FROM
Student
WHERE
s_id IN (
SELECT
s_id
FROM
Score
JOIN Course ON Course.c_id = Score.c_id
JOIN Teacher ON Course.t_id = Teacher.t_id
WHERE
t_name = 張三
);


7.查詢學過編號為「01」的課程並且也學過編號為「02」的課程的學生的學號、姓名

SELECT
s_id,
s_name
FROM
Student
WHERE
s_id IN (
SELECT
a.s_id
FROM
( SELECT s_id FROM Score WHERE c_id = 01 ) AS a
JOIN ( SELECT s_id FROM Score WHERE c_id = 02 ) AS b ON a.s_id = b.s_id
);


8.查詢課程編號為「02」的總成績（也可用group by having）

SELECT
SUM( s_score ) AS score
FROM
Score
WHERE
c_id = 02;


9.查詢所有課程成績小於60分的學生學號、姓名（錯用join on鏈接；注意s_id為08的成績）

SELECT
s_id,
s_name
FROM
Student
WHERE
s_id NOT IN ( SELECT s_id FROM Score WHERE s_score >= 60 );


10.查詢沒有學全所有課的學生的學號、姓名（group by跟where in的解法）

SELECT
Student.s_id,
Student.s_name
FROM
Student
JOIN Score ON Score.s_id = Student.s_id
GROUP BY
s_id,
s_name
HAVING
COUNT( c_id ) < ( SELECT COUNT( c_id ) FROM Course );

SELECT
s_id,
s_name
FROM
Student
WHERE
s_id IN ( SELECT s_id FROM Score GROUP BY s_id HAVING COUNT( c_id ) < ( SELECT COUNT( c_id ) FROM Course ) );

11.查詢至少有一門課與學號為「01」的學生所學課程相同的學生的學號和姓名（注意DISTINCT的限定或使用group by分組）

SELECT
Student.s_id,
s_name
FROM
Student
JOIN Score ON Score.s_id = Student.s_id
WHERE
c_id IN ( SELECT c_id FROM Score WHERE s_id = 01 )
AND Student.s_id <> 01
GROUP BY
s_id;


12.查詢和「01」號同學所學課程課程完全相同的其他同學的學號和姓名

SELECT
Student.s_id,
Student.s_name
FROM
Student
JOIN Score ON Score.s_id = Student.s_id
WHERE
c_id IN ( SELECT c_id FROM Score WHERE s_id = 01 )
AND Score.s_id <> 01
GROUP BY
s_id
HAVING
COUNT( c_id ) = ( SELECT COUNT( c_id ) FROM Score WHERE s_id = 01 );


13.把「Score」表中「張三」老師教的課的成績都更改為此課程的平均成績（難點）

UPDATE Score AS a
JOIN (
SELECT
AVG( s_score ) AS t,
Score.c_id
FROM
Score
JOIN Course ON Score.c_id = Course.c_id
JOIN Teacher ON Teacher.t_id = Course.t_id
WHERE
t_name = 張三
GROUP BY
c_id
) AS b ON a.c_id = b.c_id
SET a.s_score = b.t;


14.查詢和「02」號的同學學習的課程完全相同的其他同學學號和姓名

SELECT
Student.s_id,
Student.s_name
FROM
Student
JOIN Score ON Score.s_id = Student.s_id
WHERE
c_id IN ( SELECT c_id FROM Score WHERE s_id = 02 )
AND Score.s_id <> 01
GROUP BY
s_id
HAVING
COUNT( c_id ) = ( SELECT COUNT( c_id ) FROM Score WHERE s_id = 02 );


15.刪除學習「張三」老師課的Score表記錄

DELETE
FROM
Score
WHERE
c_id IN ( SELECT c_id FROM Course JOIN Teacher ON Course.t_id = Teacher.t_id WHERE t_name = 張三 );


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

SELECT
s_name,
c_id,
s_score
FROM
Score,
Student
WHERE
Student.s_id = Score.s_id
AND c_id = 01
AND s_score < 60
ORDER BY
s_score DESC;


17.按平均成績從高到低顯示所有學生的「資料庫」（c_id=04）、「企業管理」（c_id=01）、「英語」（c_id=06）三門的課程成績，按如下形式顯示：學生ID，資料庫，企業管理，英語，有效課程數，有效平均分

SELECT
s_id AS 學生ID,
( SELECT s_score FROM Score WHERE Score.s_id = sc.s_id AND c_id = 04 ) AS 資料庫,
( SELECT s_score FROM Score WHERE Score.s_id = sc.s_id AND c_id = 01 ) AS 企業管理,
( SELECT s_score FROM Score WHERE Score.s_id = sc.s_id AND c_id = 06 ) AS 英語,
COUNT( c_id ) AS 有效課程數,
AVG( s_score ) AS 有效平均分
FROM
Score AS sc
GROUP BY
s_id
ORDER BY
AVG( s_score ) DESC;


18.查詢各科成績最高和最低的分：以如下的形式顯示：課程ID、最高分、最低分

SELECT
c_id AS 課程ID,
MAX( s_score ) AS 最高分,
MIN( s_score ) AS 最低分
FROM
Score
GROUP BY
c_id;


19.按各科平均成績從低到高和及格率百分數從高到低排列，以如下形式顯示：課程號、課程名、平均成績、及格百分數 （超難，慢慢理解）

SELECT
Course.c_id AS 課程號,
Course.c_name AS 課程名,
AVG( Score.s_score ) AS 平均成績,
CONCAT( d.c / a.b * 100, % ) AS 及格百分數
FROM
( SELECT c_id, COUNT( DISTINCT s_id ) AS b FROM Score GROUP BY c_id ) AS a # b=7 a=01 6 02 6 03 6
JOIN ( SELECT c_id, COUNT( DISTINCT s_id ) AS c FROM Score WHERE s_score >= 60 GROUP BY c_id ) AS d ON a.c_id = d.c_id # c=7 d=01 4 02 5 03 4
JOIN Course ON a.c_id = Course.c_id
JOIN Score ON Course.c_id = Score.c_id
GROUP BY
Course.c_id,
Course.c_name
ORDER BY
平均成績,
及格百分數 DESC;


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

SELECT
Course.c_id AS 課程ID,
Course.c_name AS 課程名稱,
SUM( CASE WHEN Score.s_score BETWEEN 85 AND 100 THEN 1 ELSE 0 END ) AS [85-100],
SUM( CASE WHEN Score.s_score >= 70 AND Score.s_score < 85 THEN 1 ELSE 0 END ) AS [70-85),
SUM( CASE WHEN Score.s_score >= 60 AND Score.s_score < 70 THEN 1 ELSE 0 END ) AS [60-70),
SUM( CASE WHEN Score.s_score < 60 THEN 1 ELSE 0 END ) AS [<60]
FROM
Score
JOIN Course ON Score.c_id = Course.c_id
GROUP BY
Course.c_id,
Course.c_name;


21.查詢學生平均成績及其名次（難點：運行不成功）

SELECT
1+ (
SELECT
COUNT( * )
FROM
( SELECT s_id, AVG( s_score ) AS 平均成績 FROM Score GROUP BY s_id ) AS b
WHERE
b.平均成績 > a.平均成績
) AS RANK,
s_id,
平均成績
FROM
( SELECT s_id, AVG( s_score ) AS 平均成績 FROM Score GROUP BY s_id ) AS a
ORDER BY
平均成績 DESC;


22.查詢各科成績前三名的記錄（不考慮成績並列情況，注意邏輯順序）

SELECT
c_id,
s_id,
s_score
FROM
Score AS a
WHERE
( SELECT COUNT( * ) FROM Score AS b WHERE a.c_id = b.c_id AND a.s_score < b.s_score ) <= 2
ORDER BY
c_id ASC,
s_score DESC;


23.查詢每門課程被選修的學生數

SELECT
c_id,
COUNT( s_id ) AS num
FROM
Score
GROUP BY
c_id;


24.查詢只選修了兩門課程的全部學生的學號和姓名

SELECT
Student.s_id,
Student.s_name
FROM
Student
JOIN Score ON Student.s_id = Score.s_id
GROUP BY
s_id,
s_name
HAVING
COUNT( c_id ) = 2;


25.查詢男生、女生人數

SELECT
s_sex,
COUNT( * ) AS nums
FROM
Student
GROUP BY
s_sex;


26.查詢名字中含有「風」字的學生信息

SELECT
*
FROM
Student
WHERE
s_name LIKE %風%;


27.查詢同名同姓學生名單並統計同名人數

SELECT
a.s_id,
a.s_name,
a.s_birth,
a.s_sex
FROM
Student AS a
JOIN Student AS b ON a.s_name = b.s_name
WHERE
a.s_id <> b.s_id;


28.1990年出生的學生名單

SELECT
*
FROM
Student
WHERE
YEAR ( s_birth ) = 1990;


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

SELECT
Score.s_id,
Student.s_name,
AVG( Score.s_score ) AS score
FROM
Score
JOIN Student ON Score.s_id = Student.s_id
GROUP BY
s_id
HAVING
score > 85;


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

SELECT
c_id,
AVG( s_score ) AS score
FROM
Score
GROUP BY
c_id
ORDER BY
score,
c_id DESC;


31.查詢課程名稱為「數學」且分數低於60分的學生姓名和分數

SELECT
s_name,
s_score
FROM
Student
JOIN Score ON Score.s_id = Student.s_id
JOIN Course ON Score.c_id = Course.c_id
WHERE
c_name = 數學
AND s_score < 60;


32.查詢所有學生的選課情況

SELECT
Student.s_id,
Student.s_name,
Course.c_id,
Course.c_name
FROM
Student
JOIN Score ON Student.s_id = Score.s_id
JOIN Course ON Score.c_id = Course.c_id;


33.查詢任何一門課程成績在70分以上的姓名、課程名稱和分數（注意審題，不能使用group by分組，直接使用having或where）

SELECT
Student.s_name,
Course.c_name,
Score.s_score
FROM
Student
JOIN Score ON Student.s_id = Score.s_id
JOIN Course ON Score.c_id = Course.c_id
HAVING
s_score > 70;

SELECT
Student.s_name,
Course.c_name,
Score.s_score
FROM
Student
JOIN Score ON Student.s_id = Score.s_id
JOIN Course ON Score.c_id = Course.c_id
WHERE
s_score > 70;

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

SELECT
Course.c_id,
Course.c_name,
Score.s_score
FROM
Course
JOIN Score ON Course.c_id = Score.c_id
WHERE
s_score < 60
ORDER BY
c_id DESC,
s_score DESC;


35.查詢課程編號為03且課程成績在80分以上的學生的學號和姓名

SELECT
Student.s_id,
Student.s_name
FROM
Student
JOIN Score ON Score.s_id = Student.s_id
WHERE
Score.c_id = 03
AND s_score > 80;


36.查詢選了課程的學生人數

SELECT
COUNT( DISTINCT s_id ) AS nums
FROM
Score;


37.查詢選修「張三」老師所授課程的學生中成績最高的學生姓名和成績

SELECT
Student.s_name,
Score.s_score
FROM
Student
JOIN Score ON Student.s_id = Score.s_id
JOIN Course ON Score.c_id = Course.c_id
JOIN Teacher ON Course.t_id = Teacher.t_id
WHERE
t_name = 張三
ORDER BY
Score.s_score DESC
LIMIT 1;


38.查詢各個課程及相應的選修人數

SELECT
c_id,
COUNT( s_id )
FROM
Score
GROUP BY
c_id;


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

SELECT DISTINCT
a.s_id,
a.c_id,
a.s_score
FROM
Score AS a
JOIN Score AS b ON a.s_id = b.s_id
AND a.c_id <> b.c_id
WHERE
a.s_score = b.s_score;


40.查詢每門課程成績最好的前兩名

SELECT
Student.s_name,
Course.c_name,
a.s_score
FROM
Score AS a
JOIN Student ON Student.s_id = a.s_id
JOIN Course ON Course.c_id = a.c_id
WHERE
( SELECT COUNT( * ) FROM Score AS b WHERE b.c_id = a.c_id AND b.s_score >= a.s_score ) <= 2
ORDER BY
a.c_id,
a.s_score DESC;


41.統計每門課程的學生選修人數(超過5人的課程才統計)。要求輸出課程號和選修人數，查詢結果按人數降序排序，若人數相同，按課程號升序排序

SELECT
c_id AS 課程號,
COUNT( s_id ) AS 選修人數
FROM
Score
GROUP BY
c_id
HAVING
COUNT( s_id ) > 5
ORDER BY
選修人數 DESC,
c_id;


42.查詢至少選修兩門課程的學生學號

SELECT
s_id
FROM
Score
GROUP BY
s_id
HAVING
COUNT( c_id ) >= 2;


43.查詢選修了全部課程的學生信息

SELECT
Student.s_id,
Student.s_name
FROM
Student
JOIN Score ON Score.s_id = Student.s_id
GROUP BY
s_id
HAVING
COUNT( Score.c_id ) = ( SELECT COUNT( DISTINCT c_id ) FROM Score );


44.查詢沒學過「張三」老師講授的任意一門課程的學生姓名

SELECT
s_name
FROM
Student
WHERE
s_id NOT IN (
SELECT
s_id
FROM
Score
JOIN Course ON Score.c_id = Course.c_id
JOIN Teacher ON Teacher.t_id = Course.t_id
WHERE
t_name = 張三
);


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

SELECT
s_id,
AVG( s_score ) AS score
FROM
Score
WHERE
s_score < 60 GROUP BY s_id HAVING COUNT( c_id ) >= 2;


46.檢索課程編號為「04」且分數小於60分的學生學號，結果按分數降序排列

SELECT
s_id
FROM
Score
WHERE
c_id = 04
AND s_score < 60
ORDER BY
s_score DESC;


47.刪除學生編號為「02」的課程編號為「01」的成績

DELETE
FROM
Score
WHERE
s_id = 02
AND c_id = 01;


此50題參考出處

青檸檬：SQL筆試題50道答案整理?

zhuanlan.zhihu.com
推薦閱讀：

相关文章