SQL经典面试题集（一）

                                    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 C.CN='税收基础')
  --解法二:
  select S.S#,S.SN from S inner join (select S# from C left join SC  on  C.C#=SC.C# where C.CN='税收基础') 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   售后

 

create table testtable2
(
id int IDENTITY,
dptID int,
name varchar(12)
)
insert into testtable2 values(1,'张三')
insert into testtable2 values(1,'李四')
insert into testtable2 values(2,'王五')
insert into testtable2 values(3,'彭六')
insert into testtable2 values(4,'陈七')
insert into testtable2 values(5,'陈七')

select t2.id,t2.dptID,t1.department,t2.name from testtable2 t2  left join  testtable1 t1 on t1.id=t2.dptID

select * from testtable2

用一条SQL语句，怎么显示如下结果
id dptID department name
1   1      设计        张三
2   1      设计        李四
3   2      市场        王五
4   3      售后        彭六
5   4      黑人        陈七

--解答：
  --解法一:                                             
  select t2.id,t2.dptID,t1.department,t2.name from testtable2 t2  left join  testtable1 t1 on t1.id=t2.dptID
  --解法二:
  SELECT t2.id , t2.dptID, ISNULL(t1.department,'黑人') dptName,t2.name FROM testtable1 t1 right join testtable2 t2 on t2.dptID = t1.ID

  --注意下面两个语句查询结果与上面答案的区别
  select t2.id,t2.dptID,t1.department,t2.name from testtable1 t1,testtable2 t2 where t1.id=t2.dptID
  select t2.id,t2.dptID,t1.department,t2.name from testtable2 t2  inner join  testtable1 t1 on t1.id=t2.dptID

 

第三题：
有表A，结构如下：
A: p_ID p_Num s_id
1 10 01
1 12 02
2 8 01
3 11 01
3 8 03
其中：p_ID为产品ID，p_Num为产品库存量，s_id为仓库ID。请用SQL语句实现将上表中的数据合并，合并后的数据为：
p_ID s1_id s2_id s3_id
1 10 12 0
2 8 0 0
3 11 0 8
其中：s1_id为仓库1的库存量，s2_id为仓库2的库存量，s3_id为仓库3的库存量。如果该产品在某仓库中无库存量，那么就是0代替。

create table A
(
 p_ID  int,
 p_Num  int,
 s_id int
)

insert into A values(1,10,01)
insert into A values(1,12,02)
insert into A values(2,8,01)
insert into A values(3,11,01)
insert into A values(3,8,03)

--解答：
select p_id ,
       sum(case when s_id=1 then p_num else 0 end) as s1_id,
       sum(case when s_id=2 then p_num else 0 end) as s2_id,
       sum(case when s_id=3 then p_num else 0 end) as s3_id
from A group by p_id

 

第四题：
--1.查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不是连续增长的列？

create table A
(
 id  int IDENTITY,
 Name  varchar (25)
)

--1.查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不是连续增长的列？
--解答：
  select top 10 * from A where ID >(select max(ID) from (select top 30 ID from A order by id ) T) order by id
 
第五题：
--查询A(ID,Name)表中存在ID重复三次以上的记
drop table A
create table A
(
 id  int,
 Name  varchar (25)
)

insert into A values(1,'a')
insert into A values(2,'a')
insert into A values(3,'a')
insert into A values(1,'a')
insert into A values(2,'a')
insert into A values(3,'a')
insert into A values(4,'a')
insert into A values(1,'a')
--解答：
select id,name from A where id in (select id from A group by id having count(id)>3)order by id

第六题：
原表Course:
courseid coursename score
-------------------------------------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
-------------------------------------
为了便于阅读,查询此表后的结果显式如下(及格分数为60):
courseid coursename score mark
---------------------------------------------------
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
---------------------------------------------------
写出此查询语句。

create table Course(
  courseid int IDENTITY,
  coursename varchar (25),
  score int
)

insert into Course values ( 'java',70)
insert into Course values ( 'oracle',90)
insert into Course values ( 'xml',40)
insert into Course values ( 'jsp',30)
insert into Course values ( 'servlet',80)

--解答：
--oracle：
select courseid, coursename ,score ,decode（sign(score-60),-1,'fail','pass') as mark from course

 --SQL Server：
select *, (case when score<60 then 'failed' else 'pass' end) as mark from Course

第七题：
 有表：emp(id, name, age)
 要求：列出所有名字重复的人的记录?

create table emp(
   id int IDENTITY,
   name varchar (25),
   age int
)

insert into emp values('Zhang1',26)
insert into emp values('Zhang2',27)
insert into emp values('Zhang3',28)
insert into emp values('Zhang1',26)
insert into emp values('Zhang2',27)
insert into emp values('Zhang3',29)
insert into emp values('Zhang1',26)
insert into emp values('Zhang2',27)
insert into emp values('Zhang3',28)
insert into emp values('Zhang1',26)
insert into emp values('Zhang4',22)
insert into emp values('Wang1',27)
insert into emp values('wang2',28)
insert into emp values('Wang2',26)
insert into emp values('Wang1',22)

--列出所有名字重复的人的记录?
--解法一：要知道所有名字有重复人资料，首先必须知道哪个名字重复了：
select id,name,age from emp where name in (select name from emp group by name having count(*)>1)

