Oracle SQL 练习题 4

1
2
3
4
5
6

-- 1.列出至少有一个雇员的所有部门
select deptno, dname, loc 
from dept
where deptno in (select deptno
                  from emp
                  group by deptno);

1
2
3
4
5
6

-- 2.列出薪金比"SMITH"多的所有雇员
select *
from emp
where sal > (select sal
              from emp
              where ename = 'SMITH');

1
2
3
4

-- 3.列出入职日期早于其直接上级的所有雇员
select e1.ename, e1.empno, e1.mgr, e1.hiredate, e2.hiredate mgrhiredate
from emp e1, emp e2
where e1.mgr = e2.empno and e1.hiredate < e2.hiredate;

1
2
3
4

-- 4.找员工姓名和直接上级的名字
select e1.ename, e2.ename mgrname
from emp e1 left join emp e2
on e1.mgr = e2.empno;

1
2
3
4
5

-- 5.显示部门名称和人数
-- 不能count(*),因为40号部门没人但也会是一条记录
select dname, count(emp.empno)
from dept left join emp on emp.deptno = dept.deptno
group by dname;

1
2
3
4

-- 6.显示每个部门的最高工资的员工
select *
from emp e1
where sal = (select max(sal) from emp e2 group by deptno having e1.deptno = e2.deptno);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 7.显示每个部门的工资前2名的员工
select deptno, ename, sal      
from emp e1     
where      
   (  
    select count(*)     
    from emp e2     
    where e2.deptno=e1.deptno and e2.sal>e1.sal  
   ) <2
order by deptno;

1
2
3
4

-- 8.显示出和员工号7369部门相同的员工姓名，工资
select ename, sal
from emp
where deptno = (select deptno from emp where empno=7369);

1
2
3
4

-- 9.显示出和姓名中包含"W"的员工相同部门的员工姓名
select ename
from emp
where deptno in (select deptno from emp where ename like '%W%');

1
2
3
4

-- 10.显示出工资大于平均工资的员工姓名，工资
select ename, sal
from emp
where sal > (select avg(sal) from emp);

1
2
3
4

-- 11.显示出工资大于本部门平均工资的员工姓名，工资
select ename, sal
from emp e1
where e1.sal > (select avg(sal) from emp e2 group by deptno having e1.deptno = e2.deptno);

1
2
3
4

-- 12.显示员工"KING"所管理的员工姓名
select ename
from emp
where mgr = (select empno from emp where ename='KING');

1
2
3
4

-- 13.显示每位经理管理员工的最低工资，及最低工资者的姓名
select sal, ename, mgr
from emp e1
where e1.sal = (select min(sal) from emp e2 group by e2.mgr having e1.mgr = e2.mgr);

1
2
3
4
5
6

-- 14.显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间
select ename, hiredate
from emp
where hiredate > (select hiredate 
                   from emp 
                   where sal = (select max(sal) from emp));

1
2
3
4
5
6
7
8

-- 15.显示出平均工资最高的的部门平均工资及部门名称
select dname, avg(sal) maxavgsal
from emp natural join dept
group by dname
having avg(sal) = (select max(avgsal)
                    from (select max(avg(sal)) avgsal
                          from emp
                          group by deptno));

1
2
3
4
5
6

-- 16.创建序列，起始位1，自增为1，最小值为1，最大值为9999
create sequence mysequence
increment by 1
start with 1
minvalue 1
maxvalue 9999;

1
2
3
4

-- 17.创建序列，起始值为50，每次增加5
create sequence mysequence2
increment by 5
start with 50;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

/* 18.请分析按照以下要求都需要建立什么类型的字段?
–(1)最大2000个字节定长字符串                      -- char(2000)
–(2)如果输入‘张三’ 后添空格6个                   -- nchar
–(3)性别输入'男'或'女’                           -- char(2)e
–(4)最大4000个字节变长字符串                      -- varchar2(4000) 参数为字节数
–(5)如果在数据库中输入'张三'则显示数据'张三’      -- nvarchar2(10) unicode编码，参数为字符数
–(6)表示数字范围为- 10的125次方到10的126次方, 可
以表示小数也可以表示整数                            -- number
–(7)最大表示4位整数 -9999 到 9999                 -- number(4)
–(8)表示5位有效数字2位小数的一个小数-999.99
到999.99                                           -- number(5,2)
–(9)包含年月日和时分秒                             -- date
–(10)包含年月日和时分秒毫秒                       -- timestamp
–(11)二进制大对象图像/声音  */                    -- LONGRAW 或者 BLOB

1
2
3
4
5

-- 19.请为工资大于10000的员工创建视图，要求显示员工的部门信息，职位信息，部门所在地.
create view highsal
as (select deptno, dname, "JOB", loc
    from emp natural join dept
    where emp.sal > 10000);

1
2
3
4
5
6

-- 20.现在按照工资的降序排序，分页显示，其中一页数据为25条，请显示第3页数据。
select sal, rn
from (select sal, rownum rn
      from emp 
      order by sal desc)
where rn between 50 and 75;

1
2
3
4
5

-- 21.创建视图，要求包含字段：部门名，部门平均工资
create view deptview
as (select dname, avg(sal)
     from emp natural join dept
     group by dname);

1
2
3
4

-- 22.针对21题创建的视图，执行insert,update,delete,语句能否成功，为什么？
/*
不能
*/

1
2
3
4
5
6

-- 23.显示出员工工资排在第五到第十名的员工信息
select empno, ename, "JOB", mgr, hiredate, sal, comm, deptno, rn
from(select empno, ename, "JOB", mgr, hiredate, sal, comm, deptno, rownum rn
      from emp 
      order by sal)
where rn between 5 and 10;

1
2
3

-- 24.在表copy_dept中插入记录，其中部门号采用17题中创建的序列生成
insert into dept
values(mysequence2.nextval, 'it', 'Dalian');