SQL經典面試題集（二）
第十一題：
有表students(name,class,grade),請用標準sql語句完成
name class grade
張三 數學 81
李四 語文 70
王五 數學 90
張三 語文 60
李四 數學 100
王五 語文 90
王五 英語 81
要求: 用sql語句輸出各門功課都大于80分的同學姓名?
create table students (
name varchar(25),
class varchar(25),
grade int
)
insert into students values ('張三','語文',20)
insert into students values ('張三','數學',90)
insert into students values ('張三','英語',50)
insert into students values ('李四','語文',81)
insert into students values ('李四','數學',60)
insert into students values ('李四','英語',90)
insert into students values ('王二','數學',81)
insert into students values ('王二','英語',90)
insert into students values ('李五','數學',83)
insert into students values ('李五','英語',90)
insert into students values ('李五','化學',90)
---選出所有成績大于80分的學生姓名-----
------解法一------
select name from students group by name having min(grade)>80
------解法二------
select distinct Name from students where grade >80 and Name not in (select Name from students where grade <80)
------解法三------
select distinct name from students where name not in (select name from students where grade <=80 group by name )
-----解法四-------
select name from students group by name having name not in (select name from students where grade<=80)
第十二題：
已知一個表的結構為：
姓名 科目 成績
張三 語文 20
張三 數學 30
張三 英語 50
李四 語文 70
李四 數學 60
李四 英語 90
怎樣通過select語句把他變成以下結構：
姓名 語文 數學 英語
張三 20 30 50
李四 70 60 90
create table students (
name varchar(25),
class varchar(25),
grade int
)
insert into students values ('張三','語文',20)
insert into students values ('張三','數學',90)
insert into students values ('張三','英語',50)
insert into students values ('李四','語文',81)
insert into students values ('李四','數學',60)
insert into students values ('李四','英語',90)
--解答：
select A.Name,A.grade as 語文,B.grade as 數學,C.grade as 英語
from students A,students B,students C
where A.Name=B.Name and B.Name=C.Name
and A.class='語文' and B.class='數學'
and C.class='英語'
第十三題：
我現在有兩張表個表
create table userinfo
(
id int,
username varchar(32),
u_id int
)
create table checkinfo
(
id int,
checktype varchar(32) --出勤的類型（正常，病假，事假）
u_id int
)
兩張表通過u_id關聯的
怎么查詢出每個用戶的某個月出勤的情況：
比如說，1月份，正常出勤多少天，事假多少天，病假多少天?
例如：
username 病假（天數） 事假（天數） 病假（天數）
張三 15 5 2
create table userinfo
(
id int,
username varchar(32),
u_id int
)
create table checkinfo
(
id int,
checktype varchar(32), --出勤的類型（正常，病假，事假）
u_id int
)
delete from userinfo
insert into userinfo values(1,'user1',1)
insert into userinfo values(2,'user2',2)
insert into userinfo values(3,'user3',3)
insert into userinfo values(4,'user4',4)
insert into checkinfo values(1,'正常',1)
insert into checkinfo values(2,'正常',1)
insert into checkinfo values(3,'病假',1)
insert into checkinfo values(4,'正常',2)
insert into checkinfo values(5,'事假',2)
insert into checkinfo values(6,'病假',2)
insert into checkinfo values(7,'正常',2)
insert into checkinfo values(8,'病假',2)
insert into checkinfo values(9,'正常',3)
insert into checkinfo values(10,'事假',3)
insert into checkinfo values(11,'病假',3)
insert into checkinfo values(12,'正常',3)
insert into checkinfo values(13,'正常',3)
insert into checkinfo values(14,'正常',3)
insert into checkinfo values(15,'正常',3)
insert into checkinfo values(16,'病假',3)
insert into checkinfo values(17,'正常',4)
insert into checkinfo values(18,'事假',4)
insert into checkinfo values(19,'病假',4)
insert into checkinfo values(20,'正常',4)
insert into checkinfo values(21,'事假',4)
insert into checkinfo values(22,'病假',4)
insert into checkinfo values(23,'事假',4)
insert into checkinfo values(24,'病假',4)
---解法一:
select b.*,m.正常,m.事假,m.病假
from userinfo b
join
(select a.u_id,
count(case when a.checktype='病假' then '1' end ) 病假 ,
count(case when a.checktype='正常' then '1' end ) 正常 ,
count(case when a.checktype='事假' then '1' end ) 事假
from checkinfo a group by a.u_id) m
on m.u_id=b.u_id
---解法二:
select b.* ,m1.正常,m2.病假,m3.事假 from userinfo b
left join (select a.u_id, count(a.checktype) 正常 from checkinfo a where a.checktype='正常' group by a.u_id ) m1 on b.u_id=m1.u_id
left join (select a.u_id, count(a.checktype) 病假 from checkinfo a where a.checktype='病假' group by a.u_id ) m2 on b.u_id=m2.u_id
left join (select a.u_id, count(a.checktype) 事假 from checkinfo a where a.checktype='事假' group by a.u_id ) m3 on b.u_id=m3.u_id
第十四題：
產品 顏色 數量
產品1 紅色 100
產品1 藍色 80
產品2 藍色 103
產品2 紅色 NULL
產品2 紅色 89
產品1 紅色 100
1：按產品分類，僅列出各類商品中紅色多于藍色的商品名稱及差額數量
create table products(
name varchar(20),
color char(20),
quantities int
)
insert into products values('產品1','紅色',100)
insert into products values('產品1','藍色',80)
insert into products values('產品2','紅色',null)
insert into products values('產品2','藍色',103)
insert into products values('產品2','紅色',89)
insert into products values('產品1','紅色',100)
-----解答:
---第一步：查詢出每種商品中蘭色和紅色數量及產品名稱
--紅色：
select name,sum(quantities) from products where color='紅色' group by name
--藍色：
select name,sum(quantities) from products where color='藍色' group by name
---第二步：查詢出要求的結果：
select t1.name,t1.x-t2.x as balance
from
(select name,sum(quantities) as x from products where color='紅色' group by name) t1,
(select name,sum(quantities) as x from products where color='藍色' group by name) t2
where t1.x >t2.x and t1.name=t2.name
第十五題：
--查詢學生表中，選修課超過5門的名字!
create table students (
id int IDENTITY,
name varchar(20),
elective_course varchar(20)
)
insert into students values('student1','course1')
insert into students values('student1','course2')
insert into students values('student1','course3')
insert into students values('student1','course4')
insert into students values('student1','course6')
insert into students values('student1','course6')
insert into students values('student2','course1')
insert into students values('student2','course2')
insert into students values('student2','course3')
insert into students values('student2','course4')
insert into students values('student2','course5')
insert into students values('student3','course1')
insert into students values('student3','course2')
insert into students values('student3','course3')
insert into students values('student3','course4')
insert into students values('student4','course1')
insert into students values('student4','course2')
insert into students values('student4','course3')
insert into students values('student4','course4')
insert into students values('student4','course5')
insert into students values('student4','course6')
insert into students values('student4','course7')
insert into students values('student5','course2')
insert into students values('student5','course3')
insert into students values('student5','course4')
insert into students values('student5','course5')
insert into students values('student5','course6')
insert into students values('student5','course7')
insert into students values('student5','course8')
insert into students values('student5','course9')
insert into students values('student6','course7')
insert into students values('student6','course8')
insert into students values('student6','course9')
--解答:
select name from students group by name having count(elective_course)>=5
第十六題：
DbTable表有三列，id，name，data，其中name列里每行都含有'{data}',如第一行里為'aa{data}bb'，第二行為'abc{data}cd'，要求用對應data列的
數據替換掉'{data}'，sql怎么寫？
create table DbTable (
ID int IDENTITY,
name varchar(20),
data varchar(10)
)
insert into DbTable values ('a1{data}bb','1')
insert into DbTable values ('a2{data}bb','2')
insert into DbTable values ('a3{data}bb','3')
insert into DbTable values ('a4{data}bb','4')
insert into DbTable values ('a5{data}bb','5')
--解答:
update DbTable set name=replace(name,'{data}',data)
select * from DbTable
第十七題：
存在表table（FID，FCLASS，FSSCORE），三字段分別代表姓名、班級、成績。用最高效、最簡單的SQL語句列出人數大于30的各班最高成績的列表，顯示
班級、成績兩個字段。
create table F3 (
FID varchar(20),
FLASS varchar(20),
FSSCORE int
)
insert into F3 values ('S_Name1','Class1',67)
insert into F3 values ('S_Name2','Class1',57)
insert into F3 values ('S_Name3','Class1',27)
insert into F3 values ('S_Name4','Class1',37)
insert into F3 values ('S_Name5','Class1',97)
insert into F3 values ('S_Name6','Class2',67)
insert into F3 values ('S_Name7','Class2',57)
insert into F3 values ('S_Name8','Class2',27)
insert into F3 values ('S_Name9','Class2',37)
insert into F3 values ('S_Name10','Class2',97)
insert into F3 values ('S_Name11','Class2',37)
insert into F3 values ('S_Name112','Class2',97)
insert into F3 values ('S_Name17','Class3',57)
insert into F3 values ('S_Name18','Class3',27)
insert into F3 values ('S_Name19','Class3',37)
insert into F3 values ('S_Name110','Class3',88)
insert into F3 values ('S_Name111','Class3',37)
insert into F3 values ('S_Name1112','Class3',67)
insert into F3 values ('S_Name117','Class4',57)
insert into F3 values ('S_Name118','Class4',27)
insert into F3 values ('S_Name119','Class4',37)
insert into F3 values ('S_Name1110','Class4',82)
insert into F3 values ('S_Name1111','Class4',37)
insert into F3 values ('S_Name11112','Class4',67)
insert into F3 values ('S_Name11111','Class5',37)
insert into F3 values ('S_Name111112','Class5',67)
---解答：為了便于組裝測試數據，這里一以5為人數
--解法一：
select F3.FLASS, Max(FSSCORE) from F3 group by FLASS having count(*) >=5
--解法二：
--第一步：查詢出人數大于5的班級--
select FLASS ,count(*) as Total from F3 group by FLASS having count(*) >= 5
--第二步：查詢出所有人數大于5的班級的所有學生記錄--
select * from F3 where FLASS in (select FLASS from F3 group by FLASS having count(*) >= 5 )
--第三步：通過對第二步的記錄根據FCLASS分組查詢--
select FLASS, Max(FSSCORE) from F3 where FLASS in (select FLASS from F3 group by FLASS having count(*) >= 5 ) group by FLASS
--解法三：
select FLASS,max(fsscore) from
(
select * from F3
where FLASS in (select FLASS from F3 group by FLASS having count(*)>=5)
) T group by FLASS
第十八題：
有一張老師表Teachers，字段是T_ID,T_NAME；有一張學生表Students，字段是S_ID,S_NAME；還有一張班級表Classes，字段是T_ID,S_ID,C_NAME，其中
C_NAME的取值只有‘大班’和‘小班’，請查詢出符合條件的老師的名字，條件是老師在大班中帶的學生數大于此老師在小班中帶的學生數。
create table Teachers (
T_ID int,
T_NAME varchar(20)
)
create table Students (
S_ID int,
S_NAME varchar(20)
)
create table Classes (
T_ID int,
S_ID int,
C_NAME varchar(20)
)
insert into Teachers values(1,'T1')
insert into Teachers values(2,'T2')
insert into Teachers values(3,'T3')
insert into Teachers values(4,'T4')
insert into Teachers values(5,'T5')
insert into Students values(1,'S1')
insert into Students values(2,'S1')
insert into Students values(3,'S1')
insert into Students values(4,'S1')
insert into Students values(5,'S1')
insert into Students values(6,'S1')
insert into Students values(7,'S1')
insert into Students values(8,'S1')
insert into Students values(9,'S1')
insert into Students values(10,'S1')
insert into Students values(11,'S1')
insert into Students values(12,'S1')
insert into Students values(13,'S1')
insert into Students values(14,'S1')
insert into Students values(15,'S1')
insert into Students values(16,'S1')
insert into Classes values(1,1,'大班')
insert into Classes values(1,2,'大班')
insert into Classes values(1,3,'小班')
insert into Classes values(1,4,'大班')
insert into Classes values(1,13,'大班')
insert into Classes values(1,14,'大班')
insert into Classes values(1,15,'小班')
insert into Classes values(1,16,'大班')
insert into Classes values(2,1,'大班')
insert into Classes values(2,2,'小班')
insert into Classes values(2,3,'大班')
insert into Classes values(2,4,'大班')
insert into Classes values(2,16,'小班')
insert into Classes values(2,15,'小班')
insert into Classes values(2,14,'小班')
insert into Classes values(3,5,'大班')
insert into Classes values(3,6,'小班')
insert into Classes values(3,7,'大班')
insert into Classes values(4,4,'大班')
insert into Classes values(4,5,'大班')
insert into Classes values(4,6,'小班')
insert into Classes values(4,7,'小班')
insert into Classes values(4,8,'小班')
insert into Classes values(5,9,'大班')
insert into Classes values(5,10,'小班')
insert into Classes values(5,11,'小班')
insert into Classes values(5,12,'小班')
--第一步：查詢出每個老師所帶的小班的人數--------
select T_ID,count(*) as x from Classes where C_Name='小班' group by T_ID
--第二步：查詢出每個老師所帶的大班的人數--------
select T_ID,count(*) as x from Classes where C_Name='大班' group by T_ID
--第三步：在上面一二步的基礎上查詢出大班人數大于小班人數的老師------------
select T_NAME
from Teachers t,
(select T_ID,count(*) as x from Classes where C_Name='小班' group by T_ID) T1,
(select T_ID,count(*) as x from Classes where C_Name='大班' group by T_ID) T2
where T1.x<T2.x
and T1.T_ID=T2.T_ID and t.T_ID=T1.T_ID
--考察要點：1.分組查詢. 2.把查詢出來的某些結果作為表來連接查詢出相關結果.
第十九題：
前提:a 部門表 b 員工表
a表字段(
id --部門編號
departmentName-部門名稱
)
b表字段(
id--部門編號
employee- 員工名稱
)
問題:如何一條sql語句查詢出每個部門共有多少人?
*/
create table departments(
ID int IDENTITY,
Name varchar (20),
)
create table employees(
ID int,
Name varchar (20)
)
insert into departments values ('DeparmentA')
insert into departments values ('DeparmentB')
insert into departments values ('DeparmentC')
insert into departments values ('DeparmentD')
insert into departments values ('DeparmentE')
insert into employees values (1,'Zhang3')
insert into employees values (1,'Zhang4')
insert into employees values (1,'Zhang5')
insert into employees values (2,'Li3')
insert into employees values (2,'Li4')
insert into employees values (2,'Li5')
insert into employees values (2,'Li6')
insert into employees values (3,'Zhao3')
insert into employees values (3,'Zhao4')
insert into employees values (3,'Zhao5')
insert into employees values (4,'Chen4')
insert into employees values (4,'Chen5')
insert into employees values (5,'Zhu4')
--解法一----
select b.id,a.Name,count(b.id)as employeecount from departments a left join employees b on a.id=b.id group by b.id,a.Name
---或
select b.id,a.Name,count(*)as employeecount from departments a left join employees b on a.id=b.id group by b.id,a.Name
---解法二---
select t.id as 'Id', t.name as 'Name',count (*) as 'EmployeeCount' from (select d.id,d.name from departments as d inner join employees as e on d.id=e.id) t group by t.id,t.name
--解法三----
select a.id,a.Name,count(b.id)as employeecount from departments a left join employees b on a.id=b.id group by a.id,a.Name
第二十題：
在Oracle數據庫中有一張表A
編號 名稱
1 a
2 b
3 c
4 d
如何寫一條SQL語句，顯示以下結果
ab,ac,ad,bc,cd
drop table B select b.id,b.name from B b
create table B (
id int IDENTITY,
name varchar (20)
)
insert into B values ('a')
insert into B values ('b')
insert into B values ('c')
insert into B values ('d')
--SQL Server:
select a.name+b.name,a.name+c.name,a.name+d.name,b.name+c.name,c.name+d.name from B a, B b,B c,B d where a.id=1 and b.id=2 and c.id=3 and d.id=4
--Oracle:
select distinct a.name||b.name||','||a.name||c.name||','||a.name||d.name||','||b.name||c.name||','||c.name||d.name
from B a,B b,B c,B d
where a.number=1 and b.number=2 and c.number=3 and d.number=4
--其它參考：如果要求你在同一列顯示呢？
ab
ac
ad
bc
bd
cd
--參考答案：
select a.name+b.name from B a,B b
where a.id<b.id group by a.name+b.name
一天，一個月，一年?？傆幸惶鞎兊貌灰粯印?/p>