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)
一天，一個月，一年。總有一天會變得不一樣。