RrkQEeQL

1
2
3DROP DATABASE IF EXISTS personnel;
4
5CREATE DATABASE personnel;
6USE personnel;
7
8CREATE TABLE DEPT(
9		DEPTNO INT(2) NOT NULL,
10		DNAME VARCHAR(14), 
11		LOC VARCHAR(14),
12		PRIMARY KEY(DEPTNO)
13		);
14CREATE TABLE EMP(
15		EMPNO INT(4) NOT NULL, 
16        ENAME VARCHAR(10), 
17        JOB VARCHAR(25), 
18        MGR INT(4),
19        HIREDATE DATE, 
20        SAL FLOAT(7,2), 
21        COMM FLOAT(7,2),
22        DEPTNO INT(2),
23        PRIMARY KEY(EMPNO),
24      	FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
25);
26CREATE TABLE PROJ(
27		PROJ_CODE INT(3) NOT NULL, 
28        DESCRIPTION VARCHAR(15), 
29        PRIMARY KEY(PROJ_CODE)
30);
31
32CREATE TABLE ASSIGN(
33		EMPNO INT(4) NOT NULL, 
34        PROJ_CODE INT(3) NOT NULL, 
35		A_TIME INT(3),
36        PRIMARY KEY(EMPNO, PROJ_CODE),
37        FOREIGN KEY(EMPNO) REFERENCES EMP(EMPNO),
38        FOREIGN KEY(PROJ_CODE) REFERENCES PROJ(PROJ_CODE)
39);
40 INSERT INTO DEPT(DEPTNO,DNAME,LOC) 
41	VALUES (10,"ACCOUNTING","ATHENS");
42 INSERT INTO DEPT(DEPTNO, DNAME, LOC) 
43	VALUES (20, 'SALES', 'LONDON	');
44INSERT INTO DEPT(DEPTNO, DNAME, LOC) 
45	VALUES (30, 'RESEARCH', 'ATHENS');
46INSERT INTO DEPT(DEPTNO, DNAME, LOC) 
47	VALUES (40, 'PAYROLL', 'LONDON');
48INSERT INTO EMP(EMPNO,ENAME,JOB,HIREDATE,MGR,SAL,COMM,DEPTNO)
49	VALUES (10,'CODD','ANALYST','1989/01/01',15, 3000.00, NULL, 10);
50INSERT INTO EMP(EMPNO,ENAME,JOB,HIREDATE,MGR,SAL,COMM,DEPTNO)
51	VALUES (15,'ELMASRI','ANALYST','1995/05/02',15, 1200.00, 150, 10);
52INSERT INTO EMP(EMPNO,ENAME,JOB,HIREDATE,MGR,SAL,COMM,DEPTNO)
53	VALUES (20,'NAVATHE','SALESMAN','1977/07/07',20, 2000.00, NULL, 20);
54INSERT INTO EMP(EMPNO,ENAME,JOB,HIREDATE,MGR,SAL,COMM,DEPTNO)
55	VALUES (30,'DATE','PROGRAMMER','2004/05/04',15, 1800.00, 200, 10);
56INSERT INTO PROJ VALUES 
57(100,'PAYROLL'),
58(200,'PERSONNEL'),
59(300,'SALES');
60INSERT INTO ASSIGN VALUES
61(10,100,40),
62(10,200,60),
63(15,100,100),
64(20,200,100),
65(30,100,100);
66ALTER TABLE EMP
67ADD ADDRESS VARCHAR(50);
68ALTER TABLE DEPT
69MODIFY COLUMN LOC VARCHAR(30);
70
71CREATE TABLE TASK(
72		TASK_CODE INT(3) NOT NULL, 
73        DESCRIPTION VARCHAR(20), 
74        PRIMARY KEY(TASK_CODE)
75);