oracle练习题.docx

oracle练习题

查询练习一

--查询姓名首字母为“A”或第二个字符为“A”的所有员工信息
SELECT *
FROM emp
WHERE ename LIKE 'A%'
      OR ename LIKE '_A%';
--查询部门20和30中的、岗位不是“CLERK”或“SALESMAN”的所有员工信息
SELECT *
FROM emp
WHERE job != 'CLERK'
      AND job != 'SALESMAN'
      AND deptno IN (20, 30);
--查询出工资在2500-3500之间，1981年入职的，没有奖金的所有员工信息
SELECT *
FROM emp
WHERE EXTRACT(YEAR FROM hiredate) = 1981
      AND sal BETWEEN 2500 AND 3000
      AND comm IS NULL;
--查询比平均员工工资高的员工信息
SELECT deptno, dname, loc
FROM dept
WHERE deptno IN (
      SELECT deptno
      FROM emp
      GROUP BY deptno
      HAVING AVG(sal) > 2000
);
--查询平均工资高于2000的部门信息
select deptno ,dname,loc
from dept
where deptno in(select deptno
                from emp
                group by deptno
                having avg(sal)>2000);
--查询出ward的工作所在地
SELECT loc
FROM dept
WHERE deptno IN (
      SELECT deptno
      FROM emp
      WHERE ename = 'WARD'
);
--查询出工资比ADAMS高的所有人姓名、部门、所在地
SELECT a.ename, b.dname, a.sal
FROM emp a, dept b
WHERE a.deptno = b.deptno
      AND a.sal > (
            SELECT sal
            FROM emp
            WHERE ename = 'ADAMS'
      );
--查询出工资排名第7的员工信息
SELECT *
FROM (
      SELECT rank() OVER (ORDER BY sal DESC) AS rk, emp.*
      FROM emp
)
WHERE rk = 7;
/*minue:两个结果值相减，
  uniou:两个结果集拼到一起
 （1~7）-（1~6）=排名第7
*/
--查询与部门20岗位不同的员工工资
SELECT sal, job,deptno
FROM emp
WHERE job NOT IN (
      SELECT job
      FROM emp
      WHERE deptno = 20
);
--验证 --20部门的岗位
/*select job
from emp
where deptno=20;*/
--查询与smith部门岗位完全相同的员工姓名、工作、工资
SELECT *
FROM emp
WHERE deptno IN (
            SELECT deptno
            FROM emp
            WHERE ename = 'SMITH'
      )
      AND job IN (
            SELECT job
            FROM emp
            WHERE ename = 'SMITH'
      );
  --查询emp表中的所有信息
  select *
  from emp;
  --查询emp表中的员工姓名和工资
  select ename,sal
  from emp;
  --查询emp表中部门编号为20的并且sal大于3000的所有员工信息
  select *
  from emp
  where deptno=20 and sal>3000;

  --查询emp表中部门编号为20的或者sal大于3000的所有员工信息
   select *
  from emp
  where deptno=20 or sal>3000;
  --使用between and查询工资在2000到4000之间的员工
  select *
  from emp
  where sal between 2000 and 4000;
  --使用in查询 部门编号10，20的所有员工
  select *
  from emp
  where deptno in (10,20);
  --使用like查询所有名字中包括W的员工信息
  select*
  from emp
  where ename like '%W%';
  --使用like查询所有员工名字中的第二子字母为W的员工信息
  select*
  from emp
  where ename like'_W%';
  --查询所有员工信息并按照部门编号和工资进行排序
  select *
  from emp
  order by deptno,sal ;
  --显示员工共工资上浮20%的结果
  select sal+sal*0.2
  from emp;

/*最后一题的另一种思路
  minue:两个结果值相减，
  uniou:两个结果集拼到一起
 （1~7）-（1~6）=排名第7
*/

