sql经典题

loyalxuan

SQL经典面试题及答案
1.一道SQL语句面试题，关于group by表内容：

2005-05-09 胜

2005-05-09 胜

2005-05-09 负

2005-05-09 负

2005-05-10 胜

2005-05-10 负

2005-05-10 负



如果要生成下列结果, 该如何写sql语句?



                    胜 负

2005-05-09  2   2

2005-05-10  1   2

------------------------------------------

create table  #tmp(rq varchar(10),shengfu nchar(1))



insert into #tmp values('2005-05-09','胜')

insert into #tmp values('2005-05-09','胜')

insert into #tmp values('2005-05-09','负')

insert into #tmp values('2005-05-09','负')

insert into #tmp values('2005-05-10','胜')

insert into #tmp values('2005-05-10','负')

insert into #tmp values('2005-05-10','负')



1)select rq, sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when shengfu='负' then 1 else 0 end)'负' from #tmp group by rq



2) select N.rq,N.勝,M.負 from (

select rq,勝=count(*) from #tmp where shengfu='胜'group by rq)N inner join

(select rq,負=count(*) from #tmp where shengfu='负'group by rq)M on N.rq=M.rq



3)select a.col001,a.a1 胜,b.b1 负 from

(select col001,count(col001) a1 from temp1 where col002='胜' group by col001) a,

(select col001,count(col001) b1 from temp1 where col002='负' group by col001) b

where a.col001=b.col001





2.请教一个SQL语句的查询问题

表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。

------------------------------------------

select (case when a>b then a else b end ),

(case when b>c then b esle c end)

from table_name





3.一个日期判断的sql语句？

请取出tb_send表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型，包含日期与时间)

------------------------------------------

select * from tb where datediff(dd,SendTime,getdate())=0





4.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：  

   大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。  

       显示格式：  

       语文              数学                英语  

       及格              优秀                不及格   

------------------------------------------

select

(case when 语文>=80 then '优秀'

        when 语文>=60 then '及格'

else '不及格') as 语文,

(case when 数学>=80 then '优秀'

        when 数学>=60 then '及格'

else '不及格') as 数学,

(case when 英语>=80 then '优秀'

        when 英语>=60 then '及格'

else '不及格') as 英语,

from table





5.在sqlserver2000中请用sql创建一张用户临时表和系统临时表，里面包含两个字段ID和IDValues,类型都是int型，并解释下两者的区别?

------------------------------------------

用户临时表:create table #xx(ID int, IDValues int)

系统临时表:create table ##xx(ID int, IDValues int)



区别:

用户临时表只对创建这个表的用户的Session可见,对其他进程是不可见的.

当创建它的进程消失时这个临时表就自动删除.



全局临时表对整个SQL Server实例都可见,但是所有访问它的Session都消失的时候,它也自动删除.





7.请用一个sql语句得出结果

从table1,table2中取出如table3所列格式数据，注意提供的数据及结果不准确，只是作为一个格式向大家请教。

如使用存储过程也可以。



table1



月份mon 部门dep 业绩yj

-------------------------------

一月份      01      10

一月份      02      10

一月份      03      5

二月份      02      8

二月份      04      9

三月份      03      8



table2



部门dep      部门名称dname

--------------------------------

      01      国内业务一部

      02      国内业务二部

      03      国内业务三部

      04      国际业务部



table3 （result）



部门dep 一月份      二月份      三月份

--------------------------------------

      01      10        null      null

      02      10         8       null

      03      null        5        8

      04      null       null       9

------------------------------------------

1)

select a.部门名称dname,b.业绩yj as '一月份',c.业绩yj as '二月份',d.业绩yj as '三月份'

from table1 a,table2 b,table2 c,table2 d

where a.部门dep = b.部门dep and b.月份mon = '一月份' and

a.部门dep = c.部门dep and c.月份mon = '二月份' and

a.部门dep = d.部门dep and d.月份mon = '三月份' and



2)

select a.dep,

sum(case when b.mon=1 then b.yj else 0 end) as '一月份',

sum(case when b.mon=2 then b.yj else 0 end) as '二月份',

sum(case when b.mon=3 then b.yj else 0 end) as '三月份',

sum(case when b.mon=4 then b.yj else 0 end) as '四月份',

