DROP DATABASE IF EXISTS personnel; 
CREATE DATABASE personnel;
USE personnel;
CREATE TABLE DEPT(DEPTNO INT NOT NULL, 
                     DNAME VARCHAR(14), LOC VARCHAR(14),
                     PRIMARY KEY(DEPTNO));
CREATE TABLE JOB(JOBCODE INT , 
                     JOB_DESCR VARCHAR(14), SAL INT,
                     PRIMARY KEY(JOBCODE));
CREATE TABLE EMP(EMPNO INT NOT NULL, 
                    NAME VARCHAR(10), JOBCODE INT, 
                    COMM FLOAT(7,2),
                    DEPTNO INT, 
         PRIMARY KEY(EMPNO),
         FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO),
         FOREIGN KEY(JOBCODE) REFERENCES JOB(JOBCODE))
         ;
         
INSERT INTO DEPT(DEPTNO, DNAME, LOC) 
            VALUES (50, 'SALES', 'ATHENS'); 
INSERT INTO DEPT VALUES (60, 'ACCOUNTING', 'ATHENS');                                                                     
INSERT INTO DEPT(DNAME, DEPTNO, LOC)
            VALUES ('PAYROLL', 70, 'VOLOS');  
            
 INSERT INTO JOB
            VALUES (100, 'SALESMAN',2000),(200, 'ANALYST',2000),(300, 'DBA',1000);            


INSERT INTO EMP(EMPNO, NAME, JOBCODE, COMM, DEPTNO)
         VALUES (10, 'CODD', 100,NULL,50),(20, 'NAVATHE', 200,450,50),(30, 'ELMASRI', 300,NULL,60),(40, 'DATE', 100,NULL,50);

-- 3.Δείξτε (SELECT) name, empno, job_descr, sal, deptno, dname των υπαλλήλων
-- που είναι SALESMAN, ANALYST, DBA. Ταξινομήστε ανά θέση (job_descr).
SELECT name, empno, job_descr, sal, EMP.deptno, dname 
FROM EMP,DEPT,JOB 
WHERE JOB.JOBCODE=EMP.JOBCODE AND EMP.DEPTNO=DEPT.DEPTNO
AND JOB_DESCR IN ('SALESMAN','ANALYST','DBA')
ORDER BY JOB_DESCR
;

-- 4. Δείξτε με 2 τρόπους name,empno,job_descr,sal για υπάλληλους με σύνολο
-- αμοιβών μεγαλύτερο ή ίσο των 1000 και μικρότερο ή ίσο των 3000 ευρώ.
SELECT name, empno, job_descr, sal, EMP.deptno, dname 
FROM EMP,DEPT,JOB 
WHERE JOB.JOBCODE=EMP.JOBCODE AND EMP.DEPTNO=DEPT.DEPTNO
AND SAL>=1000 AND SAL <=3000
ORDER BY JOB_DESCR
;
SELECT name, empno, job_descr, sal, EMP.deptno, dname 
FROM EMP,DEPT,JOB 
WHERE JOB.JOBCODE=EMP.JOBCODE AND EMP.DEPTNO=DEPT.DEPTNO
AND SAL between 1000 AND 3000
ORDER BY JOB_DESCR
;

-- 5. Δείξτε (SELECT) empno, name, job, sal, comm, deptno, dname για τους
-- υπάλληλους που είναι SALESMAN, ANALYST, DBA με μισθό μεγαλύτερο των
-- 1500 και μικρότερο από 4000 ευρώ. Οι υπάλληλοι θα φαίνονται ανά θέση
-- και οι υπαλληλοι που έχουν την ίδια θέση θα εμφανίζονται αλφαβητικά.

SELECT name, empno, job_descr, sal,COMM, EMP.deptno, dname 
FROM EMP,DEPT,JOB 
WHERE JOB.JOBCODE=EMP.JOBCODE AND EMP.DEPTNO=DEPT.DEPTNO
AND JOB_DESCR IN ('SALESMAN','ANALYST','DBA')
AND SAL between 1500 AND 4000
ORDER BY JOB_DESCR,NAME
;


-- 6.Γράψτε δήλωση SELECT η οποία υπολογίζει ανά κωδικό θέσης (100, 200, …)
-- πόσοι είναι οι υπάλληλοι που κατέχουν τη θέση.
SELECT COUNT(*),DEPTNO FROM EMP GROUP BY DEPTNO;

-- 7. Τροποποιήστε την προηγούμενη δήλωση SELECT έτσι ώστε αν και υπολογίζει
-- πόσοι είναι οι υπάλληλοι ανά κωδικό θέσης (100, 200, 300, …) να δείχνει
-- μόνο θέσεις που τις κατέχουν τουλάχιστον δύο υπάλληλοι.
SELECT COUNT(*),DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(*) >=2;
-- 8. Δείξτε τους υπάλληλους που έχουν μισθό ίσο με τον μεγαλύτερο μισθό
-- μεταξύ των υπαλλήλων που εργάζονται στο τμήμα 'SALES'

SELECT * FROM EMP,JOB WHERE JOB.JOBCODE=EMP.JOBCODE AND SAL= (SELECT MAX(SAL) FROM EMP,DEPT,JOB WHERE JOB.JOBCODE=EMP.JOBCODE AND EMP.DEPTNO=DEPT.DEPTNO AND DNAME='SALES');

-- 9. Δείξτε όλα τα τμήματα με τριγράμματη συντομογραφία αποτελούμενη από
-- τα τρία πρώτα γράμματα του ονόματός τους
SELECT SUBSTR(DNAME,1,3) FROM DEPT;
-- 10. Σχεδιάστε το Μοντέλο Οντοτήτων–Συσχετίσεων με MySQL Workbench.