Real-World Performance Training - oracle.com

1 Real-World Performance Training SQL Introduction Real-World Performance Team Copyright 2014, oracle and/or its affiliates. All rights reserved. |. SQL. Basics SQL. Structured Query Language Declarative You express what you want to do, not how to do it Despite the name, provides ability to modify (create, update, delete) data too Copyright 2014, oracle and/or its affiliates. All rights reserved. |. SQL. Basic Anatomy of a SQL Statement select dname, job, avg(sal), max(sal), count(*) Select list from emp e join dept d Tables on = Table Aliases where job != 'CLERK'. group by dname, job Join having avg(sal) > 1500. order by dname, avg(sal) Predicate Aggregation Having Sorting Copyright 2014, oracle and/or its affiliates.

2 All rights reserved. |. SQL. Sub-query ( correlated ). select empno, ename, sal Sub-query from emp e1. where sal > (. select avg(sal). from emp e2. where = group by deptno ). Copyright 2014, oracle and/or its affiliates. All rights reserved. |. SQL. Inline View select empno, ename, sal, avg_sal from emp e join ( select deptno , avg(sal) avg_sal Inline from emp View group by deptno ) v on = Copyright 2014, oracle and/or its affiliates. All rights reserved. |. SQL. Common Table Expression (CTE). with avg_sal as ( select deptno , avg(sal) avg_sal Common from emp group by deptno Table ) Expression select empno, ename, sal, avg_sal from emp e join avg_sal a on = Copyright 2014, oracle and/or its affiliates.

3 All rights reserved. |. SQL. Join Inner Join A B. from A. join B. on = Copyright 2014, oracle and/or its affiliates. All rights reserved. |. SQL. Example Tables EMP DEPT. ENAME JOB DEPTNO DEPTNO DNAME LOC. CLARK MANAGER 10 10 ACCOUNTING NEW YORK. KING PRESIDENT 10 20 RESEARCH DALLAS. FORD ANALYST 20 30 SALES CHICAGO. WARD SALESMAN 30 40 OPERATIONS BOSTON. BILLY MECHANIC 50. Copyright 2014, oracle and/or its affiliates. All rights reserved. | 9. SQL. Inner Join SELECT , , , , FROM emp e JOIN dept d ON = ;. Copyright 2014, oracle and/or its affiliates. All rights reserved. |. SQL. Inner Join Results RESULTS. ENAME JOB DEPTNO DNAME LOC. CLARK MANAGER 10 ACCOUNTING NEW YORK.

4 KING PRESIDENT 10 ACCOUNTING NEW YORK. FORD ANALYST 20 RESEARCH DALLAS. WARD SALESMAN 30 SALES CHICAGO. Copyright 2014, oracle and/or its affiliates. All rights reserved. | 11. SQL. Outer Joins Left Outer Join Right Outer Join Full Outer Join A B A B A B. from A from A from A. left outer join B right outer join B full outer join B. on = on = on = Copyright 2014, oracle and/or its affiliates. All rights reserved. | 12. SQL. Outer Joins SELECT , , , , FROM emp e LEFT/FULL/RIGHT OUTER JOIN dept d ON = ;. Copyright 2014, oracle and/or its affiliates. All rights reserved. |. SQL. Inner Join Results INNER JOIN. ENAME JOB DEPTNO DNAME LOC. CLARK MANAGER 10 ACCOUNTING NEW YORK.

5 KING PRESIDENT 10 ACCOUNTING NEW YORK. FORD ANALYST 20 RESEARCH DALLAS. WARD SALESMAN 30 SALES CHICAGO. Copyright 2014, oracle and/or its affiliates. All rights reserved. | 14. SQL. Left Outer Join Results LEFT OUTER JOIN. ENAME JOB DEPTNO DNAME LOC. CLARK MANAGER 10 ACCOUNTING NEW YORK. KING PRESIDENT 10 ACCOUNTING NEW YORK. FORD ANALYST 20 RESEARCH DALLAS. WARD SALESMAN 30 SALES CHICAGO. BILLY MECHANIC 50. Copyright 2014, oracle and/or its affiliates. All rights reserved. | 15. SQL. Right Outer Join Results RIGHT OUTER JOIN. ENAME JOB DEPTNO DNAME LOC. CLARK MANAGER 10 ACCOUNTING NEW YORK. KING PRESIDENT 10 ACCOUNTING NEW YORK. FORD ANALYST 20 RESEARCH DALLAS. WARD SALESMAN 30 SALES CHICAGO.

