ex2_new_personnel.sql (3065B)
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', '1989-01-01', 15, 3000, NULL, 10), 44 (15, 'ELMASRI', 'ANALYST', '1995-05-02', 15, 1200, 150, 10), 45 (20, 'NAVATHE', 'SALESMAN', '1977-07-07', 20, 2000, NULL, 20), 46 (30, 'DATE', 'PROGRAMMER', '2004-05-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 .header on 62 .mode column 63 64 -- Question 1 65 SELECT 66 ENAME, 67 SAL AS "ΜΙΣΘΟΣ", 68 COMM AS "ΠΡΟΜΗΘΕΙΑ", 69 IFNULL(ROUND(COMM * 100 / SAL, 1) || "%", "0.00%") AS "ΠΟΣΟΣΤΟ" 70 FROM EMP; 71 72 -- Question 2 73 SELECT 74 ENAME AS "ΕΠΩΝΥΜΟ", 75 SAL AS "ΜΗΝΙΑΙΕΣ ΑΠΟΔΟΧΕΣ", 76 strftime("%Y", "now") - strftime("%Y", HIREDATE) AS "ПРОΥΠΗΡΕΣΙΑ" 77 FROM EMP; 78 79 -- Question 3 80 SELECT 81 ENAME AS "ΕΠΩΝΥΜΟ", 82 JOB AS "ΘΕΣΗ", 83 HIREDATE AS "ΠΡΟΣΛΗΨΗ" 84 FROM EMP 85 WHERE strftime("%d", HIREDATE) <= "05"; 86 87 -- Question 4 88 SELECT 89 ENAME AS "ΕΠΩΝΥΜΟ" 90 FROM EMP 91 INNER JOIN DEPT ON 92 DEPT.DEPTNO = EMP.DEPTNO AND 93 DEPT.DNAME = "ACCOUNTING"; 94 95 -- Question 5 96 SELECT 97 ENAME AS "ΕΠΩΝΥΜΟ", 98 MAX( 99 (strftime("%Y", "now") - strftime("%Y", HIREDATE)) * 100 12 * SAL 101 ) AS "ΣΥΝΟΛΙΚΟΣ ΜΙΣΘΟΣ" 102 FROM EMP; 103 104 105 -- Output 106 107 -- Question 1: 108 -- ENAME ΜΙΣΘΟΣ ΠΡΟΜΗΘΕΙΑ ΠΟΣΟΣΤΟ 109 -- ------- ------ --------- ------- 110 -- CODD 3000.0 0.00% 111 -- ELMASRI 1200.0 150.0 12.5% 112 -- NAVATHE 2000.0 0.00% 113 -- DATE 1800.0 200.0 11.1% 114 115 -- Question 2: 116 -- ΕΠΩΝΥΜΟ ΜΗΝΙΑΙΕΣ ΑΠΟΔΟΧΕΣ ПРОΥΠΗΡΕΣΙΑ 117 -- ------- ----------------- ----------- 118 -- CODD 3000.0 32 119 -- ELMASRI 1200.0 26 120 -- NAVATHE 2000.0 44 121 -- DATE 1800.0 17 122 123 -- Question 3: 124 -- ΕΠΩΝΥΜΟ ΘΕΣΗ ΠΡΟΣΛΗΨΗ 125 -- ------- ---------- ---------- 126 -- CODD ANALYST 1989-01-01 127 -- ELMASRI ANALYST 1995-05-02 128 -- DATE PROGRAMMER 2004-05-04 129 130 -- Question 4: 131 -- ΕΠΩΝΥΜΟ 132 -- ------- 133 -- CODD 134 -- ELMASRI 135 -- DATE 136 137 -- Question 5: 138 -- ΕΠΩΝΥΜΟ ΣΥΝΟΛΙΚΟΣ ΜΙΣΘΟΣ 139 -- ------- ---------------- 140 -- CODD 1152000.0