drop table LICCTF;
drop table LIC;
drop table EMP;
drop table DEPT;
create table DEPT
( DEPTNO integer
constraint DEPT_PK primary key
, DNAME varchar(20)
, LOC varchar(30)
);
create table LIC
( LICNO integer
constraint LIC_PK primary key
, LNAME varchar(20)
, LVDR varchar(20)
, LICINC integer
);
create table EMP
( EMPNO integer
constraint EMP_PK primary key
, ENAME varchar(20)
, JOB varchar(20)
, MGR integer
, HIREDATE date
, SAL integer
, COMM integer
, DEPTNO integer
constraint EMP_FK references DEPT(DEPTNO)
);
create table LICCTF
( EMPNO integer
, LICNO integer
, CTFDATE date
, constraint LICCTF_PK primary key(EMPNO,LICNO)
, constraint LICCTF_EMP_FK foreign key(EMPNO) references EMP(EMPNO)
, constraint LICCTF_LIC_FK foreign key(LICNO) references LIC(LICNO)
);
INSERT INTO DEPT VALUES (10,'Admin','5F');
INSERT INTO DEPT VALUES (20,'Planning','2F');
INSERT INTO DEPT VALUES (30,'Sales','3F');
INSERT INTO DEPT VALUES (40,'Operations','4F');
INSERT INTO LIC VALUES(11256,'MCT','MS',100000);
INSERT INTO LIC VALUES(11263,'MCSE','MS',30000);
INSERT INTO LIC VALUES(11563,'MCSD','MS',30000);
INSERT INTO LIC VALUES(12101,'Silver','Ora',10000);
INSERT INTO LIC VALUES(12102,'Gold','Ora',50000);
INSERT INTO LIC VALUES(13640,'CCNA','Cisco',10000);
INSERT INTO LIC VALUES(13642,'CCNP','Cisco',50000);
INSERT INTO LIC VALUES(14101,'CompM','Comp',3800);
INSERT INTO LIC VALUES(14102,'CompO','Comp',2400);
INSERT INTO LIC VALUES(15303,'KitSp','Int',8400);
INSERT INTO LIC VALUES(15606,'TradeA','Jet',43000);
INSERT INTO LIC VALUES(15707,'CFA','JCFA',6100);
INSERT INTO LIC VALUES(16106,'BCM','CJSD',8800);
INSERT INTO EMP VALUES (101,'Nishida','Director',NULL,'2001/11/17',500000,NULL,10);
INSERT INTO EMP VALUES (102,'Nohira','Manager',101,'2001/5/1',285000,NULL,30);
INSERT INTO EMP VALUES (103,'Kiyama','Manager',101,'2001/6/9',245000,NULL,10);
INSERT INTO EMP VALUES (104,'Ohkawa','Manager',101,'2001/4/2',297500,NULL,20);
INSERT INTO EMP VALUES (105,'Kajiyama','Salesman',102,'2001/9/28',125000,140000,30);
INSERT INTO EMP VALUES (106,'Kohsaka','Salesman',102,'2001/2/20',160000,30000,30);
INSERT INTO EMP VALUES (107,'Nishikawa','Salesman',102,'2001/9/8',150000,0,30);
INSERT INTO EMP VALUES (108,'Sasaki','Clerk',102,'2001/12/3',95000,NULL,30);
INSERT INTO EMP VALUES (109,'Ichikawa','Salesman',102,'2001/2/22',125000,50000,30);
INSERT INTO EMP VALUES (110,'Yamamoto','Engineer',104,'2001/12/3',300000,NULL,20);
INSERT INTO EMP VALUES (111,'Komatsu','Clerk',110,'2000/12/17',80000,NULL,20);
INSERT INTO EMP VALUES (112,'Aizawa','Engineer',104,'2002/12/9',300000,NULL,20);
INSERT INTO EMP VALUES (113,'Saitoh','Clerk',112,'2003/1/12',110000,NULL,NULL);
INSERT INTO EMP VALUES (114,'Inoue','Clerk',103,'2002/1/23',130000,NULL,10);
INSERT INTO LICCTF VALUES (101,11256,'2016/8/10');
INSERT INTO LICCTF VALUES (101,11263,'2015/1/5');
INSERT INTO LICCTF VALUES (101,14101,'2012/12/3');
INSERT INTO LICCTF VALUES (102,11256,'2017/4/3');
INSERT INTO LICCTF VALUES (102,11563,'2016/12/8');
INSERT INTO LICCTF VALUES (102,14101,'2016/5/30');
INSERT INTO LICCTF VALUES (103,11563,'2017/4/3');
INSERT INTO LICCTF VALUES (103,14101,'2014/2/3');
INSERT INTO LICCTF VALUES (104,11263,'2015/6/18');
INSERT INTO LICCTF VALUES (104,14101,'2018/5/8');
INSERT INTO LICCTF VALUES (105,11256,'2015/6/18');
INSERT INTO LICCTF VALUES (105,14102,'2016/7/8');
INSERT INTO LICCTF VALUES (106,12101,'2017/9/10');
INSERT INTO LICCTF VALUES (106,12102,'2017/9/30');
INSERT INTO LICCTF VALUES (106,14102,'2014/8/23');
INSERT INTO LICCTF VALUES (107,11563,'2017/11/5');
INSERT INTO LICCTF VALUES (107,12102,'2018/3/10');
INSERT INTO LICCTF VALUES (107,14102,'2013/11/11');
INSERT INTO LICCTF VALUES (108,14102,'2019/8/9');
INSERT INTO LICCTF VALUES (108,15707,'2021/9/8');
INSERT INTO LICCTF VALUES (109,14102,'2022/3/14');
INSERT INTO LICCTF VALUES (109,15303,'2022/6/6');
INSERT INTO LICCTF VALUES (109,15606,'2018/10/23');
INSERT INTO LICCTF VALUES (110,13640,'2015/4/18');
INSERT INTO LICCTF VALUES (110,13642,'2019/5/29');
INSERT INTO LICCTF VALUES (110,14102,'2023/5/9');
INSERT INTO LICCTF VALUES (110,15303,'2021/6/4');
INSERT INTO LICCTF VALUES (111,14102,'2012/8/5');
INSERT INTO LICCTF VALUES (112,12101,'2012/5/9');
INSERT INTO LICCTF VALUES (112,12102,'2018/3/2');
INSERT INTO LICCTF VALUES (112,14102,'2024/3/16');
INSERT INTO LICCTF VALUES (112,15606,'2022/8/23');
INSERT INTO LICCTF VALUES (112,15707,'2013/12/6');
INSERT INTO LICCTF VALUES (113,14102,'2015/9/30');
INSERT INTO LICCTF VALUES (113,15303,'2013/2/14');
INSERT INTO LICCTF VALUES (114,14102,'2019/8/14');
INSERT INTO LICCTF VALUES (114,15707,'2013/11/25');
COMMIT;