Oracle实验一上机作业

1．对基本表EMP和DEPT操作：

1）列出工资在1000到2000之间的所有员工的ENAME，DID，SALARY。 Select ename,deptno,sal from emp where sal between 1000 and 2000; 2）显示DEPT表中的部门号和部门名称，并按部门名称排序。 select deptno,dname from dept order by dname;

3）列出部门号在10到20之间的所有员工，并按名字的字母排序。 select * from emp where deptno between 10 and 20 order by ename; 4）显示名字中包含TH和LL的员工名字。

select * from emp where ename like *%TH%*or ename like *%LL%*; 5）显示在1983年中雇佣的员工。

select ename,hiredate from emp where to_char(hiredate,*YYYY*)=*1983* 6）查询每个部门的平均工资。

Select deptno ,avg(sal) from emp group by deptno; 7）查询出每个部门中工资最高的职工。

select * from emp where sal in (select max(sal) from emp group by deptno); 8）查询出每个部门比平均工资高的职工人数。

select a.deptno,count(*) from emp a,(select deptno,avg(sal) avgsal from emp group by deptno) b where a.deptno=b.deptno and a.sal>b.avgsal group by a.deptno;

2.生成一个数据表PROJECTS，其字段定义如下，其中PROJID是主键并且要求P_END_DATE不能比P_START_DATE早。CHECK(P_END_DATE )= P_START_DATE); 字段名称数据类型 长度 PROJID NUMBER 4

P_DESC VARCHAR2 20 P_START_DATEDATE P_END_DATEDATE

BUDGET_AMOUNTNUMBER 7,2 MAX_NO_STAFFNUMBER 2 create table PROJECTS(

PROJID number(4) primary key, P_DESC varchar2(20), P_START_DATE DATE, P_END_DATE DATE,

BUDGET_AMOUNT number(7,2), MAX_NO_STAFF number(2),

CHECK(P_END_DATE >= P_START_DATE) ) ;

3.生成一个数据表ASSIGNMENTS，其字段定义如下，其中PROJID是外键引自PROJECTS数据表，EMPNO是数据表EMP的外键，并且要求PROJID和EMPNO不能为NULL。

字段名称 数据类型 长度 PROJID NUMBER 4 EMPNO NUMBER 4 A_START_DATEDATE A_END_DATEDATE

BILL_RATENUMBER 4,2 ASSIGN_TYPEVARCHAR2 2 create table ASSIGNMENTS( PROJID number(4) not null, EMPNO number(4) not null, A_START_DATE date, A_END_DATE date,

BILL_RATE number(4,2), ASSIGN_TYPE varchar2(2),

FOREIGN KEY(PROJID) REFERENCES PROJECTS(PROJID), FOREIGN KEY(EMPNO) REFERENCES EMP(EMPNO));

4.用DESCRIBE命令查看2和3题定义的字段。 desc PROJECTS; desc ASSIGNMENTS;

5.给2题中的PROJECTS数据表增加一个COMMENTS字段，其类型为LONG。给3题中的ASSIGNMENTS数据表增加一个HOURS字段，其类型为NUMBER。 alter table PROJECTS add COMMENTS long; alter table ASSIGNMENTS add HOURS number;

6.在PROJECTS数据库表中增加下列记录： PROJID 1 2 P_DESC WRITE C030 COURSEP ROOF READ NOTES P_START_DATE 02-JAN-88 01-JAN-89 P_END_DATE 07-JAN-88 10-JAN-89 BUDGET_AMOUNT 500 600 MAX_NO_STAFF 1 1 COMMENTS BR CREATIVE YOUR CHOICE

Insert into PROJECTS values(1,*WRITE C030 COURSE*,*02-1月-88*,*07-1月-88*,500,1, *BR CREATIVE*);

Insert into PROJECTS values(2,*PROOF READ NOTES*,*01-1月-89*,*10-1月-89*,600,1, *YOUR CHOICE*);

7.在ASSIGNMENTS数据库表中增加下列记录： PROJID 1 1 2

EMPNO 7369 7902 7844

