实验要求:
1、create table emp(deptno number(2),empno number(4),ename varchar2(20),job varchar2(20),sal number(10,2)); insert into emp values(10,7782,'clark','manager',3282.13); insert into emp values(10,7839,'king','president',3337.58); insert into emp values(20,7369,'smith','clerk',3337.58); insert into emp values(20,7566,'jones','manager',3599.75); insert into emp values(20,7788,'scott','analyst',3337.58); insert into emp values(20,7876,'adams','clerk',3448.50); insert into emp values(20,7902,'ford','analyst',2964.50); insert into emp values(30,7499,'allen','salesman',3630); insert into emp values(30,7521,'ward','salesman',6050); insert into emp values(30,7654,'martin','salesman',3337); insert into emp values(30,7698,'blake','manager',3337.58); insert into emp values(30,7844,'turner','salesman',4558.5); insert into emp values(30,7900,'james','clerk',4523); 2、create table dept(deptno number(5),dname varchar2(20),loc varchar2(20)); insert into dept values(10,'accounting','new york'); insert into dept values(20,'research','dallas'); insert into dept values(30,'salese','chicago'); insert into dept values(40,'operations','boston'); 3、创建一个查询部门编号为20的记录的视图。 4、向视图emp_view中插入一条记录(9537,'东方','manager',20),然后修改这条记录的ename字段值为’ 西方‘。 5、创建一个只读视图,要求该视图可以获得部门编号不等于88的其他所有部门信息,通过该只读视图修改所有部门的位置为'长春'. 6、创建一个视图能够查询每个部门的工资情况(deptno, 最高工资,最低工资,平均工资)。 7、创建一个dept表与 emp相关联的视图(dname,loc,empno,ename),并要求该视图只能查询编号为20的记录信息。 8、删除上题中创建的视图、 9、为emp表的deptno列创建索引, 10、首先创建基本表并插入数据,语句如下: CREATE TABLE t (quantity NUMBER(9), price NUMBER(9)); INSERT INTO t VALUES(3, 50);在t表格上创建一个名为view_tc的视图,限制条件为字段price的值大于10, 创建完成后,向视图view_tc插入、更新和删除等操作时,会收到检查条件的限制。向视图view_tc插入数据(3,5)。 |
代码如下:
1,2题为创建表和插入数据
3.
create view emp_view
as
select empno,ename,job,sal
from emp
where deptno=20;
4.
insert
into emp_view
values(9537,'东方','manager',20);
update emp_view
set ename='西方'
where empno=9537;
5.
create view dept_view
as
select deptno,dname,loc
from dept
where deptno!=88
with read only;
update dept_view
set loc='长春';
6.
create view e_view
as
select deptno,max(sal) as 最高工资,min(sal) as 最低工资,avg(sal) as 平均工资
from emp
group by deptno;
7.
create view de_view
as
select dname,loc,empno,ename
from dept,emp
where dept.deptno=emp.deptno and emp.deptno=20
with check option;
8.
drop view emp_view;
drop view dept_view;
drop view e_view;
drop view de_view;
9.
create index empdeptno on emp(deptno);
10.
create view view_tc
as
select quantity,price
from t
where price>10
with check option;
insert into view_tc
values(3,5);