--解法二：如果对每个名字都和原表进行比较，大于2个人名字与这条记录相同的就是合格的 ，就有：
select * from emp where (select count(*) from emp e where e.name=emp.name)>1

--解法三：如果有另外一个名字相同的人工号不与她他相同那么这条记录符合要求：
select * from emp where exists (select * from emp e  where e.name=emp.name and e.id<>emp.id)
--或：
select distinct emp.* from emp inner join emp e on emp.name=e.name and emp.id<>e.id

 

第八题：
有例表：emp(name,age)
 Tom   16
 Sun   14    
 Tom   16    
 Tom   16
要求：过滤掉所有多余的重复记录

create table emp(
 name varchar(20),
 age int
)

insert into emp values('Tom',16)
insert into emp values('Sun',14)
insert into emp values('Tom',16)
insert into emp values('Tom',16)

--解法一：通过distinct、group by过滤重复：
select distinct * from  emp    
或 
select name,age from emp group by name,age

--获得需要的数据，如果可以使用临时表就有解法：
select distinct * into #tmp from emp
delete from emp
insert into emp select *  from #tmp

--但是如果不可以使用临时表，那该怎么办？
alter table emp add chk int identity(1,1)
--重复记录可以表示为：
select * from  emp  where (select count(*) from emp e where e.name=emp.name)>1
--要删除的是：
delete from emp where (select count(*) from emp e where e.name=emp.name and e.chk>=emp.chk)>1
--再把添加的列删掉，出现结果。
alter table emp drop column chk

--)另一个思路：视图
select min(chk) from emp group by name having   count(*)   >1
--获得有重复的记录chk最小的值,于是可以
delete from emp where chk not in (select min(chk) from emp group by name)

 

第九题：

有列表：emp（emp_no, name,age）
001 Tom 17    
002 Sun 14    
003 Tom 15    
004 Tom 16

要求生成序列号

create table emp(
emp_no int,
name varchar(20),
age int
)

insert into emp values(001,'Tom',17)
insert into emp values(002,'Sun',14)
insert into emp values(003,'Tom',15)
insert into emp values(004,'Tom',16)

--(1)最简单的方法：

alter table emp add chk int identity(1,1)
--或
select *,identity(int,1,1) chk into #tmp from emp
select * from emp
alter table emp drop column chk

--如果需要控制顺序怎么办？
select *,identity(int,1,1) chk  into #tmp from emp order by age
delete from emp
alter table emp add chk int
insert into emp select * from #tmp
select * from #tmp
drop table #tmp

--(2)假如不可以更改表结构，怎么办？

如果不可以唯一区分每条记录是没有办法的，
select emp.*,(select count(*) from emp e where e.emp_no<=emp.emp_no) from emp order by (select count(*) from emp e where e.emp_no<=emp.emp_no)

 

第十题：
学科表:
姓名 选课
---------------------
张三 数学
张三 物理
张三 语文
张三 化学

李四 数学
李四 化学
李四 语文

王五 数学
王五 物理
王五 语文

赵六 数学
赵六 物理
赵六 语文

周七 数学
周七 物理

问题一：只选数学，物理，语文的学生, 查询结果如下,写出相应SQL语句

姓名 选课
---------------------
王五 数学
王五 物理
王五 语文

赵六 数学
赵六 物理
赵六 语文

问题二：同时选了数学，物理，语文的学生, 查询结果如下,写出相应SQL语句

姓名 选课
---------------------
张三 数学
张三 物理
张三 语文

王五 数学
王五 物理
王五 语文

赵六 数学
赵六 物理
赵六 语文

create table course(
  Name varchar(25),
  CName varchar(25)
)

insert into course values ('张三','数学')
insert into course values ('张三','物理')
insert into course values ('张三','语文')
insert into course values ('张三','化学')

insert into course values ('李四','数学')
insert into course values ('李四','语文')
insert into course values ('李四','化学')

insert into course values ('王五','数学')
insert into course values ('王五','物理')
insert into course values ('王五','语文')

insert into course values ('赵四','数学')
insert into course values ('赵四','物理')
insert into course values ('赵四','语文')

insert into course values ('周七','数学')
insert into course values ('周七','物理')

select * from course

--问题一：只选数学，物理，语文的学生, 查询结果如下,写出相应SQL语句------

--解法一：
select A.Name,B.CName from
  (select T.Name from (select Name,CName from Course where CName in('数学','物理','语文'))T group by Name having count(*)=3 )A,
  (select Name,CName from Course where CName in('数学','物理','语文'))B
where A.Name=B.Name
      and A.Name not in (select Name from Course group by Name having count(*)>3 )
--解法二：
select * from course
where name in (select name from course where CName in('数学','物理','语文') group by name having count(*)=3)
      and name not in(select name from course group by name having count(*)>3)

--问题二：同时选了数学，物理，语文的学生, 查询结果如下,写出相应SQL语句---
--解法一：
select A.Name,B.CName from
  (select T.Name from (select Name,CName from Course where CName in('数学','物理','语文'))T group by Name having count(*)=3 )A,
  (select Name,CName from Course where CName in('数学','物理','语文'))B
where A.Name=B.Name

--解法二：
select * from course
where name in (select name from course where CName in('数学','物理','语文') group by name having count(*)=3)