ex3_new_personnel.sql (3523B)
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 (25, 'SMITH', 'PROGRAMMER', '2000-01-04', 30, 2800, 200, 30), 47 (30, 'DATE', 'PROGRAMMER', '2004-05-04', 15, 1800, 200, 10); 48 49 INSERT INTO PROJ VALUES 50 (100, 'PAYROLL'), 51 (200, 'PERSONELL'), 52 (300, 'SALES'); 53 54 INSERT INTO ASSIGN VALUES 55 (10, 100, 40), 56 (10, 200, 60), 57 (15, 100, 100), 58 (20, 200, 100), 59 (30, 100, 100); 60 61 -- Using SQLite3 syntax 62 .header on 63 .mode column 64 65 -- Question 1 66 SELECT 67 ENAME, DEPTNO FROM EMP 68 WHERE DEPTNO = 10 69 ORDER BY SAL DESC; 70 71 -- Question 2 72 SELECT 73 ENAME, JOB, SAL FROM EMP 74 ORDER BY JOB ASC, SAL DESC; 75 76 -- Question 3 77 SELECT 78 DEPTNO AS "ΤΜΗΜΑ", 79 AVG(SAL) AS "ΜΕΣΟΣ ΜΙΣΘΟΣ" 80 FROM EMP 81 GROUP BY DEPTNO 82 HAVING COUNT(*) >= 1; 83 84 -- Question 4 85 SELECT 86 DEPTNO AS "ΤΜΗΜΑ", 87 AVG(strftime("%Y", "now") - strftime("%Y", HIREDATE)) AS "ПРОΥΠΗΡΕΣΙΑ (ετη)" 88 FROM EMP 89 GROUP BY DEPTNO; 90 91 -- Question 5 92 SELECT 93 PROJ.DESCRIPTION AS PNAME, 94 EMP.ENAME, 95 EMP.JOB 96 FROM EMP, PROJ 97 INNER JOIN DEPT 98 ON EMP.DEPTNO = DEPT.DEPTNO AND 99 DEPT.DNAME = PROJ.DESCRIPTION 100 ORDER BY PNAME, EMP.JOB ASC; 101 102 -- Question 6 103 SELECT 104 DEPT.DNAME AS Department, 105 mgr.ENAME AS Manager, 106 empl.ENAME AS Employee 107 FROM EMP empl 108 INNER JOIN EMP mgr 109 ON empl.MGR = mgr.EMPNO, 110 DEPT WHERE empl.DEPTNO = DEPT.DEPTNO 111 ORDER BY Department, Employee ASC; 112 113 -- Question 7 114 SELECT 115 ENAME as Ename, 116 JOB as Job, 117 DEPT.LOC as Loc 118 FROM EMP 119 INNER JOIN DEPT 120 ON DEPT.DEPTNO = EMP.DEPTNO 121 WHERE DEPT.DNAME = "RESEARCH"; 122 123 -- OUTPUT 124 125 -- Question 1: 126 -- ENAME DEPTNO 127 ------- ------ 128 -- CODD 10 129 -- DATE 10 130 -- ELMASRI 10 131 132 -- Question 2: 133 -- ENAME JOB SAL 134 ------- ---------- ------ 135 -- CODD ANALYST 3000.0 136 -- ELMASRI ANALYST 1200.0 137 -- SMITH PROGRAMMER 2800.0 138 -- DATE PROGRAMMER 1800.0 139 -- NAVATHE SALESMAN 2000.0 140 141 -- Question 3: 142 -- ΤΜΗΜΑ ΜΕΣΟΣ ΜΙΣΘΟΣ 143 ----- ------------ 144 -- 10 2000.0 145 -- 20 2000.0 146 -- 30 2800.0 147 148 -- Question 4: 149 -- ΤΜΗΜΑ ПРОΥΠΗΡΕΣΙΑ (ετη) 150 ----- ----------------- 151 -- 10 25.0 152 -- 20 44.0 153 -- 30 21.0 154 155 -- Question 5: 156 -- PNAME ENAME JOB 157 ----- ------- -------- 158 -- SALES NAVATHE SALESMAN 159 160 -- Question 6: 161 -- Department Manager Employee 162 -- ---------- ------- -------- 163 -- ACCOUNTING ELMASRI CODD 164 -- ACCOUNTING ELMASRI DATE 165 -- ACCOUNTING ELMASRI ELMASRI 166 -- RESEARCH DATE SMITH 167 -- SALES NAVATHE NAVATHE 168 169 -- Question 7: 170 -- Ename Job Loc 171 ----- ---------- ------ 172 -- SMITH PROGRAMMER ATHENS