SQL经典面试题集（二）

                                  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

 

 

评论

1 楼 xindianshang 2009-12-05  

很好的题啊~