内容发布更新时间 : 2024/5/21 20:38:13星期一 下面是文章的全部内容请认真阅读。
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
实验六：