A_START_DATE01-JAN-8804-JAN-8801-JAN-89 A_END_DATE03-JAN-8807-JAN-8810-JAN-89 BILL_RATE50.00 55.0045.50 ASSIGN_TYPEWR WR PF HOURS 15 20 30

Insert into ASSIGNMENTS values(1,7369,*01-1月-88*,*03-1月-88*,50.00,*WR*,15); Insert into ASSIGNMENTS values(1,7902,*04-1月-88*,*07-1月-88*,55.00,*WR*,20); Insert into ASSIGNMENTS values(2,7844,*01-1月-89*,*10-1月-89*,45.50,*PF*,30);

8.把ASSIGMENTS表中ASSIGNMENT TYPE的WR改为WT，其他的值不变。

update ASSIGNMENTS set ASSIGN_TYPE=*WT* where ASSIGN_TYPE=*WR*;

9.在PROJECTS 和ASSIGNMENTS插入更多的记录。

Insert into PROJECTS values(3,*WRITE C030 COURSE*,*02-1月-88*,*07-1月-88*,400,1, *BR CREATIVE*);

Insert into PROJECTS values(4,*PROOF READ NOTES*,*01-1月-90*,*10-1月-90*,700,1, *YOUR CHOICE*);

Insert into ASSIGNMENTS values(3,7521,*04-5月-88*,*07-5月-88*,58.00,*WR*,20); Insert into ASSIGNMENTS values(4,7499,*01-2月-89*,*10-2月-89*,65.50,*PF*,25);

实验二（视图）：

1．根据ORACLE EMPLOYEES表中的雇员编号、雇员名字、部门编号创建一个名为EMPLOYEES_VU的视图。把雇员名字的表头改为EMPLOYEE。

create view EMPLOYEES_VU(EMPLOYEE_ID,EMPLOYEE,DEPARTMENT_ID) as select EMPLOYEE_ID,LAST_NAME,DEPARTMENT_ID FROM EMPLOYEES; 2．显示EMPLOYEES_VU视图的内容。 select * FROM EMPLOYEES_VU;

3．从USER_VIEWS数据字典视图中选择视图的名字和文本。 EMP_DETAILS_VIEW作为你的模式的一部分已被创建。

注意：为了看到一个LONG列的更多内容，可以使用iSQL*Plus中SET LONG n命令，这里n的值就是你想看到的LONG列的字符的数目。 SELECT VIEW_NAME,TEXT FROM USER_VIEWS;

4．使用EMPLOYEES_VU视图，输入一个查询显示所有雇员的名字和部门号。 SELECT EMPLOYEE,DEPARTMENT_ID FROM EMPLOYEES_VU;

5．建立一个名为DEPT50的视图，其中包含在50部门中所有雇员的雇员号、雇员名和部门编号，标识视图列为EMPNO, EMPLOYEE和 DEPTNO。不能允许通过视图将一个雇员再分配到另一个部门。

CREATE VIEW DEPT50(EMPNO,EMPLOYEE,DEPTNO) AS SELECT

EMPLOYEE_ID,LAST_NAME,DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID=50;

6．显示DEPT50视图的结构和内容

DESC DEPT50; SELECT * FROM DEPT50; 7．试图把Matos调派到部门80中去。

UPDATE DEPT50 SET DEPTNO=80 WHERE EMPLOYEE=*Matos*;

8．建立名为SALARY_VU的视图，其中包含所有雇员的名、部门名字、薪水和薪水等级。使用 EMPLOYEES, DEPARTMENTS和JOB_GRADES 表。分别用Employee、Department、 Salary 和 Grade标识各列。

CREATE VIEW SALARY_VU(Employee,Department,Salary,Grade) as select a.LAST_NAME,a.Salary,b.DEPARTMENT_NAME,c.JOB_TITLE

from EMPLOYEES a,DEPARTMENTS b,JOBS c where a.DEPARTMENT_ID=b. DEPARTMENT_ID and a. JOB_ID=c. JOB_ID

实验三：练习题

1、 查询emp表每个部门有多少人，平均工资，最高工资和最低工资。

