oracle练习题

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 BETWEEN2500AND3000

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=20and sal>3000;

--查询emp表中部门编号为20的或者sal大于3000的所有员工信息select*

from emp

where deptno=20or sal>3000;

--使用between and查询工资在2000到4000之间的员工

select*

from emp

where sal between2000and4000;

--使用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.j ob

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;

comm1integer;

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>10then

select comm +10000into comm1from emp where empno = i;

elsif years /12>5then

select comm +5000into comm1from 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 c1is

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 https://www.360docs.net/doc/f514955585.html,m%type;

comm1 https://www.360docs.net/doc/f514955585.html,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 <5000then

select nvl(comm,0) +5001into comm1from emp where empno = i;

else

select nvl(comm,0) +100into comm1from 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;

sal1emp.sal%type;

t_deptno emp.deptno%type;

avsal emp.sal%type;

cursor c_1is

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 https://www.360docs.net/doc/f514955585.html,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 =0then

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 >4000then

RAISE_application_error(-20112,'工资高于4000');

elsif t_comm =0or 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);