oracle sql面试题



雇员表：EMPLOYEE

(EMPNO    CHARACTER(6)   NOT NULL,--雇员编号，定义为主键  

FIRSTNAME VARCHAR(12)     NOT NULL,--雇员第一个名字  

MIDINIT  CHARACTER(1),            --雇员中间名字的首字母  

LASTNAME VARCHAR(15)     NOT NULL,--雇员最后一个名字  

WORKDEPT SMALLINT,                --部门编号，外键，对应部门表DEPTNO  

PHONENO  CHARACTER(4),            --电话号码  

HIREDATE DATE,                    --雇用日期  

JOB      CHARACTER(8),            --工作岗位

EDLEVEL  SMALLINT       NOT NULL,--学历(1：中学及以下，2：大学，3：硕士及以上)

SEX      CHARACTER(1),            --性别(1：男，2：女)

BIRTHDATEDATE,                    --出生日期(60周岁以上为退休)  

SALARY   DECIMAL(9,2),           --薪水  

BONUS    DECIMAL(9,2)            --奖金

);

部门表：DEPT

 (DEPTNO  SMALLINT      NOT NULL, --部门编号，主键

 DEPTNAME VARCHAR(14),   --部门名称

 MANAGER  SMALLINT  --部门经理编号，每个经理也是雇员表中一个雇员 )；

);

1、找出收入（SALARY和BONUS之和）最低的10个EMPNO，按照收入从低到高排序Oracle:

selectnvl(bonus, 0) + nvl(salary, 0) as income

 from EMPLOYEE t  order by income

2、从雇员表中删除雇用日期在1940年1月1日以前的雇员。Oracle：

Delete fromEMPLOYEE wherehiredate<to_date('2012-01-01','yyyy-mm-dd');

3、算出所有雇员的平均收入（SALARY和BONUS之和）、最低收入、最高收入，取整数位，小数点后面四舍五入。 oracle:

select round(sum(nvl(salary, 0) + nvl(bonus, 0)), 0),

       round(min(nvl(salary, 0) +nvl(bonus, 0)), 0),

       round(max(nvl(salary, 0) +nvl(bonus, 0)), 0)

 from employee 

4.列出所有雇员的全名，全部转换成大写字母，名字每一部分中间用空格隔开。oracle:

selectupper(FIRSTNME||' '||MIDINIT||' '||LASTNAME) fromemployee

5、找出雇员数大于50的部门的编号。oracle:

select deptno, deptname from dept where deptno in (selectworkdept from employee   group by workdept  having count(*) > 50) 

6、在雇员表JOB字段上新建一个索引，取名idx_emp_job。 oracle:

Createindex idx_emp_job onemployee(job)  

7、找到FIRSTNME=’John’的员工所在部门的名称和部门经理编号 oracle

select manager, deptname from dept where deptno in(selectworkdept fromemployee t  where t.firstname = 'Jhon')

8、更新雇员表的薪水，如果为空置为0，如果不为空，不变更字段值。oracle:

update employee set bonus=0 where bonus is null  

9、往部门表新增一个部门，DEPTNO=123，DEPTNAME=’Market’，MANAGER=20。 oracle:

Insertinto dept values(123,'Market',20)

10、列出每个员工的字母缩写，忽略中间的名字，例如，CHRISTINEI HAAS缩写为CH，BRUCE ADAMSON缩写为BA。oracle:

selectsubstr (upper(firstname),1,1)||substr(upper(lastname),1,1)fromemployee

                                                     第二部分

雇员表：EMPLOYEE

