My SQL SELECT empno ename job sal FROM

Μία κάπως σύνθετη δήλωση (My. SQL) SELECT empno, ename, job, sal FROM EMP WHERE (job IN ('ANALYST', 'PROGRAMMER', 'CLERK')) AND (sal>= 850 OR sal+IFNULL(comm, 0) >= 1500) AND (ename LIKE '%ES' OR ename LIKE 'A%') AND hiredate BETWEEN '1989 -01 -01' AND '2005 -12 -31' ORDER BY job, ename DESC; ORACLE: NVL-Null Va. Lue Hiredate BETWEEN '1989/01/01' AND '2005/12/31' 7

8

Δήλωση SELECT που περιλαμβάνει φωλιασμένο SELECT …; SELECT …GROUP BY …; SELECT … GROUP BY … HAVING …; SELECT … JOIN …; SELECT … UNION SELECT …; SELECT … INTERSECT SELECT …; SELECT … MINUS SELECT …; 7

Υποαναζήτηση φωλιασμένη σε αναζήτηση SELECT ename, job, sal, deptno FROM EMP WHERE sal > (SELECT MIN(sal) FROM EMP WHERE deptno IN (10, 20)) ORDER BY deptno, ename; Η υποπρόταση SELECT MIN(sal) FROM EMP WHERE deptno IN (10, 20)) έχει ως αποτέλεσμα ΜΙΝ(SAL) 800 Επομένως η αρχική εκτελείται ως SELECT ename, job, sal, deptno FROM EMP WHERE sal > 800 ORDER BY deptno, ename; 10

11

Υποαναζήτηση φωλιασμένη σε αναζήτηση SELECT ename, job, sal, deptno FROM EMP WHERE sal>(SELECT MIN(sal) FROM EMP WHERE deptno IN (SELECT deptno FROM dept WHERE dname IN (“RESEARCH”, “SALES”))) ORDER BY deptno, ename; 12

13

Δήλωση SELECT που περιλαμβάνει GROUP BY SELECT …GROUP BY …; 11

16

Ακολουθούν παραδείγματα ορθής σύνταξης της υποπρότασης SELECT deptno, AVG(sal), COUNT(*) FROM emp GROUP BY DEPTNO; SELECT deptno, AVG(sal), COUNT(*) FROM emp GROUP BY DEPTNO HAVING deptno > 20 AND COUNT(*)>1; SELECT deptno, AVG(sal), COUNT(*) FROM emp GROUP BY DEPTNO HAVING (deptno > 20) AND (COUNT(*)>1 OR AVG(sal)>1200); 17

18

19

WHERE … GROUP BY SELECT deptno, AVG(sal), COUNT(*) FROM EMP WHERE JOB NOT IN (“ANALYST”) GROUP BY DEPTNO HAVING COUNT(*)>2 ORDER BY DEPTNO; SELECT deptno, AVG(sal), COUNT(*) FROM EMP GROUP BY DEPTNO HAVING COUNT(*)>2 ORDER BY DEPTNO; 20

21

SELECT deptno, job, AVG(sal), COUNT(*) FROM EMP GROUP BY DEPTNO, JOB ORDER BY DEPTNO, JOB; 22

23

# My. SQL CREATE TABLE my_employee(empno INT(4) NOT NULL, ename VARCHAR(10), deptno INT(2)); CREATE TABLE my_department(deptno INT(2) NOT NULL, dname VARCHAR(14)); INSERT INTO my_department SELECT deptno, dname FROM dept; INSERT INTO my_employee SELECT empno, ename, deptno FROM emp; SELECT * FROM MY_DEPARTMENT; SELECT * FROM MY_EMPLOYEE; 25

26

My. SQL 28

29

