Oracle的四道经典面试题分享

前言

本文整理了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;

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对我们的支持。

(0)

相关推荐

  • ORACLE 如何查询被锁定表及如何解锁释放session

    ORACLE EBS操作某一个FORM界面,或者后台数据库操作某一个表时发现一直出于"假死"状态,可能是该表被某一用户锁定,导致其他用户无法继续操作 复制代码 代码如下: --锁表查询SQLSELECT object_name, machine, s.sid, s.serial# FROM gv$locked_object l, dba_objects o, gv$session s WHERE l.object_id = o.object_id AND l.session_id =

  • Linux系统(X64)安装Oracle11g完整安装图文教程另附基本操作

    一.修改操作系统核心参数 在Root用户下执行以下步骤: 1)修改用户的SHELL的限制,修改/etc/security/limits.conf文件 输入命令:vi /etc/security/limits.conf,按i键进入编辑模式,将下列内容加入该文件. oracle soft nproc 2047 oracle hard nproc 16384 oracle soft nofile 1024 oracle hard nofile 65536 编辑完成后按Esc键,输入":wq"

  • oracle数据库tns配置方法详解

    TNS简要介绍与应用 Oracle中TNS的完整定义:transparence Network Substrate透明网络底层,监听服务是它重要的一部分,不是全部,不要把TNS当作只是监听器. TNS是Oracle Net的一部分,专门用来管理和配置Oracle数据库和客户端连接的一个工具,在大多数情况下客户端和数据库要通讯,必须配置TNS,当然在少数情况下,不用配置TNS也可以连接Oracle数据库,比如通过JDBC.如果通过TNS连接Oracle,那么客户端必须安装Oracle client

  • Oracle 查看表空间的大小及使用情况sql语句

    SQL1: 复制代码 代码如下: --1.查看表空间的名称及大小 SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name; --2.查看表空间物理文件的名称及大小 SELECT tables

  • Oracle存储过程基本语法介绍

    Oracle存储过程基本语法 存储过程 1 CREATE OR REPLACE PROCEDURE 存储过程名 2 IS 3 BEGIN 4 NULL; 5 END; 行1: CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程, 如果存在就覆盖它; 行2: IS关键词表明后面将跟随一个PL/SQL体. 行3: BEGIN关键词表明PL/SQL体的开始. 行4: NULL PL/SQL语句表明什么事都不做,这句不能删

  • oracle常用sql语句

    正在看的ORACLE教程是:oracle常用sql语句.SQL*Plus system/manager 2.显示当前连接用户 SQL> show user 3.查看系统拥有哪些用户 SQL> select * from all_users; 4.新建用户并授权 SQL> create user a identified by a;(默认建在SYSTEM表空间下) SQL> grant connect,resource to a; 5.连接到新用户 SQL> conn a/a

  • Oracle数据库下载及安装图文操作步骤

    一.Oracle 下载 注意Oracle分成两个文件,下载完后,将两个文件解压到同一目录下即可. 路径名称中,最好不要出现中文,也不要出现空格等不规则字符. 官方下地址: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html以下两网址来源此官方下载页网. win 32位操作系统 下载地址: http://download.oracle.com/otn/nt/oracle11g/1120

  • oracle 创建表空间详细介绍

    注意点: 1.如果在PL/SQL 等工具里打开的话,直接修改下面的代码中[斜体加粗部分]执行 2.确保路径存在,比如[D:\oracle\oradata\Oracle9i\]也就是你要保存文件的路径存在 /*分为四步 */ /*第1步:创建临时表空间 */ 复制代码 代码如下: create temporary tablespace user_temp tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf' size 50m autoextend

  • ORACLE 10g 安装教程[图文]

    首先将ORACLE 10g的安装光盘放入光驱,如果自动运行,一般会出现如图1安装界面: 图1 单击"开始安装",就可以安装ORACLE 10g,一般会检查系统配置是否符合要求,然后出现"Oracle DataBase 10g安装"对话框,如图2所示: 图2 在安装Oracle DataBase 10g时可以选择"基本安装"和"高级安装"两种方法.选择"基本安装"时,"Oracle主目录位置&quo

  • oracle中to_date详细用法示例(oracle日期格式转换)

    TO_DATE格式(以时间:2007-11-02 13:45:25为例) 1. 日期和字符转换函数用法(to_date,to_char) 复制代码 代码如下: select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;   //日期转化为字符串  select to_char(sysdate,'yyyy')  as nowYear   from dual;   //获取时间的年  select to_char(sys

随机推荐