The Advanced SQL Course

Explore coalesce and nvl, two functions for handling null values in data sets; learn how ordering and data type consistency affect results, with practical examples.

Learn how to trim and pad strings in SQL using trim with leading, trailing, or both options, and apply lpad and rpad to ensure fixed lengths with specified characters.

Learn how to use the greatest and least functions to cap salaries and enforce minimums, using expressions in parentheses and table examples from emp and amp.

Pivot transforms row values into columns, using region id and subregion id with sum or count as the aggregation, demonstrated on the iba_countries table to create a population matrix.

Learn how to unpivot a pivoted table to turn columns into rows, creating a measure name column and a value column, demonstrated with classroom test scores.

Identify distinct job values (analyst, clerk, salesman, manager, president) and build a pivot query that shows deptNo rows with job columns, aggregating using sum on the emp table.

Practice sql commands to prepare an assignment, create and insert into a table, then unpivot the results by aliasing the season as year and naming the aggregated column goals.

Learn regular expressions and metacharacters in SQL to search and manipulate text using pattern matching and literals across char, varchar, clob, and other character data.

Explore pattern matching in regex with the dot, plus, question mark, and asterisk metacharacters, and understand case sensitivity for literals versus metacharacters while matching entire strings.

Explore the \d and \w metacharacters for digits and word characters, their case-insensitive behavior, and how plus, star, and question mark control repetition in full-string regex matches.

Learn interval notation in curly brackets to repeat subexpressions, use square brackets for lists and ranges, group with parentheses, and apply \d and \w to match digits and alphanumeric characters.

Learn advanced pattern matching with the pipe operator, subexpressions, and back references; apply escape characters and line anchors to match at string starts or ends and handle literals.

Explore four Oracle SQL regex functions (regexp_like, regexp_replace, regexp_instr, regexp_substr) to filter, replace, locate, and extract patterns, noting Oracle SQL specificity compared with SQL Server and MySQL.

Learn to use the regexp_like function in the where clause to match patterns, handle case sensitivity, and solve assignments with US postcodes and Steven with a V or PH.

Explore the regexp in string function to locate a pattern in a string, set start position and nth occurrence, and practice assignments like first 'n' at position seven and parentheses.

Use the regex underscore substring function with start and end anchors. Select country names that begin with A and end with A, and the first word of multiword names.

Explore class metacharacters that define digit and alphabet groups in regex, including [A-Za-z] and \w. See how they extract first names from customers with regexp_substr.

Practice regular expressions through trial and error and testing; don't memorize all the different meta characters, and use resources to flag patterns like IDs in data.

Master analytical (window) functions using the over clause to calculate the percent of region total population, compare with traditional joins, and learn partition by, order by, and window frame syntax.

Explore the over clause in window functions, showing partitioning, ordering, and window frames, with sum population over the entire countries table.

Master the over clause with partition by to compute region and subregion population totals using window functions. Learn how partitioning by region id, or by region and subregion, shapes results.

Master the over clause and order by in analytic functions to sum population, order by region_id and subregion_id, with partition by region_id and ascending by default.

Explore defining a window frame in analytical functions using rows or range with preceding and following bounds across partitions ordered by a column; observe unbounded preceding and current row effects.

Explore window frames by partitioning the countries table on region id and subregion id, ordering by subregion id, and computing sums of population with unbounded preceding and following.

Explore window frames with range in an analytical query by partitioning on region_id and ordering by subregion_id, defining the frame as one preceding to one following.

Explore the default window frame behavior in sql by summing population over partitions of region_id with and without order by. Compare unbounded preceding to current row versus unbounded following.

Discover quirks of analytical functions with window frames using single bounds, learn how n following behaves, and implement a between current row and two following workaround.

Explore the four types of window functions in SQL: aggregate, ranking, distribution, and analytical, with examples like sum, average, row number, percent rank, lead, and nth value.

Explore aggregate functions sum, avg, max, min, and count applied to country populations, using over and partition by region id to reveal regional totals and insights.

Explore analytical window functions including lag, lead, ntile, and nth_value using over clauses, order by, and partitioning to compute previous, next, and grouped values.

Explore row number, rank, and dense rank in the over clause and partitions by region id. See row number yield positions, while rank and dense rank handle ties with gaps.

Discover percent_rank and cume_dist as distribution functions, using order by over and partition by to rank values within groups. Understand their 0 to 1 range and Oracle's mandatory order by.

Master the SQL query order of execution by tracing how the from clause, joins, and where filters feed into group by and having, with window functions evaluated after having.

Calculate each country's population difference from the table maximum using max, alias it as diff from max pop, and compare Ireland to China.

Explore using the lead function to order countries by population descending and compute variance between adjacent rows, with an example comparing China, India, and the USA.

Flag the top ten percentile per region using percent_rank over (partition by region_id order by population desc) with a case when, marking 1 for rank ≤ 0.1.

Order countries by population ascending and compute a running total with the sum over clause, using rows between unbounded preceding and current row to show the running population.

Build a view that joins orders with order items, aggregates unit_price times quantity by order_id to compute a total order value, filters for complete orders, and exposes all order fields.

Select the order_id and customer_id, use rank over partition by customer_id order by order_total desc to show each customer's orders from highest to lowest, via the v_orders view.

Define a range-based window frame on month-year data to calculate a rolling three-month average of order totals. Convert dates to a numeric month-year key with to_char and to_number.

