View source for SQL advanced in Oracle

[[Category:SQL]]

<!--
<slideshow style="nobleprog" headingmark="⌘" incmark="…" scaled="true" font="Trebuchet MS" >
;title: Presentation Title
;author: Your Name
</slideshow>
-->

== SQL advanced in Oracle Training Materials ==

 IMPORTANT NOTE for trainers only: 
 Please DO NOT change these materials, especially the EXERCISES, without direct permission from: GRZEGORZ PRUSZCZYNSKI or LUKASZ SOKOLOWSKI.

{{Can I use your material}}

<!--
In direct cooperation with:

 [[File:UMIOLogoHoriz.png|600px]] -->

<!-- TODO: hidden for now, was necessary only in bespoke course for Maastricht Uni
== AGENDA ==
<pre>
SQL advanced in Oracle (1-2 day(s) course)

General Introduction
- Quick recap of basics
- High level comparison between standard SQL and Oracle SQL (Teradata => Oracle)
- Query Structures & Most commonly used queries

Key Criteria’s to be used in query
- Built in functions (filtering and transforming data)
- Date and time functions and their date formats
- Advanced clauses
- Regular expressions
- Hierarchical queries

Advanced Grouping
- Aggregating methods and functions

Subqueries
- Selects in a row, column, table
- Correlated subqueries
- Comparison with joins

Creating and editing tables (DDL)
- Difference from writing procedures
</pre>
-->

== Standard SQL vs Oracle SQL ==
* Oracle was the first company which implemented SQL standard in commercial db server
* Oracle db supports the whole core of SQL standards
* All additional parts of the official standards are mostly supported by Oracle's own inventions
* More here 
<syntaxhighlight lang="sql">
--- https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Oracle-and-Standard-SQL.html#GUID-330DEBBB-006E-4B35-A516-5C0AEFFE06B9
</syntaxhighlight>

== Recap of basics ==
* Connect with "sys" user
** Create db for exercises
* Create '''mike_recap.sql''' worksheet
** Paste this script from here: [[SQL_Programming#Recap_of_Basics.2C_Basic_Cheat_Sheet_.28Oracle.29 | SQL_BASIC]] 
* Discussion

=== Connection for 'sys' user ===
[[File:SysOracleSetup.png|600px]]

=== Connection for 'mike' user ===
Do this one '''only''' after '''[[SQL_advanced_in_Oracle#DDL_part|"DDL setup"]]''' from below 
[[File:SqlOraMike.png|600px]]

=== Unlocking HR example with sys user ===
* From command line cli or via '''sql developer'''
<pre>
DB SETUP:

connect sys/asdf@localhost:1521/XEPDB1 as SYSDBA

ALTER USER HR IDENTIFIED BY asdf ACCOUNT UNLOCK;

/* Before executing fix all the necessary paths in the script 'hr_main.sql' */

@?\DEMO\SCHEMA\HUMAN_RESOURCES\HR_MAIN.SQL;
(or this way @\D:\app\lsokolowski\product\18.0.0\dbhomeXE\demo\schema\human_resources\hr_main.sql )

connect hr/asdf@localhost:1521/XEPDB1;

show user;  
</pre>

== DDL part ==
<syntaxhighlight lang="sql">
DROP USER mike CASCADE;

CREATE USER mike IDENTIFIED BY mike;
ALTER USER mike DEFAULT TABLESPACE system
              QUOTA UNLIMITED ON system;
ALTER USER mike TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION, CREATE VIEW, ALTER SESSION, CREATE SEQUENCE TO mike;
GRANT CREATE SYNONYM, CREATE DATABASE LINK, RESOURCE TO mike;


CONNECT mike/mike
ALTER SESSION SET NLS_LANGUAGE=English;
ALTER SESSION SET NLS_TERRITORY=Poland;



DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE SALGRADE;


CREATE TABLE DEPT (
 DEPTNO              NUMBER(2) NOT NULL,
 DNAME               VARCHAR(14),
 LOC                 VARCHAR(13),
 CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO));

/*
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');
COMMIT;
*/

INSERT INTO dept(DEPTNO, DNAME, LOC)
VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept(DEPTNO, DNAME, LOC)
VALUES (20, 'RESEARCH', 'LONDON');
INSERT INTO dept(DEPTNO, DNAME, LOC)
VALUES (30, 'SALES', 'PARIS');
INSERT INTO dept(DEPTNO, DNAME, LOC)
VALUES (40, 'OPERATIONS', 'BERLIN');


CREATE TABLE EMP (
 ID               NUMBER(4) NOT NULL,
 ENAME               VARCHAR(10),
 JOB                 VARCHAR(9),
 MGR                 NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (ID),
 HIREDATE            DATE,
 SAL                 NUMBER(7,2),
 COMM                NUMBER(7,2),
 DEPTNO              NUMBER(2) NOT NULL,
 CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),
 CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (ID));