--11显示emp表的员工姓名以及工资和奖金的和
--12显示dept表的内容，使用别名将表头转换成中文显示
--13查询员工姓名和工资，并按工资从小到大排序
--14查询员工姓名和雇佣日期，并按雇佣日期排序，后雇佣的先显示
--15查询员工信息，先按部门标号从小到大排序，再按雇佣日期的先后排序

多表查询练习

/*多表查询练习*/
/*多表查询练习*/
select * from emp where job = 'MANAGER';
select * from dept;
select * from salgrade;
--列出在部门sales工作的员工的姓名
select ename
  from emp a, dept b
 where b.dname = 'SALES'
   and a.deptno = b.deptno;
--列出所有员工的姓名，部门名称和工资
select ename, dname, sal from emp a, dept b where a.deptno = b.deptno;
--列出所有部门的详细信息和部门人数
select *
  from dept
  full join (select deptno, count(*) from emp group by deptno) b on dept.deptno =
                                                                    b.deptno;
--列出各个部门职位为manager的最低薪金
select deptno, min(sal) 最低薪金
  from emp
 where job in 'MANAGER'
 group by deptno;
--查询出部门人数至少是1的部门名字
select dname
  from dept
  full join (select deptno, count(*) 人数 from emp group by deptno) b on dept.deptno =
                                                                       b.deptno
 where 人数 >= 1;
--列出工资比smith多的员工
select * from emp where sal > (select sal from emp where ename = 'SMITH');
--列出所有员工的对应领导的姓名
select a.*, b.ename 领导 from emp a left join emp b on b.empno = a.mgr;
--求出某个员工的领导，并要求这些领导的薪水高于或等于3000
select a.ename, a.sal
  from emp a, emp b
 where a.empno = b.mgr
   and a.sal >= 3000;
--列出部门名称，和这些部门的员工信息
select dname, a.* from emp a, dept b where a.deptno = b.deptno;
--列出所有职位为clerk的员工姓名及其部门名称，部门的人数
SELECT ename, dname, a.*, job
  FROM emp, dept b
  FULL JOIN (SELECT deptno, COUNT(*) AS 人数 FROM emp GROUP BY deptno) a ON b.deptno =
                                                                          a.deptno
 WHERE emp.deptno = a.deptno
   AND job = 'CLERK';
--列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级@
select e1.ename, dname, e2.ename 领导, e1.sal，s.grade
  from emp e1, dept d, emp e2, salgrade s
 where e1.mgr = e2.empno(+)
   and e1.deptno = d.deptno
   and e1.sal > (select avg(sal) from emp)
   and e1.sal between s.losal and s.hisal;
--列出与scott从事相同工作的所有员工共及部门名称
select ename, dname
  from dept d, emp e
 where d.deptno = e.deptno
   and e.job = (select job from emp where ename = 'SCOTT');
--列出薪金大与部门30中的任意员工的薪金的所有员工的姓名和薪金
select ename, sal
  from emp
 where sal > ANY (select sal from emp where deptno = 30);
--列出薪金大雨部门30中的全部员工的信息的所有员工的姓名和薪金，部门名称
SELECT e.*, dname
  FROM emp e, dept d
 WHERE sal > ALL (SELECT sal FROM emp WHERE deptno = 30)
   AND d.deptno = e.deptno;
--列出每个部门的员工数量，平均工资
SELECT count(*) 人数, avg(sal), deptno FROM emp GROUP BY deptno;
--列出每个部门的员工数量，平均工资和平均服务期限（月）@
select deptno,
       count(*) 员工数量,
       trunc(avg(sal + nvl(comm, 0))) 平均工资,
       to_char(to_date('0001-01', 'yyyy-mm') + avg(sysdate - hiredate) - 366 - 31,
               'yy"年"mm"月') 平均服务期限
  from emp
 group by deptno;
/*select sysdate sysdate+(sysdate-hiredate),
from emp*/
--列出各种工作的最低工资及从事工资最低工资的雇员名称@
select e.ename, a.*
  from emp e
 right join (select min(sal) 最低工资, job from emp group by job) a on e.job =
                                                                   a.job
 where e.sal = a.最低工资;