(EMPNO    CHARACTER(6)   NOT NULL,--雇员编号，定义为主键  

FIRSTNAME VARCHAR(12)     NOT NULL,--雇员第一个名字  

MIDINIT  CHARACTER(1),            --雇员中间名字的首字母  

LASTNAME VARCHAR(15)     NOT NULL,--雇员最后一个名字  

WORKDEPT SMALLINT,                --部门编号，外键，对应部门表DEPTNO  

PHONENO  CHARACTER(4),            --电话号码  

HIREDATE DATE,                    --雇用日期  

JOB      CHARACTER(8),            --工作岗位

EDLEVEL  SMALLINT       NOT NULL,--学历(1：中学及以下，2：大学，3：硕士及以上)

SEX      CHARACTER(1),            --性别(1：男，2：女)

BIRTHDATE DATE,                    --出生日期(60周岁以上为退休)  

SALARY   DECIMAL(9,2),           --薪水  

BONUS    DECIMAL(9,2)            --奖金

);

部门表：DEPT

 (DEPTNO  SMALLINT      NOT NULL, --部门编号，主键

 DEPTNAME VARCHAR(14),   --部门名称

 MANAGER  SMALLINT  --部门经理编号，每个经理也是雇员表中一个雇员 )；

);

1、如果雇员对应的经理编号小于100，更新雇员表的薪水和奖金字段，如果为空置为0，如果不为空，在原有基础上加10%，结果按四舍五入取整。oracle:

update employeea

  set salary =nvl(salary,0)*1.1,bonus = nvl(bonus,0) * 1.1

 whereexists (select1

         fromdept b

        whereb.manager <2

          anda.workdept = b.deptno)

2、在三个不同的地区，雇员表各有一张，分别是EMPLOYEE_CN、EMPLOYEE_US、EMPLOYEE_EU，三张表表结构一样，存放的是本地区雇员的信息，三张表的雇员编号没有重复。同样在不同的地区，也各自有一张部门表，DEPT_CN、DEPT_US、DEPT_EU，这三张表有重复的记录。需要算出全公司范围内各个部门的薪酬（薪水加奖金）成本，已经撤销的部门在部门表中也有记录，需要在结果集中体现。写出SQL。要求输出格式如下：

部门名称    薪酬       人数

Market        500000    5 

Head Office 1200000     20 

Plains       0          0

Selectb.deptname,sum(income),sum(cnt)

 from (Selectworkdept,count(*) cnt,sum(salary+ bonus) incomefrom EMPLOYEE_CN groupbyworkdept

       Unionall

       Selectworkdept,count(*) cnt,sum(salary+ bonus) income  from EMPLOYEE_USgroupbyworkdept

       Unionall

       Selectworkdept,count(*) cnt,sum(salary+ bonus) income from EMPLOYEE_EU groupbyworkdept) a

 rightouterjoin (selectdeptno, deptnamefrom dept

                  Union

                  Selectdeptno, deptname from dept

                  Union

                  Selectdeptno, deptname from dept) b

   On a.workdept = b.deptno

 groupby b.deptname

3、从部门表删除人数为0的部门。 oracle:

Deletefrom dept a where not exists (select a.deptno fromemployee b wherea.deptno=b.workdept)

4、新建一张表EMPLOYEE_TMP，表结构与EMPLOYEE一样，不需要数据。oracle:

Createtable employee_tmp asselect *fromemployee where1=2

5、列出平均收入大于50000的部门名称。Oracle:

Selectdeptname,avg(salary+bonus)fromemployee a,dept b Where a.workdept=b.deptnogroupbydeptname  having(avg(salary+bonus)>50000)

6、类似于论坛性质的应用，从表tab1中取记录，每页展示20条记录，请取出按照字段uid排序的第21至第40条记录。 Db2:

select * from (selectrownumrn, a.* from tab1whererownum<=40)wherern >=21 orderbyuid

7、写出得到当前时间加1年加2个月加3天是星期几的SQL

select to_char(add_months(sysdate,1 *12 +2)+3,'yyyy-mm-ddHH24:MI:SS')from dual;

8、使用查询语句实现如下统计报表：

学历

人数

工资

平均工资

男

女

 合计

男

女

合计

男

女

中学及以下

 

 

 

 

 

 

 

 

大学

 

 

 

 

 

 

 

 

