DROP DATABASE IF EXISTS database_tour_one;
CREATE DATABASE database_tour_one;
USE database_tour_one;

CREATE TABLE DEPT(DEPTNO INT(2) NOT NULL, 
                     DNAME VARCHAR(14), LOC VARCHAR(14),
                     PRIMARY KEY(DEPTNO));
DESCRIBE DEPT;

CREATE TABLE EMP(EMPNO INT(4) NOT NULL, 
      ENAME VARCHAR(10), JOB VARCHAR(25), 
      HIREDATE DATE, MGR INT(4),  SAL FLOAT(7,2), COMM FLOAT(7,2),
      DEPTNO INT(2), 
      PRIMARY KEY(EMPNO),
      FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO));
DESCRIBE EMP;

CREATE TABLE PROJ (projno INT(3) NOT NULL,
                   pname VARCHAR(15),
                   budget FLOAT(12,2),
     PRIMARY KEY(projno));
DESCRIBE PROJ;

CREATE TABLE ASSIGN(
 EMPNO INT(4) NOT NULL, PROJNO INT(3) NOT NULL,
 PTIME INT(3),
PRIMARY KEY(EMPNO,PROJNO),
FOREIGN KEY(EMPNO) REFERENCES EMP(EMPNO),
FOREIGN KEY(PROJNO) REFERENCES PROJ(PROJNO));
DESCRIBE ASSIGN;

SHOW TABLES;