--求出部门名称带字符‘S’的部门员工，工资合计，部门人数@
select dname, a.*
  from dept d
  full join (select sum(sal) 工资合计, deptno from emp group by deptno) a on d.deptno =
                                                                         a.deptno
 where dname like '%S%';
--求出部门平均工资以及等级
select d.dname, b.*, s.grade
  from salgrade s,
       dept d,
       (select avg(sal) 平均工资, deptno from emp group by deptno) b
 where d.deptno(+) = b.deptno
   and b.平均工资 between s.losal and s.hisal;
--不使用函数查询工资最高人的信息
select * from emp where sal >= all (select sal from emp);
--求出平均工资最高的部门名称
select d.dname, b.平均工资
  from dept d,
       (select avg(sal) 平均工资, deptno from emp group by deptno) b
 where d.deptno = b.deptno
   and b.平均工资 >= all
 (select avg(sal) 平均工资 from emp group by deptno);
--求平均工资的等级最低的部门名称 @
select dname, b.grade
  from dept,
       (select grade, deptno
          from salgrade,
               (select deptno, avg(sal) 平均工资 from emp e group by deptno) a
         where a.平均工资 between losal and hisal) b
 where b.grade =
       (select min(grade)
          from salgrade,
               (select deptno, avg(sal) 平均工资 from emp e group by deptno) a
         where a.平均工资 between losal and hisal)
   and b.deptno = dept.deptno;
--部门经理人中平均工资最低的部门名称
select dname, a.*
  from dept,
       (select avg(sal), rank() over(order by avg(sal)) rk, deptno
          from emp
         where job = 'MANAGER'
         group by deptno) a
 where a.rk = 1
   and dept.deptno = a.deptno;

select sysdate,sysdate-hiredate, sysdate+(sysdate-hiredate)
from emp

select deptno,count(*) 员工数量,
trunc(avg(sal+nvl(comm,2))) 平均工资,
to_char(to_date('0001-01','yyyy-mm') + avg(sysdate-hiredate)-366-31,'yy"年"mm"月') 平均服务期限
from emp group by deptno;

to_char(to_date('0001-01','yyyy-mm') + avg(sysdate-hiredate)-366-31,'ddddd"天') 平均服务期限

pl/sql练习题

--1.自定义输入任意员工编号，输出该员工编号、姓名、工资、部门名称、所在地
declare
  empno integer;
  ename varchar2(10);
  sal   integer;
  dname varchar2(20);
  loc   varchar2(20);
  i     integer;
begin
  select empno, ename, sal, dname, loc
    into empno, ename, sal, dname, loc
    from emp,dept
   where emp.deptno = dept.deptno
     and empno = &i;
     dbms_output.put_line('姓名：'||ename||chr(13)||'工资:'||sal||chr(13)||'部门名称：'||dname||chr(13)||'所在地:'||loc);
  exception
  when no_data_found then
  dbms_output.put_line('工号不存在');
  end;

--2.自定义输入任意员工编号，如果该员工入职时间大于10年，则奖金加1W，如果该员工入职时间大于五年，奖金加5000，否则奖金不加，最终输出员工编号、姓名、入职时间、原奖金、现奖金
--【--第六天1.2】
declare
  empno    integer;
  ename    varchar2(10);
  hiredate date;
  comm     integer;
  comm1    integer;
  years    int;
  i        number;
begin
  i := &i;
  select empno, ename, hiredate, comm
    into empno, ename, hiredate, comm
    from emp
   where empno = i;
  select months_between(sysdate, hiredate)
    into years
    from emp
   where empno = i;
  dbms_output.put_line('员工编号：' || empno || chr(13) || '姓名：' || ename ||
                       chr(13) || '入职时间' || hiredate || chr(13) || '原奖金' || comm ||
                       chr(13));
  if years / 12 > 10 then
    select comm + 10000 into comm1 from emp where empno = i;
  elsif years / 12 > 5 then
    select comm + 5000 into comm1 from emp where empno = i;

  end if;
  dbms_output.put_line('现奖金' || comm1);
