ORACLE数据库习题(3)

var_sal number(9,2); begin --执行部分的开始

select ename ，sal into var_name ，var_sal from emp where empno = var_empno; dbms_output.put_line(var_empno || ' 的名字是 :' || var_name); --输出语句 dbms_output.put_line(var_empno || ' 的工资是 :' || var_ sal ); --输出语句 end; --执行部分的结束，end后面要加 ;

----执行结果为：

7369 的名字是 : SMITH PL/SQL 过程已成功完成。

17.编写一sql语句，将7369号员工的入职日期改为1997/7/1，工资提高800 ，其他信息不变。

update emp set hiredate=to_date('1997/7/1','yyyy-mm-dd'),sal=sal+800 where empno=7369;

18.假定现在有一张表，表名为T_emp ，其表结构和现有的emp表结构完全相同，但是T_emp中没有记录，请编写一sql语句，实现将emp中部门编号为20的员工信息添加到T_emp中。

19. 对所有员工,如果该员工职位是MANAGER，并且在DALLAS工作那么就给他薪金加15％； 如果该员工职位是CLERK，并且在NEW YORK工作那么就给他薪金扣除5％;其他情况不作处理。要求：使用游标或函数编程实现。 set serveroutput on; declare

cursor c1 is select * from emp; c1rec c1%rowtype; v_loc varchar2(20); begin

for c1rec in c1 loop

select loc into v_loc from dept where deptno = c1rec.deptno; if c1rec.job = 'MANAGER' and v_loc = 'DALLAS' then

update emp set sal = sal * 1.15 where empno = c1rec.empno; elsif c1rec.job='CLERK' and v_loc = 'NEW YORK' then

update emp set sal = sal * 0.95 where empno = c1rec.empno; else null; end if;

end loop;

end;

20. 对直接上级是'BLAKE'的所有员工，按照参加工作的时间加薪：1981年6月以前入职的员工加薪10％、1981年6月以后入职的员工加薪5％。 要求：使用游标或函数编程实现。

declare

cursor c1 is select * from emp where mgr = (select

empno from emp where ename='BLAKE'); --直接上级是'BLAKE'的所有员工 c1rec c1%rowtype; begin

for c1rec in c1 loop

if c1rec.hiredate < '01-6月-81' then

update emp set sal = sal * 1.1 where empno = c1rec.empno; else

update emp set sal = sal * 1.05 where empno = c1rec.empno; end if; end loop; end;

1. 创建一个角色MYROLE，此角色具有建表，建视图的系统权限和查找SCOTT用户

EMP,DEPT表的权限，将此角色授权给TEST用户。 CREATE ROLE MYROLE;

GRANT CREATE TABLE,CREATE VIEW TO MYROLE; GRANT SELECT ON SCOTT.EMP TO MYROLE; GRANT SELECT ON SCOTT.DEPT TO MYROLE; CREATE USER TEST IDENTIFIED BY TEST; GRANT MYROLE TO TEST;

2. 创建EMP，DEPT两张表的副表（包括它们之间的约束）

CREATE TABLE mydept AS SELECT * FROM dept;

ALTER TABLE mydept ADD CONSTRAINT pk_mydept_deptno PRIMARY KEY(deptno); CREATE TABLE myemp AS SELECT * FROM emp

ALTER TABLE myemp ADD CONSTRAINT pk_myemp_empno PAIMARY KEY(empno); ALTER TABLE myemp ADD CONSTRAINT fk_myemp_mydept FOREIGN KEY(deptno) REFERENCES mydept(deptno);

3. 找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪

金>=2000的所有雇员的详细资料

select * from emp where (deptno=10 and job=upper('manager')) or (deptno=20 and job=upper('clerk ')) or (job<>upper(?manager?) and job<>upper(?clerk?) and sal>=2000)

4. 找出不收取佣金或收取的佣金低于100的雇员

select * from emp where nvl(comm,0)<100;

5. 显示不带有'R'的雇员姓名

Select ename from emp where ename not like ?%R%?;

6. 显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序

Select ename,job,sal from emp order by job desc ,sal asc

7. 求哪些人工资在平均工资之上，列出姓名和工资。

select ename,sal from emp where sal > (select avg(sal) from emp);

8. 显示除20部门外，部门平均工资在2500以上的部门，列出部门编号和平均工资。

select avg(sal) , deptno from emp where deptno <>20 group by deptno having avg(sal) > 2500;

9. 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

select * from emp where sal in (select sal from emp where deptno=30);

10. 列出所有部门的详细信息和部门人数。

select a.*,(select count(*) from emp where deptno=a.deptno) tot from dept a ;

11. 列出各种工作的最低月收入。

select job,min(sal+nvl(comm,0)) from emp group by job;

12. 返回工资水平(等级)高于平均工资水平的员工详细信息。

select * from emp e join salgrade s on (e.sal between s.losal and s.hisal) where s.grade>(select distinct s.grade from emp e join salgrade s on ((select avg(sal) from emp) between s.losal and s.hisal));

