SQL经典面试题集(一)

第一题：

为管理业务培训信息，建立3个表：

S(S#,SN,SD,SA)S#,SN,SD,SA分别代表学号，学员姓名，所属单位，学员年龄C(C#,CN)C#,CN分别代表课程编号，课程名称

SC(S#,C#,G) S#,C#,G分别代表学号，所选的课程编号，学习成绩

(1)使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名?

(2) 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位?

(3) 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位?

(4) 查询选修了课程的学员人数?

(5) 查询选修课程超过5门的学员学号和所属单位?

drop table S;

drop table C;

drop table SC;

create table S

(

S# varchar(10),

SN varchar (25),

SD varchar (25),

SA int

)

create table C

(

C# varchar(10),

CN varchar (25)

)

create table SC

(

S# varchar(10),

C# varchar(10),

G int

Primary Key(S#, C#)

)

insert into S values ('10001','Students1','department1',23)

insert into S values ('10002','Students2','department1',24)

insert into S values ('10003','Students3','department2',25)

insert into S values ('10004','Students4','department2',26)

insert into S values ('10005','Students5','department3',23)

insert into S values ('10006','Students6','department3',24)

insert into S values ('10007','Students7','department3',25)

insert into S values ('10008','Students8','department4',25)

insert into C values ('C1','数学')

insert into C values ('C2','物理')

insert into C values ('C3','化学')

insert into C values ('C4','英语')

insert into C values ('C5','中文')

insert into C values ('C6','税收基础')

insert into C values ('C7','传媒')

insert into C values ('C8','日语')

insert into SC values ('10001','C1',67)

insert into SC values ('10001','C2',77)

insert into SC values ('10001','C3',87)

insert into SC values ('10001','C4',97)

insert into SC values ('10001','C5',57)

insert into SC values ('10001','C6',47)

insert into SC values ('10002','C1',62)

insert into SC values ('10002','C2',72)

insert into SC values ('10002','C3',82)

insert into SC values ('10002','C4',92)

insert into SC values ('10002','C5',52)

insert into SC values ('10002','C6',42)

insert into SC values ('10004','C2',74)

insert into SC values ('10004','C5',54)

insert into SC values ('10004','C6',44)

--（1）使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名?

--解法一:

select S#,SN from S where S# in (select S# from C, SC where C.C#=SC.C# and ='税收基础')

--解法二:

select S.S#,S.SN from S inner join (select S# from C left join SC on C.C#=SC.C# where ='税收基础') T on T.S#=S.S#

--(2) 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位?

--解答：

select S.SN,S.SD from S,SC where S.S#=SC.S# and SC.C#='C2'

--(3) 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位?

--解答：

select distinct S.SN,S.SD from S where S.S# not in (select S.S# from S,SC where S.S#=SC.S# and SC.C#='C5')

--(4) 查询选修了课程的学员人数?

--解法一:

select 学员人数=count(distinct s#) from sc

--解法二:

select count(*) as 学员人数from (select distinct SC.S# from SC) t

--(5) 查询选修课程超过5门的学员学号和所属单位?

--解法一:

select S#,SD from S where S.S# in (select SC.S# from SC group by SC.S# having count(*)>5) --解法二:

select S#,SD from S where S# in(select S# from SC group by S# having count(distinct C#)>5)

第二题：

create table testtable1

(

id int IDENTITY,

department varchar(12)

)

insert into testtable1 values('设计')

insert into testtable1 values('市场')

insert into testtable1 values('售后')

结果：

id department

1 设计

2 市场

3 售后