经典sql-高级-实验三

实验三、

1．建立一个表（表名自定），表结构与EMP相同，没有任何记录。

create table my_emp as select * from emp;

2．用Insert语句输入5条记录，并提交。

3．扩大该表的记录数到约40条，并使雇员号不重复；每个雇员都有所属部门，雇员在同一部门的经理是同一人。

insert….

update…

commit

4．建立一个与DEPT表结构和记录完全相同的新表，并与前项新表建立参照完整性约束。

alter table my_dept add( constraint s1 primary key(deptno));

alter table my_emp add(constraint s2 foreign key(deptno) references dept(deptno));

5．对在‘NEW YORK’工作的雇员加工资，每人加200。

6． *如果雇员姓名与部门名称中有一个或一个以上相同的字母，则该雇员的COMM增加500。

update my_emp a

set comm=NVL(comm,0)+500

where a.ename<>(

select translate(a.ename,b.dname,CHR(27))

from my_dept b where b.deptno=a.deptno

);

--a.deptno与b.deptno必须有主外键连接，否则可能出错，为什么？commit;

7．删除部门号为30的记录，并删除该部门的所有成员。

delete from emp where deptno=30;

delete from dept where deptno=30;

commit

8．新增列性别SEX，字符型。

alter table emp add(sex char(2));

9．修改新雇员表中的MGR列，为字符型。

该列数据必须为空

alter table emp modify(mgr varchar2(20));

10．试着去删除新表中的一个列。

alter table my_emp drop (comm);

实验四、

1．查询部门号为30的所有人员的管理层次图。

select level,ename from emp

connect by mgr=prior empno

start with deptno=30and job='MANAGER';

2．查询员工SMITH的各个层次领导。

select level,ename from emp

connect by prior mgr= empno

start with ENAME='SMITH';

3．查询显示EMP表各雇员的工作类型，并翻译为中文显示

用decode函数

4． *查询显示雇员进入公司当年是什么属相年（不考虑农历的年份算法）用decode函数

5．建立一个视图myV_emp，视图包括myEMP表的empno、ename、sal，并按sal 从大到小排列。

create view myV_EMP as select empno,ename,sal from emp;

6．定义一个mySeq，对select mySeq.nextval,my_emp.* from my_emp的执行结果进行说明。

7．定义序列mySeq、myEMP、myV_emp的同义词，能否用同义词对上述对象进行访问。

8．在myEMP表中建立ename的唯一性索引。

9．如何在sql*plus中，运行sql的脚本（即后缀为.sql的文件）

实验五、

1．观察下列PL/SQL的执行结果

declare

s emp%rowtype;

begin

select * into s

from emp

where ename='KING';

DBMS_OUTPUT.PUT_LINE(s.empno||s.ename||s.job||s.sal);

END;

2．编写一个PL/SQL，显示ASC码值从32至120的字符。

begin

for i in32..120

loop

dbms_output.put_line(chr(i));

end loop;

end;

3．计算myEMP表中COMM最高与最低的差值，COMM值为空时按0计算。declare

var1 number;

var2 number;

val_comm number;

begin

select max(nvl(comm,0)) into var1 from myemp;

select min(nvl(comm,0)) into var2 from myemp;

val_comm:=var1-var2;

dbms_output.put_line(val_comm);

end;

4．根据表myEMP中deptno字段的值，为姓名为‘JONES’的雇员修改工资；若部门号为10，则工资加100；部门号为20，加200；其他部门加400。

declare

c1 number;

c2 number;

begin

select deptno into c1 from emp where ename=’JONES’;

if c1=10then

c2:=100;

elsif c1=20then

c2:=200;

else c2:=400;

end if;

update emp set sal=sal+c2 where ename=’JONES’;

commit;

end;

5．计算显示部门人数最多的部门号、人数、工资总和，以及部门人数最少的部门号、人数、工资总和。

6．计算myEMP中所有雇员的所得税总和。假设所得税为累进税率，所得税算法为：工资收入为0－1000为免税；收入1000－2000者，超过1000的部分税率10％；2000－3000者超过2000部分按20％税率计算；3000－4000者超过3000部分按30％税率计算；4000以上收入，超过4000部分按40％税率计算。（请查阅累进税率的概念）

declare

sum_xx number:=0;

xx number;

begin

--计算收入为1000－2000的所得税总额

select sum((sal-1000)*0.1) into xx from emp where sal >1000and sal<=2000; sum_xx:=sum_xx+xx;

--计算收入为2000－3000的所得税总额

select sum((sal-2000)*0.2+100) into xx from emp where sal >2000and sal<=3000;

sum_xx:=sum_xx+xx;