ex1_new_personnel.sql (3287B)
1 CREATE TABLE DEPT ( 2 DEPTNO INT(2) NOT NULL, 3 DNAME VARCHAR, 4 LOC VARCHAR, 5 PRIMARY KEY(DEPTNO) 6 ); 7 8 CREATE TABLE EMP ( 9 EMPNO INT(2) NOT NULL, 10 ENAME VARCHAR, 11 JOB VARCHAR, 12 HIREDATE DATE, 13 MGR INT(2), 14 SAL FLOAT(7,2), 15 COMM FLOAT(7,2), 16 DEPTNO INT(2) NOT NULL, 17 PRIMARY KEY(EMPNO), 18 FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO) 19 ); 20 21 CREATE TABLE PROJ ( 22 PROJ_CODE INT(3) NOT NULL, 23 DESCRIPTION VARCHAR, 24 PRIMARY KEY(PROJ_CODE) 25 ); 26 27 CREATE TABLE ASSIGN ( 28 EMPNO INT(2) NOT NULL, 29 PROJ_CODE INT(3), 30 A_TIME INT(3), 31 PRIMARY KEY(EMPNO, PROJ_CODE), 32 FOREIGN KEY(EMPNO) REFERENCES EMP(EMPNO), 33 FOREIGN KEY(PROJ_CODE) REFERENCES PROJ(PROJ_CODE) 34 ); 35 36 INSERT INTO DEPT VALUES 37 (10, 'ACCOUNTING', 'ATHENS'), 38 (20, 'SALES', 'LONDON'), 39 (30, 'RESEARCH', 'ATHENS'), 40 (40, 'PAYROLL', 'LONDON'); 41 42 INSERT INTO EMP VALUES 43 (10, 'CODD', 'ANALYST', '1/1/89', 15, 3000, NULL, 10), 44 (15, 'ELMASRI', 'ANALYST', '2/5/95', 15, 1200, 150, 10), 45 (20, 'NAVATHE', 'SALESMAN', '7/7/77', 20, 2000, NULL, 20), 46 (30, 'DATE', 'PROGRAMMER', '4/5/04', 15, 1800, 200, 10); 47 48 INSERT INTO PROJ VALUES 49 (100, 'PAYROLL'), 50 (200, 'PERSONELL'), 51 (300, 'SALES'); 52 53 INSERT INTO ASSIGN VALUES 54 (10, 100, 40), 55 (10, 200, 60), 56 (15, 100, 100), 57 (20, 200, 100), 58 (30, 100, 100); 59 60 /* Using SQLite3 syntax*/ 61 62 /* .schema -> DESCRIBE */ 63 .schema 64 65 /* Make output pretty */ 66 .header on 67 .mode column 68 69 /* Show contents of every table */ 70 SELECT * FROM DEPT; 71 SELECT * FROM EMP; 72 SELECT * FROM PROJ; 73 SELECT * FROM ASSIGN; 74 75 /* 76 * Output after execution (`sqlite3 -init new_personnel.sql`): 77 * 78 * CREATE TABLE DEPT ( 79 * DEPTNO INT(2) NOT NULL, 80 * DNAME VARCHAR, 81 * LOC VARCHAR, 82 * PRIMARY KEY(DEPTNO) 83 * ); 84 * CREATE TABLE EMP ( 85 * EMPNO INT(2) NOT NULL, 86 * ENAME VARCHAR, 87 * JOB VARCHAR, 88 * HIREDATE DATE, 89 * MGR INT(2), 90 * SAL FLOAT(7,2), 91 * COMM FLOAT(7,2), 92 * DEPTNO INT(2) NOT NULL, 93 * PRIMARY KEY(EMPNO), 94 * FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO) 95 * ); 96 * CREATE TABLE PROJ ( 97 * PROJ_CODE INT(3) NOT NULL, 98 * DESCRIPTION VARCHAR, 99 * PRIMARY KEY(PROJ_CODE) 100 * ); 101 * CREATE TABLE ASSIGN ( 102 * EMPNO INT(2) NOT NULL, 103 * PROJ_CODE INT(3), 104 * A_TIME INT(3), 105 * PRIMARY KEY(EMPNO, PROJ_CODE), 106 * FOREIGN KEY(EMPNO) REFERENCES EMP(EMPNO), 107 * FOREIGN KEY(PROJ_CODE) REFERENCES PROJ(PROJ_CODE) 108 * ); 109 * DEPTNO DNAME LOC 110 * ------ ---------- ------ 111 * 10 ACCOUNTING ATHENS 112 * 20 SALES LONDON 113 * 30 RESEARCH ATHENS 114 * 40 PAYROLL LONDON 115 * EMPNO ENAME JOB HIREDATE MGR SAL COMM DEPTNO 116 * ----- ------- ---------- -------- --- ------ ----- ------ 117 * 10 CODD ANALYST 1/1/89 15 3000.0 10 118 * 15 ELMASRI ANALYST 2/5/95 15 1200.0 150.0 10 119 * 20 NAVATHE SALESMAN 7/7/77 20 2000.0 20 120 * 30 DATE PROGRAMMER 4/5/04 15 1800.0 200.0 10 121 * PROJ_CODE DESCRIPTION 122 * --------- ----------- 123 * 100 PAYROLL 124 * 200 PERSONELL 125 * 300 SALES 126 * EMPNO PROJ_CODE A_TIME 127 * ----- --------- ------ 128 * 10 100 40 129 * 10 200 60 130 * 15 100 100 131 * 20 200 100 132 * 30 100 100 133 */