INSERT INTO emp   (ID, ENAME, JOB, HIREDATE, SAL, DEPTNO)
 VALUES   (7839, 'BUSH', 'PRESIDENT', TO_DATE('11/17/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 5000, 10);
INSERT INTO emp    (ID, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
 VALUES    (7566, 'PUTIN', 'MANAGER', 7839, TO_DATE('04/02/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2975, 20);
INSERT INTO emp   (ID, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
 VALUES   (7788, 'CARNEGIE', 'ANALYST', 7566, TO_DATE('12/09/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3000, 20);
 INSERT INTO emp   (ID, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
 VALUES   (7876, 'FORD', 'CLERK', 7788, TO_DATE('01/12/1983 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1100, 20);
INSERT INTO emp   (ID, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
 VALUES   (7902, 'TOOSK', 'ANALYST', 7566, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3000, 20);
INSERT INTO emp    (ID, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
 VALUES    (7369, 'THATCHER', 'CLERK', 7902, TO_DATE('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 800, 20);
INSERT INTO emp   (ID, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
 VALUES   (7698, 'BLAIR', 'MANAGER', 7839, TO_DATE('05/01/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2850, 30);
INSERT INTO emp    (ID, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
 VALUES    (7499, 'BAROSSO', 'SALESMAN', 7698, TO_DATE('02/20/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1600, 300, 30);
INSERT INTO emp    (ID, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
 VALUES    (7521, 'WALTON', 'SALESMAN', 7698, TO_DATE('02/22/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1250, 500, 30);
INSERT INTO emp   (ID, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
 VALUES   (7654, 'CHIRACK', 'SALESMAN', 7698, TO_DATE('09/28/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1250, 1400, 30);
INSERT INTO emp   (ID, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
 VALUES   (7844, 'GATES', 'SALESMAN', 7698, TO_DATE('09/08/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1500, 0, 30);
INSERT INTO emp   (ID, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
 VALUES   (7900, 'BUFFETT', 'CLERK', 7698, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 950, 30);
INSERT INTO emp   (ID, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
 VALUES   (7782, 'MERKEL', 'MANAGER', 7839, TO_DATE('06/09/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2450, 10);
INSERT INTO emp   (ID, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
 VALUES   (7934, 'ELISON', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1300, 10);
COMMIT;

/* Shorter way, just for the comparison's sake
INSERT INTO EMP VALUES (7839,'BUSH','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAIR','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'MERKEL','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'PUTIN','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'CHIRACK','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES (7499,'BAROSSO','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES (7844,'GATES','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES (7900,'BUFFETT','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WALTON','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES (7902,'TOOSK','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'THATCHER','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP VALUES (7788,'CARNEGIE','ANALYST',7566,'1982-12-09',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'FORD','CLERK',7788,'1983-01-12',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'ELISON','CLERK',7782,'1982-01-23',1300,NULL,10);
*/


CREATE TABLE SALGRADE (
 GRADE               NUMBER,
 LOSAL               NUMBER,
 HISAL               NUMBER);

INSERT INTO salgrade (GRADE, LOSAL, HISAL)
VALUES (1, 700, 1200);
INSERT INTO salgrade (GRADE, LOSAL, HISAL)
VALUES (2, 1201, 1400);
INSERT INTO salgrade(GRADE, LOSAL, HISAL)
VALUES (3, 1401, 2000);
INSERT INTO salgrade (GRADE, LOSAL, HISAL)
VALUES (4, 2001, 3000);
INSERT INTO salgrade(GRADE, LOSAL, HISAL)
VALUES (5, 3001, 9999);
COMMIT;


/* Shorter way, just for the comparison's sake
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
*/
</syntaxhighlight>

=== DDL con't examples ===
<syntaxhighlight lang="sql">
--- https://livesql.oracle.com
----- https://livesql.oracle.com/apex/livesql/file/tutorial_D39T3OXOCOQ3WK9EWZ5JTJA.html

--triggers, indexes, etc
</syntaxhighlight>

== Examples ==
<syntaxhighlight lang="sql">
---- Links for related documentation
-- https://docs.oracle.com/en/database/oracle/oracle-database/19/development.html
-- https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/index.html
-- https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlqr/index.html
</syntaxhighlight>


=== Escape in Like ===
<syntaxhighlight lang="sql">
--- Escape string in LIKE clause
SELECT
    dname, loc
FROM
    dept
WHERE
    loc LIKE '%\_YORK' ESCAPE '\'
;
</syntaxhighlight>


=== Conditional expressions ===
'''CASE''' expression
* Example
<syntaxhighlight lang="sql"> 
---- Case examples
-- Simple vs Searched 
SELECT DISTINCT job, sal,
  ---- Simple - same condition
  (CASE grade 
    WHEN 5 THEN 'Bossy'
    WHEN 4 THEN 'CEO monies'
    ELSE 'Peanuts' END) AS How_Much,
  ---- Searched - multiple conditions
  (CASE
    WHEN sal BETWEEN 500 AND 1200 THEN 'Rice'
    WHEN ((sal BETWEEN 1250 AND 1800) AND comm IS NULL ) THEN 'Beans'
    ELSE 'Coconuts' END) AS "Who's rich?"
  FROM emp JOIN salgrade
    ON sal BETWEEN losal AND hisal
  ORDER BY 2;
---------------------------------
---- https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm
</syntaxhighlight>
* Exercise - Calculate report per job, like below
** summarize salary bigger than 2000, otherwise put 0
** average of commissions lower than 1000, otherwise 0
** how many people
<pre>
-- 5 rows
----------------------------------------------------------------------
Job        Summarized Salary    Average Commission    Number of people
----------------------------------------------------------------------
ANALYST	   6000                 0                     2
CLERK	   0                    0                     4
SALESMAN   0	                200                   4
MANAGER	   8275                 0                     3
PRESIDENT  5000                 0                     1
----------------------------------------------------------------------
</pre>
<!-- Solution example
SELECT
  job "Job",
  sum(CASE WHEN e.sal > 2000 THEN e.sal ELSE 0 END) "Summarised Salary",
  avg(CASE WHEN e.comm IS NOT NULL and e.comm < 1000 THEN e.comm ELSE 0 END) "Average Commission",
  count(e.deptno) "Number of people"
  FROM emp e
  GROUP BY job;
-->
'''DECODE''' expression
* Example
<syntaxhighlight lang="sql"> 
SELECT id, ename,
  DECODE (grade, 1, 'Basic',
                 2, 'Common',
                 3, 'Medium',
                 4, 'High',
                 5, 'Extreme',
                    'Sky') "Monies"
  FROM emp JOIN salgrade
    ON sal BETWEEN losal AND hisal
  ORDER BY grade DESC
;
--------------------------------
---- https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm 
</syntaxhighlight>
* Exercise - make this ''Prima Aprilis'' report
** differentiate concatenated answers accordingly to 'deptno'
<pre>
Login        Descr
------------------------------------
BAROSSO	     Lovely Food
BLAIR	     Lovely Food
BUFFETT	     Lovely Food
BUSH	     ACCOUNTING Cheater
CARNEGIE     Posh ladie from LONDON
CHIRACK      Lovely Food
ELISON       ACCOUNTING Cheater
FORD         Posh ladie from LONDON
GATES        Lovely Food
MERKEL       ACCOUNTING Cheater
PUTIN        Posh ladie from LONDON
THATCHER     Posh ladie from LONDON
TOOSK        Posh ladie from LONDON
WALTON       Lovely Food
------------------------------------
</pre>
<!-- Solution example
SELECT ename,
  DECODE (e.deptno, 10, dname || ' Cheater',
                  20, 'Posh ladie from ' || loc,
                  30, 'Lovely Food',
                  40, 'Empty Department',
                      'Marsians') "Descr"
  FROM emp e JOIN dept d
    ON e.deptno = d.deptno
  ORDER BY 1;
-->

=== JOIN the Party and make new Union ===
Gathering data from many tables
* [[SQL_Programming#Selecting_data_from_many_tables | JOINS]]

Exercise - report such story (-;
* take only those complaining subordinates, who have boss earning less than 3000
* boss should complain only about his inferior earning more than 1500
<pre>
-- 14 rows
-----------------------------------------------------------------------------------------
Blamers for blamed, The Histerical History
-----------------------------------------------------------------------------------------
BAROSSO from SALES in PARIS blames his boss BLAIR for his very low salary grade 3
BUFFETT from SALES in PARIS blames his boss BLAIR for his very low salary grade 1
Boss BLAIR blames his slave BAROSSO from SALES in PARIS for his very lazy attitude
Boss BUSH blames his slave BLAIR from SALES in PARIS for his very lazy attitude
Boss BUSH blames his slave MERKEL from ACCOUNTING in NEW YORK for his very lazy attitude
Boss BUSH blames his slave PUTIN from RESEARCH in LONDON for his very lazy attitude
Boss PUTIN blames his slave CARNEGIE from RESEARCH in LONDON for his very lazy attitude
Boss PUTIN blames his slave TOOSK from RESEARCH in LONDON for his very lazy attitude
CARNEGIE from RESEARCH in LONDON blames his boss PUTIN for his very low salary grade 4
CHIRACK from SALES in PARIS blames his boss BLAIR for his very low salary grade 2
ELISON from ACCOUNTING in NEW YORK blames his boss MERKEL for his very low salary grade 2
GATES from SALES in PARIS blames his boss BLAIR for his very low salary grade 3
TOOSK from RESEARCH in LONDON blames his boss PUTIN for his very low salary grade 4
WALTON from SALES in PARIS blames his boss BLAIR for his very low salary grade 2
-----------------------------------------------------------------------------------------
</pre>
<!-- Solution example
select 
  e.ename || ' from ' || dname || ' in ' || loc || ' blames his boss ' || b.ename || ' for his very low salary grade ' || grade
  AS "Blamers for blamed and blamed for blamers, The Histerical History"
  from emp e join emp b
    on e.mgr = b.id
    join dept d
      on e.deptno = d.deptno
      join salgrade
        on e.sal between losal and hisal
  where
    b.sal < 3000

union

select
  'Boss ' || b.ename || ' blames his slave ' || e.ename || ' from ' || dname || ' in ' || loc || ' for his very lazy attitude'
  from emp e join emp b
    on e.mgr = b.id
    join dept d
      on e.deptno = d.deptno
      join salgrade
        on e.sal between losal and hisal
  where
    e.sal > 1500
--  order by 1 
;
-->

=== Aggregation ===
* Querying data grouped before it's filtered 
* [[SQL_Programming#Aggregate_Functions_.28GROUP_BY.29 | GROUP BY]]
* Oracle supports aggregation with clauses and built-in functions
<syntaxhighlight lang="sql">
---- https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Aggregate-Functions.html#GUID-62BE676B-AF18-4E63-BD14-25206FEA0848
</syntaxhighlight>

==== GROUPING, ROLLUP, CUBE, GROUPING SETS ====
* '''GROUPING''' function distinguishes '''superaggregate''' rows from '''regular grouped''' rows
* '''ROLLUP''' and '''CUBE''' produce superaggregate rows - set of all values is represented by null
** ''ROLLUP(n)'' - gives '''n+1''' groupings; Usage examples: with SUM(), COUNT() gives '''subtotal''' values
** ''CUBE(n)'' - gives '''2<sup>n</sup>''' groupings; Usage example: '''cross-tabulation''' values
* '''GROUPING SETS''' - multiple groupings of data, efficient aggregation by '''pruning''' the aggregates we don't need; ''UNION ALL''
** '''composite columns''' - within parentheses, db treats them as a unit while computing ROLLUP or CUBE
** '''concatenated grouping sets''' - separate multiple grouping sets, ROLLUP, and CUBE operations with commas
*** db combines them into a single GROUP BY clause (cross-product of groupings from each grouping set)
<syntaxhighlight lang="sql">
------ EXAMPLES - GROUPING, ROLLUP, CUBE
---- Exercises
-- 1. Show also empty department, does it give the true answer?
-- 2. Change order of ROLLUP arguments, what is the difference? (Hide empty dept first)
-- 3. Replace ROLLUP with CUBE and compare the results - what can you see?
----
SELECT
    DECODE(GROUPING(dname), 1, 'ALL DEPARTMENTS', dname)
      AS department,
    DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
    COUNT(*) "Total Empl",
    round(AVG(sal) * 12) AS "Average Sal"
  FROM emp e, dept d
  WHERE d.deptno = e.deptno
  GROUP BY ROLLUP (dname, job)
  ORDER BY department, job;
-----------------------------------------
------ EXAMPLE - GROUPING SETS
---- Exercises
-- 1. Compare it with simple grouping (undo the comment)
-- 2. Wrap '(deptno, job)' set with CUBE and later ROLLUP 
-- 3. Improve it - group also by 'grade' and count it
----
select deptno, job, sum(sal)
  from emp
  where deptno in (20, 30)
  -- group by deptno, job
  group by GROUPING SETS( 
      (deptno, job), 
      (deptno), 
      (job) ) 
  order by deptno, job
;
</syntaxhighlight>
* More exercises
*# Solve '''Ex.14''' from here: [[SQL_Exercises]]. Do it without ''set operator'' and no ''subqueries'' allowed (-:
*# Join all 3 tables and roll it up - aggregate with ''dname'' and ''grade''. Sort it by ''Department'' and ''Total_Empl''.
<pre>
-- 15 rows
-------------------------------------------------------
Department       Grade        Total_Empl    Average_Sal
-------------------------------------------------------
ACCOUNTING	 4	      1	            29400
ACCOUNTING	 2	      1	            15600
ACCOUNTING	 5	      1	            60000
ACCOUNTING	 All GRADES   3	            35000
ALL DEPARTMENTS	 All GRADES   14	    24964
OPERATIONS	 (null)	      0	            (null)
OPERATIONS	 All GRADES   0	            (null)
RESEARCH	 1	      2	            12000
RESEARCH	 4	      3	            35900
RESEARCH	 All GRADES   5	            26340
SALES	         4	      1	            34200
SALES	         1	      1	            11400
SALES	         3	      2	            18600
SALES	         2	      2	            15000
SALES	         All GRADES   6	            18800
-------------------------------------------------------
</pre>
<!-- Solutions examples
select deptno, job, grade, sum(sal), count(*)
  from emp, salgrade
  where deptno in (20, 30)
  and sal between losal and hisal
  -- group by deptno, job
  group by GROUPING SETS( 
      --CUBE (deptno, job, grade), 
      ROLLUP (deptno, job, grade),
      (deptno, grade), 
      (job, grade) ) 
  order by deptno, job, grade
;
-- ex14
SELECT
    DECODE(GROUPING(deptno), 1, '* ALL DEPTS *', deptno) AS DeptNo,
    DECODE(GROUPING(ename), 1, '* ALL EMPLOYEES *', ename) AS Empl_Name,
    sum(sal) as "SALARIES"
  FROM emp
  WHERE deptno IN (10, 20)
  GROUP BY ROLLUP (deptno, ename)
  ORDER BY 1, 2
;
-- ex2
SELECT
    DECODE(GROUPING(dname), 1, 'ALL DEPARTMENTS', dname) AS Department,
    DECODE(GROUPING(grade), 1, 'All GRADES', grade)      AS Grade,
    COUNT(e.deptno)                                      AS "Total Empl",
    round(AVG(sal) * 12)                                 AS "Average Sal"
  FROM emp e RIGHT JOIN dept d
    ON d.deptno = e.deptno
    LEFT JOIN salgrade s
      ON sal between losal and hisal 
  GROUP BY ROLLUP (dname, grade)
--  GROUP BY CUBE (dname, grade)
  ORDER BY Department, 3;
-->

=== Subqueries ===
* Nested SELECT clause
* Useful for example in improving aggregation results or with correlated data
<syntaxhighlight lang="sql">
---- Sub-queries 
--- https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Using-Subqueries.html#GUID-53A705B6-0358-4E2B-92ED-A83DE83DFD20
-- Most common places in SELECT clause, where the sub-clause can appear
SELECT
    SUBQ.cid,
    cname,
    ( select  'regid' from dual ) as "REGID",
    ( select count("REGION_ID") from HR."COUNTRIES" where region_id = 3 ) as "HowMany"
FROM
    (select
        "COUNTRY_ID" as cid,
        "COUNTRY_NAME" as cname,
        "REGION_ID" as regid
      from HR."COUNTRIES"
      where
        "COUNTRY_NAME" like '%a'
        and
        REGION_ID = 3
    ) SUBQ
WHERE
    cid LIKE '%N'
    AND
    (select
        avg("REGION_ID")
      from
        HR."COUNTRIES"
      where
        SUBQ.regid = "REGION_ID"
    ) < 10
;
</syntaxhighlight>

==== Subq Exercises ====
[[SQL_Programming#Exercises_7|Exercises]]

=== Hierarchical Query ===
<syntaxhighlight lang="sql">
---- Hierarchical queries examples
SELECT
    id
    , ename
    , mgr
--    , LEVEL
   FROM emp
--   START WITH id = 7839
   CONNECT BY PRIOR id = mgr
--   ORDER SIBLINGS BY ename
;

--- CONNECT_BY_ROOT examples
--1 name of each employee in department 10, 
--1 each manager at the highest level above that employee in the hierarchy,
--1 the number of levels between manager and employee,
--1 and the path between the two
SELECT ename "Employee", CONNECT_BY_ROOT ename "Manager",
   LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(ename, '/') "Path"
   FROM emp
   WHERE LEVEL > 1 and deptno = 10
   CONNECT BY PRIOR id = mgr
   ORDER BY "Employee", "Manager", "Pathlen", "Path";

--2 returns the total salary of each employee in department 10 and all employees above that employee in the hierarchy
SELECT name, SUM(sal) "Total_Salary" FROM (
   SELECT CONNECT_BY_ROOT ename as name, sal
      FROM emp
      WHERE deptno = 10
      CONNECT BY PRIOR id = mgr)
      GROUP BY name
   ORDER BY name, "Total_Salary";
</syntaxhighlight>

==== HQ Exercises ====
1. Show all employees in hierarchical order. Begin with the employee whose job is '''Analyst'''.
* Filter it by '''Manager''' instead
* Select only the '''first two''' levels of the management hierarchy
<pre>
Organogram      ID      MGR     JOB
-------------  ------  ------  ----------
CARNEGIE         7788    7566  ANALYST
  FORD           7876    7788  CLERK
TOOSK            7902    7566  ANALYST
  THATCHER       7369    7902  CLERK
</pre>
<!-- Solution example
SELECT LPAD(' ',2*(LEVEL-1)) || ename Organogram, 
        id, mgr, job
    FROM emp
    START WITH job = 'ANALYST' -- 'MANAGER'
    CONNECT BY PRIOR id = mgr -- AND LEVEL <= 2
;
-->
2. Solve Exercise '''nr 41''' from here: [[SQL_Programming]]
* Use Hierarchical Query
<!-- Solution example
SELECT ename "Employee", hiredate "Emp hir", CONNECT_BY_ROOT ename "Manager", CONNECT_BY_ROOT hiredate "Man hir"
   FROM emp
   WHERE LEVEL = 2
    and hiredate < CONNECT_BY_ROOT hiredate
   CONNECT BY PRIOR id = mgr
   ORDER BY "Employee"
;
-->
3. Improve '''Blamers''' exercise with HQ - show bosses department ''name'' and ''location''
* "...his boss BLAIR from SALES in PARIS..."
* "Boss BUSH from ACCOUNTING in NEW YORK blames..."
* And then show only bosses who work in a different department than their subordinates 
<!-- Solution example
select 
  ename || ' from ' || dname || ' in ' || loc || ' blames his boss ' 
  || CONNECT_BY_ROOT ename || ' from ' || CONNECT_BY_ROOT dname || ' in ' 
  || CONNECT_BY_ROOT loc || ' for his very low salary grade ' || grade
  AS "Blamers for blamed and blamed for blamers, The Histerical History"
  from emp
    join dept d
      on emp.deptno = d.deptno
      join salgrade
        on emp.sal between losal and hisal
  where
    CONNECT_BY_ROOT sal < 3000
    and
    LEVEL = 2
    -- and CONNECT_BY_ROOT dname <> dname
  CONNECT BY PRIOR id = mgr

union

select
  'Boss ' || CONNECT_BY_ROOT ename || ' from ' || CONNECT_BY_ROOT dname || ' in '  || CONNECT_BY_ROOT loc 
  || ' blames his slave ' || ename || ' from ' || dname || ' in ' || loc || ' for his very lazy attitude'
  from emp
    join dept d
      on emp.deptno = d.deptno
--      join salgrade
--        on emp.sal between losal and hisal
  where
    emp.sal > 1500
    and
    LEVEL = 2
    -- and CONNECT_BY_ROOT dname <> dname
  CONNECT BY PRIOR id = mgr
;
-->

=== Regular Expressions ===
<syntaxhighlight lang="sql">
---- Regexp examples
SELECT
    *
FROM
    emp
---- Name starts with B or T
-- WHERE REGEXP_LIKE(ename, '^(B|T).*$')
---- Name contains double adjacent consonants 
-- WHERE REGEXP_LIKE (ename, '([bcdfghjklmnpqrstvwxz])\1', 'i')
ORDER BY
    ename
;
---- Exercises
--- Job ends with K or R
--- Manager's id contains double adjacent digit


---- REGEXP_INSTR , REGEXP_REPLACE , and REGEXP_SUBSTR
-- https://livesql.oracle.com/apex/livesql/docs/sqlrf/regexp_instr/find-location.html
-- https://livesql.oracle.com/apex/livesql/docs/sqlrf/regexp_replace/match-replace1.html


---- REGEXP_REPLACE examples
--- Replaces two or more spaces with one space
SELECT
    REGEXP_REPLACE('37-450      Stalowa Wola,       Podkarpackie,  Poland', '( ){2,}', ' ') "REGEXP_REPLACE Example"
FROM DUAL;

--- Converts 'camel case' to 'underscore':
WITH functions_names as (
    SELECT 'GetAllTheBookingForms' f FROM dual union all
    SELECT 'PrepareDelegatesDetails' f FROM dual union all
    SELECT 'HelperFunction' f FROM dual
)
SELECT
    f "Function name",
    lower( regexp_replace(f, '([A-Z0-9])', '_\1', 2) )
    as "FN after"
FROM functions_names;

---- Exercise
---* Convert the format of a hiredate from '2019-01-01' into '01.01.2019'
</syntaxhighlight>

=== Casting Data Types ===
<syntaxhighlight lang="sql">
---- https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj33562.html
</syntaxhighlight>

=== Functions ===
* Oracle db has plenty of built in functions
* Allows also to create user-define functions
<syntaxhighlight lang="sql">
---- https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Functions.html#GUID-D079EFD3-C683-441F-977E-2C9503089982
</syntaxhighlight>

==== Date and time functions ====
* [[SQL_Programming#Dealing_with_date_and_time_in_Oracle.2A]]
* List from docs
<syntaxhighlight lang="sql">
---- https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Single-Row-Functions.html#GUID-5652DBC2-41C7-4F07-BEDD-DAF620E35F3C
</syntaxhighlight>

<!-- TODO: improve this part
==== Partitions ====
<syntaxhighlight lang="sql"> 
---- https://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.htm 
---- https://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm
</syntaxhighlight>
-->
==== Analytic Functions ====
'''Example 1'''
* Find the 2 highest paid employees in each department
<syntaxhighlight lang="sql">
SELECT deptno, ename, sal
FROM
(
  SELECT
    deptno, ename, sal,
    ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal desc) rn
  FROM emp
)
WHERE rn <= 2
ORDER BY deptno, sal DESC;
-----------------------------
---- https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174
</syntaxhighlight>
<pre>
-- 6 rows
-----------------------
DEPTNO  ENAME     SAL
-----------------------
10	BUSH	  5000
10	MERKEL	  2450
20	CARNEGIE  3000
20	TOOSK	  3000
30	BLAIR	  2850
30	BAROSSO	  1600
-----------------------
</pre>

'''Exercise 1'''
* Count median of salaries by manager
** only for people from departments 10 and 20
** sort it by hierarchy and id 
<pre>
-- 9 rows
-------------------------------------
MGR     ID      SAL     Median_by_Mgr
-------------------------------------
7698    7499	1600	1250
7698    7521	1250	1250
7698    7654	1250	1250
7698    7844	1500	1250
7698    7900	950	1250
7782    7934	1300	1300
7839    7698	2850	2650
7839    7782	2450	2650
(null)  7839	5000	5000
-------------------------------------
</pre>
<!-- Solution example
SELECT mgr, id, sal,
  MEDIAN(sal) OVER (PARTITION BY mgr) "Median by Mgr"
  FROM emp
  WHERE deptno IN (10, 30)
  ORDER BY mgr, id;
-->

'''Example 2'''
<syntaxhighlight lang="sql">
------ LAG vs LEAD example - no need to use 'self-join'
--- For each Clerk find the salary of the employee hired just before
SELECT hiredate, ename, sal,
  LAG(sal, 1, 0) OVER (ORDER BY hiredate) AS prev_sal
  FROM emp
  WHERE job = 'CLERK'
  ORDER BY hiredate;
----------------------
--- For each employee in Department 30 find the hire date of the employee hired just after
SELECT hiredate, ename,
  LEAD(hiredate, 1) OVER (ORDER BY hiredate) AS "NextHired"
  FROM emp
  WHERE deptno = 30
  ORDER BY hiredate;
</syntaxhighlight>

'''Example 3'''
* Ten-bucket histogram on the ''sal'' column
** Employees with sal >= to the max value are assigned to the overflow bucket, 11
<syntaxhighlight lang="sql">
SELECT id, ename, sal,
  WIDTH_BUCKET(sal, 500, 3500, 10) "Sal Group"
  FROM emp
  ORDER BY 4;
</syntaxhighlight>
<pre>
-- 14 rows
------------------------------------
ID      ENAME       SAL    Sal Group
------------------------------------
7369	THATCHER    900    2
7900	BUFFETT     950    2
7654	CHIRACK     1250   3
7934	ELISON      1300   3
7521	WALTON      1250   3
7876	FORD        1100   3
7499	BAROSSO     1600   4
7844	GATES       1500   4
7782	MERKEL      2450   7
7698	BLAIR       2850   8
7788	CARNEGIE    3000   9
7566	PUTIN       2975   9
7902	TOOSK       3000   9
7839	BUSH        5000   11
------------------------------------
</pre>

'''Example 4'''
<syntaxhighlight lang="sql">
---- FIRST vs LAST example
/* Calculate:
- the minimum salary among the employees who make the lowest commission
- the maximum salary among the employees who make the highest commission
- for each employee within the department */
SELECT ename, deptno, sal,
  MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY comm)
    OVER (PARTITION BY deptno) "Worst",
  MAX(sal) KEEP (DENSE_RANK LAST ORDER BY comm)
    OVER (PARTITION BY deptno) "Best"
FROM emp
ORDER BY deptno, sal;
</syntaxhighlight>

== More exercises ==
Additional examples and exercises

=== City Examples ===

Use script '''citiPriceTbl.sql''' in ''mike'' session
* '''Median of prices'''
** For each paper and date, after the 1st of Apr but before the 6th (both inclusive):
*** count prices median
*** filter it by paper 5 and 6
*** roll it up
*** sort it with paper ID and date
<pre>
-- 15 rows
-------------------------------------------
Market_ID         PriceDate    Median_Price
-------------------------------------------
ALL Market IDs    All DATES    112,5
ISIN_5            21/04/01     220
ISIN_5            21/04/02     222
ISIN_5            21/04/03     229
ISIN_5            21/04/04     223
ISIN_5            21/04/05     215
ISIN_5            21/04/06     212
ISIN_5            All DATES    221
ISIN_6            21/04/01     11
ISIN_6            21/04/02     11
ISIN_6            21/04/03     12
ISIN_6            21/04/04     11
ISIN_6            21/04/05     13
ISIN_6            21/04/06     10
ISIN_6            All DATES    11
-------------------------------------------
</pre>
<!-- Solution example
SELECT
    DECODE(GROUPING(market_id), 1, 'ALL Market IDs', market_id) AS MarketID,
    DECODE(GROUPING(pdate), 1, 'All DATES', pdate)              AS "Date",
    MEDIAN(price) Median_Price --OVER (PARTITION BY market_id) "Median by MarketID"
  FROM price_table
  WHERE market_id IN ('ISIN_5', 'ISIN_6' ) and pdate between '21/04/01' and '21/04/06'
  GROUP BY ROLLUP (market_id, pdate)
--  GROUP BY CUBE (market_id, pdate)
  ORDER BY 1, 2;
-->
* '''WIDTH_BUCKET histogram'''
** For prices of papers calculate ten-bucket report
*** sort it by the bucket
*** use range of <nowiki>[10, 220)</nowiki>
*** use regular expression to filter papers between 1-4
<pre>
-- 40 rows
---------------------------------------------
MARKET_ID    PDATE       PRICE    Price Group
---------------------------------------------
ISIN_3       21/04/05    30       1
ISIN_2       21/04/01    30       1
ISIN_2       21/04/04    30       1
ISIN_4       21/04/01    14       1
(...)
---------------------------------------------
</pre>
<!-- Solution example
SELECT market_id, pdate, price,
WIDTH_BUCKET(price, 10, 220, 10) "Price Group"
FROM price_table
WHERE REGEXP_LIKE(market_id, '^.*[1-4]$')
ORDER BY 4;
-->
* '''REAL issue from City'''
<syntaxhighlight lang="sql">
/*
From a bigger table with stocks/bonds prices per day:
-- Get last 5 security valuations (this part is already done - PRICE_TABLE, but try to think how to manage that part of the problem in separate)
-- Check if the prices has changed in the last 5 days
---- Flag it with 'Y' if price didn't change, otherwise with 'N'
*/
</syntaxhighlight>
<pre>
-- comment only, don't show it       4/6/2021  4/7/2021  4/8/2021  4/9/2021  4/10/2021	

Market_ID    Security description    Date-4    Date-3    Date-2    Date-1    Date    IS_Stale_Price
---------    --------------------    ------    ------    ------    ------    ----    --------------
   ISIN_1	        Security1    48        52	 49        48        51      N
   ISIN_2	        Security2    33        33        33        33        33      Y
   ISIN_3	        Security3    31        34        32        34        33      N
   ISIN_4	        Security4    200       208       214       226       209     N
   ISIN_5	        Security5    212       212       212       212       212     Y
   ISIN_6	        Security6    10        10        11        14        10      N
   ISIN_7	        Security7    12        13        12        13        10      N
   ISIN_8	        Security8    73        74        73        69        71      N
</pre>
<!-- Solution example
-- First part (Get last 5 security valuations)
/*
Sort big table by date descending and filter it by 'sysdate - 4':
-------------------------------------
(...)
WHERE
    ---- safer solution (why?):
    extract(day from sysdate) -  extract(day from pdate)  between 0 and 4 
    and
    extract(month from sysdate) = extract(month from pdate)

    ---- shorter solution:
    sysdate - pdate between 0 and 4
    ---- or with known date instead:
    to_date('2021-04-12','YYYY-MM-DD') - pdate  between 0 and 4
ORDER BY pdate DESC
;
-------------------------------------
And then save the result as SQL VIEW and ask it with below query (-:
*/
-- 2nd part
select distinct 
    market_id
    , security_description_
    , (select price from price_table where market_id = pt.market_id and pdate = '21/04/06') as "Date4"
    , (select price from price_table where market_id = pt.market_id and pdate = '21/04/07') as "Date3"
    , (select price from price_table where market_id = pt.market_id and pdate = '21/04/08') as "Date2"
    , (select price from price_table where market_id = pt.market_id and pdate = '21/04/09') as "Date1"
    , (select price from price_table where market_id = pt.market_id and pdate = '21/04/10') as "Date0"
--    , ( CASE WHEN ( ("Date0" = "Date1") AND ("Date0" = "Date2") AND ("Date0" = "Date3") AND ("Date0" = "Date4") ) THEN 'Y' ELSE 'N' END ) /* this will not gonna work (why?) */
    , ( CASE WHEN ( ( (select price from price_table where market_id = pt.market_id and pdate = '21/04/10') = (select price from price_table where market_id = pt.market_id and pdate = '21/04/09')) 
    AND ((select price from price_table where market_id = pt.market_id and pdate = '21/04/10') = (select price from price_table where market_id = pt.market_id and pdate = '21/04/08')) 
    AND ((select price from price_table where market_id = pt.market_id and pdate = '21/04/10') = (select price from price_table where market_id = pt.market_id and pdate = '21/04/07')) 
    AND ((select price from price_table where market_id = pt.market_id and pdate = '21/04/10') = (select price from price_table where market_id = pt.market_id and pdate = '21/04/06')) ) 
    THEN 'Y' ELSE 'N' END ) as IS_Stale_Price
from price_table pt
--group by market_id
order by market_id
;
-->
* '''Row Pattern Matching'''
** Usually useful to query stock price data
** V-shape example - find all cases where stock prices dipped to a bottom price and then rose
** Let's do the v-shape for prices in Citi table
<syntaxhighlight lang="sql">
SELECT *
FROM price_table MATCH_RECOGNIZE (
     PARTITION BY market_id
     ORDER BY pdate
     MEASURES STRT.pdate AS start_pdate,
              LAST(DOWN.pdate) AS bottom_pdate,
              LAST(UP.pdate) AS end_pdate
     ONE ROW PER MATCH
     AFTER MATCH SKIP TO LAST UP
     PATTERN (STRT DOWN+ UP+)
     DEFINE
        DOWN AS DOWN.price < PREV(DOWN.price),
        UP AS UP.price > PREV(UP.price)
     ) MR
ORDER BY MR.market_id, MR.start_pdate;
</syntaxhighlight>

 Exercise - homework: try to solve previous issue (last 5 days stock prices) with 'row pattern matching' method (-:

== PL/SQL Fundamentals ==
[[PLSQL_Fundamentals|PL/SQL Fundamentals]]