uni

University stuff
git clone git://git.christosmarg.xyz/uni-assignments.git
Log | Files | Refs | README | LICENSE

ex2_new_personnel.sql (3035B)

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