sum(case when b.mon=5 then b.yj else 0 end) as '五月份',

sum(case when b.mon=6 then b.yj else 0 end) as '六月份',

sum(case when b.mon=7 then b.yj else 0 end) as '七月份',

sum(case when b.mon=8 then b.yj else 0 end) as '八月份',

sum(case when b.mon=9 then b.yj else 0 end) as '九月份',

sum(case when b.mon=10 then b.yj else 0 end) as '十月份',

sum(case when b.mon=11 then b.yj else 0 end) as '十一月份',

sum(case when b.mon=12 then b.yj else 0 end) as '十二月份',

from table2 a left join table1 b on a.dep=b.dep


8.怎么把这样一个表儿
year  month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4
查成这样一个结果
year    m1  m2  m3  m4
1991   1.1  1.2  1.3  1.4
1992   2.1  2.2  2.3  2.4

答案一、
select year,
(select amount from  aaa m where month=1  and m.year=aaa.year) as m1,
(select amount from  aaa m where month=2  and m.year=aaa.year) as m2,
(select amount from  aaa m where month=3  and m.year=aaa.year) as m3,
(select amount from  aaa m where month=4  and m.year=aaa.year) as m4
from aaa  group by year

这个是ORACLE  中做的：
select * from (select name, year b1, lead(year) over
(partition by name order by year) b2, lead(m,2) over(partition by name order by year) b3,rank()over(
partition by name order by year) rk from t) where rk=1;


9.学生表 如下:
自动编号   学号   姓名 课程编号 课程名称 分数
1        2005001  张三  0001      数学    69
2        2005002  李四  0001      数学    89
3        2005001  张三  0001      数学    69
删除除了自动编号不同,其他都相同的学生冗余信息

A: delete tablename where 自动编号 not in(select min(自动编号) from tablename group by 学号,姓名,课程编号,课程名称,分数)



10.一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。
------------------------------------------
select id, Count（*) from tb group by id having count(*)>1

select * from(select count(ID) as count from table group by ID)T where T.count>1



11..用一条SQL语句 查询出每门课都大于80分的学生姓名  
name   kecheng   fenshu
张三     语文       81
张三     数学       75
李四     语文       76
李四     数学       90
王五     语文       81
王五     数学       100
王五     英语       90
A: select distinct name from table  where  name not in (select distinct name from table where fenshu<=80)


12.一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合.
你先按你自己的想法做一下，看结果有我的这个简单吗？
答：select a.name, b.name
from team a, team b
where a.name < b.name


13.请用SQL语句实现：从TestDB数据表中查询出所有月份的发生额都比101科目相应月份的发生额高的科目。请注意：TestDB中有很多科目，都有1－12月份的发生额。
AccID：科目代码，Occmonth：发生额月份，DebitOccur：发生额。
数据库名：JcyAudit，数据集：Select * from TestDB

答：select a.*
from TestDB a
,(select Occmonth,max(DebitOccur) Debit101ccur from TestDB where AccID='101' group by Occmonth) b
where a.Occmonth=b.Occmonth and a.DebitOccur>b.Debit101ccur


14.有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value换为A中对应的value
这道题的SQL语句怎么写？
update   b   set   b.value=(select   a.value   from   a   where   a.key=b.key)   where   b.id   in(select   b.id   from   b,a   where   b.key=a.key);


15.原表:
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
写出此查询语句

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


16.
有表a存储二叉树的节点,要用一条sql语句查出所有节点及节点所在的层.
表a
c1 c2 A ----------1
---- ---- / \
A B B C --------2
A C / / \
B D D N E ------3
C E / \ \
D F F K I ---4
E I
D K
C N


所要得到的结果如下

jd cs
----- ----
A 1
B 2
C 2
D 3
N 3
E 3
F 4
K 4
I 4

有高手指导一下,我只能用pl/sql写出来,请教用一条sql语句的写法
SQL> select c2, level + 1 lv
2 from test start
3 with c1 = 'A'
4 connect by c1 = prior c2
5 union
6 select 'A', 1 from dual
7 order by lv;

C2 LV
-- ----------
A 1
B 2
C 2
D 3
E 3
N 3
F 4
I 4
K 4

已选择9行。