硕士及以上

 

 

 

 

 

 

 

 

Select edlevel,

      sum(casewhensex = 1then1else0end),

      sum(casewhensex = 2then1else0end),

      count(*),

      sum(casewhensex = 1then salary  else0end),

      sum(casewhensex = 2then salary else0end),

      sum(salary),

      avg(casewhensex = 1then salary end),

      avg(casewhensex = 2then salary end),

      avg(salary)

 from employee

 groupby edlevel

 orderby edlevel

9、表TAB1内容：

pdate      result

2009-05-09   胜

2009-05-09   胜

2009-05-09   负

2009-05-09   负

2009-05-10   胜

2009-05-10   负

2009-05-10   负

……

如果要生成类似下列结果：

 日期      胜    负

2009-05-09   2     2

2009-05-10  1     2

……

写出SQL语句。 oracle

Select pdate, sum(casewhenresult ='胜'then1else0end), sum(casewhenresult ='负'then1else0end)fromtab1groupby pdateorderbypdate

10、找出数据库中所有以’TB’开头的表。 oracle:

select * fromall_tables where TABLE_NAMElike'TB%'

第三部分

雇员表：EMPLOYEE

(EMPNO    CHARACTER(6)   NOT NULL,--雇员编号，定义为主键  

FIRSTNAME VARCHAR(12)     NOT NULL,--雇员第一个名字  

MIDINIT  CHARACTER(1),            --雇员中间名字的首字母  

LASTNAME VARCHAR(15)     NOT NULL,--雇员最后一个名字  

WORKDEPT SMALLINT,                --部门编号，外键，对应部门表DEPTNO  

PHONENO  CHARACTER(4),            --电话号码  

HIREDATE DATE,                    --雇用日期  

JOB      CHARACTER(8),            --工作岗位

EDLEVEL  SMALLINT       NOT NULL,--学历(1：中学及以下，2：大学，3：硕士及以上)

SEX      CHARACTER(1),            --性别(1：男，2：女)

BIRTHDATEDATE,                    --出生日期(60周岁以上为退休)  

SALARY   DECIMAL(9,2),           --薪水  

BONUS    DECIMAL(9,2)            --奖金

);

部门表：DEPT

 (DEPTNO  SMALLINT      NOT NULL, --部门编号，主键

 DEPTNAME VARCHAR(14),   --部门名称

 MANAGER  SMALLINT  --部门经理编号，每个经理也是雇员表中一个雇员 )；

);

1、有一个与雇员表结构一样的临时表EMPLOYEE_TMP，存放雇员的临时信息，每天晚上12点需要将雇员的临时信息同步到雇员表中，如果雇员表中没有相应记录，insert，如果有，update WORKDEPT、PHONENO、JOB、SALARY、BONUS。使用一条SQL实现。

Mergeinto employee a

Using (select *fromemployee_tmp) b

on(a.empno=b.empno)

Whenmatchedthen 

updateset workdept=b.workdept,phoneno=b.phoneno,job=b.job,salary=b.salary,bonus=b.bonus

whennotmatchedthen

 insert (EMPNO,  FIRSTNAME,MIDINIT, LASTNAME, WORKDEPT,PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE,SALARY, BONUS)

values(b.EMPNO,b.FIRSTNAME,b.MIDINIT,b.LASTNAME,b.WORKDEPT,b.PHONENO,b.HIREDATE,b.JOB,b.EDLEVEL,b.SEX,b.BIRTHDATE,b.SALARY,b.BONUS)

2、有一张表tab1，有类似下面的记录: 

 No name 

1    a 

2    b

3    c

4    d

……

请写一条SQL显示以下结果：

ab 

ac 

ad

……

bc 

bd 

……

cd

……

Select a.name||b.name fromtab1 a,tab1 bwhere a.name<b.nameorderbya.name,b.name

3、有两张表： 

Record(recNumber <主键>学号, recScore分数) 

