/*创建employee表*/
CREATE TABLE employee
(empid NUMBER(8),
 empname VARCHAR2(20),
 age NUMBER(2),
 address VARCHAR2(50),
 sal NUMBER(7)
);
/*创建department表*/
CREATE TABLE department
(deptno NUMBER(8),
 dname VARCHAR2(15),
 mgr VARCHAR2(20)
);
/*创建work表*/
CREATE TABLE work
(empid NUMBER(8),
 deptno NUMBER(8),
 worktime NUMBER(2)
);
/*插入数据*/
insert into employee values(1001,'tom',35,'海淀',8000);
insert into employee values(1002,'dennis',24,'朝阳',5000);
insert into employee values(1003,'john',35,'海淀',4000);
insert into employee values(1004,'rick',41,'朝阳',7000);
insert into employee values(1005,'bill',45,'海淀',4000);
insert into employee values(1006,'frank',24,'朝阳',7000);
insert into department values(001,'人事',1002);
insert into department values(002,'信息',1004);
insert into department values(003,'销售',1005);
insert into department values(004,'财务',1006);
insert into work values(1001,002,6);
insert into work values(1002,001,1);
insert into work values(1001,004,2);
insert into work values(1001,001,2);
insert into work values(1002,004,0);
insert into work values(1003,001,4);
insert into work values(1004,002,1);
insert into work values(1005,003,2);
insert into work values(1006,004,4);
/*为财务部门的雇员加薪，3年以上加5%*/
UPDATE employee SET sal=sal+sal*0.05 WHERE empid IN 
    (SELECT e.empid,w.worktime FROM employee e INNER JOIN work w ON 
    e.empid=w.empid INNER JOIN department d ON d.deptno=w.deptno 
WHERE d.dname='财务' AND w.worktime>3);