蝉唐虞世南经典SQL面试题总结

2021年1月21日发(作者：房凤友)
表

Student(S#,Sname,Sage,Ssex)
学生表


CREATE TABLE student (
sid varchar(10) NOT NULL,
sName varchar(20) DEFAULT NULL,
sAge datetime DEFAULT '1980-10-12 23:12:36',
sSex varchar(10) DEFAULT NULL,
PRIMARY KEY (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Course(C#,Cname,T#)
课程表


CREATE
TABLE
course
(


cid
varchar(10)
NOT
NULL,


cName
varchar(10)
DEFAULT
NULL,


tid
int(20)
DEFAULT
NULL,


PRIMARY
KEY
(cid)
)
ENGINE=InnoDB
DEFAULT
CHARSET=utf8;

SC(S#,C#,score)
成绩表


CREATE TABLE sc (
sid varchar(10) DEFAULT NULL,
cid varchar(10) DEFAULT NULL,
score int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Teacher(T#,Tname)
教师表


CREATE TABLE teacher (
tid int(10) DEFAULT NULL,
tName varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
数据：（
MySQL
）

insert

into
teacher(tid,tName)
values
(1,'
李老师
'),(2,'
何以琛
') ,(3,'
叶平
');
insert

into
student(sid,sName,sAge,sSex)
values
('1001','
张三丰
','1980-10-12 23:12:36','< br>男
'),('1002','
张无极
','1995-
10-12 < br>23:12:36','
男
'),('1003','
李奎
','19 92-10-12
23:12:36','
女
'),('1004','
李 元宝
','1980-10-12
23:12:36','
女
'),('1 005','
李
世明
','1981-10-12 23:12:36','
男
'),('1006','
赵六
','1986-10-12 23:12:36' ,'
男
'),('1007','
田七
','1981-10-12 23:12:36','
女
');
insert

into
sc(sid,cid,score)
values
('1','001',80 ),('1','002',60),('1','003',75),('2','001',85),('2 ','002',70),('3','004',100),
('3','001',90),('3 ','002',55),('4','002',65),('4','003',60);
insert

into
course(cid,cName,tid)
values
('001','
企业管理
',3),('002','马克思
',3),('003','UML',2),('004','
数据库
' ,1),('005
','
英语
',1);

1
、查询< br>“001”
课程比
“002”
课程成绩高的所有学生的学号
;

select
a.S#
from
(select
s#,score
from
SC
where
C#='001')
a,(select
s#,score

from
SC
where
C#='002')
b

where
>
and
a.s#=b.s#;

2
、查询平均成绩大于
60
分的同学的学号和平均成绩
;

select
S#,avg(score)

from
sc

group
by
S#
having
avg(score) >60;

3
、查询所有同学的学号、姓名、选课数、总成绩
;

select
Student.S#,,count(SC.C#),sum(score)

from
Student
left
Outer
join
SC
on
Student.S#=SC.S#

group
by
Student.S#,Sname

4
、查询姓
“
李
”
的老师的个数
;

select
count(distinct(Tname))

from
Teacher

where
Tname
like
'
李
%';

5
、查询没学过
“
叶平
”
老师课的同学的学号、姓名
;

select
Student.S#,

from
Student

where
S#
not
in
(select
distinct( SC.S#)
from
SC,Course,Teacher
where
SC.C#=Course.C#
and
Teacher.T#=Cou
rse.T#
and
='
叶平
');

6
、查询学过
“001”并且也学过编号
“002”
课程的同学的学号、姓名
;

select
Student.S#,
from
Student,SC
where
Student.S#=SC.S#
and
SC.C#='001'and exists(
Select
*
from
SC
as
SC_2 where
SC_2.S#=SC.S#
and
SC_2.C#='002');

7
、查询学过
“
叶平
”
老师所教的所有课的同学的学号、姓 名
;

select
S#,Sname

from
Student

where
S#
in (select
S#
from
SC ,Course
,Teacher
where
SC.C#=Course.C#
and
Teacher.T#=Course.T#
and
Te
='
叶平
'
group
by
S#
having
count(SC.C#)=(select
count(C#)
from
Course,Teacher
where
Teacher.T
#=Course.T#
and
Tname='
叶平
'));

8
、
查询课程编 号
“002”
的成绩比课程编号
“001”
课程低的所有同学的学号、
姓名
;

Select
S#,Sname
from
(select
Student.S#,,score
,(select
score
from
SC
SC_2 where
SC_2.S#=Stu
dent.S#
and
SC_2.C#='002')
score2

from
Student,SC
where
Student.S#=SC.S#
and
C#='001')
S_2
where
score2
9
、查询所有课程成绩小于
60
分的同学的学号、姓名
;

select
S#,Sname

from
Student

where
S#
not
in
(select
Student.S#
from
Student,SC
where
S.S#=SC.S#
and
score>60);

10
、查询没有学全所有课的同学的学号、姓名
;

select
Student.S#,

from
Student,SC

where
Student.S#=SC.S#
group by
Student.S#,
having
count(C#) <(select
count(C#)
from
Cours
e);

1 1
、查询至少有一门课与学号为
“1001”
的同学所学相同的同学的学号和姓名;

select
S#,Sname
from
Student,SC
where
Student.S#=SC.S#
and C#
in
select
C#
from
SC
where S#='1001';

12
、查询至少学过 学号为
“001”
同学所有一门课的其他同学学号和姓名
;

select
distinct
SC.S#,Sname

from
Student,SC

where
Student.S#=SC.S#
and
C#
in
(select
C#
from
SC where
S#='001');

13
、把
“SC”
表中
“
叶平
”
老师教的课的成绩都更改为此课程的平均成绩
;

update
SC
set
score=(select
avg(SC_)

from
SC
SC_2

where
SC_2.C#=SC.C#
)
from
Course,Teacher
where
Course.C#=SC.C#
and
Course.T#=Teacher.T#
and
Teache
='
叶平
');

14
、查询和< br>“1002”
号的同学学习的课程完全相同的其他同学学号和姓名
;

select
S#
from
SC
where
C#
in (select
C#
from
SC where
S#='1002')

group
by
S#
having
count(*)=(select
count(*)
from
SC where
S#='1002');

15< br>、删除学习
“
叶平
”
老师课的
SC
表记录
;

Delect
SC

from
course
,Teacher

where
Course.C#=SC.C#
and
Course.T#=
Teacher.T#
and
Tname='
叶平
';

16
、向
SC
表中插入一些记录，这些记录要求符合以下条件：没有上过编号
“00
3”
课程的同 学学号、
2
号课的平均成绩
;

Insert
SC
select
S#,'002',(Select
avg(score)

from
SC
where
C#='002')
from
Student
where
S#
not
in
(Select
S#
from
SC where
C#='002');

17
、按平均成绩从高到低显示所有学生的
“
数据库
”
、
“
企业管理
”
、
“
英语
”
三门的
课程成绩，按如下形式显示：

学生
ID, ,
数据库
,
企业管理
,
英语
,
有效课程数
,
有效
平均分


SELECT
S#
as
学生
ID
,(SELECT
score
FROM
SC
WHERE
SC.S#=t.S#
AND
C#='004')
AS
数据库

,(SELECT
score
FROM
SC
WHERE
SC.S#=t.S#
AND
C#='001')
AS
企业管理

,(SELECT
score
FROM
SC
WHERE
SC.S#=t.S#
AND
C#='006')
AS
英语

,COUNT(*)
AS
有效课程数
,
AVG()
AS
平均成绩

FROM
SC
AS
t

GROUP
BY
S#

ORDER
BY
avg()

18
、查询各科成绩最 高和最低的分：以如下形式显示：课程
ID
，最高分，最低
分


SELECT
L.C#
As
课程
ID, AS
最高分
,
AS
最低分

FROM
SC
L
,SC
AS R

WHERE
L.C#
= R.C#
and


= (SELECT
MAX()

FROM
SC
AS
IL,Student
AS
IM

WHERE
L.C#
= IL.C#
and
IM.S#=IL.S#

GROUP
BY
IL.C#)

AND

= (SELECT MIN()

FROM
SC
AS
IR

WHERE R.C# =
IR.C#

GROUP
BY
IR.C#

);
19
、按各科平均成绩从低到高和及格率的百分数从高到低顺序


SELECT
t.C#
AS
课程号
,max()AS
课程名
,isnull(AVG(score),0)
AS
平均成绩

,100 *
SUM(CASE
WHEN
isnull(score,0)>=60
THEN
1
ELSE
0
END)/COUNT(*)
AS
及格百分数

FROM
SC
T,Course

where
t.C#=course.C#

GROUP
BY
t.C#

ORDER
BY 100
*
SUM(CASE
WHEN
isnull(score,0)>=60
THEN 1 ELSE
0
END)/COUNT(*)
DESC

20
、< br>查询如下课程平均成绩和及格率的百分数
(
用

行

显示
):
企业管理
（
001
）
，
马克思（
0 02
），
OO&UML
（
003
），数据库（
004
）


SELECT
SUM(CASE
WHEN
C# ='001'
THEN
score ELSE 0 END)/SUM(CASE
C#
WHEN
'001'
THEN 1 ELSE 0 END)

AS
企业管理平均分

,100 *
SUM(CASE
WHEN
C# =
'001'
AND
score >=
60
THEN
1 ELSE 0 END)/SUM(CASE
WHEN
C#
=
'001'
THE
N
1
ELSE 0 END)
AS
企业管理及格百分数

,SUM(CASE
WHEN
C#
=
'002'
THEN score ELSE 0 END)/SUM(CASE
C#
WHEN
'002'
THEN
1
ELSE
0 END)
AS
马
克思平均分

,100 *
SUM(CASE
WHEN
C# =
'002'
AND
score >=
60
THEN
1 ELSE 0 END)/SUM(CASE
WHEN
C#
=
'002'
THE
N
1
ELSE 0 END)
AS
马克思及格百分数

,SUM(CASE
WHEN
C#
=
'003'
THEN score ELSE 0 END)/SUM(CASE
C#
WHEN
'003'
THEN
1
ELSE
0 END)
AS U
ML
平均分

,100 *
SUM(CASE
WHEN
C# =
'003'
AND
score >=
60
THEN
1 ELSE 0 END)/SUM(CASE
WHEN
C#
=
'003'
THE
N
1
ELSE 0 END)
AS
UML
及格百分数

,SUM(CASE
WHEN
C#
=
'004'
THEN score ELSE 0 END)/SUM(CASE
C#
WHEN
'004'
THEN
1
ELSE
0 END)
AS
数
据库平均分

,100 *
SUM(CASE
WHEN
C# =
'004'
AND
score >=
60
THEN
1 ELSE 0 END)/SUM(CASE
WHEN
C#
=
'004'
THE
N
1
ELSE 0 END)
AS
数据库及格百分数

FROM
SC

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


SELECT
max(Z.T#)
AS
教师
ID,MAX()
AS
教师姓名
,C.C# AS
课程ＩＤ
,MAX()
AS
课程名称
,AVG(S
core)
AS
平均成绩

FROM
SC
AS
T,Course
AS
C
,Teacher
AS
Z

where
T.C#=C.C#
and
C.T#=Z.T#

GROUP
BY
C.C#

ORDER
BY AVG(Score)
DESC

22
、查询如下课程成绩第

3
名到第

6
名的学生成绩单：企业管理（
001），马
克思（
002
），
UML
（
003
），数据库（
004
）


[< br>学生
ID],[
学生姓名
],
企业管理
,
马克思,UML,
数据库
,
平均成绩

SELECT
DISTINCT
top 3

SC.S#
As
学生学号
,

AS
学生姓名

,