Oracle Analytic Functions In-Depth & Advanced Oracle SQL

Learn to group and aggregate data in sql, review group by and group functions, and apply rollup, cube, grouping sets, composite columns, and concatenated groupings.

Refresh your understanding of oracle's group functions and the group by clause, comparing single row and multiple row functions and noting popular examples like average, sum, min, max, and count.

Explore group functions and the group by clause with practical examples of max, min, sum, avg, count, distinct, and null handling using nvl.

Master group functions and group by rules in Oracle SQL by aggregating salaries by department and job, applying having, order by, and clarifying alias usage and where restrictions.

Demonstrate group functions and null handling in Oracle SQL, showing that all group functions ignore nulls except count(*), and compare count, count(*), and count distinct using a test table.

Discover how rollup extends group by to produce subtotals and a grand total in Oracle SQL, with examples counting employees per department and summing salaries.

Explore how rollup enhances grouping by department and job, producing original counts, subtotals, and grand totals with minimal SQL and alternatives using unions.

Explore rollup across three columns—region, country, and city—by aggregating sums with group by, creating subtotals and grand totals from a sample info_sales table.

Explore roll up as an extension of group by that produces subtotals and grand totals across region, country, city, and shop, with n plus one totals and the right-to-left aggregation.

Explore the cube operator in the group by clause, compare it with rollup, and see how cube yields subtotals for all combinations and a grand total for cross tabular reports.

Explore rollup and cube on two columns (department id, job id) to generate subtotals and grand totals for salaries, and compare with union all as demonstrated.

Explore how Oracle analytic functions cube and rollup generate subtotals across region, country, and city, producing eight combinations for three columns and sixteen for four, including the grand total.

Learn how the Oracle grouping function differentiates nulls from rollup and cube subtotals in group by queries. See practical examples with department salaries and grand totals.

Explore how the grouping function distinguishes nulls from rollup or cube results, and format totals with decode and nvl for clear department and grand total summaries.

Explores advanced use of the grouping function with rollup and cube to compute subtotals and grand totals of salaries across department and job dimensions.

Explore the grouping function with three columns using rollup and cube, generating subtotals and grand totals for region, country, and city in oracle sql.

Explore the grouping_id function as a compact way to identify subtotal and grand total rows in group by queries, with examples using department salaries and region, country, city totals.

Master grouping sets in Oracle SQL to compute multiple groupings and subtotals in one pass, compare with rollup and cube, and optimize queries for grand totals.

Explore grouping sets in Oracle SQL to generate region, country, and city subtotals, compare bracketed versus separate sets, and understand cube usage and common grouping errors.

Discover composite columns in Oracle SQL, using rollup and cube to produce regional totals by region, country, and city, with aliases to simplify grouping.

Explore concatenated groupings using grouping sets to produce region totals and country-city totals, with cross products like region-country and region-city, summing amount from the info underscore sales table.

Explore four methods to compute sum(amount) across region, country, and city in info_sales, including union all, grouping sets, rollup, and cube.

Master rank and dense_rank functions in Oracle, ranking salaries with an order by clause, optional partitions. Learn the three stages of query processing and how to handle duplicates and nulls.

Learn how Oracle executes rank and dense_rank in three stages—join and where, rank calculation, and the final select order by—ensuring the ranking remains consistent despite the final ordering.

Explore how rank and dense_rank work in Oracle, using partition by to reset rankings by department or job, with order by and nulls last, across salaries and hire dates.

Explore three methods to find the highest salary per department in Oracle SQL: pairwise comparison with max, dense_rank with partition by department, and exists-based alternatives.

Apply aggregate syntax to rank and dense_rank in Oracle, distinguishing it from analytical syntax, with salary, department, and name-based examples that show where clauses and grouping influence results.

Percent_rank computes a value's share within a group using the rank function and an order by clause, ranking salaries in ascending order from 0 to 1 for easy top-percent filtering.

Explore percent_rank and rank over partition by department_id order by salary, with practical examples, including where clauses, calculating 0 to 1 ranges, and interpreting Alexander's 0.8 rank.

Explore the percent_rank function with aggregate syntax using a movies table example, showing how to compute vote percent and percent rank over ordered votes.

Explore the cume_dist function, a cumulative distribution with optional partition and mandatory order by, returning a 0–1 value as last rank divided by partition size.

Master the cumulative distribution (cume_dist) in Oracle SQL using the aggregate syntax with a mandatory order by, including partition and the last rank over total rows formula.

Explore the listagg function in Oracle, using the aggregate syntax to join employee first names by department with a delimiter, ordered by name and grouped by department.

Explore the LISTAGG function in Oracle SQL, comparing aggregate and analytic syntaxes with department and job examples. Distinguish distinct values and learn how to convert between syntax forms.

Explore ntile to divide a result set into a specified number of approximately equal groups, using an optional partition and a mandatory order by, demonstrated with student lists.

Learn the difference between rownum and row_number in Oracle, see how order by affects result ordering, and use a subquery to apply ordering before assigning rownum.

Explore top n queries with rownum and row_number, use fetch first two rows only with ties in 12c, and compare dense_rank to handle salary ties.

Discover rownum and the analytic function row_number, including how partition by department and order by salary control numbering. Compare row_number with dense_rank and see how ties are ignored.

Explore lag and lead analytic functions in Oracle SQL, learning to access previous and next rows with an over clause, including offset and default behavior.

