SQL面试必备50题（超详细步骤讲解）

1.首先说明几个表的关系

如下图所示。一共四个表student、score、course、teacher。其中student表中有字段s_id,s_name,s_birth,s_sex；score表中有字段s_id,c_id,s_score；course表中有字段c_id,c_name,t_id；teacher表中有字段t_id,t_name；各表两两相互关联。

2.建立数据库与表

DROP DATABASE IF EXISTS my_test50;
CREATE DATABASE my_test50;

#建学生表
DROP TABLE IF EXISTS student;
CREATE TABLE student(
s_id VARCHAR(20) PRIMARY KEY,
s_name VARCHAR(20) NOT NULL DEFAULT ‘’,
s_birth VARCHAR(20) NOT NULL DEFAULT ‘’,
s_sex VARCHAR(10) NOT NULL DEFAULT ‘’
);

#建课程表
DROP TABLE IF EXISTS course;
CREATE TABLE course(
c_id VARCHAR(20) PRIMARY KEY,
c_name VARCHAR(20) NOT NULL DEFAULT ‘’,
t_id VARCHAR(20) NOT NULL
);

#建教师表
DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher(
t_id VARCHAR(20) PRIMARY KEY,
t_name VARCHAR(20) NOT NULL DEFAULT ‘’

);

#建成绩表
DROP TABLE IF EXISTS score;
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’,‘男’),
(‘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
VALUES(‘01’,‘语文’,‘02’),
(‘02’,‘数学’,‘01’),
(‘03’,‘英语’,‘03’);

#插入教师表数据
INSERT INTO teacher
VALUES(‘01’,‘张三’),
(‘02’,‘李四’),
(‘03’,‘王五’);

#插入成绩表数据
INSERT INTO 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’);

3.开始做题

1.查询课程编号为01的课程比02的课程成绩高的所有学生的学号（重点）

#step1.在score表中查询课程编号为01的学生编号和分数
SELECT s_id,s_score FROM score WHERE c_id=‘01’;

#step2.在score表中查询课程编号为02的学生编号和分数
SELECT s_id,s_score FROM score WHERE c_id=‘02’;

#step3.将student表和step1与step2得出的表连接
SELECT st.s_id,st.s_name,a.s_score,b.s_score
FROM student st
LEFT JOIN (SELECT s_id,s_score FROM score WHERE c_id=‘01’) a ON st.s_id=a.s_id
LEFT JOIN (SELECT s_id,s_score FROM score WHERE c_id=‘02’) b ON st.s_id=b.s_id
WHERE a.s_score>b.s_score;

2.查询平均成绩大于60分的学生的学号和平均成绩（重点）

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

3.查询所有学生的学号、姓名、选课数、总成绩（不重要）

SELECT st.s_id,st.s_name,COUNT(sc.c_id),SUM(sc.s_score)
FROM student st
LEFT JOIN score sc
ON st.s_id=sc.s_id
GROUP BY st.s_id;

4.查询姓“猴”的老师的个数（不重要）

SELECT COUNT(t_id)
FROM teacher t
WHERE t_name LIKE ‘猴%’;

5.查询没学过“张三”老师课程的学生的学号、姓名（重点）

SELECT st.s_id,st.s_name
FROM student st
WHERE st.s_id NOT IN (
SELECT sc.s_id
FROM score sc
LEFT JOIN course co ON sc.c_id=co.c_id
LEFT JOIN teacher te ON co.t_id =te.t_id
WHERE te.t_name=‘张三’
);

6.查询学过“张三”老师课程的学生的学号、姓名（重点）

SELECT st.s_id,st.s_name
FROM student st
WHERE st.s_id IN (
SELECT sc.s_id
FROM score sc
LEFT JOIN course co ON sc.c_id=co.c_id
LEFT JOIN teacher te ON co.t_id =te.t_id
WHERE te.t_name=‘张三’
);

7.查询学过编号为’01’课程并且也学过’02’课程的学生的学号、姓名（重点）

#step1.查询学过编号为’01’课程的学生编号
SELECT sc.s_id
FROM score sc
WHERE sc.c_id=‘01’;

#step2.查询学过编号为’02’课程的学生编号
SELECT sc.s_id
FROM score sc
WHERE sc.c_id=‘02’;

#step3.查询出step1与step2的交集
SELECT
st.s_id,
st.s_name
FROM
student st
LEFT JOIN score s ON st.s_id = s.s_id
WHERE
s.c_id = “01” UNION
SELECT
st.s_id,
st.s_name
FROM
student st
LEFT JOIN score s ON st.s_id = s.s_id
WHERE
s.c_id = “02”

7.1.查询学过编号为’01’课程但没学过’02’课程的学生的学号、姓名（重点）
SELECT
st.s_id,
st.s_name
FROM
student st
LEFT JOIN score s ON st.s_id = s.s_id
WHERE
s.c_id = “01” UNION
SELECT
st.s_id,
st.s_name
FROM
student st
WHERE
st.s_id NOT IN (
SELECT
st.s_id
FROM
st
LEFT JOIN score s ON st.s_id = s.s_id
WHERE
s.c_id = “02”)

8.查询课程编号为’02’的总成绩（不重点）
SELECT SUM(sc.s_score)
FROM score sc
WHERE sc.c_id=‘02’;