Student(stuNumber <主键>学号, stuName姓名,stuClass班级) 

用SQL语句实现以下查询： 

查询每一个班级中第二名的同学的姓名。

with tmp as

(selectstuName,recScore,dense_rank()over(orderbyb.recScoredesc)ID

 from student a, record bwhereb.recnumber = a.stuNumber)

select stuName fromtmpwhereID =2

4、有这样一张表tb：

  PDate        T1      T2    T3

2010-01-28 04:30:09 NULL   NULL

2010-01-28 18:30:00 NULL   NULL

2010-02-09 03:35:25 NULL   NULL

2010-01-28 NULL  04:31:09  NULL 

2010-01-28 NULL  05:31:09  NULL

2010-01-28 NULL  06:31:09  NULL

2010-01-28 NULL  07:31:09  NULL

2010-02-09 NULL  03:36:25  NULL

2010-01-28 NULL     NULL 04:32:09

2010-02-09 NULL    NULL  03:37:25

要求写出单条SQL语句，通过处理可得到以下结果：

  PDate        T1        T2      T3 

2010-01-28 04:30:09 04:31:09 04:32:09 

2010-01-28 18:30:00 05:31:09  NULL

2010-01-28   NULL    06:31:09  NULL

2010-01-28   NULL    07:31:09  NULL

2010-02-09 03:35:25 03:36:25 03:37:25

selectcoalesce(a.pdate,b.pdate, c.pdate) pdate, a.t1,b.t2, c.t3

 from (selectt.*,row_number()over(partitionbypdateorderby pdate,t1) rrfromtb twheret1isnotnull)a

 fullouterjoin

      (selectt.*,row_number()over(partitionbypdateorderby pdate,t2) rrfromtb twheret2isnotnull)b

   on (a.pdate= b.pdateanda.rr = b.rr)

 fullouterjoin

      (selectt.*,row_number()over(partitionbypdateorderby pdate,t3) rrfromtb twheret3isnotnull)c

   on (b.pdate= c.pdateandb.rr = c.rr)

 orderby1,2,3,4

5、表SALES(

YEAR INTEGER, QUARTER INTEGER,

MONTH INTEGER,

FEE DECIMAL(12,2))，记录如下：

Year quarter month  fee

2009     1       1    10000

2009     1       2    10000

2009     1       3    10000

2009     2       4    10000

2009     2       5    10000

2009    2       6   10000

2009    3       7   10000

2009    3       8   10000

2009    3       9   10000

2009    4      10  10000

2009    4      11  10000

2009    4       12  10000

使用最简单的SQL，得到下面的结果：

 year  quarter month  sum(fee)

2009    1         1     10000

  2009    1        2      10000

2009    1        3     10000 

2009    1       NULL   30000 

2009    2       4      10000 

2009    2       5      10000 

2009    2       6      10000

  2009    2      NULL   30000 

2009    3        7      10000 

2009    3       8      10000

2009    3       9      10000 

2009    3     NULL    30000 

2009    4      10      10000 

2009    4      11      10000

 2009   4      12      10000 

2009    4     NULL     30000 

2009   NULL    NULL    120000

NULL   NULL   NULL    120000 

请注意排序

Selectyear,quarter,month,sum(fee)fromsalesgroupbyrollup(year,quarter,month)orderbyyear,quarter,month

 相当于

select* from (

 Selectyear,quarter,month,sum(fee)from sales group by year,quarter,month

  unionall

 Selectyear,quarter,null,sum(fee)from sales group by year,quarter

  unionall

 Selectyear,null,null,sum(fee)from sales group by year

 unionall

  selectnull,null,null,sum(fee) from sales

 )orderby year,quarter,month;

 

Rollup()：分组函数可以理解为group by的精简模式，具体分组模式如下：

Rollup(a,b,c):(a,b,c),(a,b),(a),(全表)

Cube():分组函数也是以group by为基础，具体分组模式如下：

cube(a,b,c):(a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),(全表)