Explore lag and lead functions in Oracle SQL, including offset and default values, using over clauses and partition by department id to compute previous and next hire dates.

Compare aggregate and analytic functions to see how they reduce or preserve rows. Explain how over, partition by, order by, and the windowing clause influence analytic queries.

Compare aggregate and analytic functions, explain over syntax with empty brackets and partition by, and illustrate per-employee and per-department averages and counts.

Master aggregate functions with the windowing clause, using over and partition by department to compute per-department average salary, and compare range versus rows in window calculations.

Explore Oracle analytic functions and the windowing clause, focusing on range and rows definitions, moving average, and the default unbounded preceding to current row, with examples.

Explore moving average with analytic functions in Oracle, comparing rows between unbounded preceding and current row versus ranges, and see how salary-based ordering affects results.

Learn how aggregate functions and windowing work in Oracle SQL, building a supermarket transactions table, computing per-row amounts, and using running totals with over clauses and primary key order.

Explore the windowing clause in oracle sql, including unbounded preceding, unbounded following, and current row, with range and rows between start and end points.

Explore aggregate functions and windowing in Oracle as you learn how sum(val) over order by val range between unbounded preceding and unbounded following yields a constant 22 for all rows.

Learn how Oracle computes window sums using range between one preceding and current row in order by val, and how equal values share the same calculation.

Explore Oracle SQL aggregate functions with the windowing clause by solving queries four to six using sum over rows between preceding and current row and between preceding and following.

Explore the windowing clause in Oracle SQL by computing a running salary total using range between unbounded preceding and current row, partitioned by department ID and ordered by employee ID.

Explore first_value and last_value analytic functions, their syntax, and how over and order by determine results; compare behavior with respect to nulls (respect or ignore) and practical examples.

Explore first_value and last_value with respect nulls and ignore nulls, showing how nulls affect order by and over clauses in Oracle SQL.

Explore important cases for first_value and last_value in Oracle SQL, including execution with and without order by, partition by, and how where conditions and final ordering affect results.

Explore the Oracle dbms_random package to generate random numbers and strings using built-in functions like random and value, with range control, rounding, and examples from dual.

Explore the dbms_random string function to generate random strings for passwords using length. Choose options l, a, x, or p to produce lower-case, mixed-case, uppercase alphanumeric, or printable strings.

Use the select statement to build dynamic sql that adds a created by varchar(100) column to all tables via dictionary and user tables.

Learn how hierarchical queries build a tree structure, from Banner root to modules, submodules, and screens, using level concepts like Banner (level 1), students (level 2), and course catalog.

explore hierarchical queries using a self-join on the employees table, identifying roots and levels, with manager_id referring to employee_id, and building top-down queries to reveal nested staff.

Explore Oracle hierarchical queries using start with and connect by prior, demonstrating top-down versus bottom-up trees with employee hierarchies starting at 100 and 113.

Learn hierarchical queries in oracle sql, applying connect by prior to traverse top-down and bottom-up hierarchies, with levels, subqueries, lpad, and manager-employee examples.

Create a single table driven hierarchical menu with menu_id, title, and parent_id, populate banner, students, and course catalog, and avoid order by in SQL so the application can present tree.

Explore the dbms_error_log package to capture row-level DML errors and log them into an error table. Learn to use log errors with reject limit unlimited to identify failing rows.

Explore pivoting in Oracle SQL to create matrix reports, converting department id and job id data into horizontal columns with counts and hire date pivots.

Explore four setup options to install and access the HR schema on Oracle 11g/12c, including unlocking accounts, navigating the schema, and connecting via Sqlplus or SQL Developer.

Create the HR schema and HR tables in Oracle 12c by running two scripts: create_hr_user.sql and create_hr_tables.sql. Connect as sysdba, switch to the pluggable database, and grant the necessary privileges.

Understand the Oracle 12c architecture with a container database (CDB) and multiple pluggable databases (PDBs), enabling centralized management, cost savings, and easy backup, cloning, and role separation, vs 11g.

Learn how to download Oracle database 12c release two for Windows 64-bit, including the edition choice in a single download, and note licensing (only 11g express is free).

Install Oracle database 12c on Windows by unzipping the download, running setup, and selecting desktop class with enterprise edition. Define the container database ORCL and the pluggable ORCL PDB.

Connect to the Oracle database using SQL Plus or SQL Developer, log in as sysdba with the ssis password, and use the orcl tnsnames.ora entry to access root container database.

Unlock the air account in the pluggable Orcl PDB. Move from the container to the PDB and read tables and views, and update the tnsnames.ora entry.

Move from the container database (CDB) to the pluggable ORCL PDB, open the PDB, and unlock the HR account to enable HR schema access.

Fix ORA-01033 initialization or shutdown in progress by connecting as CIS DBA and executing 'alter pluggable database all open' to open all PDBs, with a bonus permanent solution.

Install Oracle 11g express edition as a lightweight alternative to 12c, unlock the HR account, and connect via SQL Developer, noting container databases and pluggable databases differences.

Explore the Oracle hr schema by examining core tables like employees, departments, locations, countries, regions, jobs, and job_history, and learn their primary and foreign key relationships.

Navigate the HR schema to explore tables like employees and departments, learn key columns and data types (number, varchar2, date), and identify foreign keys for querying data.