常见的SQL笔试题和面试题（上）：经典50题

CREATE DATABASE SQL50题

GO

USE SQL50题

GO

-- 学生表

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,

PRIMARY KEY(s_id,c_id)

);

GO

-- 插入学生表测试数据

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

查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号

算法一:

这个使用了表的自联结

USE SQL50题

SELECT a.s_id

FROM Score a JOIN Score b ON a.s_id=b.s_id

WHERE a.c_id='01' AND b.c_id='02'

AND a.s_score>b.s_score

算法二：

使用子查询

select a.s_id

from (select * from Score where c_id = '01') as a

join (select * from Score where c_id='02') as b

on a.s_id = b.s_id

where a.s_score > b.s_score;

查询学生的学号、修课数、平均分，并按照平均分进行排序。

SELECT s_id '学号',COUNT(*) '课程数',AVG(s_score) '平均分'

FROM Score

GROUP BY s_id

ORDER BY AVG(s_score)

使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

SELECT Score.c_id,c_name,

SUM(CASE WHEN s_score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '[100-85]',

SUM(CASE WHEN s_score >=70 and s_score<85 THEN 1 ELSE 0 END) AS '[85-70]',

SUM(CASE WHEN s_score>=60 and s_score<70 THEN 1 ELSE 0 END) AS '[70-60]',

SUM(CASE WHEN s_score<60 THEN 1 ELSE 0 END) AS '[<60]'

FROM Score JOIN Course ON Score.c_id=Course.c_id

GROUP BY Score.c_id,c_name

四、查询出每门课程的及格人数和不及格人数

SELECT c_id,

SUM(CASE WHEN s_score<60 THEN 1 ELSE 0 END) '合格',

SUM(CASE WHEN s_score>=60 THEN 1 ELSE 0 END) '不合格'

FROM Score

GROUP BY c_id

五、查询出每门课程的及格百分比

这个考察了类型转换

SELECT c_id,

(SUM(CAST((CASE WHEN s_score>=60 THEN 1 ELSE 0 END) AS FLOAT))/COUNT(*)) '合格百分比'

FROM Score

GROUP BY c_id

六、查询和“01”号同学所学课程完全相同的其他同学的学号

算法一、

SELECT s_id --选出来所学课程为01学号课程子集的学生
FROM Score
WHERE c_id IN
(SELECT c_id --选出来01学号学的全部课程
FROM Score
WHERE s_id = '01')
AND s_id <> '01'
INTERSECT --取交集
SELECT s_id --选出来和01学号选修课程数相同的学生
FROM Score
WHERE s_id <> '01'
GROUP BY s_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM Score WHERE s_id = '01')

算法二

SELECT s_id --选出来所学课程为01学号子集的学生
FROM Score
WHERE c_id IN
(SELECT c_id --选出来01学号学的全部课程
FROM Score
WHERE s_id = '01')
AND s_id <> '01'
GROUP BY s_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM Score WHERE s_id = '01')

算法一和算法二实质相同，都是先查询选择的课程是01号学生学的课程的子集，在查询选择的课程与01号选择的课程数相同的学生。

最后两者进行取交集。

但是第二种写法更简单。

七、把“SCORE”表中“张三”老师教的课的成绩都更改为此课程的平均成绩

UPDATE Score
SET s_score=
(SELECT AVG(s_score) --算出来平均分
FROM Score
WHERE c_id IN
(SELECT sc.c_id --选出来张三老师教的课
FROM Score sc
JOIN Course co ON sc.c_id = co.c_id
JOIN Teacher te ON te.t_id = co.t_id
WHERE te.t_name = '张三'))
WHERE c_id IN
(SELECT sc.c_id --选出来张三老师教的课
FROM Score sc
JOIN Course co ON sc.c_id = co.c_id
JOIN Teacher te ON te.t_id = co.t_id
WHERE te.t_name = '张三')

在原文还有另外一种解法，但是SQL SERVER不支持：

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;

八、按各科平均成绩从低到高和及格率的百分数从高到低排列，以如下形式显示：课程号，课程名平均成绩，及格百分数

SELECT c_name '科目',
AVG(s_score) '平均分',
SUM(CAST(CASE WHEN s_score >= 60 THEN 1 ELSE 0 END AS FLOAT)) / COUNT(*) '及格率'
FROM Score
JOIN Course ON Score.c_id = Course.c_id
GROUP BY c_name
ORDER BY AVG(s_score) ASC, SUM(CAST(CASE WHEN s_score >= 60 THEN 1 ELSE 0 END AS FLOAT)) / COUNT(*) DESC

和第五题类似，考察了类型转换，同时还考察了二表连接

九、查询不同老师所教不同课程平均分从高到低显示

SELECT t_name,c_name,AVG(s_score)

FROM Score JOIN Course ON Score.c_id=Course.c_id JOIN Teacher ON Teacher.t_id=Course.t_id

GROUP BY t_name,c_name

ORDER BY AVG(s_score) DESC

考察了分组、聚合函数和三表连接

十、查询各科成绩前三名（包括重复）

要查询前三名，则比其成绩高的人个个数要小于三个。

SELECT st.s_name,co.c_name,sc.c_id

FROM Student st JOIN Score sc ON st.s_id=sc.s_id

JOIN Course co ON sc.c_id=co.c_id

WHERE (SELECT COUNT(*) FROM Score WHERE s_score>sc.s_score AND sc.c_id=c_id)< 3

ORDER BY c_name,sc.c_id

十一、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名

这个题的解法比较多，可以通过子查询、自连接、求交集和逻辑和进行求解：

逻辑和

select s_id,s_name

from Student

where s_id in

(select s_id from Score where c_id = '01')

AND s_id in

(select s_id from Score where c_id = '02')

二、自连接

select a.s_id,a.s_name

from Student a JOIN Score b ON a.s_id=b.s_id

JOIN Score c ON a.s_id=c.s_id

where b.c_id='01' and c.c_id='02'

and b.s_id=c.s_id ;