INSERT INTO DEPT(DEPTNO, DNAME, LOC) 
            VALUES (10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT(DEPTNO, DNAME, LOC)
            VALUES (20, 'RESEARCH', 'DALLAS');                                                                     
INSERT INTO DEPT(DEPTNO, DNAME, LOC)
            VALUES (30, 'SALES', 'CHICAGO');                                                                    
INSERT INTO DEPT(DEPTNO, DNAME, LOC)
            VALUES (40, 'OPERATIONS', 'BOSTON');                                                                     

INSERT INTO EMP
  VALUES (10, 'CODD', 'ANALYST', '1989/01/01', 15, 3000, NULL, 10);
INSERT INTO EMP
  VALUES (15, 'ELMASRI', 'ANALYST', '1995/05/02', 15, 1200, 150, 10);
INSERT INTO EMP
  VALUES (20, 'NAVATHE', 'SALESMAN', '1977/07/07', 20, 2000, NULL, 20);                    
INSERT INTO EMP
  VALUES (30, 'DATE', 'PROGRAMMER', '2004/05/04', 15, 1800, 200, 10);                    

INSERT INTO proj(projno, pname, budget)
            VALUES(100, 'PAYROLL', 100000);
INSERT INTO proj(projno, pname, budget)
            VALUES(200, 'PERSONNEL',200000 );
INSERT INTO proj(projno, pname, budget)
            VALUES(300, 'SALES', 150000);

INSERT INTO assign(empno, projno, ptime)
                 VALUES(10,100, 40);
INSERT INTO assign(empno, projno, ptime)
                 VALUES(10, 200, 60);
INSERT INTO assign(empno, projno, ptime)
                 VALUES(15, 100, 100);
INSERT INTO assign(empno, projno, ptime)
                 VALUES(20, 200, 100);
INSERT INTO assign(empno, projno, ptime)
                 VALUES(30, 100, 100);
 

SELECT * FROM EMP;
SELECT * FROM DEPT;
SELECT * FROM PROJ;
SELECT * FROM ASSIGN;

#1 Δείξτε υπαλλήλους της εταιρείας  με το μισθό τους.
SELECT  * FROM EMP;
SELECT  EMPNO "ΚΩΔΙΚΟΣ ΥΠΑΛΛΗΛΟΥ", ENAME  "ΟΝΟΜΑ ΥΠΑΛΛΗΛΟΥ", SAL "ΜΙΣΘΟΣ"
FROM EMP;
SELECT  EMPNO "ΚΩΔΙΚΟΣ ΥΠΑΛΛΗΛΟΥ", ENAME  "ΟΝΟΜΑ ΥΠΑΛΛΗΛΟΥ", SAL "ΜΙΣΘΟΣ"
FROM EMP;

#2 Δείξτε υπαλλήλους της εταιρείας  με το μισθό τους, την ωριαία αμοιβή και το σύνολο αποδοχών.
SELECT EMPNO, ENAME, SAL/(25*8), SAL+IFNULL(COMM,0) FROM EMP;
SELECT EMPNO, ENAME, SAL/(25*8) "ΩΡΙΑΙΑ ΑΜΟΙΒΗ",
               SAL+IFNULL(COMM,0) "ΣΥΝΟΛΟ ΑΠΟΔΟΧΩΝ" 
FROM EMP;

#3 Δείξτε όνομα και μισθό υπαλλήλου με τίτλους στο αποτέλεσμα "ΟΝΟΜΑ ΥΠΑΛΛΗΛΟΥ", "ΜΙΣΘΟΣ" 
# Ταξινομήστε τα αποτελέσματα κατά όνομα 
SELECT  EMPNO, ENAME  "ΟΝΟΜΑ ΥΠΑΛΛΗΛΟΥ", SAL "ΜΙΣΘΟΣ" 
FROM EMP
ORDER BY ENAME;
# Δείτε και τα εξής
SELECT EMPNO, ENAME, SAL, DEPTNO 
FROM EMP
ORDER BY JOB, ENAME;
SELECT EMPNO, ENAME, SAL, DEPTNO 
FROM EMP
ORDER BY DEPTNO, ENAME;


#4 Δείξτε μαζί κωδικό και όνομα υπαλλήλου
select CONCAT(empno, ' ', ename) from emp;


#5 Που διαφέρουν οι δηλώσεις;
SELECT  job
from  emp;
SELECT  DISTINCT job
from  emp;


#6 Τι δείχνει η δήλωση;
SELECT  DISTINCT job, deptno
from  emp;

#7 Τι δείχνει η δήλωση;
SELECT  DISTINCT job, deptno
from  emp
ORDER BY job, deptno;

#8 Τι δείχνει η δήλωση;
SELECT  DISTINCT job, deptno
from  emp
ORDER BY deptno, job;

#9 Δείξε τα τμήματα που έχουν έδρα NEW YORK.
SELECT  DNAME, LOC
FROM  DEPT
WHERE  LOC = 'NEW YORK';


#10 Δείξε τους υπάλληλους του Τμήματος 10.
SELECT *
FROM EMP
WHERE DEPTNO = 10;

#11 Δείξε κωδικό, όνομα, θέση υπαλλήλου και κωδικό τμήματος για τους αναλυτές (ANALYST).
SELECT ENAME,EMPNO,JOB, DEPTNO
FROM EMP
WHERE JOB = 'ANALYST';

#12 Δείξε όνομα, αριθμό Τμήματος για τμήματα με κωδικό (αριθμό) μεγαλύτερο του 20.
SELECT DNAME,DEPTNO
FROM DEPT
WHERE DEPTNO > 20;

#13 Δείξε όνομα, αριθμό Τμήματος για τμήματα με κωδικό >= 20.
SELECT DNAME,DEPTNO
FROM DEPT
WHERE DEPTNO >= 20;

#14 Δείξε υπαλλήλους με προμήθεια μεγαλύτερη του δέκατου του μισθού τους.
SELECT ENAME,SAL,SAL/10, COMM
FROM EMP
WHERE COMM > SAL/10;

SELECT ENAME,SAL,SAL/10, COMM
FROM EMP
WHERE IFNULL(COMM,0) > SAL/10;

# Χρήση τελεστών σύγκρισης
#15 Δείξτε πωλητές με μισθό πάνω από 1,300 ή προμήθεια μεγαλύτερη του ενός δεκάτου του μισθού τους
# Λανθασμένη λύση
SELECT  ENAME "ΟΝΟΜΑ", SAL "ΜΙΣΘΟΣ", COMM "ΠΡΟΜΗΘΕΙΑ"
FROM EMP
WHERE JOB = 'SALESMAN'
    AND (SAL> 1300) OR (COMM > SAL/10);
# ορθή λύση
SELECT  ENAME "ΟΝΟΜΑ", SAL "ΜΙΣΘΟΣ", COMM "ΠΡΟΜΗΘΕΙΑ"
FROM EMP
WHERE JOB = 'SALESMAN'
    AND ((SAL> 1300) OR (COMM > SAL/10));



# Χρήση BETWEEN … AND
#16 Δείξτε πωλητές που έχουν μισθό μεταξύ 1800 και 2000.
SELECT  ENAME, JOB, SAL
FROM EMP
WHERE JOB = 'SALESMAN'
AND SAL BETWEEN 1800 AND 2000;
# Εναλλακτική λύση 
SELECT ENAME, JOB, SAL
FROM EMP
WHERE JOB = 'SALESMAN'
AND (SAL>=1800 AND SAL<=2000);

# Χρήση συνόλων (τελεστών IN) 
#17 Δείξτε πωλητές ή αναλυτές που έχουν μισθό μεγαλύτερο από 1250
SELECT  ENAME, JOB, SAL
FROM EMP
WHERE JOB IN ('SALESMAN', 'ANALYST')
AND SAL> 1250;
# Εναλλακτική λύση 
SELECT  ENAME, JOB, SAL
FROM EMP
WHERE (JOB='SALESMAN' OR JOB='ANALYST')
AND SAL> 1250;

# Χρήση συνάρτησης substr
#18 Δείξτε αναλυτές με όνομα που αρχίζει από C, D, E.
SELECT  ename, job FROM   emp
WHERE  (ename >= 'C'  AND ename < 'F' )
AND  job = 'ANALYST';

# χρήση LIKE
#19 Δείξτε υπάλληλους με επώνυμο που αρχίζει από το γράμμα C
SELECT ename, job 
FROM emp 
WHERE ename LIKE 'C%';
#
# Χρήση BETWEEN … AND με ημερομηνίες
#20 Δείξτε υπάλληλους που το επώνυμο τους αρχίζει από C ή D και προσλήφθηκαν από το 1989 έως και το 1995
SELECT  ename, hiredate from  emp
where  ename between 'C' and 'F' 
and  hiredate Between '1989/01/01' and '1995/12/31';

#21 Δείξτε όνομα,θέση και μισθό των υπαλλήλων του Τμήματος 20 που κερδίζουν πάνω από 2,000.
SELECT ENAME,JOB,SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 10
AND SAL > 2000;

#22 Δείξτε τους πωλητές της Τμήματος 20 με μισθό μεγαλύτερο ή ισο των 1,500.
SELECT ENAME, SAL, JOB, DEPTNO FROM EMP
WHERE JOB = 'ANALYST'
 AND DEPTNO = 10
 AND SAL >= 1500;

#23 Δείξτε υπάλληλους που είναι πωλητές ή αναλυτές (με 2 τρόπους, IN, χρήση OR) 
# Ταξινομήστε ανά θέση κατά φθίνουσα σειρά  
SELECT ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE JOB = 'ANALYST'
OR JOB = 'SALESMAN'
ORDER BY JOB DESC;
SELECT ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE JOB IN('ANALYST', 'SALESMAN')
ORDER BY JOB DESC;


#24 Δείξτε τους τους πωλητές ή αναλυτές που ανήκουν στο τμήμα 10.
SELECT ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE (JOB = 'ANALYST' OR JOB = 'SALESMAN')
AND DEPTNO=10;

#25 Δείξτε τους πωλητές ή αναλυτές που δεν ανήκουν στο τμήμα 10.
SELECT ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE (JOB = 'ANALYST' OR JOB = 'SALESMAN')
AND DEPTNO <> 10;

#26 Δείξτε τους πωλητές οποιουδήποτε τμήματος και τους αναλυτές του Τμήματος 10.
SELECT *
FROM EMP
WHERE JOB = 'SALESMAN' 
OR (JOB = 'ANALYST' AND DEPTNO = 10);

#27 Δείξτε τους υπάλληλους του Τμήματος 10 που δεν είναι πωλητές ή αναλυτές.
SELECT *
FROM EMP
WHERE NOT (JOB = 'SALESMAN' OR JOB = 'ANALYST') 
AND DEPTNO = 10;

#28 Δείξτε τους υπαλλήλους που έχουν μισθό μεταξύ 1,500 και 1,800 
# ταξινομημένους ανά όνομα.
SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL BETWEEN 1500 AND 1800
ORDER BY ENAME;


#29 Χρησιμοποιήστε τελεστή BETWEEN ... AND και τελεστή NOT για να βρείς τους υπόλοιπους υπάλληλους.
SELECT EMPNO, ENAME,JOB,SAL
FROM EMP
WHERE SAL NOT BETWEEN 1500 AND 1800
ORDER BY ENAME;

#30 Δείξτε υπάλληλους που είναι αναλυτές ή πωλητές ή DBA.
SELECT EMPNO, ENAME,JOB,DEPTNO
FROM EMP
WHERE JOB IN ('ANALYST','SALESMAN', 'DBA');

#31 Δείξτε τους υπόλοιπους υπάλληλους.
SELECT EMPNO, ENAME,JOB,DEPTNO
FROM EMP
WHERE JOB NOT IN ('ANALYST','SALESMAN', 'DBA');


#32 Δείξτε τους υπάλληλους που το επώνυμο τους αρχίζει με γράμμα C ή E.
SELECT EMPNO, ENAME,JOB,DEPTNO
FROM EMP
WHERE (ENAME LIKE 'C%' OR ENAME LIKE 'E%');

#33 Δείξτε τους υπάλληλους που το όνομα τους τελειώνει σε THE και είναι Πωλητές.
SELECT EMPNO, ENAME,JOB,DEPTNO
FROM EMP
WHERE ENAME LIKE '%THE'
AND JOB = 'SALESMAN';