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 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.

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.

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

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 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 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.

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.

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.

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.

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 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 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 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.

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.

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 pivoting in Oracle SQL to create matrix reports, converting department id and job id data into horizontal columns with counts and hire date pivots.

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.

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.

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.

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.