Oracle经典面试SQL

小咪咪 2021-10-29 10:44 350阅读 0赞

1、关于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

答案：  
1): select rq ' ',sum(case when shengfu='胜' then 1 else 0 end) as '胜',  
sum(case when shengfu='负' then 1 else 0 end) as '负' from tb1 group by rq  
2): select N.rq,N.胜,M.负 from  
(select rq,count(\*) as 胜 from tb1 where shengfu='胜' group by rq) N inner join  
(select rq,count(\*) as 负 from tb1 where shengfu='负' group by rq) M on N.rq=M.rq  
3): select a.rq,a.胜 as 胜,b.负 as 负 from  
(select rq,count(shengfu) 胜 from tb1 where shengfu='胜' group by rq) a,  
(select rq,count(shengfu) 负 from tb1 where shengfu='负' group by rq) b where a.rq=b.rq;  
2.表中有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 else c end) from tab4  
3.一个日期判断的sql语句请取出tab5表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型，包含日期与时间)

答案：  
select \* from tab5 t where to\_char(t.SendTime,'yyyy-mm-dd')=to\_char(sysdate,'yyyy-mm-dd')  
4.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件 显示出来（并写出您的思路）：  
大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。  
显示格式：  
语文 数学 英语  
及格 优秀 不及格  
答案：  
select  
(case when语文>=80 then '优秀' when语文>60 then '及格' else '不及格' end) as 语文,  
(case when 数学>=80 then '优秀' when数学>60 then '及格' else '不及格' end) as数学,  
(case when英语>=80 then '优秀' when英语>60 then '及格' else '不及格' end) as 英语  
from tab5  
5.请用一个sql语句得出结果  
从table1,table2中取出如table3所列格式数据

table1

月份mon 部门dep 业绩yj  
\-------------------------------  
一月份 01 10  
一月份 02 10  
一月份 03 5  
二月份 02 8  
二月份 04 9  
三月份 03 8

table2

部门dep 部门名称depname  
\--------------------------------  
01 国内业务一部  
02 国内业务二部  
03 国内业务三部  
04 国际业务部

table3 （result）

部门dep 一月份 二月份 三月份  
\---------------------------------------------------  
01 10 null null  
02 10 8 null  
03 5 null 8  
04 null 9 null

\-------------------------------------------------------------------------------------------  
1)  
select t.depname,  
(select yj from tab6 where mon='一月份' and dep=t.dep) 一月份,  
(select yj from tab6 where mon='二月份' and dep=t.dep) 二月份,  
(select yj from tab6 where mon='三月份' and dep=t.dep) 三月份  
from tab7 t

\---------------------------------------------------------  
2)求总销售额  
select  
sum(case when t1.mon='一月份' then t1.yj else 0 end) 一月份,  
sum(case when t1.mon='二月份' then t1.yj else 0 end) 二月份,  
sum(case when t1.mon='三月份' then t1.yj else 0 end) 三月份  
from tab7 t,tab6 t1 where t.dep=t1.dep

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

select \* from (select tab8,count(id) as num from tab8 group by id) t where t.num>1

7.用一条SQL语句 查询出每门课都大于80分的学生姓名

name kecheng fenshu  
张三 语文 81  
张三 数学 75  
李四 语文 76  
李四 数学 90  
王五 语文 81  
王五 数学 100  
王五 英语 90

a): select distinct name from tab9 where name not in (select distinct name from tab9 where fengshu<=80)

b): select \* from tab9 t7 where t7.name not in (select t5.name from (select \* from (select t1.kecheng from tab9 t1 group by t1.kecheng),(select t2.name from tab9 t2 group by t2.name)) t4,(select \* from tab9) t5 where t4.name = t5.name and t4.kecheng = t5.kecheng and t5.fengshu < 80)  
8.一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合.

select t.bh||'vs'||t1.bh from tab10 t,tab10 t1 where t.bh<>t1.bh这个是分主客场的

select t.bh||'vs'||t1.bh from tab10 t,tab10 t1 where t.bh<>t1.bh and t.bh>t1.bh这个是不分的

9.怎么把这样一个表儿  
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

a):  
select t.year,  
(select a.amout from tab11 a where a.month=1 and a.year=t.year) m1,  
(select b.amout from tab11 b where b.month=2 and b.year=t.year) m2,  
(select c.amout from tab11 c where c.month=3 and c.year=t.year) m3,  
(select d.amout from tab11 d where d.month=4 and d.year=t.year) m4  
from tab11 t group by t.year

10.拷贝表(拷贝数据,源表名：a 目标表名：b)

SQL: insert into b(a, b, c) select d,e,f from b;

create table test as select \* from dept; --从已知表复制数据和结构

create table test as select \* from dept where 1=2; --从已知表复制结构但不包括数据

11.显示文章、提交人和最后回复时间

select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

12.日程安排提前五分钟提醒

13.两张关联表，删除主表中已经在副表中没有的信息  
delete from fubiao a where a.fid not in(select id from zhubiao)  
14.有两个表tab12和tab13，均有key和value两个字段，如果tab13的key在tab12中也有，就把tab13的value换为tab12中对应的value

update tab13 set value=(select value from tab12 where tab12.key=tab13.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 t.courseid,t.coursename,t.score,(case when score>60 then 'pass' else 'fail' end) mark from tab14 t

16.表15  
a1 a2  
1 a  
1 b  
2 x  
2 y  
2 z  
用select能选成以下结果吗？  
1 ab  
2 xyz

SELECT a1, replace(max(sys\_connect\_by\_path(a2, ' ')),' ','') NAME  
FROM (SELECT a1, a2, rn, LAG(rn) OVER(PARTITION BY a1 ORDER BY rn) rn1  
FROM (SELECT a1, a2, row\_number() OVER(ORDER BY a1) rn FROM t) rn)  
START WITH rn1 IS NULL  
CONNECT BY rn1 = PRIOR rn  
GROUP BY a1;

结果:  
a1 a2  
\------  
1 ab  
2 xyz  
17、  
SELECT SELLER,a.total-t.BAL FROM t,(SELECT SUM(BAL) total FROM t)a;  
结果：  
\-------  
A 900  
B 800  
C 700  
D 600  
17.题为  
有两个表, t1, t2,  
Table t1:

SELLER | NON\_SELLER  
\----- -----  
A B  
A C  
A D  
B A  
B C  
B D  
C A  
C B  
C D  
D A  
D B  
D C

SELLER | BAL  
\------ --------  
A 100  
B 200  
C 300  
D 400  
要求用SELECT 语句列出如下结果:------如A的SUM(BAL)为B,C,D的和,B的SUM(BAL)为A,C,D的和.......  
且用的方法不要增加数据库负担,如用临时表等