select count(*) deptCount,avg(sal),max(sal),min(sal) from emp group by deptno; 2、 查询emp表每个部门工资低于3000人的总人数。

select count(*) from emp where sal<3000 group by deptno; 3、 查询emp表平均工资高于2000的部门。

select deptno from emp group by deptno having avg(sal)>2000; （二） 索引

1、写出对S表的S#建立索引index_S#的SQL语句。 create index index_S# on S(S#); ORA-01408: 此列列表已编制索引

2、写出对S表的SNAME建立不重名索引index_SNAME的SQL语句。 SQL> create unique index index_SNAME on S(SNAME); Index created

3、写出对SC表的主键+成绩（降序）的索引

SQL> create index S#_C#_Grade on SC(S#,C#,GRADE desc); Index created

实验五：

1.对PAY_TABLE表写一个查询所有记录的过程。 SQL> create or replace procedure pay_table_Pro1 as 2 cursor my_cursor is 3 select * from pay_table; 4 begin

5 dbms_output.put_line(*name pay_type pay_rate eff_date prev_pay*); 6 for info in my_cursor loop

7 dbms_output.put_line(info.name||* *||info.pay_type||* *||info.pay_rate||* *||info.eff_date||* *||info.prev_pay); 8 end loop;

9 end pay_table_Pro1; 10 /

Procedure created

SQL> exec pay_table_Pro1;

name pay_type pay_rate eff_date prev_pay

SANDRA SAMUELS SALARY 31937.5 01-1月 -04 ROBERT BOBAY SALARY 29382.5 15-5月 -03 KEITH JONES SALARY 25550 31-10月-04 SUSAN WILLIAMS SALARY 24911.25 01-5月 -04 CHRISSY ZOES SALARY 50000 01-1月 -04 CLODE EVANS SALARY 42150 01-3月 -04 JOHN SMITH SALARY 35000 15-6月 -03 KEVIN TROLLBERG SALARY 27500 15-6月 -03 yanglz salary 60000 15-6月 -13

PL/SQL procedure successfully completed

2.对PAY_TABLE表写一个插入一条记录的过程，要求输入参数为一条记录。 SQL> create or replace procedure addpay_pro2( 2 v_name in pay_table.name%type,

3 v_pay_type in pay_table.pay_type%type, 4 v_pay_rate in pay_table.pay_rate%type, 5 v_eff_date in pay_table.eff_date%type) is 6 begin

7 insert into pay_table(name,pay_type,pay_rate,eff_date) values(v_name,v_pay_type,v_pay_rate,v_eff_date); 8 end addpay_pro2; 9 10 /

Procedure created

SQL> exec addpay_pro2(*yanglz*,*salary*,60000.00,*15-6月-13*);

PL/SQL procedure successfully completed

3.写一个过程实现如下要求：对工作时间超过8个月的职员，如果PAY_TYPE

是HOURLY,则改为SALARY,并将PAY_RATE改为按每天7小时工作的年薪. SQL> create or replace procedure updatepay_pro3 is 2 begin

3 update pay_table set pay_type=*SALARY*,pay_rate=pay_rate*7*365 where (sysdate-eff_date)/365*12>8 and pay_type=*HOURLY*; 4 end updatepay_pro3; 5 /

Procedure created

SQL> exec updatepay_pro3;

PL/SQL procedure successfully completed

SQL> select * from pay_table;

NAME PAY_TYPE PAY_RATE EFF_DATE PREV_PAY -------------------- -------- ---------- ----------- ---------- SANDRA SAMUELS SALARY 31937.50 2004/1/1 ROBERT BOBAY SALARY 29382.50 2003/5/15 KEITH JONES SALARY 25550.00 2004/10/31 SUSAN WILLIAMS SALARY 24911.25 2004/5/1 CHRISSY ZOES SALARY 50000.00 2004/1/1 CLODE EVANS SALARY 42150.00 2004/3/1 JOHN SMITH SALARY 35000.00 2003/6/15

KEVIN TROLLBERG SALARY 27500.00 2003/6/15 yanglz salary 60000.00 2013/6/15

9 rows selected

实验六：