13. 列出至少有两个员工的所有部门信息

select * from dept where deptno in

(select deptno from emp group by deptno having count(*)>1);

14. 查询emp表的第6行到第10行内容

Select * from (select rownum r,e.* from emp e) where r between 6 and 10;

15. 查询入职最早的前5到10名员工的姓名和入职日期（按年月日格式显示日期）

Select ename,to_char(hiredate,?yyyy-mm-dd?) from (select ename,hiredate,rownum r from (select ename,hiredate from emp order by hiredate ) where rownum<=10) where r>=5;

16. 编写一sql语句，将7369号员工的入职日期改为1997/7/1，工资提高800 ，其他信息

不变。 update emp set hiredate=to_date('1997/7/1','yyyy-mm-dd'),sal=sal+800 where empno=7369;

17. 查询雇员和其直接领导姓名及他们所在部门名称,没有领导的雇员也要列出.

select e.ename,ed.dname,m.ename,md.dname from emp e,emp m,dept ed,dept md where e.deptno=ed.deptno and e.mgr=m.empno(+) and m.deptno=md.deptno(+); 18. 列出部门编号,部门名称,部门的员工数量,部门的平均工资.

select d.deptno,d.dname,t.cou,t.avg from dept d,(select deptno,count(empno) cou,avg(sal) avg from emp group by deptno) t where d.deptno=t.deptno;

19. 定义游标update_cur，根据职务调整雇员的工资，如果职务为?SALESMAN?或者

?ANALYST?工资上调100元，如果职务为?MANAGER?工资上调200元，其它职务工资上调50元。 DECLARE v_job emp.job%TYPE; CURSOR update_cur IS SELECT job FROM emp FOR UPDATE; BEGIN OPEN emp_cur; LOOP FETCH update_cur INTO v_job; EXIT WHEN emp_cur%NOTFOUND; CASE WHEN v_job='SALESMAN' OR v_job='ANALYST' THEN update emp set sal=sal+100 where current of emp_cur; WHEN v_job=' MANAGER ' THEN update emp set sal=sal+200 where current of emp_cur; ELSE update emp set sal=sal+50 where current of emp_cur; END CASE; END LOOP; END; 20. 创建一个触发器，使其可以修改DEPT表的DEPTNO。 create or replace trigger update_dept after update on dept for each row begin update emp set deptno=:new.deptno where deptno=:old.deptno; end;

1.

对所有员工,如果该员工职位是MANAGER，并且在DALLAS工作那么就给他薪金加15％；

如果该员工职位是CLERK，并且在NEW YORK工作那么就给他薪金扣除5％;其他情况不作处理。要求：使用游标或函数编程实现。

1.

set serveroutput on; declare

cursor c1 is select * from emp; c1rec c1%rowtype; v_loc varchar2(20); begin

for c1rec in c1 loop

select loc into v_loc from dept where deptno = c1rec.deptno; if c1rec.job = 'MANAGER' and v_loc = 'DALLAS' then

update emp set sal = sal * 1.15 where empno = c1rec.empno; elsif c1rec.job='CLERK' and v_loc = 'NEW YORK' then

update emp set sal = sal * 0.95 where empno = c1rec.empno; else null; end if;

end loop; end;

2. 用PL/sql实现输出7369号员工的姓名。

set serveroutput on; -- 将sql*plus的输出功能开启

declare --声明部分

var_empno number(4) := 7369 ; --声明一个变量var_empno ，并给赋值，用:= 赋值var_name varchar2(10); --声明一个变量var_name

var_sal number(9,2); begin --执行部分的开始

select ename ，sal into var_name ，var_sal from emp where empno = var_empno; dbms_output.put_line(var_empno || ' 的名字是 :' || var_name); --输出语句 dbms_output.put_line(var_empno || ' 的工资是 :' || var_ sal ); --输出语句 end; --执行部分的结束，end后面要加 ;

----执行结果为：

7369 的名字是 : SMITH

1. 定义游标update_cur，根据职务调整雇员的工资，如果职务为

?SALESMAN?或者?ANALYST?工资上调100元，如果职务为?MANAGER?工资上调200元，其它职务工资上调50元。

1.

DECLARE

v_job emp.job%TYPE; CURSOR update_cur IS

SELECT job FROM emp FOR UPDATE; BEGIN

OPEN emp_cur; LOOP

FETCH update_cur INTO v_job;

EXIT WHEN emp_cur%NOTFOUND; CASE

WHEN v_job='SALESMAN' OR v_job='ANALYST' THEN update emp set sal=sal+100 where current of emp_cur; WHEN v_job=' MANAGER ' THEN

update emp set sal=sal+200 where current of emp_cur; ELSE

update emp set sal=sal+50 where current of emp_cur;

END CASE; END LOOP; END;

2. 创建一个触发器，使其可以修改DEPT表的DEPTNO。

create or replace trigger update_dept after update on dept for each row begin

update emp set deptno=:new.deptno where deptno=:old.deptno; end;

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库ORACLE数据库习题(3)在线全文阅读。