第二次作业别看

一．实验目标

通过理论学习与实验设计，使自己具备如下能力：

1. 具备分析不同用户的特性，设计用户的数据需求方案并实施的能力。
2. 选用合适的数据库管理工具承担数据库系统的实施、运行、监控与维护工作的能力。

二．实验项目

1.为上面的三个表补充定义主码、外码(级联删除、级联修改)。

alter table countries1

add primary key(cid);

alter table dept1

add primary key(deptno);

alter table dept1 

add constraint fk_dept1

foreign key (cid) references countries1

on delete cascade;

alter table emp1

add primary key(empno);

alter table emp1

add constraint fk_emp1

foreign key (deptno) references dept1

on delete cascade;

 

2.为上面的三个表补充定义check约束，请写出相应的SQL语句并执行：

a. 工资介于5000到100000之间

b. 雇佣日期大于2000-01-01

c. 工作类型的取值只能是clerk,salesman,analyst,manager,presiden

alter table emp1

add constraint chk_empa

check(sal  between 5000 and 100000);

alter table emp1

add constraint chk_empb

check(hiredate > to_date('2002-02-02','yyyy-mm-dd'));

alter table emp1

add constraint chk_empc

 check(job in('clerk','salesman','analyst','manager','president'));

3.公司与湘潭市达成协议，在湘潭市设立一个生产部门，部门编号为：50，部门名称为： PRODUCTION。请写出相应的SQL语句并执行

insert into dept1 values(50,'production','CN','Xiang Tan',null,null);

4. 2022年5月1日，公司决定聘请你担任PRODUCTION部门的经理（职工编号为'9'+你学号的最后3位数，如你的学号为202001020304，则职工编号为：9304，工资自定，没有佣金），并为你招聘了下列职工，开始创业。请写出相应的SQL语句并执行。

insert into emp1

values('9440','许心原','manager',0123,to_date('2022-05-01 09:20:18' , 'yyyy-mm-dd hh24:mi:ss'),'80000',null,50);

insert into emp1

values('9001','唐僧','clerk',9440,to_date('2022-05-02 00:00:00' , 'yyyy-mm-dd hh24:mi:ss'),'34000',null,50);

insert into emp1

values('9002','孙悟空','clerk',9440,to_date('2022-05-03 00:00:00' , 'yyyy-mm-dd hh24:mi:ss'),'50000',null,50);

insert into emp1

values('9003','猪八戒','clerk',9440,to_date('2022-05-04 00:00:00' , 'yyyy-mm-dd hh24:mi:ss'),'20000',null,50);

insert into emp1

values('9004','沙僧','clerk',9440,to_date('2022-05-06 00:00:00' , 'yyyy-mm-dd hh24:mi:ss'),'10000',null,50);

5.为了扩大生产，2022年5月10日，公司将与你同姓的人都招聘到你的部门工作。待聘人员名单在视图“待聘人员”中。
要求：用临时编号作为职工编号，工作类型为“clerk”，工资15000。

insert into emp1

select 临时编号,姓名,'clerk','9440',to_date('2022-05-10 09:25:00' , 'yyyy-mm-dd hh24:mi:ss'),15000,null,50

from 待聘人员

where 姓名 like  '许%' ;

6.DEPT表中的部门人数、部门职工收入总和两列还没有填写。请写出相应的SQL语句并执行。

update dept1

set emps = case

when dept1.deptno = 10 then (select count(empno) from emp1 where emp1.deptno = 10)

when dept1.deptno = 20 then (select count(empno) from emp1 where emp1.deptno = 20)

when dept1.deptno = 30 then (select count(empno) from emp1 where emp1.deptno = 30)

when dept1.deptno = 40 then (select count(empno) from emp1 where emp1.deptno = 40)

when dept1.deptno = 50 then (select count(empno) from emp1 where emp1.deptno = 50)

end;

update dept1

set income = case

when dept1.deptno = 10 then ((select sum(sal) from emp1 where emp1.deptno = 10))

when dept1.deptno = 20 then ((select sum(sal) from emp1 where emp1.deptno = 20))

when dept1.deptno = 30 then ((select sum(sal) from emp1 where emp1.deptno = 30)+ ( select sum(comm) from emp1 where comm is not null or emp1.deptno = 30))

when dept1.deptno = 40 then ((select sum(sal) from emp1 where emp1.deptno = 40))

when dept1.deptno = 50 then ((select sum(sal) from emp1 where emp1.deptno = 50))

end;

7.删除20号部门，请写出相应的SQL语句并执行。

delete from dept1 where deptno = 20 ;

8.删除工资低于15000的职工，请写出相应的SQL语句并执行。

delete from emp1 where sal < 15000;

9．所有职工的工资增加5000

update emp1 set sal = sal + 5000;

      

结果如下图。

 

 

 

三．实验错误解决方案

问题一： ORA-00903问题

1、问题的出现

在实验项目1中给emp添加外键，SQL语句如下：

alter table emp

add constraint fk_emp

foreign key (deptno) references dept

on delete cascade;

出现错误：

ORA-02298: 无法验证 (A202005960440.FK_EMP)-未找到父项关键字

 

2、问题分析

    要插入的表emp里，有外键连接到表dept的主键，在表emp的外键列插入的值，在表dept的主键列找不到就不能插入。主要看两表中的数据是否一致，从表中要关联外键的字段中的数据必须包含在主表相关字段的数据内。

3、解决方案

解决方案如下：

alter table emp1

add constraint fk_emp1

foreign key (deptno) references dept1

on delete cascade;

重新建表并执行该语句后，表已创建，问题解决。

问题二： ORA-02291问题

1. 问题的出现

在实验项目3中添加信息，SQL语句如下：

insert into dept1 values(50,'PRODUCTION','cn','xiangtan',null,null);

出现错误：

ORA-02291: 违反完整约束条件 (A202005960440.FK_DEPT)-未找到父项关键字

 

1. 问题分析

dept1中的值数据类型和长度不一致。特别是数据长度，必须要一致。

1. 解决方案

解决方案如下：

insert into dept1 values(50,'PRODUCTION','CN','Xiang Tan',null,null);

执行该语句后，表已创建，问题解决。

问题三：ORA-02290问题

1. 问题的出现

在实验项目4中添加信息，SQL语句如下：

insert into emp

values('9004','沙僧','clerk',9440,to_date('2022-05-06 00:00:00' , 'yyyy-mm-dd hh24:mi:ss'),'2000',null,50);

出现错误：ORA-02290: 违反检查约束条件 (A202005960440.CHECK_EMP)

 

2、问题分析

  添加“沙僧”的信息时，工资设置不满足约束条件工资在5000-100000之间

3、解决方案

解决方案如下：

insert into emp1

values('9004','沙僧','clerk',9440,to_date('2022-05-06 00:00:00' , 'yyyy-mm-dd hh24:mi:ss'),'10000',null,50);

执行该语句后，表已创建，问题解决。

问题四：ORA-00947问题

1. 问题的出现

在测试约束条件案例时出现问题，SQL语句如下：

insert into dept1 values(50,'saving','Los Angles');

出现错误：

 

1. 问题分析

添加的信息中缺少空值

3、解决方案

解决方案如下：

insert into dept1 values(50,'saving',null,'Los Angles',null,null);