----------------------------建表，与插入数据
--创建职务表
CREATE TABLE POSITION(
POSITIONID VARCHAR2(2),
POSITIONNAME VARCHAR2(20),
CONSTRAINTS POS_ID_PK PRIMARY KEY (POSITIONID)
);
--创建工资级别表
CREATE TABLE EMPLEVEL(
LEVELNO VARCHAR2(2),
LOWSALARY NUMBER(7,2),
HIGHSALARY NUMBER(7,2),
CONSTRAINTS EMP_NO_PK PRIMARY KEY (LEVELNO)
);
--创建学位表
CREATE TABLE QUAFICATION(
QUALID CHAR(1),
QUALNAME VARCHAR2(10),
CONSTRAINTS QUA_ID_PK PRIMARY KEY (QUALID)
);
--创建部门表
CREATE TABLE DEPARTMENT(
DEPTID VARCHAR2(2),
DEPTNAME VARCHAR2(30),
LOCATION VARCHAR2(30),
MANAGERID VARCHAR2(4),
CONSTRAINTS DEP_ID_PK PRIMARY KEY (DEPTID)
);
--创建员工表
CREATE TABLE EMPLOYEE(
EMPLOYEEID VARCHAR2(4),
EMPLOYEENAME VARCHAR2(40) NOT NULL,
HIREDATE DATE,
SALARY NUMBER(7,2),
COMMISSION NUMBER(7,2),
MANAGERID VARCHAR2(4),
DEPTID VARCHAR2(2),
POSITIONID VARCHAR2(2),
QUALID CHAR(1),
CONSTRAINTS EMP_ID_PK PRIMARY KEY (EMPLOYEEID),
CONSTRAINTS EMP_DEPTID_FK FOREIGN KEY (DEPTID) REFERENCES DEPARTMENT(DEPTID),
CONSTRAINTS EMP_PID_FK FOREIGN KEY (POSITIONID) REFERENCES POSITION(POSITIONID),
CONSTRAINTS EMP_QUALID_FK FOREIGN KEY (QUALID) REFERENCES QUAFICATION(QUALID)
);
---------添加 员工表EMPLOYEE 外键,参照原表
ALTER TABLE EMPLOYEE
ADD CONSTRAINTS EMP_MID_FK FOREIGN KEY (MANAGERID) REFERENCES EMPLOYEE(EMPLOYEEID);
---------添加 部门表DEPARTMENT 外键,参照 EMPLOYEE 表
ALTER TABLE DEPARTMENT
ADD CONSTRAINTS DEP_MAN_FK FOREIGN KEY (MANAGERID) REFERENCES EMPLOYEE(EMPLOYEEID);
------INSERT POSITION
INSERT into POSITION(POSITIONID,POSITIONNAME) values('01','组长');
INSERT into POSITION VALUES('02','经理');
INSERT into POSITION VALUES('03','总监');
INSERT into POSITION VALUES('04','执行总裁');
---select * from POSITION;
-----INSERT EMPLEVEL
INSERT INTO EMPLEVEL VALUES('0',1000,2000);
INSERT INTO EMPLEVEL VALUES('1',2000,3000);
----select * from EMPLEVEL;
---INSERT QUAFICATION
INSERT INTO QUAFICATION VALUES('1','学士');
INSERT INTO QUAFICATION VALUES('2','博士');
------select * from QUAFICATION;
-----INSERT DEPARTMENT
INSERT INTO DEPARTMENT VALUES('01','企化部','组长办公室',NULL);
INSERT INTO DEPARTMENT VALUES('02','管理部','经理办公室',NULL);
----select * from DEPARTMENT;
----INSERT EMPLOYEE
INSERT INTO EMPLOYEE VALUES(01,'小张',TO_DATE('2009-09-01','YYYY-MM-DD'),1500,600,NULL,NULL,NULL,NULL);
----与下面的记录相同,EMPLOYEEID,SALARY
INSERT INTO EMPLOYEE VALUES(02,'小李',TO_DATE('2009-09-02','YYYY-MM-DD'),1600,300,NULL,NULL,NULL,NULL);
-----与上面的记录相同
INSERT INTO EMPLOYEE VALUES(03,'小李',TO_DATE('2009-09-02','YYYY-MM-DD'),1600,400,NULL,NULL,NULL,NULL);
INSERT INTO EMPLOYEE VALUES(04,'小勇',TO_DATE('2009-09-03','YYYY-MM-DD'),2100,500,NULL,NULL,NULL,NULL);
INSERT INTO EMPLOYEE VALUES(05,'小勇',TO_DATE('2009-09-03','YYYY-MM-DD'),null,500,NULL,NULL,NULL,NULL);
INSERT INTO EMPLOYEE VALUES(06,'小刚',null,2100,500,NULL,NULL,NULL,NULL);
INSERT INTO EMPLOYEE VALUES(07,'小华',null,2100,500,NULL,NULL,NULL,NULL);
---select * from EMPLOYEE;
--update EMPLOYEE
UPDATE EMPLOYEE SET COMMISSION = 300;
UPDATE EMPLOYEE SET COMMISSION = 200 WHERE EMPLOYEEID = 1;
UPDATE EMPLOYEE SET COMMISSION = 400 WHERE EMPLOYEEID = 3;
UPDATE EMPLOYEE SET COMMISSION = 500 WHERE EMPLOYEEID = 4;
UPDATE EMPLOYEE SET COMMISSION = 200 WHERE EMPLOYEEID = 2;
UPDATE EMPLOYEE SET EMPLOYEENAME = '小胖' WHERE EMPLOYEEID = 4;
UPDATE EMPLOYEE SET EMPLOYEENAME = '张小华' WHERE EMPLOYEEID = 1;
UPDATE EMPLOYEE SET E