潘仪君2019最全SQL经典面试题集

2021年1月21日发(作者：宗裕昆)
2019
最全
SQL
经典面试题集

2019.3
表（
MYSQL
）

Student(sid,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(cid,Cname,tid)
课程表


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(sid,cid,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(tid,Tname)
教师表


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

insert

into
taacher(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);

ORACLE(
表
+
数据
)
CREATE

TABLE
student (

sid

varchar2
(
10
)
NOT

NULL
,
sName
varchar2
(
20
)
DEFAULT

NULL
,
sAge
date
,
sSex
varchar2
(
10
)
DEFAULT

NULL
,

PRIMARY

KEY
(
sid
)
)

CREATE

TABLE
course (
cid
varchar2
(
10
)
NOT

NULL
,
cName
varchar2
(
10
)
DEFAULT

NULL
,
tid
number
(
20
)
DEFAULT

NULL
,

PRIMARY

KEY
(cid)
)

CREATE

TABLE
sc (

sid

varchar2
(
10
)
DEFAULT

NULL
,
cid
varchar2
(
10
)
DEFAULT

NULL
,
score
number
(
10
)
DEFAULT

NULL

)


CREATE

TABLE
teacher (
tid
number
(
10
)
DEFAULT

NULL
,
tName
varchar2
(
10
)
DEFAULT

NULL

)

insert

into
course(cid,cName,tid)
values
(
'001'
,
'
企业管理
'
,
3
);
insert

into
course(cid,cName,tid)
values
(
'002'
,
'
马克思
'
,
3
);
insert

into
course(cid,cName,tid)
values
(
'004'
,
'
数据库
'
,
1
);
insert

into
course(cid,cName,tid)
values
(
'005'
,
'
英语
'
,
1
);

insert

into
sc(
sid
,cid,score)
values
(
'1001'
,
'001'
,
80
);
insert

into
sc(
sid
,cid,score)
values
(
'1001'
,
'002'
,
60
);
insert

into
sc(
sid
,cid,score)
values
(
'1001'
,
'003'
,
70
);
insert

into
sc(
sid
,cid,score)
values
(
'1002'
,
'001'
,
85
);
insert

into
sc(
sid
,cid,score)
values
(
'1002'
,
'002'
,
70
);
insert

into
sc(
sid
,cid,score)
values
(
'1003'
,
'004'
,
90
);
insert

into
sc(
sid
,cid,score)
values
(
'1003'
,
'001'
,
90
);
insert

into
sc(
sid
,cid,score)
values
(
'1003'
,
'002'
,
99
);
insert

into
sc(
sid
,cid,score)
values
(
'1004'
,
'002'
,
65
);
insert

into
sc(
sid
,cid,score)
values
(
'1004'
,
'003'
,
50
);
insert

into
sc(
sid
,cid,score)
values
(
'1005'
,
'005'
,
80
);
insert

into
sc(
sid
,cid,score)
values
(
'1005'
,
'004'
,
70
);
insert

into
sc(
sid
,cid,score)
values
(
'1003'
,
'003'
,
10
);
insert

into
sc(
sid
,cid,score)
values
(
'1003'
,
'005'
,
10
);


insert

into
student(
sid
,sName,sAge,sSex)
values
(
'1001'
,
'
张三丰
',to_date(
'1980-10-12 23:12:36'
,
'YYYY-MM-DD HH24:MI:SS'
),
'
男
'
);
insert

into
student(
sid
,sName,sAge,sSex)
values
(
'1002'
,
'
张无极
',to_date(
'1995-10-12 23:12:36'
,
'YYYY-MM-DD HH24:MI:SS'
),
'
男
'
);
insert

into
student(
sid
,sName,sAge,sSex)
values
(
'1003'
,
'
李奎
'
,to_date(
'1992-10-12 23:12:36'
,
'YYYY- MM-DD HH24:MI:SS'
),
'
女
'
);
insert

into
student(
sid
,sName,sAge,sSex)
values
(
'1004'
,
'
李元宝
',to_date(
'1980-10-12 23:12:36'
,
'YYYY-MM-DD HH24:MI:SS'
),
'
女
'
);
insert

into
student(
sid
,sName,sAge,sSex)
values
(
'1005'
,
'
李世明
',to_date(
'1981-10-12 23:12:36'
,
'YYYY-MM-DD HH24:MI:SS'
),
'
男
'
);
insert

into
student(
sid
,sName,sAge,sSex)
values
(
'1006'
,
'
赵六
'
,to_date(
'1986-10-12 23:12:36'
,
'YYYY- MM-DD HH24:MI:SS'
),
'
男
'
);
insert

into
student(
sid
,sName,sAge,sSex)
values
(
'1007'
,
'
田七
'
,to_date(
'1981-10-12 23:12:36'
,
'YYYY- MM-DD HH24:MI:SS'
),
'
女
'
);

insert

into
teacher(tid,tName)
values
(
1
,
'
李老师
'
);
insert

into
teacher(tid,tName)
values
(
2
,
'
何以琛
'
);
insert

into
teacher(tid,tName)
values
(
3
,
'
叶平
'
);










问题：


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

select

from
(select
sid,score
from
SC
where
cid='001')
a,(select
sid,score

from
SC
where
cid='002')
b

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

select
sid,avg(score)

from
sc

group
by
sid
having
avg(score) >60;

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

select
,,count(),sum(score)

from
Student
left
Outer
join
SC
on
=

group
by
,Sname

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

select
count(distinct(Tname))

from
Teacher

where
Tname
like
'
李
%';

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

select
,

from
Student

where
sid
not
in
(select
distinct( )
from
SC,Course,Teacher
where
=
and
=Cour

and
='
叶平
');

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

A:select
,
from
Student,SC
where
=
and
='001'and
exists(
Select

*
from
SC
as
SC_2 where
SC_=
and
SC_='002');

B:SELECT
,

FROM

student
s,
(SELECT
sid,COUNT(cid)
FROM

sc
WHERE
cid
IN ('001','002')
GROUP
BY sid
HAVING
CO
UNT(cid)>=2)
t
WHERE =

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

select
sid,Sname

from
Student

where
sid
in
(select
sid
from
SC ,Course
,Teacher
where
=
and
=
and
Teac
='
叶平
'
group
by
sid
having
count()=(select
count(cid)
from
Course,Teacher where
=

and
Tname='
叶平
'));

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

1>Select
sid,Sname
from
(select ,,score
,(select
score
from
SC
SC_2 where
SC_=S

and
SC_='002')
score2

from
Student,SC
where
=
and
cid='001')
S_2
where
score2
2>SELECT , FROM
student
s,

(SELECT
sid,score
FROM
sc
WHERE
cid =
'001')
sc_1,
(SELECT
sid,score
FROM
sc
WHERE
cid =
'002')
sc_2
WHERE
sc_ =
sc_
AND
= sc_
AND
sc_
<
sc_
9
、查询所有课程成绩小于
60
分的同学的学号、姓名
;

select
sid,Sname

from
Student

where
sid
not
in
(select

from
Student,SC
where
=
and
score>60);

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

1>
select
,

from
Student,SC

where
=
group
by
,
having
count(cid)
<(select
count(cid)
from
Cours
e);
2>
SELECT ,
FROM student
s,
(SELECT
sid,COUNT(cid)
FROM
sc
GROUP
BY
sid
HAVING
COUNT(cid) < (SELECT
COUNT(cid)
FROM
course)
)t
WHERE


=


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

select
sid,Sname
from
Student,SC
where
=
and
cid
in
(select
cid
from
SC
where
sid='1001');