整理了 4 道 Oracle 经典面试题, 与大家分享学习.
第一题
- create table test(
- id number(10) primary key,
- type number(10) ,
- t_id number(10),
- value varchar2(6)
- );
- insert into test values(100,1,1,'张三');
- insert into test values(200,2,1,'男');
- insert into test values(300,3,1,'50');
- insert into test values(101,1,2,'刘二');
- insert into test values(201,2,2,'男');
- insert into test values(301,3,2,'30');
- insert into test values(102,1,3,'刘三');
- insert into test values(202,2,3,'女');
- insert into test values(302,3,3,'10');
- select * from test;
代码生成表格如:
根据以上代码生成的表写出一条查询语句, 查询结果如下:
姓名 | 性别 | 年龄 |
张三 | 男 | 50 |
刘二 | 男 | 30 |
刘三 | 女 | 10 |
- /*
- 根据表格可以分析出 type 列中 1 代表姓名, 2 代表性别, 3 代表年龄, 而 t_id 中 id 一样的为同一个人的属性
- 查询结果中列依次为姓名, 性别, 年龄, 而 type 列决定姓名, 性别, 年龄
- */
- /* 使用分组, 先对 t_id 进行分组, 然后用 decode 函数过滤数据, 例: decode(type, 1, value) type=1 就显示为 value
- 由于分组后 select 后面的列字段只能是分组的字段或者组函数, 所有使用 max().
- 同一个人的 type 没有重复数值所以 decode(type, 1, value) 返回的值只有一个, 最大值也就是这个值
- */
- select max(decode(type, 1, value)) "姓名",
- max(decode(type, 2, value)) "性别",
- max(decode(type, 3, value)) "年龄"
- from test
- group by t_id;
- /* 使用连表, 通过 where 过滤生成 3 张 type 分别等于 1(姓名),2(性别),3(年龄) 的 3 张虚拟表 如:
- 再通过 where 连接条件 三张表 t_id 相等的为同一个人或者说同一条记录 (行)
- */
- select t1.value "姓名",t2.value "性别",t3.value "年龄" from
- (select value,t_id from test where type=1) t1,
- (select value,t_id from test where type=2) t2,
- (select value,t_id from test where type=3) t3
- where t1.t_id=t2.t_id and t1.t_id=t3.t_id;
第二题
/*
2. 一道 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 varchar2(10),shengfu varchar2(5));
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','负');
select * from tmp;
*/
-- 使用分组
-- 按日期分组, 用 conut 函数计算次数
- select rq "日期",
- count(decode(shengfu, '胜', 1)) "胜",
- count(decode(shengfu, '负', 1)) "负"
- from tmp
- group by rq
- order by rq;
-- 使用连表
-- 这道题本身就需要分组, 不建议使用连表做
-- 以下使用的是 SQL1999 的连表方式, 语法不一样效果与第一题使用的 SQL1992 的一样
select t1.rq,t1. 胜, t2. 负 from
- (select count(decode(shengfu, '胜', 1)) "胜", rq from tmp group by rq) t1
- join
- (select count(decode(shengfu, '负', 1)) "负", rq from tmp group by rq) t2
- on t1.rq=t2.rq;
第三题
- /*3. 生成题目所需的表
- create table STUDENT_SCORE
- (
- name VARCHAR2(20),
- subject VARCHAR2(20),
- score NUMBER(4,1)
- );
- insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0);
- insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0);
- insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0);
- insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0);
- insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0);
- insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0);
- insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0);
- insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0);
- insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0);
- 3.1 得到类似下面的结果
- 姓名 语文 数学 英语
- 王五 89 56 89
- 李四 xx xx xx
- select * from STUDENT_SCORE;
- 3.2 有一张表, 里面有 3 个字段: 语文, 数学, 英语. 其中有 3 条记录分别表示语文 70 分, 数学 80 分, 英语 58 分,
- 请用一条 sql 语句查询出这三条记录并按以下条件显示出来 (并写出您的思路):
- 大于或等于 80 表示优秀, 大于或等于 60 表示及格, 小于 60 分表示不及格.
- 显示格式:
- 语文 数学 英语
- 及格 优秀 不及格
- ------------------------------------------
- */
- --3.1
-- 使用分组
- select name "姓名",
- max(decode(subject, '语文' ,score)) "语文",
- max(decode(subject, '数学' ,score)) "数学",
max(decode(subject, '英语' ,score)) 英语
- from STUDENT_SCORE
- group by name;
-- 使用连表
select t1.name 姓名, t1.score 语文, t2.score 数学, t3.score 英语 from
- (select name,score from STUDENT_SCORE where subject='语文') t1
- join
- (select name,score from STUDENT_SCORE where subject='数学') t2
- on t1.name=t2.name
- join
- (select name,score from STUDENT_SCORE where subject='英语') t3
- on t1.name=t3.name;
- --3.2
-- 在 3.1 的基础上使用 case when then esle end
select t. 姓名,
(case when t. 语文 >=80 then '优秀'
when t. 语文 >=60 then '及格'
else '不及格' end) 语文,
(case when t. 数学 >=80 then '优秀'
when t. 数学 >=60 then '及格'
else '不及格' end) 数学,
(case when t. 英语 >=80 then '优秀'
when t. 英语 >=60 then '及格'
else '不及格' end) 英语
from
(select t1.name 姓名, t1.score 语文, t2.score 数学, t3.score 英语 from
- (select name,score from STUDENT_SCORE where subject='语文') t1
- join
- (select name,score from STUDENT_SCORE where subject='数学') t2
- on t1.name=t2.name
- join
- (select name,score from STUDENT_SCORE where subject='英语') t3
- on t1.name=t3.name
- ) t;
第四题 (这道题难度相对较高)
- /*4. 请用一个 sql 语句得出结果
- 从 table1,table2 中取出如 table3 所列格式数据, 注意提供的数据及结果不准确,
- 只是作为一个格式向大家请教.
- table1
- 月份 mon 部门 dep 业绩 yj
- -------------------------------
- 一月份 01 10
- 一月份 02 10
- 一月份 03 5
- 二月份 02 8
- 二月份 04 9
- 三月份 03 8
- table2
- 部门 dep 部门名称 dname
- --------------------------------
- 国内业务一部
- 国内业务二部
- 国内业务三部
- 国际业务部
- table3 (result)
- 部门 dep 一月份 二月份 三月份
- --------------------------------------
- 10 null null
- 10 8 null
- null 5 8
- null null 9
- ------------------------------------------
- create table yj01(
- month varchar2(10),
- deptno number(10),
- yj number(10)
- )
- insert into yj01(month,deptno,yj) values('一月份',01,10);
- insert into yj01(month,deptno,yj) values('二月份',02,10);
- insert into yj01(month,deptno,yj) values('二月份',03,5);
- insert into yj01(month,deptno,yj) values('三月份',02,8);
- insert into yj01(month,deptno,yj) values('三月份',04,9);
- insert into yj01(month,deptno,yj) values('三月份',03,8);
- create table yjdept(
- deptno number(10),
- dname varchar2(20)
- )
- insert into yjdept(deptno,dname) values(01,'国内业务一部');
- insert into yjdept(deptno,dname) values(02,'国内业务二部');
- insert into yjdept(deptno,dname) values(03,'国内业务三部');
- insert into yjdept(deptno,dname) values(04,'国际业务部');
- */
- select * from yj01;
- select * from yjdept;
-- 使用分组
select deptno,
max(decode(month,'一月份',yj)) 一月份,
max(decode(month,'二月份',yj)) 二月份,
max(decode(month,'三月份',yj)) 三月份
- from yj01 group by deptno
- order by deptno;
-- 这道题给出了两张表, 而用分组做, 使用 yj01 表就能做出来了, 所以这道题考察的应该是连表的知识
/* 这两张表中有的月份有的部门业绩是空的, 而用前几道题的做法, 不匹配条件的值会被过滤掉,
例如 month = 一月份的只有 1 部门, 形成的表里 deptno 只有 1 和二月份, 三月份形成的表中的 deptno 无法匹配
而 yjdept 表中包含了所有部门编号 deptno, 这时就可以用到外连接的特性
(在满足一张表的内容都显示的基础上, 连接另外一张表, 如果连接匹配则正常显示, 连接不匹配, 另外一张表补 null)
*/
select t1.deptno, t1.yj 一月份, t2.yj 二月份, t3.yj 三月份
- from
- (select y2.deptno,y1.yj from
- (select yj, deptno from yj01 where month='一月份') y1 right join yjdept y2 on y1.deptno=y2.deptno)t1
- join
- (select y2.deptno,y1.yj from
- (select yj, deptno from yj01 where month='二月份') y1 right join yjdept y2 on y1.deptno=y2.deptno)t2
- on t1.deptno=t2.deptno
- join
- (select y2.deptno,y1.yj from
- (select yj, deptno from yj01 where month='三月份') y1 right join yjdept y2 on y1.deptno=y2.deptno)t3
- on t1.deptno=t3.deptno
- order by t1.deptno;
来源: http://www.linuxidc.com/Linux/2019-05/158535.htm