Έστω βάση Διοίκησης Προσωπικού αποτελούμενη από τους παρακάτω πίνακες SELECT * FROM emp; Empno 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 7998 Ename SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER BATES Job CLERK SALESMAN MANAGER ANALYST PRESIDENT SALESMAN CLERK ANALYST Mgr 7902 7698 7839 7566 7698 7788 7698 7566 7782 7566 Hiredate 17/12/00 20/02/01 22/02/01 02/04/01 28/09/01 01/05/01 09/06/01 19/04/07 17/11/01 08/09/01 23/05/07 03/12/01 23/01/02 17/11/07 Sal 800 1600 1250 2975 1250 2850 2450 3000 5000 1500 1100 950 3000 1300 1000 Comm. 300 500 1400 0 Deptno 20 30 30 10 20 10 30 20 10 SELECT * FROM dept; Deptno 10 20 30 40 Dname ACCOUNTING RESEARCH SALES OPERATIONS Loc NEW YORK DALLAS CHICAGO BOSTON 31

33

Σε λογικό επίπεδο γίνεται σύνδεση των δύο πινάκων: Αντί της στήλης Deptno του σχήματος στην πραγματικότητα έχουμε δύο στήλες, τις στήλες Emp. deptno, Dept. deptno, όπου Emp. deptno=Dept. deptno Empno 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 7998 Ename SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER BATES Job CLERK SALESMAN MANAGER ANALYST PRESIDENT SALESMAN CLERK ANALYST Mgr 7902 7698 7839 7566 7698 7788 7698 7566 7782 7566 Hiredate 17/12/00 20/02/01 22/02/01 02/04/01 28/09/01 01/05/01 09/06/01 19/04/07 17/11/01 08/09/01 23/05/07 03/12/01 23/01/02 17/11/07 Sal 800 1600 1250 2975 1250 2850 2450 3000 5000 1500 1100 950 3000 1300 1000 Comm 300 500 1400 0 Deptno 20 30 30 10 20 10 30 20 10 Dname RESEARCH SALES ACCOUNTING RESEARCH ACCOUNTING SALES RESEARCH ACCOUNTING SELECT * FROM dept, emp WHERE deptno=emp. deptno; SELECT * FROM emp, dept WHERE emp. deptno=deptno; Προσοχή η υπάλληλος 7998 δεν συμπεριλαμβάνεται στη σύνδεση. Loc DALLAS CHICAGO NEW YORK DALLAS NEW YORK CHICAGO DALLAS NEW YORK 34

35

Και να η επιλογή στηλών (ή πράξεων μεταξύ των στηλών) για προβολή Empno Ename 7902 7788 FORD SCOTT Job Sal+IFNULL(Comm, Emp. Deptno 0) ANALYST 3000 20 Dname RESEARCH Να και μία παραλλαγή της προηγούμενης δήλωσης. SELECT empno, ename, job, sal+IFNULL(comm, 0), emp. deptno, dname FROM dept , emp WHERE deptno= emp. deptno AND job IN ('ANALYST', 'PROGRAMMER') ORDER BY ename; My. SQL: IFNULL, ORACLE: NVL-Null Va. Lue 36

37

Σε λογικό επίπεδο γίνεται σύνδεση των δύο πινάκων: Αντί της στήλης Deptno του πίνακα στην πραγματικότητα έχουμε δύο στήλες, τις στήλες Emp. deptno, Dept. deptno, όπου Emp. deptno=Dept. deptno Empno 7782 7839 7934 7369 7566 7788 7876 7902 7499 7521 7654 7698 7844 7900 Ename CLARK KING MILLER SMITH JONES SCOTT ADAMS FORD ALLEN WARD MARTIN BLAKE TURNER JAMES Job MANAGER PRESIDENT CLERK MANAGER ANALYST CLERK ANALYST SALESMAN MANAGER SALESMAN CLERK Mgr 7839 7782 7902 7839 7566 7788 7566 7698 7839 7698 Hiredate 09/06/01 17/11/01 23/01/02 17/12/00 02/04/01 19/04/07 23/05/07 03/12/01 20/02/01 22/02/01 28/09/01 01/05/01 08/09/01 03/12/01 Sal 2450 5000 1300 800 2975 3000 1100 3000 1600 1250 2850 1500 950 Comm 300 500 1400 0 Deptno 10 10 10 20 20 20 30 30 30 Dname ACCOUNTING RESEARCH RESEARCH SALES SALES Loc NEW YORK DALLAS DALLAS CHICAGO CHICAGO 38

