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)

第十一题：
有表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
第二十题：
怎么样抽取重复记录
表：

id name

1 test1
2 test2
3 test3
4 test4
5 test5
6 test6
2 test2
3 test3
2 test2
6 test6

查出所有有重复记录的数据，用一句sql 来实现

create table D(
id varchar (20),
name varchar (20)
)

insert into D values(‘1’,‘test1’)
insert into D values(‘2’,‘test2’)
insert into D values(‘3’,‘test3’)
insert into D values(‘4’,‘test4’)
insert into D values(‘6’,‘test6’)
insert into D values(‘5’,‘test5’)
insert into D values(‘2’,‘test2’)
insert into D values(‘3’,‘test3’)
insert into D values(‘4’,‘test4’)
insert into D values(‘7’,‘test7’)
insert into D values(‘4’,‘test4’)
insert into D values(‘6’,‘test6’)
insert into D values(‘5’,‘test5’)
insert into D values(‘2’,‘test2’)
insert into D values(‘3’,‘test3’)
insert into D values(‘4’,‘test4’)
insert into D values(‘8’,‘test8’)
insert into D values(‘10’,‘test4’)

select * from D where

–解法一:
–查询出重复的记录
select id,name from D group by id,name having count()>1
–查询出重复的记录及重复次数
select a.id,a.name from D a,(select id,name from D group by id,name having count()>1) b where a.id=b.id and a.name=b.name

–解法二:
select t1.* from D t1,
(select sum(1) as Sig,id,name from D group by id,name) as t2
where t1.name=t2.name and t1.id=t2.id and t2.Sig>1

第二十一题：
已知原表(t_salary)
year salary
2000 1000
2001 2000
2002 3000
2003 4000
先要实现显示结果(salary为以前的工资和)
year salary
2000 1000
2001 3000
请问SQL语句怎么写?

create table t_salary(
year int,
salary int
)

select * from t_salary
insert into t_salary values(2000,1000)
insert into t_salary values(2001,2000)
insert into t_salary values(2002,3000)
insert into t_salary values(2003,4000)

–解答：
select year, (select sum(salary) from t_salary b where b.year <= a.year) salary from t_salary a group by year

第二十二题：
year month total
1996 1 3000
1996 3 4000
1996 7 5000
1997 3 4000
1997 6 3000
.
要求按如下格式输出：
year m1,m2,m3,m4
year 为年，m1等为季度，要求按上行输出

create table Outputs (
year char(4),
month int,
total int
)

insert into Outputs values (‘1996’,1,3000)
insert into Outputs values (‘1996’,3,4000)
insert into Outputs values (‘1996’,7,5000)

insert into Outputs values (‘1997’,3,4000)
insert into Outputs values (‘1997’,6,3000)
insert into Outputs values (‘1997’,2,3000)

insert into Outputs values (‘1998’,1,3000)
insert into Outputs values (‘1998’,4,3500)

select * from Outputs

----------------解答-------------------
–解法一：
select year,
sum(case when month<=3 then total else 0 end) as M1,
sum(case when month>3 and month<=6 then total else 0 end) as M2,
sum(case when month>6 and month<=9 then total else 0 end) as M3,
sum(case when month>9 and month<=12 then total else 0 end) as M2
from Outputs group by year

–解法二：
select year,
sum(case when month in(1,2,3) then total else 0 end) as M1,
sum(case when month in(4,5,6) then total else 0 end) as M2,
sum(case when month in(7,8,9) then total else 0 end) as M3,
sum(case when month in(10,11,12) then total else 0 end) as M2
from Outputs group by year

第二十三题：

普通行列转换
问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理

---

李四 74 84 94
张三 74 83 93

create table tb(
姓名 varchar(10) ,
课程 varchar(10) ,
分数 int)
insert into tb values(‘张三’ , ‘语文’ , 74)
insert into tb values(‘张三’ , ‘数学’ , 83)
insert into tb values(‘张三’ , ‘物理’ , 93)
insert into tb values(‘李四’ , ‘语文’ , 74)
insert into tb values(‘李四’ , ‘数学’ , 84)
insert into tb values(‘李四’ , ‘物理’ , 94)
insert into tb values(‘李四’ , ‘历史’ , 94)
go

-----解法一：SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)---------
select 姓名,
max(case when 课程=‘语文’ then 分数 end) as 语文,
max(case when 课程=‘数学’ then 分数 end) as 数学,
max(case when 课程=‘物理’ then 分数 end) as 物理
from tb
group by 姓名

-----解法二：–SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同) ---------
declare @sql varchar(8000)
set @sql = ‘select 姓名 ’
select @sql = @sql + ’ , max(case 课程 when ‘’’ + 课程 + ‘’’ then 分数 else 0 end) [’ + 课程 + ‘]’
from (select distinct 课程 from tb) as a
set @sql = @sql + ’ from tb group by 姓名’
exec(@sql)

-----解法三：SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b

-----解法四：SQL SERVER 2005 静态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ‘,’ , ‘’) + 课程 from tb group by 课程
exec (‘select * from (select * from tb) a pivot (max(分数) for 课程 in (’ + @sql + ‘)) b’)

---

问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分

---

李四 74 84 94 84.00 252
张三 74 83 93 83.33 250

–SQL SERVER 2000 静态SQL。
select 姓名 姓名,
max(case 课程 when ‘语文’ then 分数 else 0 end) 语文,
max(case 课程 when ‘数学’ then 分数 else 0 end) 数学,
max(case 课程 when ‘物理’ then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名

–SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = ‘select 姓名 ’
select @sql = @sql + ’ , max(case 课程 when ‘’’ + 课程 + ‘’’ then 分数 else 0 end) [’ + 课程 + ‘]’
from (select distinct 课程 from tb) as a
set @sql = @sql + ’ , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名’
exec(@sql)

–SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名

–SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ‘,’ , ‘’) + 课程 from tb group by 课程
exec (‘select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (’ + @sql + ‘)) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名’)

drop table tb

---

---

问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果)：
姓名 课程 分数

---

李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93

create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values(‘张三’,74,83,93)
insert into tb values(‘李四’,74,84,94)
go

–SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = ‘语文’ , 分数 = 语文 from tb
union all
select 姓名 , 课程 = ‘数学’ , 分数 = 数学 from tb
union all
select 姓名 , 课程 = ‘物理’ , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when ‘语文’ then 1 when ‘数学’ then 2 when ‘物理’ then 3 end

–SQL SERVER 2000 动态SQL。
–调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ’ union all ’ , ‘’ ) + ’ select 姓名 , [课程] = ’ + quotename(Name , ‘’’’) + ’ , [分数] = ’ + quotename(Name) + ’ from tb’
from syscolumns
where name! = N’姓名’ and ID = object_id(‘tb’) --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ’ order by 姓名 ')

–SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t

–SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。

---

问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程 分数

---

李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00

select * from
(
select 姓名 as 姓名 , 课程 = ‘语文’ , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = ‘数学’ , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = ‘物理’ , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = ‘平均分’ , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = ‘总分’ , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when ‘语文’ then 1 when ‘数学’ then 2 when ‘物理’ then 3 when ‘平均分’ then 4 when ‘总分’ then 5 end