尚学堂--oralce练习3答案

create table Employees(
       employeeID varchar2(6) not null primary key,
       name varchar2(10) not null,
       birthday date not null,
       sex varchar2(4) not null,
       address varchar2(20),
       zip varchar2(6),
       phoneNumber varchar2(12),
       emailAddress varchar2(30),
       departmentID varchar2(3)
);

create table Departments (
       departmentID varchar2(3) not null primary key,
       departmentName varchar2(20) not null,
       note varchar2(16)
);

create table Salary(
       employeeID varchar2(6) not null primary key,
       income number(8,2) not null,
       outcome number(8,2) not null
);

drop table Employees;
drop table Departments;
drop table Salary;

insert into Employees values('010001','王平',to_date('1981-01-05','yyyy-mm-dd'),'女',null,null,null,null,'1');
insert into Employees values('010003','韦严平',to_date('1979-11-05','yyyy-mm-dd'),'男',null,null,null,null,'2');
insert into Employees values('010021','吴庆红',to_date('1984-07-02','yyyy-mm-dd'),'女',null,null,null,null,'3');
insert into Employees values('010031','李方',to_date('1980-08-04','yyyy-mm-dd'),'男',null,null,null,null,'4');
insert into Employees values('010005','李明',to_date('1985-02-10','yyyy-mm-dd'),'男',null,null,null,null,'5');
select * from Employees;

insert into Departments values('1','办公室',null);
insert into Departments values('2','人力资源部',null);
insert into Departments values('3','销售部',null);
insert into Departments values('4','财务部',null);
insert into Departments values('5','生产部',null);
select * from Departments;

insert into Salary values('010001','3000','100');
insert into Salary values('010003','3000','50');
insert into Salary values('010021','5000','50');
insert into Salary values('010031','3000','50');
insert into Salary values('010005','2500','50');
select * from salary;

alter table Employees add constraints fk_employees_departtmentID foreign key(departmentID) references Departments(departmentID);
alter table Employees add constraints ck_sex check(sex='男'or sex='女');
alter table Departments add constraints un_Departments_departmentName unique(departmentName); 

alter table Employees drop constraints ck_sex;

alter table Employees add QQ number(15);

update salary  set  income=2890 where employeeID=010001
update  Departments set departmentName='生产计划部' where departmentName='生产部'
update salary  set  income=income+100