SQL Advanced

Install Oracle database by downloading the Windows installer from the provided Google Drive, run the wizard, accept the license, and remember the password for the default sis and system accounts.

Learn how null values affect arithmetic and aggregates in SQL, yielding unexpected results. See how averages, counts, and sums ignore nulls, and use the interval function to include nulls.

Learn how to perform multi-table inserts in Oracle using insert all to add multiple rows to one or several tables, with practical column and value examples.

Explore analytical functions in SQL, learn how window-based calculations compute moving sums, ranks, and cumulative values across groups, and understand ranking, window, aggregate, reporting aggregate, and lag/lead families.

Learn how analytical functions combine detailed data and aggregates into a single result set, compute averages over partitions, and avoid duplicating data scans.

Learn to use analytical functions to display aggregates at different groupings, such as by sales date, by month, and for the entire table, all within a single SQL statement.

Learn to transform row-level data to columns using the case statement in SQL advanced, turning sales data by salesman and monthly product daily into a multi-column pivot.

Learn to transform column level data into row level records using union all, converting a sales column dataset into row-wise totals for each product and month.

Learn to sort hierarchical data in SQL using the siblings by clause instead of order by, preserving the tree structure while ordering by key values in ascending or descending order.

Explore hierarchical data to calculate total sales for every salesperson under manager Raj, including indirect reports, by grouping by top boss and joining on salesperson id.

Explore extensions to group by in Oracle, including roll up, grouping sets, and the grouping function, plus the group_id function, to display subtotals at multiple levels.

Use the cube function to display subtotals and grand totals across all group by column combinations, showing monthly subtotals (february, march, april) and product-level subtotals with the overall total.

Discover how the grouping_id function identifies subtotals and the grand total when grouping by multiple columns, using a single flag indicator that shows actual subtotal, product-level subtotal, and grand total.

Explore hash partitioning, where a hash key distributes rows evenly across partitions, using a four-partition setup on customer id and observing data placement.

Learn how to design composite partitioning by combining range and hash partitions, with range partitions subpartitioned by hash on customer IDs, and read the explain plan.

Explore interval partitioning as an enhancement to range partitioning in Oracle 11g, which automatically creates a new partition for each month as data arrives.

Learn to create a materialized view with on commit, using immediate and fast refresh, so changes to the source reflect instantly in the view, with cost considerations.

discover how to use the on demand option for materialized views, refreshing data manually with the dbms refresh command and verifying results after inserts.

Explore how query rewrite leverages a materialized view to speed up aggregated queries on sales data, with explain plans showing the rewrite to the materialized view for faster results.

Explore how regular expressions search data by patterns, combining literals and operators like the pipe to match complex strings, and use functions to find, replace, and extract matches.

Use the pipe operator to combine multiple expressions in regular expressions in sql advanced, showing how the OR operator connects conditions and helps build patterns for city and salesperson data.