SQL advanced in Oracle: Difference between revisions
Jump to navigation
Jump to search
Lsokolowski1 (talk | contribs) |
Lsokolowski1 (talk | contribs) |
||
| Line 783: | Line 783: | ||
'''Exercise 1''' | '''Exercise 1''' | ||
* Count median of salaries by manager | * Count median of salaries by manager | ||
** only for people from departments 10 and | ** only for people from departments 10 and 30 | ||
** sort it by hierarchy and id | ** sort it by hierarchy and id | ||
<pre> | <pre> | ||
Latest revision as of 14:14, 19 December 2023
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.
Copyright Notice
Copyright © 2004-2023 by NobleProg Limited All rights reserved.
This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise.
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
--- https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Oracle-and-Standard-SQL.html#GUID-330DEBBB-006E-4B35-A516-5C0AEFFE06B9
Recap of basics
- Connect with "sys" user
- Create db for exercises
- Create mike_recap.sql worksheet
- Paste this script from here: SQL_BASIC
- Discussion
Connection for 'sys' user
Connection for 'mike' user
Do this one only after "DDL setup" from below
Unlocking HR example with sys user
- From command line cli or via sql developer
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;
DDL part
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);
*/
DDL con't examples
--- https://livesql.oracle.com
----- https://livesql.oracle.com/apex/livesql/file/tutorial_D39T3OXOCOQ3WK9EWZ5JTJA.html
--triggers, indexes, etc
Examples
---- 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
Escape in Like
--- Escape string in LIKE clause
SELECT
dname, loc
FROM
dept
WHERE
loc LIKE '%\_YORK' ESCAPE '\'
;
Conditional expressions
CASE expression
- Example
---- 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
- 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
-- 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 ----------------------------------------------------------------------
DECODE expression
- Example
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
- Exercise - make this Prima Aprilis report
- differentiate concatenated answers accordingly to 'deptno'
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 ------------------------------------
JOIN the Party and make new Union
Gathering data from many tables
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
-- 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 -----------------------------------------------------------------------------------------
Aggregation
- Querying data grouped before it's filtered
- GROUP BY
- Oracle supports aggregation with clauses and built-in functions
---- https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Aggregate-Functions.html#GUID-62BE676B-AF18-4E63-BD14-25206FEA0848
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 2n 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)
------ 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
;
- 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.
-- 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 -------------------------------------------------------
Subqueries
- Nested SELECT clause
- Useful for example in improving aggregation results or with correlated data
---- 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
;
Subq Exercises
Hierarchical Query
---- 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";
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
Organogram ID MGR JOB ------------- ------ ------ ---------- CARNEGIE 7788 7566 ANALYST FORD 7876 7788 CLERK TOOSK 7902 7566 ANALYST THATCHER 7369 7902 CLERK
2. Solve Exercise nr 41 from here: SQL_Programming
- Use Hierarchical Query
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
Regular Expressions
---- 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'
Casting Data Types
---- https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj33562.html
Functions
- Oracle db has plenty of built in functions
- Allows also to create user-define functions
---- https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Functions.html#GUID-D079EFD3-C683-441F-977E-2C9503089982
Date and time functions
- SQL_Programming#Dealing_with_date_and_time_in_Oracle.2A
- List from docs
---- https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Single-Row-Functions.html#GUID-5652DBC2-41C7-4F07-BEDD-DAF620E35F3C
Analytic Functions
Example 1
- Find the 2 highest paid employees in each department
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
-- 6 rows ----------------------- DEPTNO ENAME SAL ----------------------- 10 BUSH 5000 10 MERKEL 2450 20 CARNEGIE 3000 20 TOOSK 3000 30 BLAIR 2850 30 BAROSSO 1600 -----------------------
Exercise 1
- Count median of salaries by manager
- only for people from departments 10 and 30
- sort it by hierarchy and id
-- 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 -------------------------------------
Example 2
------ 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;
Example 3
- Ten-bucket histogram on the sal column
- Employees with sal >= to the max value are assigned to the overflow bucket, 11
SELECT id, ename, sal,
WIDTH_BUCKET(sal, 500, 3500, 10) "Sal Group"
FROM emp
ORDER BY 4;
-- 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 ------------------------------------
Example 4
---- 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;
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
- For each paper and date, after the 1st of Apr but before the 6th (both inclusive):
-- 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 -------------------------------------------
- WIDTH_BUCKET histogram
- For prices of papers calculate ten-bucket report
- sort it by the bucket
- use range of [10, 220)
- use regular expression to filter papers between 1-4
- For prices of papers calculate ten-bucket report
-- 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 (...) ---------------------------------------------
- REAL issue from City
/*
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'
*/
-- 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
- 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
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;
Exercise - homework: try to solve previous issue (last 5 days stock prices) with 'row pattern matching' method (-:
