Oracle练手试题

1、列出至少有一个员工的所有部门？

select distinct b.dname, b.deptno, b.loc
  from emp a
  inner join dept b
on a.deptno = b.deptno

2、列出所有员工的姓名及其直接上级的姓名？

select a.ename as 上级姓名, b.ename as 员工姓名
  from emp a
 inner join emp b
    on b.mgr = a.empno

3、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门？

select b.deptno, a.dname, b.empno, b.ename
      from dept a
      left join emp b
        on a.deptno = b.deptno;

4、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金？

select a.ename, a.sal
  from emp a
 where a.sal > (select max(b.sal) from emp b where b.deptno = 30);

5、列出所有员工的工资，按年薪从低到高排序（年薪=工资+奖金）？

select a.empno, a.ename, (nvl(a.sal, 0) + nvl(a.comm, 0)) * 12  as salpersal
  from emp a
 order by salpersal;

6、用一条语句查出 emp 表中每个部门工资第二的数据（相同的工资，一并显示）？

select *
  from (select empno,
               ename,
               deptno,
               sal,
               dense_rank() over(partition by deptno order by sal desc) rn
          from emp)
 where rn = 2

7、找出 emp 表中姓名（ename）最后一位是 S 或者第二位是 A 的所有员工？

select a.ename
  from emp a
 where a.ename like '_A%'
    or a.ename like '%S';

8、列出受雇日期早于其直接上级的所有员工？

select a.empno, a.ename
  from emp a
  inner join emp b
    on a.empno = b.mgr
 where a.hiredate > b.hiredate;

9、列出薪水大于部门平均工资的所有员工（使用标量子查询写）？

select *
  from (select e1.empno,
               e1.ename,
               e1.sal,
               (select avg(sal) from emp e2 where e2.deptno = e1.deptno) as avg
          from emp e1) t
 where t.sal > t.avg
 

10、列出薪金比‘SMITH’多的所有员工（用 exists 写）？

select a.ename
  from emp a
 where exists (select 1
          from emp b
         where b.ename = 'SMITH'
           and a.sal > b.sal);

11、用一个查询语句，实现查询各个部门、各个岗位的总工资和各个部门的总工资和所

有雇员的总工资？

select a.deptno,a.job, sum(a.sal) from emp a group by  a.deptno,a.job
union all
select a.deptno,null, sum(a.sal) from emp a group by  a.deptno,null
union all
select null,null, sum(a.sal) from emp a group by  null,null;

12、表中有 A B C 三列,用 SQL 语句实现：当 A 列大于 B 列时选择 A 列否则选择 B 列，当 B 列大于 C 列时选择 B 列否则选择 C 列？
-- 示例数据：

Create table compare(A number(2),B number(2), C number(2)); insert into compare values (70, 80, 98);
select (case
         when a > b then
          a
         else
          b
       end),
       (case
         when b > c then
          b
         else
          c
       end)
  from compare

13、有一张表，里面有 3 个字段：语文，数学，英语。其中有 3 条记录分别表示语文

70 分，数学 80 分，英语 58 分，请用一条 sql 语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：大于或等于 80 表示优秀，大于或等于 60 表示及格，小于

60 分表示不及格。

select (case
         when "语文" > 80 then
          '优秀'
         when "语文" < 60 then
          '不及格'
         else
          '及格'
       end) "语文",
       (case
         when "数学" > 80 then
          '优秀'
         when "数学" < 60 then
          '不及格'
         else
          '及格'
       end) "数学",
       (case
         when "英语" > 80 then
          '优秀'
         when "英语" < 60 then
          '不及格'
         else
          '及格'
       end) "英语"
  from exam;

14、实现下面的查询结果（行转列）：

--显示结果：

ITEM_ID
KIND1 value11 value12 value13
KIND2 value21 value22 (null)
KIND3 value31 (null)    value33
100     200     300

-- 示例数据

CREATE TABLE row_table(
item_id number(3),	kind varchar2(10),	value varchar2(10)
);

INSERT INTO row_table VALUES (100, 'kind1', 'value11'); INSERT INTO row_table VALUES (100, 'kind2', 'value21'); INSERT INTO row_table VALUES (100, 'kind3', 'value31'); INSERT INTO row_table VALUES (200, 'kind1', 'value12'); INSERT INTO row_table VALUES (200, 'kind2', 'value22'); INSERT INTO row_table VALUES (200, 'kind3', null); INSERT INTO row_table VALUES (300, 'kind1', 'value13'); INSERT INTO row_table VALUES (300, 'kind2', NULL); INSERT INTO row_table VALUES (300, 'kind3','value33'); commit;

SELECT RB.ITEM_ID, KIND1.VALUE KIND1, KIND2.VALUE KIND2, KIND3.VALUE KIND3
  FROM ROW_TABLE RB,
       (SELECT ROW_TABLE.ITEM_ID, ROW_TABLE.VALUE
          FROM ROW_TABLE
         WHERE ROW_TABLE.KIND = 'kind1') KIND1,
       (SELECT ROW_TABLE.ITEM_ID, ROW_TABLE.VALUE
          FROM ROW_TABLE
         WHERE ROW_TABLE.KIND = 'kind2') KIND2,
       (SELECT ROW_TABLE.ITEM_ID, ROW_TABLE.VALUE
          FROM ROW_TABLE
         WHERE ROW_TABLE.KIND = 'kind3') KIND3
 WHERE RB.ITEM_ID = KIND1.ITEM_ID
   AND KIND1.ITEM_ID = KIND2.ITEM_ID
   AND KIND2.ITEM_ID = KIND3.ITEM_ID
 GROUP BY RB.ITEM_ID, KIND1.VALUE, KIND2.VALUE, KIND3.VALUE