Explore cube, rollup, and grouping sets to extend the group by clause and generate subtotals and a grand total in SQL queries.

Explore how the rollup extension enhances group by to produce subtotals and grand totals across a hierarchy of columns, with partial rollups and null-handling using nvl.

Explore the cube extension in SQL, generating subtotals and grand totals for all combinations of columns in a group by, with no hierarchy and unlike rollup.

Explains using NVL to replace null subregion id with zero, clarifying rollup subtotals for region id totals and avoiding confusion between nulls and actual values.

Learn how grouping_id flags subtotals and the grand total in roll up and cube queries. It yields zero, one, two, or seven depending on the number of grouped columns.

Learn how grouping sets replace roll up or cube to customize subtotals for region id, sub region id, and organization region id, including grand totals.

Master composite columns in rollup and cube extensions by placing multiple columns in parentheses, causing them to act as a single column and be evaluated together in hierarchy.

Compute subtotals for all combinations of non-aggregated fields using cube in SQL, avoid nulls with nvl, and display grouping IDs for subregion and organization region in the population results.

Apply grouping sets to sum population by region id and organization region id, and by subregion id and organization region id, replacing nulls with zero using nvl, from iba_countries.

Explore how Oracle SQL hierarchical queries organize data in parent-child relationships, using the emp table and manager field to illustrate levels, roots, and tree structures.

Explore the connect by prior clauses to define the child and parent columns, use start with to set roots, and swap directions to view direct and indirect reports.

Discover how the level pseudocolumn reveals each record's position in a hierarchy using the connect by clause, showing root and four reporting levels.

Learn to use connect by route and connect by path in select statements to show each row’s route from a root, with level, separator, and optional aliasing.

Learn how to order hierarchical query results by using order by siblings to preserve hierarchy while sorting siblings alphabetically, instead of a plain order by clause that breaks the hierarchy.

Format your hierarchical query results with lpad to indent by level, using the string, length, and padding character parameters to clearly reveal the hierarchy.

Filter hierarchical records in sql using where and connect by clauses to exclude a specific employee and all their direct and indirect reports, starting from king as the root.

Demonstrates a hierarchical query starting from Clark's employee number 7782, using connect by prior empno = manager to reveal Miller as the sole report in the emp table.

Use Blake as the root to query the EMP table and apply order siblings by salary descending, demonstrating connect by prior empno equals manager and child salary patterns.

Reverse the connect by clause to traverse up the management chain by swapping empno and manager, making Smith the root and revealing Ford, Jones, and King's reporting lines.

Build a hierarchical query with Jones as the root to identify all employees who report directly or indirectly to Jones, then sum salaries using a subquery excluding Jones.

Explore subqueries and common table expressions through practical examples, including scalar subqueries, from and where clauses, and inline views used to filter employees by salary and department.

Explore how correlated subqueries reference the outer query and execute per row, and use where exists and where not exists to test for results in SQL.

Create and reference a common table expression (CTE) using with and as to define a named result set, then join CTEs to filter salaries above 2000.

Explore recursive cte construction to process hierarchical data, using anchor and recursive clauses with union all, and compare with hierarchical queries, including practical examples and pitfalls.

Use a correlated subquery on the EMP table to return employees who also serve as managers, via an exists check on a.empno = b.manager, revealing six such employees.

Use a recursive cte anchored on Clark to join the emp table and show Clark and his direct report Miller.

Anchor a recursive cte on Jones to traverse the emp hierarchy, tracking iter_no to show levels, with Scott and Ford under Jones, then Adams and Smith under them.

Learn how views function as virtual tables powered by sql queries, enabling secure data access and simplified results. Create, replace, or drop views with the emp example.

Compare regular views with materialized views to understand storage, performance benefits, and refresh options, including live data limitations and Oracle SQL behavior.

Explore the elements of SQL syntax for creating materialized views, including immediate versus deferred population, refresh options (fast, complete, force), on commit or on demand, and query rewrite.

Explore simple and complex materialized views, including connect by clauses, set operations (intersect, union, all, minus), distinct keywords, aggregations, groupings, and joins, with Oracle documentation for a comprehensive overview.

Explore how fast refresh updates materialized views via the materialized view log on the master table, and how to perform complete and force refresh.

Explore materialized views in Oracle by switching between refresh on commit and refresh on demand, refreshing schedules, and testing inserts into the emp table to observe MV updates.

Learn to manually refresh materialized views using dbms_refresh and related procedures, create a materialized view, perform a complete refresh on demand, disable query rewrite, and verify results after inserting data.

learn to create a materialized view using build deferred, populating it on the next refresh on demand. example defines env_def, delays one minute, then verifies data and drops the view.

Enable query rewrite so the optimizer uses the mv_eqr materialized view for faster execution, with immediate refresh on commit, while noting data may be out of date if not refreshed.

Convert an existing table into a materialized view with on prebuilt table, ensuring same name and columns, and drop the view before dropping the table.

Explore materialized views, quirks, and online guidance. Learn to resolve Oracle on commit left join errors by using inner joins or on demand, with forums as resources.

Manually install the country dataset by dropping existing tables and views, uploading the countries script, then loading data with UTF-8 encoding and proper column mapping.

Learn how to manually install the EMP/DEPT dataset by dropping and recreating tables, uploading the DDL script, and loading data with UTF-8 encoding and correct mapping.

Manually install the customer orders dataset by dropping tables, uploading the utf eight encoded data file, and loading data into customers, orders, order items, products, and stores with column mappings.