Οδηγηθήκαμε στο ίδιο αποτέλεσμα Και να η επιλογή στηλών (ή πράξεων μεταξύ των στηλών) για προβολή Empno Ename Job Sal 7902 FORD ANALYST 3000 7788 SCOTT ANALYST 3000 Sal+NVL(Comm, 0) Emp. Deptno 3000 20 Dname RESEARCH 39

Χρήση τελεστή join # Χρήση τελεστή join SELECT empno, ename, job, emp. deptno, dname FROM dept, emp WHERE deptno=emp. deptno; SELECT empno, ename, job, emp. deptno, dname FROM dept INNER JOIN emp ON deptno=emp. deptno; SELECT empno, ename, job, emp. deptno, dname FROM dept JOIN emp ON deptno=emp. deptno; 40

41

42

Outer join σε Oracle με χρήση (+) • Empno 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 7998 Σε λογικό επίπεδο γίνεται σύνδεση των δύο πινάκων: Αντί της στήλης Deptno του πίνακα στην πραγματικότητα έχουμε τις στήλες Emp. deptno, Dept. deptno Ename SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER BATES Job CLERK SALESMAN MANAGER ANALYST PRESIDENT SALESMAN CLERK ANALYST Mgr 7902 7698 7839 7566 7698 7788 7698 7566 7782 7566 Hiredate 17/12/00 20/02/01 22/02/01 02/04/01 28/09/01 01/05/01 09/06/01 19/04/07 17/11/01 08/09/01 23/05/07 03/12/01 23/01/02 17/11/07 Sal 800 1600 1250 2975 1250 2850 2450 3000 5000 1500 1100 950 3000 1300 1000 Comm 300 500 1400 0 Deptno 20 30 30 10 20 10 30 20 10 Dname RESEARCH SALES ACCOUNTING RESEARCH ACCOUNTING SALES RESEARCH ACCOUNTING Loc DALLAS CHICAGO NEW YORK DALLAS NEW YORK CHICAGO DALLAS NEW YORK 43

Outer join σε Oracle με χρήση (+) Προσοχή η υπάλληλος 7998 συμπεριλαμβάνεται τώρα στη σύνδεση. Επομένως τα αποτελέσματα είναι: Empno 7998 7902 7788 Ename BATES FORD SCOTT Job ANALYST Sal 1000 3000 Sal+NVL(Comm, 0) 1000 3000 Emp. Deptno 20 20 Dname RESEARCH 44

SELECT … SELECT vs JOIN SELECT empno, ename, job, sal, deptno FROM EMP WHERE deptno IN (SELECT deptno FROM DEPT WHERE dname='SALES') ORDER BY deptno, ename; SELECT empno, ename, job, sal, emp. deptno FROM EMP JOIN DEPT ON EMP. DEPTNO=DEPTNO WHERE dname='SALES' ORDER BY emp. deptno, ename; 45

46

My. SQL (IFNULL) SELECT empno, ename, job, sal+IFNULL(comm, 0), emp. deptno FROM emp INNER JOIN dept ON emp. deptno = deptno; 47

LEFT JOIN, RIGHT JOIN SELECT deptno, dname, empno, ename FROM dept LEFT JOIN emp ON deptno = emp. deptno; SELECT deptno, dname, empno, ename FROM dept RIGHT JOIN emp ON deptno = emp. deptno; 48

49

50

My. SQL: υποστηρίζει μόνο UNION SELECT που περιλαμβάνουν UNION, INTERSECT, MINUS. SELECT … UNION SELECT …; -- ένωση SELECT … INTERSECT SELECT …; -- Τομή SELECT … MINUS SELECT …; -- διαφορά 41