6 40 OPERATIONS BOSTON. Copyright 2014, oracle and/or its affiliates. All rights reserved. | 16. SQL. Full Outer Join Results FULL OUTER JOIN. ENAME JOB DEPTNO DNAME LOC. CLARK MANAGER 10 ACCOUNTING NEW YORK. KING PRESIDENT 10 ACCOUNTING NEW YORK. FORD ANALYST 20 RESEARCH DALLAS. WARD SALESMAN 30 SALES CHICAGO. 40 OPERATIONS BOSTON. BILLY MECHANIC 50. Copyright 2014, oracle and/or its affiliates. All rights reserved. | 17. SQL. Set Operators Union Minus Intersect A A A. B B B. select * from A select * from A select * from A. union minus intersect select * from B select * from B select * from B. Copyright 2014, oracle and/or its affiliates. All rights reserved. |. SQL. Window Function Generally a window function has a number of components An analytic function itself eg.

7 Sum(), avg(), stddev(), rank A partitioning clause To logically divide the data into sets A windowing clause Defines the set of rows for the function to work on, with respect to the current row . Order by clause Defines the order of the rows within the partition or window Copyright 2014, oracle and/or its affiliates. All rights reserved. |. SQL. Window Function analytic function select dname, ename, sal partition by clause , rank(). over (. partition by order by sal desc ) sal_rank from emp e join dept d on = order by clause for order by dname, sal_rank function Copyright 2014, oracle and/or its affiliates. All rights reserved. |. SQL. Window Function 1. deptno=10 2. 3.

8 1. 2 Rows sorted and partition by deptno=20 3 RANKed within each partition deptno=30. Copyright 2014, oracle and/or its affiliates. All rights reserved. |. SQL. Window Function rank(). DNAME ENAME SAL SAL_RANK. -------------- ---------- ---------- ---------- ACCOUNTING KING 5000 1. ACCOUNTING CLARK 2450 2. ACCOUNTING MILLER 1300 3. RESEARCH FORD 3000 1. RESEARCH SCOTT 3000 1. RESEARCH JONES 2975 3. RESEARCH ADAMS 1100 4. RESEARCH SMITH 800 5. SALES BLAKE 2850 1. SALES ALLEN 1600 2. SALES TURNER 1500 3. SALES WARD 1250 4. SALES MARTIN 1250 4. SALES JAMES 950 6. Copyright 2014, oracle and/or its affiliates. All rights reserved. |. SQL. Window Function select dname, ename, hiredate, sal , avg(sal) over (.)

9 Analytic function partition by order by hiredate partition by rows between 2 preceding and 2 following order by for ) mv_avg_sal function from emp e join dept d on = windowing order by dname, hiredate clause Copyright 2014, oracle and/or its affiliates. All rights reserved. |. SQL. Window Function deptno=10. Start of window . Current row partition by End of window . deptno=20. deptno=30. Copyright 2014, oracle and/or its affiliates. All rights reserved. |. SQL. Window Function moving average DNAME ENAME HIREDATE SAL MVG_AVG_SAL. -------------- ---------- --------- ---------- ----------- ACCOUNTING CLARK 09-JUN-81 2450 ACCOUNTING KING 17-NOV-81 5000 ACCOUNTING MILLER 23-JAN-82 1300 RESEARCH SMITH 17-DEC-80 800 RESEARCH JONES 02-APR-81 2975 RESEARCH FORD 03-DEC-81 3000 2175.

10 RESEARCH SCOTT 09-DEC-82 3000 RESEARCH ADAMS 12-JAN-83 1100 SALES ALLEN 20-FEB-81 1600 1900. SALES WARD 22-FEB-81 1250 1800. SALES BLAKE 01-MAY-81 2850 1690. SALES TURNER 08-SEP-81 1500 1560. SALES MARTIN 28-SEP-81 1250 SALES JAMES 03-DEC-81 950 Copyright 2014, oracle and/or its affiliates. All rights reserved. |. SQL Definitions Basics Query Retrieves data based on specific criteria DML Data Manipulation Language SQL used to create, update and delete data DDL Data Definition Language SQL to create, alter objects such as tables, views, indexes etc Copyright 2014, oracle and/or its affiliates. All rights reserved. | 26. Copyright 2014, oracle and/or its affiliates. All rights reserved.