oracle练习题.docx

oracle练习题查询练习一-查询姓名首字母为“A”或第二个字符为“A”的所有员工信息SELECT *FROM empWHERE ename LIKE A%OR ename LIKE _A%;-查询部门20和30中的、岗位不是“CLERK”或“SALESMAN”的所有员工信息SELECT *FROM empWHERE job != CLERKAND job != SALESMANAND deptno IN (20, 30);-查询出工资在2500-3500之间，1981年入职的，没有奖金的所有员工信息SELECT *FROM empWHERE EXTRACT(YEAR FROM hiredate) = 1981AND sal BETWEEN 2500 AND 3000AND comm IS NULL;-查询比平均员工工资高的员工信息SELECT deptno, dname, locFROM deptWHERE deptno IN (SELECT deptnoFROM empGROUP BY deptnoHAVING AVG(sal) 2000);-查询平均工资高于2000的部门信息select deptno ,dname,locfrom deptwhere deptno in(select deptno from emp group by deptno having avg(sal)2000);-查询出ward的工作所在地SELECT locFROM deptWHERE deptno IN (SELECT deptnoFROM empWHERE ename = WARD);-查询出工资比ADAMS高的所有人姓名、部门、所在地SELECT a.ename, b.dname, a.salFROM emp a, dept bWHERE a.deptno = b.deptnoAND a.sal (SELECT salFROM empWHERE ename = ADAMS);-查询出工资排名第7的员工信息SELECT *FROM (SELECT rank() OVER (ORDER BY sal DESC) AS rk, emp.*FROM emp)WHERE rk = 7;/*minue:两个结果值相减， uniou:两个结果集拼到一起（17）-（16）=排名第7*/-查询与部门20岗位不同的员工工资SELECT sal, job,deptnoFROM empWHERE job NOT IN (SELECT jobFROM empWHERE deptno = 20);-验证 -20部门的岗位/*select jobfrom empwhere deptno=20;*/-查询与smith部门岗位完全相同的员工姓名、工作、工资SELECT *FROM empWHERE deptno IN (SELECT deptnoFROM empWHERE ename = SMITH)AND job IN (SELECT jobFROM empWHERE ename = SMITH); -查询emp表中的所有信息 select * from emp; -查询emp表中的员工姓名和工资 select ename,sal from emp; -查询emp表中部门编号为20的并且sal大于3000的所有员工信息 select * from emp where deptno=20 and sal3000; -查询emp表中部门编号为20的或者sal大于3000的所有员工信息 select * from emp where deptno=20 or sal3000; -使用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:两个结果集拼到一起（17）-（16）=排名第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 bwhere 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 empwhere job in MANAGERgroup by deptno;-查询出部门人数至少是1的部门名字select dname from dept full join (select deptno, count(*) 人数 from emp group by deptno) b on dept.deptno = b.deptnowhere 人数 = 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;-求出某个员工的领导，并要求这些领导的薪水高于或等于3000select a.ename, a.sal from emp a, emp bwhere 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.deptnoWHERE 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 swhere 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 ewhere d.deptno = e.deptno and e.job = (select job from emp where ename = SCOTT);-列出薪金大与部门30中的任意员工的薪金的所有员工的姓名和薪金select ename, sal from empwhere sal ANY (select sal from emp where deptno = 30);-列出薪金大雨部门30中的全部员工的信息的所有员工的姓名和薪金，部门名称SELECT e.*, dname FROM emp e, dept dWHERE 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 empgroup by deptno;/*select sysdate sysdate+(sysdate-hiredate),from emp*/-列出各种工作的最低工资及从事工资最低工资的雇员名称select e.ename, a.* from emp eright join (select min(sal) 最低工资, job from emp group by job) a on e.job = a.jobwhere 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.deptnowhere dname like %S%;-求出部门平均工资以及等级select d.dname, b.*, s.grade from salgrade s, dept d, (select avg(sal) 平均工资, deptno from emp group by deptno) bwhere 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) bwhere 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) bwhere 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) awhere a.rk = 1 and dept.deptno = a.deptno;select sysdate,sysdate-hiredate, sysdate+(sysdate-hiredate)from empselect 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 emp.comm%type; comm1 emp.comm%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 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 emp.comm%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);