Περιπτώσεις SQL JOINs - παραδείγματα σε my. SQL CREATE TABLE DEPT(DEPTNO INT(2) NOT NULL, DNAME VARCHAR(14), LOC VARCHAR(14), PRIMARY KEY(DEPTNO)); CREATE TABLE EMP(EMPNO INT(4) NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(25), HIREDATE, MGR INT(4), SAL FLOAT(7, 2), COMM FLOAT(7, 2), DEPTNO INT(2), PRIMARY KEY(EMPNO), FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)); CREATE TABLE PROJ (projno INT(3) NOT NULL, pname VARCHAR(15), budget FLOAT(12, 2), PRIMARY KEY(projno)); 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)); 54

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', 2000, NULL, 20); INSERT INTO EMP VALUES (30, 'DATE', 'PROGRAMMER', '2004/05/04', 15, 1800, 200, 10); INSERT INTO EMP VALUES (11, 'CODD', 'ANALYST', '1989/01/01', 15, 2900, NULL, 10); INSERT INTO EMP VALUES (12, 'CODD', 'PROGRAMMER', '1995/05/02', 15, 1200, 150, 10); INSERT INTO EMP VALUES (21, 'CODD', 'SALESMAN', '1977/07/07', 2000, NULL, 10); INSERT INTO EMP VALUES (22, 'CODD', 'PROGRAMMER', '1995/05/02', 15, 1200, 150, 20); INSERT INTO EMP VALUES (23, 'CODD', 'SALESMAN', '1977/07/07', 2000, NULL, 20); INSERT INTO EMP VALUES (77, 'BATES', 'SALESMAN', '1987/07/07', 2000, NULL); 55

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); INSERT INTO assign(empno, projno, ptime) VALUES(20, 200, 100); INSERT INTO assign(empno, projno, ptime) VALUES(30, 100); 56

SELECT EMPNO, ENAME, EMP. DEPTNO, DEPT. DNAME, SAL, COMM FROM emp INNER JOIN dept ON emp. deptno=deptno ORDER BY ename; 57

SELECT empno, ename, deptno FROM emp INNER JOIN dept ON emp. deptno=deptno ORDER BY ename; SELECT empno, ename, deptno FROM emp JOIN dept ON emp. deptno=deptno ORDER BY ename; 58

SELECT EMPNO, ENAME, EMP. DEPTNO, DEPT. DNAME, SAL, COMM FROM emp INNER JOIN dept ON emp. deptno=deptno ORDER BY ename; 59

SELECT empno, ename, deptno FROM emp LEFT JOIN dept ON emp. deptno=deptno ORDER BY ename; 60

SELECT empno, ename, deptno FROM emp RIGHT JOIN dept ON emp. deptno=deptno ORDER BY ename; 61

SELECT column_names FROM table 1 FULL JOIN table 2 ON table 1. column_name=table 2. column_name; Δεν υποστηρίζεται ο τελεστής στο προϊόν my. SQL. 62

SELECT empno, ename, deptno FROM emp LEFT JOIN dept ON emp. deptno=deptno UNION SELECT empno, ename, deptno FROM emp RIGHT JOIN dept ON emp. deptno=deptno ORDER BY ename; 63

SELECT assign. empno, ename, dname, assign. projno, ptime FROM dept INNER JOIN emp ON deptno=emp. empno JOIN assign ON empno=assign. empno JOIN proj ON assign. projno=projno; 64

Διαφέρουν οι παρακάτω δηλώσεις; Γιατί; select SUM(sal)/COUNT(sal) from Emp; select AVG(sal) from Emp; select SUM(sal)/COUNT(*) from Emp; select SUM(comm)/COUNT(comm) from Emp; select AVG(comm) from Emp; select SUM(comm) / COUNT(*) from Emp; 65

66

67