end;

--3.自定义输入部门编号，查询出该部门编号下所有员工信息（姓名、工资、部门编号），并显示信息条数
declare
  ename  emp.ename%type;
  sal    emp.sal%type;
  deptno emp.deptno%type;
  i      number := &i;
  cursor c1 is
    select ename, sal, deptno from emp where deptno = i;
begin
  open c1;
  loop
    fetch c1
      into ename, sal, deptno;
    if c1%found then
      dbms_output.put_line('姓名' || ename || chr(13) || '工资' || sal ||
                           chr(13) || '部门编号：' || deptno || chr(13));
    else
      dbms_output.put_line('查询完毕！');
      dbms_output.put_line('共有' || c1%rowcount || '条记录');
      exit;
    end if;
  end loop;
  close c1;
end;

--4.自定义输入员工编号，若该员工工资低于5000，则加奖金500l；
--若员工工资高于5000，则加奖金100；
--最 终输出员工编号、姓名、工资、原奖金、先奖金
declare
  empno emp.empno%type;
  ename emp.ename%type;
  sal   emp.sal%type;
  comm  m%type;
  comm1 m%type;
  i     number := &i;
begin
  select empno, ename, comm, sal
    into empno, ename, comm, sal
    from emp
   where empno = i;
  dbms_output.put_line('员工编号：' || empno || chr(13) || '姓名：' || ename ||
                       chr(13) || '原奖金:' || nvl(comm, 0) || chr(13));
  if sal < 5000 then
    select nvl(comm, 0) + 5001 into comm1 from emp where empno = i;
  else
    select nvl(comm, 0) + 100 into comm1 from emp where empno = i;

  end if;
  dbms_output.put_line('现奖金' || comm1);
exception
  when no_data_found then
    dbms_output.put_line('工号不存在');
end;

--5.对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，则将其薪水减50 ，更新前后的薪水，员工姓名，所在部门编号
declare
  t_ename  emp.ename%type;
  t_sal    emp.sal%type;
  sal1     emp.sal%type;
  t_deptno emp.deptno%type;
  avsal    emp.sal%type;
  cursor c_1 is
    select ename, sal, deptno from emp;
begin
  open c_1;
  loop
    fetch c_1
      into t_ename, t_sal, t_deptno;
    exit when c_1%notfound;
    select avg(sal) into avsal from emp where deptno = t_deptno;
    if t_sal > avsal then
      sal1 := t_sal - 50;
    else
      sal1 := t_sal;
    end if;
    dbms_output.put_line('员工姓名：' || t_ename || chr(13) || '部门编号' ||
                         t_deptno || chr(13) || '原薪水：' || t_sal || chr(13) ||
                         '现薪水' || sal1 || chr(13));
  end loop;
  close c_1;
end;

--6.创建一个存储过程，实现：通过输入员工编号查看员工姓名、工资、奖金：
--1.1如果输入的编号不存在，进行异常处理；
--1.2如果工资高于4000，进行异常处理提示；
--1.3如果奖金没有或为0，进行异常处理提示
create or replace procedure proc(t_empno in emp.empno%type) as
  t_comm m%type;
  t_sal  emp.sal%type;
  t_name emp.ename%type;
  i      number;
begin
  select count(*) into i from emp where empno = t_empno;
  if i = 0 then
    RAISE_application_error(-20114, '编号不存在');
  else
    select comm, sal, ename
      into t_comm, t_sal, t_name
      from emp
     where empno = t_empno;
  end if;
  if t_sal > 4000 then
    RAISE_application_error(-20112, '工资高于4000');
  elsif t_comm = 0 or t_comm is null then
    RAISE_application_error(-20113, '奖金为空');
  else
    dbms_output.put_line('姓名：' || t_name || chr(13) || '工资：' || t_sal ||
                         chr(13) || '奖金：' || t_comm);
  end if;
end proc;
call proc(654);