Oracle Analytic Functions In Depth

  1. Home
  2. Oracle-database
  3. Oracle Analytic Functions In Depth

Tags: Oracle Database

Move your skills in SQL to another level by using Oracle Analytic functions + Advanced SQL, All the scripts are attached

Last updated 2022-01-10 | 4.4

- Use SQL with aggregation operators.
- SQL for Analysis and Reporting functions
- Group and aggregate data using the ROLLUP and CUBE operators

What you'll learn

Use SQL with aggregation operators.
SQL for Analysis and Reporting functions
Group and aggregate data using the ROLLUP and CUBE operators
The GROUPING function
Composite Columns and the concatenated Groupings
RANK & DENSE_RANK
PERCENT_RANK
CUME_DIST (cumulative distribution)
The LAG/LEAD Functions
LISTAGG function
Aggregate functions VS Analytic Part
Aggregate functions & windowing clause In-Depth
Moving Average and running totals
ROWNUM & ROW_NUMBER
Advanced Oracle SQL
And much more

* Requirements

* Familiarity with SQL
* Good working knowledge of the SQL language
* Familiarity with Oracle SQL Developer and SQL*Plus
* This course is not for beginners
* do not take this course if you have no experience in SQL
* Downloading oracle database and this will be covered in details on Windows OS.

Description

  • Use SQL with aggregation operators.
  • SQL for Analysis and Reporting functions
  • Group and aggregate data using the ROLLUP and CUBE operators
  • The GROUPING function
  • Composite Columns and the concatenated Groupings
  • RANK & DENSE_RANK
  • PERCENT_RANK
  • CUME_DIST (cumulative distribution)
  • The LAG/LEAD Functions
  • LISTAGG function
  • Aggregate functions VS Analytic Part
  • Aggregate functions & windowing clause In-Depth
  • Moving Average and running totals
  • ROWNUM & ROW_NUMBER
  • Advanced Oracle SQL
  • And much more

Course content

5 sections • 85 lectures

Course Preparation 6 lectures

Chapter Summary Preview 02:04

Very Important

Oracle 12c /18c Architecture Preview 05:02

Review some Basic commands in 12c / 18c Preview 06:09

Create new pluggable database Preview 04:00

Create the HR Schema Preview 07:24

Navigate the HR Schema Preview 04:25

Grouping and Aggregating Data Using SQL 22 lectures

Chapter Summary Preview 01:01

Introduction Preview 02:46

Group Functions and Group by part 1 Preview 07:40

Group Functions and Group by part 2 Preview 06:39

Group Functions and NULLS Preview 03:16

Rollup Part 1 Preview 10:28

Rollup Part 2 Preview 07:50

Rollup Part 3 Preview 06:22

Rollup Part 4 Preview 09:01

Cube Part 1 Preview 03:55

Cube Part 2 Preview 07:21

Cube Part 3 Preview 05:14

GROUPING Function Part 1 Preview 07:38

GROUPING Function Part 2 Preview 04:49

GROUPING Function Part 3 Preview 07:19

GROUPING Function Part 4 Preview 04:43

The GROUPING_ID function Preview 07:31

GROUPING SETS Part 1 Preview 09:32

GROUPING SETS Part 2 Preview 03:29

Composite Columns Preview 06:18

Concatenated Groupings Preview 04:23

Solving one task in 4 methods Preview 06:22

Analyzing and Reporting Data Using SQL 32 lectures

RANK & DENSE_RANK Part 1 Preview 12:25

RANK & DENSE_RANK Part 2 Preview 04:22

RANK & DENSE_RANK Part 3 Preview 08:49

RANK & DENSE_RANK Part 4 Preview 05:40

RANK & DENSE_RANK ( Aggregate Syntax ) Preview 07:27

PERCENT_RANK Part 1 Preview 08:00

PERCENT_RANK Part 2 Preview 05:01

PERCENT_RANK Part 3 ( Aggregate Syntax ) Preview 10:05

CUME_DIST (cumulative distribution) Part 1 Preview 08:34

CUME_DIST (cumulative distribution) Part 2 Preview 05:06

LISTAGG function Part 1 Preview 04:55

LISTAGG function Part 2 Preview 07:11

NTILE function Preview 06:07

ROWNUM & ROW_NUMBER Part 1 Preview 06:27

ROWNUM & ROW_NUMBER Part 2 Preview 04:13

ROWNUM & ROW_NUMBER Part 3 Preview 07:30

LAG & LEAD Functions Part 1 Preview 06:44

LAG & LEAD Functions Part 2 Preview 08:27

Aggregate functions VS Analytic Part 1 ( Very Important) Preview 07:16

Aggregate functions VS Analytic Part 2 ( Very Important) Preview 11:01

Aggregate functions & windowing clause 1 Preview 08:55

Aggregate functions & windowing clause 2 Preview 07:11

Aggregate functions & windowing clause 3 Preview 07:18

Aggregate functions & windowing clause 4 Preview 09:45

Aggregate functions & windowing clause 5 Preview 08:34

Aggregate functions & windowing clause 6 Preview 03:06

Aggregate functions & windowing clause 7 Preview 05:23

Aggregate functions & windowing clause 8 Preview 05:04

Aggregate functions & windowing clause 9 Preview 06:43

first_value & last_value PART 1 Preview 06:43

first_value & last_value PART 2 Preview 03:29

first_value & last_value PART 3 Preview 03:55

Advanced Oracle SQL 10 lectures

DBMS_RANDOM part 1 Preview 06:08

DBMS_RANDOM part 2 Preview 05:09

Using SELECT statement to create dynamic scripts Preview 06:34

Hierarchical Queries Part 1 Preview 03:45

Hierarchical Queries Part 2 Preview 06:50

Hierarchical Queries Part 3 Preview 07:35

Hierarchical Queries Part 4 Preview 08:46

Hierarchical Queries Part 5 Preview 07:15

DML logs using package dbms_errlog Preview 08:28

create matrix report using PIVOT Preview 06:06

Bonus section- the full installation info 15 lectures

Course Setup Notes / 4 Options Preview 04:32

Option 2- creating the HR Schema Preview 07:34

Option 3- Part 1 : Oracle Database 12c Architecture Preview 05:02

Option 3- Part 2 : Downloading Oracle DB 12c Preview 03:42

Option 3- Part 3 : Installing Oracle DB 12c Preview 09:16

Option 3- Part 4 : Connecting SYS using SQL plus & SQL Developer Preview 10:39

Option 3- Part 5: Unlock HR Account Part 1 Preview 04:30

Option 3- Part 6 : Unlock HR Account Part 2 Preview 11:49

Option 3- Part 7 : ORA-01033 oracle initialization or shutdown in progress 12c Preview 03:33

Option 4- Installing Oracle 11g express Edition + Unlock HR Preview 10:21

HR Schema in this Course Preview 04:34

Navigate HR schema Preview 07:05

oracle 18c express edition part 1 (download and install ) Preview 08:59

...

oracle 18c express edition part 2 (unlock hr ) Preview 11:32

...

oracle 18c express edition part 3 ( sql developer ) Preview 06:46

...