1 / 78

PHP+SQL 10 (SQL 3)

PHP+SQL 10 (SQL 3). Group By, Having Multi-table queries Subqueries Examples. SELECT Displayed order of suffixes. INTO FROM WHERE GROUP BY HAVING UNION/MINUS INTERSECT ORDER BY. PHP+SQL 10 (SQL 3). Group By, Having Multi-table queries Subqueries Examples.

zarola
Download Presentation

PHP+SQL 10 (SQL 3)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. PHP+SQL10(SQL 3) Group By, Having Multi-table queries Subqueries Examples OE NIK 2013

  2. SELECTDisplayed order of suffixes • INTO • FROM • WHERE • GROUP BY • HAVING • UNION/MINUS • INTERSECT • ORDER BY OE NIK 2013

  3. PHP+SQL10(SQL 3) Group By, Having Multi-table queries Subqueries Examples OE NIK 2013

  4. Grouping/Aggregate functions • SUM - Sum • AVG - Average • MIN - Minimum • MAX - Maximum • COUNT -Number of non null values (records) • GROUP_CONCAT - Concatenated list of elements • STDDEV - Standard deviation • VARIANCE - Variance OE NIK 2013

  5. Non-grouping usage • select avg(sal) as Average from emp; • select min(sal) from emp; • select min(sal) from emp where sal>2000; • select avg(distinct sal) as Average from emp; • select count(sal) from emp; • select count(comm) from emp where sal>2000; • select comm from emp where sal>2000; • select count(*) from emp where sal>2000; • select avg(comm) from emp;  NULL values are not included! OE NIK 2013

  6. Grouping • select distinct deptno from emp; • select avg(sal) from emp where deptno=10; • select avg(sal) from emp where deptno=20; • select avg(sal) from emp where deptno=30; •  select deptno, avg(sal) from emp group by deptno; OE NIK 2013

  7. Grouping IN THE SELECTION LIST (FIELD LIST) ONLY THE GROUPED FIELD(s) AND THE GROUPING FUNCTION(s) ARE ALLOWED!(YES, IN MYSQL AS WELL!!!)(ONLY_FULL_GROUP_BY) • select deptno, avg(sal) as Average, min(sal) as Minimum, count(*) as Num from emp group by deptno; OE NIK 2013

  8. Grouping and suffixes • select mgr, avg(sal) from emp group by mgr; • select ifnull(mgr, "none") as boss, lpad(avg(sal), 15, '#') as "Averagesal" from emp group by mgr; • HAVING vs. WHERE • select mgr, avg(sal) from emp where ename like '%E%' group by mgr; • select mgr, avg(sal) from emp where ename like '%E%' group by mgr having avg(sal)>1300; • select mgr, avg(sal) as average from emp where ename like '%E%' group by mgr having avg(sal)>1300 order by average desc; OE NIK 2013

  9. More complex grouping queries • select min(max(sal)), max(max(sal)), round(avg(max(sal))) from emp group by deptno; -- In Oracle this works, in MySQL „Invalid use of group function” • select min(sal+ nvl(comm,0)), mod(empno,3) from emp group by mod(empno,3) having min(sal+nvl(comm,0)) > 800; OE NIK 2013

  10. More complex grouping queries • select distinct job, substr(job,2, 1) from emp; • select avg(sal) as average, substr(job,2, 1) from emp group by substr(job, 2, 1); • select ename, sal, round(sal/1000) from emp; • select round(sal/1000) as SalCat, count(sal) as Num from emp group by round(sal/1000); OE NIK 2013

  11. More complex grouping queries • select ename, round(datediff(curdate(), hiredate)/365.25) as diff from emp; • select count(*), round(datediff(curdate(), hiredate)/365.25) as diff from emp group by round(datediff(curdate(), hiredate)/365.25); OE NIK 2013

  12. More complex grouping queries • select distinct depno, job from emp; • select deptno,job,avg(sal),min(sal),max(sal) from emp group by deptno, job order by deptno, job; •  Oracle-specific „extras” (not required): • GROUP BY GROUPING SETS • GROUP BY CUBE • GROUP BY ROLLUP OE NIK 2013

  13. PHP+SQL10(SQL 3) Group By, Having Multi-table queries Subqueries Examples OE NIK 2013

  14. SET OPERATORS • [query] [set operator] [query] • Important:two queries with same number of columns! • Operators: UNION, INTERSECT, MINUS / EXCEPT • E.g.: select min(sal) as MIN_AVG_MAX from emp UNION select avg(sal) from emp UNION select max(sal) from emp; • MySQL S***CKS: http://www.bitbybit.dk/carsten/blog/?p=71 OE NIK 2013

  15. Table structure diagram OE NIK 2013

  16. Preparations • We already have a department without a worker • create table emp1 as select * from emp; • create table dept1 as select * from dept; • update emp1 set deptno=50 where ename='WARD'; • The new tables will be inconsistent, but we need this to test everything • update emp1 set deptno=NULL where ename='WARD'; • Now we have a department without worker (40) and a worker without department (WARD) OE NIK 2013

  17. Querying multiple tables • select * from emp1, dept1; • select * from emp1, emp1; • select * from emp1 a, emp1 b; • select a.empno, a.ename, b.empno, b.ename from emp1 a, emp1 b; • select a.ename, a.deptno, b.deptno, b.dname from emp1 a,dept1 b; • „Cross Join”  Cartesian product EMP1 DEPT1 OE NIK 2013

  18. "MANUAL JOIN" • select a.ename, a.deptno, b.deptno, b.dname from emp1 a, dept1 b where a.deptno=b.deptno; • Where is Ward??? • select a.empno, a.ename, a.mgr, b.empno, b.ename, b.mgr from emp1 a, emp1 b where a.mgr=b.empno; • Where is King??? OE NIK 2013

  19. JOIN • Goes into the FROM part of a query • SELECT * FROM table1, table2 SELECT * FROM table1 JOIN_EXPRESSION table2 JOIN_CONDITION • Joining tables = connecting the foreign keys to the primary keys • Joining indexed fields is fast, joining non-indexed (non-key) fields is very slow  good-to-avoid, cannot-always-avoid OE NIK 2013

  20. NATURAL / INNER JOIN • Same result as with "MANUAL JOIN"  By default, it only works with the same field names! (…) • select * from emp1 natural inner join dept1;-- Not in MySQL! • select * from emp1 natural join dept1; • select * from emp1 inner join dept1 using (deptno); • select * from emp1 inner join dept1 on (emp1.deptno=dept1.deptno); OE NIK 2013

  21. LEFT JOIN … ON … • It must be used if we want to display the records where there is no matching primary key record • The nonexistent records' fields will be filled with NULL values  use IFNULL if needed • select a.ename, a.deptno, b.deptno, b.dname from emp1 a LEFT JOIN dept1 b ON a.deptno=b.deptno; • select a.empno, a.ename, a.mgr, b.empno, b.ename, b.mgr from emp1 a LEFT JOIN emp1 b ON a.mgr=b.empno; OE NIK 2013

  22. LEFT/RIGHT JOIN … ON … • select a.ename, a.deptno, b.deptno, b.dname from emp1 a RIGHT JOIN dept1 b ON a.deptno=b.deptno; • select a.ename, a.deptno, b.deptno, b.dname from dept1 b LEFT JOIN emp1 a ON a.deptno=b.deptno;  The LEFT JOIN is more used OE NIK 2013

  23. JOIN / ORACLE • SELECT * FROM emp1 d, emp1 fWHERE d.mgr=f.empno (+); • SELECT * FROMemp1 d LEFT JOIN emp1 f ON (d.mgr=f.empno) • SELECT * FROM emp1 d, emp1 fWHERE d.mgr(+)=f.empno; SELECT * FROMemp1 d RIGHT JOIN emp1 f ON (d.mgr=f.empno) • SELECT level, empno, ename,mgr FROM emp START WITH empno=7839 CONNECT BY mgr=prior empno ORDER BY level desc; OE NIK 2013

  24. FULL JOIN • LEFT JOIN + RIGHT JOIN • select a.empno, a.ename, a.deptno, b. deptno, b.dname from emp1 a FULL JOIN dept1 b ON (emp1.deptno=dept1.deptno); • [The standard names this type as UNION JOIN; unavailable in MySQL…]  Use the "MANUAL" JOIN and the LEFT JOIN OE NIK 2013

  25. JOIN MISTAKE??? • SELECT ename, sal, dname FROM emp, dept WHERE emp.deptno=dept.deptno; • SELECT ename, sal, dname FROM emp, dept WHERE emp.deptno=dept.deptno AND ename like '%E%'; • SELECT ename, sal, dname FROM emp, dept WHERE emp.deptno=dept.deptno AND ename like '%E%' OR sal<3000; • SELECT ename, sal, dname FROM emp, dept WHERE emp.deptno=dept.deptno AND (ename like '%E%' OR sal<3000); OE NIK 2013

  26. JOINING TABLES OE NIK 2013

  27. JOINING TABLES OE NIK 2013

  28. JOINING TABLES OE NIK 2013

  29. JOINING TABLES • SELECT * FROM emp1 worker, emp1 boss, dept1 work_dept, dept1 boss_deptWHEREworker.mgr=boss.empno ANDworker.deptno=work_dept.deptno ANDboss.deptno=boss_dept.deptno; • Good, if we don't want to see the unpaired records OE NIK 2013

  30. JOINING TABLES • SELECT * FROM emp1 worker LEFT JOIN emp1 boss ON (worker.mgr=boss.empno), dept1 work_dept, dept1 boss_deptWHEREworker.deptno=work_dept.deptno ANDboss.deptno=boss_dept.deptno; • NOT CHANGES ANYTHING OE NIK 2013

  31. JOINING TABLES • SELECT * FROM emp1 worker LEFT JOIN emp1 boss ON (worker.mgr=boss.empno) LEFT JOIN dept boss_dept ON (boss.deptno=boss_dept.deptno) LEFT JOIN dept work_dept ON (worker.deptno=work_dept.deptno); • PERFECT OE NIK 2013

  32. JOINING TABLES • After the join is made in the FROM/WHERE, the query can be built up just like as if it was a single-table query. Every suffix can be used, the result-table can be used the same way as with a single-table query • PRACTICEandSTRUCTURED QUERIESare required!!! OE NIK 2013

  33. EXAMPLE Display every boss' name and the average salary of those sub-workers who earn more than 1000 USD. Only display those records where this average is smaller than 5000 USD. OE NIK 2013

  34. EXAMPLE SELECT avg(a.sal) as AVERAGE, b.ename as BOSS FROM emp a, emp b WHERE (a.mgr=b.empno) AND (a.sal>1000) GROUP BY b.ename HAVING avg(a.sal)<5000 ORDER BY AVERAGE desc; OE NIK 2013

  35. EXAMPLE SELECT avg(a.sal) as AVERAGE, b.ename as BOSS FROM emp a LEFT JOIN emp b ON a.mgr=b.empno WHERE a.sal>1000 GROUP BY b.ename HAVING avg(a.sal)<5000 ORDER BY AVERAGE desc; OE NIK 2013

  36. PHP+SQL10(SQL 3) Group By, Having Multi-table queries Subqueries Examples OE NIK 2013

  37. SUB-QUERIES • Basic principle: in some parts of the main query (field list, WHERE suffix, FROM suffix) there is another query instead of a simple field name/expression/constant/ table  sub-query • MySQL only supports it with the newer (>4.1) versions (with some angry limitations…) OE NIK 2013

  38. Sub-queries in the field list • You can put a constant in the field list  you can put any sub-query there that returns with exactly one value [more than one row / too many values / operand should contain 1 column(s)] • SELECT sal,(select max(sal) from emp) as MAX,(select max(sal) from emp)-sal as DELTA FROM empORDER BY DELTA desc; OE NIK 2013

  39. Sub-queries in the FROM (inline view) • You can put any table in the FROM  every query returns a table  almost any sub-query can be used in the FROM, that we can use as a table in that query • The sub-query must have an alias and must be written between parentheses • select worker.salary from (select sal as salary from emp) worker order by salary desc; • select * from (select a.empno, a.ename, a.mgr, b.empno, b.ename from emp a, emp b where a.mgr=b.empno) order by ename asc; -- Field names must be unique – use an alias!!! OE NIK 2013

  40. Sub-queries in the FROM SELECT bosses.work_name as Worker, bosses.boss_name as Boss FROM ( select a.empno as work_id, a.ename as work_name, a.mgr as work_bossid, b.empno as boss_id, b.ename as boss_name from emp a, emp b where a.mgr=b.empno) bosses ORDER BY Worker asc; OE NIK 2013

  41. Sub-queries in the FROM SELECT emp.deptno, min, ename FROM ( Select deptno, min(sal) as min From emp Group by deptno ) minimums, empWHERE emp.sal=minimums.min and emp.deptno=minimums.deptno; OE NIK 2013

  42. Sub-query in the WHERE • Use constant values in the WHERE  use any sub-query that returns with exactly one value [more than one row / too many values / operand should contain 1 column(s)] • ALWAYS VERY SLOW  SUBQUERY IN THE FROM IS BETTER • select ename, sal from emp where sal>(select avg(sal) from emp); • select ename, sal from emp where sal=(select min(sal) from emp); OE NIK 2013

  43. Sub-query in the WHERE • Use lists in the WHERE  use any sub-query that returns with exactly one column • Operators: [NOT] IN, ANY, ALL • Példa: select sal, mod(round(sal/1000), 2) from emp; select sal from emp where mod(round(sal/1000), 2)=0; select sal from emp where deptno = 10; OE NIK 2013

  44. Sub-query in the WHERE • select ename, sal from emp where sal IN (select sal from emp where deptno = 10); • select ename, sal from emp where sal NOT IN (select sal from emp where deptno = 10); • Correlated sub-query • We’ll talk about optimization later • Optimization is not important this semester OE NIK 2013

  45. Sub-query in the WHERE • select ename, sal from emp where sal> ANY (select sal from emp where deptno = 10); • select ename, sal from emp where sal>(select min(sal) from emp where deptno = 10);  Same results  Oracle does an internal sort with the ANY!!! OE NIK 2013

  46. Sub-query in the WHERE • select ename, sal from emp where sal> ALL (select sal from emp where deptno = 10); • select ename, sal from emp where sal> ALL (select sal from emp where deptno = 30); • select ename, sal from emp where sal>(select max(sal) from emp where deptno = 30);  Same results  No automatic sort  Always use the ORDER BY, if required OE NIK 2013

  47. PHP+SQL10(SQL 3) Group By, Having Multi-table queries Subqueries Examples OE NIK 2013

  48. Zero • List the name, job and income for the workers who work in Dallas or Chicago. • Looks easy… Let’s use operator IN OE NIK 2013

  49. Solution using the IN SELECT ename, job, sal+nvl(comm, 0) FROM emp, dept WHERE emp.deptno=dept.deptno AND upper(dept.loc) IN ('DALLAS', 'CHICAGO'); SELECT ename, job, sal+nvl(comm, 0) FROM emp WHERE deptno IN ( SELECT deptno FROM dept WHERE upper(loc) IN ('DALLAS', 'CHICAGO') ); OE NIK 2013

  50. BAD Solution using manual join SELECT ename, job, sal+nvl(comm, 0) FROM emp, dept WHERE emp.deptno=dept.deptno AND upper(dept.loc)='DALLAS' OR upper(dept.loc)='CHICAGO'; OE NIK 2013

More Related