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.

Configure and run the course scripts by creating a user, establishing credentials, and granting access, then connect and create five tables: customer, product, sales, sales history, and sales person.

Explore how to use default values for columns in SQL, leveraging the default keyword to simplify inserts and predefine expiry dates, with practical table examples.

Explore virtual columns in SQL advanced: they appear as normal columns but are not stored, are computed at runtime via a formula, with two creation methods and alter table support.

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.

Learn to use the Oracle merge statement to update matched rows, insert new ones when not matched, and delete rows based on criteria.

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 compute a running total of sales using an aggregate function, selecting columns and ordering by a key to display the cumulative sum for each row.

Group sales by month and display each month's contribution as a percentage of total sales using ratio and analytical functions. Identify the highest contributing month and compute exact percentages.

Apply the rank function for top-n analysis by month, filtering ranks less than or equal to 3 to identify top salespersons, and switch to descending to reveal the bottom performers.

Learn to divide data into bands using a ranking function, partition by totals and order by sales in descending, creating three or more bands.

Explore lag and lead functions to analyze data over time, comparing previous and next periods such as months, quarters, and years, using offsets to reveal prior and upcoming values.

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 row-level data into column-level data using the pivot operation, replacing lengthy case statements with a single pivot that sums total by product and month.

Convert row values into a single column by applying LISTAGG, grouping by region, and using a chosen separator (comma, semicolon, etc.), with ordering by last name.

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.

Discover how to convert column level data into row level data with unpivot, using an analytical function to transform total amount values from multiple columns into a single row.

Explore hierarchical queries to handle parent-child relationships in tables and display data in hierarchical order, with examples from general ledger data, asset and liability accounts, and management hierarchies.

Explore how to query hierarchical data using the connect by clause, defining parent-child relationships with first name and manager, starting at the top and calculating depth levels.

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 the connect_by_root unary operator in hierarchical queries to surface the top-level boss, such as Jeff, and reveal manager hierarchies from first to second level.

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 the practical use of the connect by clause to generate a numeric series with the level column, demonstrating quick number generation from 1 to a defined limit.

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.

Learn how rollup extends group by in SQL to display subtotals and a grand total for each sales month, grouping by sales month and product name.

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.

Learn how the grouping function disambiguates subtotals and the grand total produced by rollup and cube in SQL, by returning flag1 and flag2 to label each row.

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.

Learn how grouping sets limit subtotals and grand totals using cube on sales month, product name, and city, enabling combinations like sales month with product name or with city.

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.

Materialized views store a table segment whose contents are periodically leveraged from base data to pre-calculate metrics like shipping costs and net profit, enabling data replication and improved performance.

Learn the options for creating materialized views, including immediate versus deferred data population, fast, complete, and force refresh methods, and triggers on commit or on demand.

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 materialized views with the refresh fast option to perform incremental refreshes by tracking changes via a materialized view log, using primary key or row id options.

Learn to configure automatic materialized view refreshes using start with options and interval settings, such as every 7 days or every 30 minutes, and verify updates with a demo.

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.

Learn to use the interval operator to match exact, minimum, and range occurrences in data, with addresses as examples, showing exact two occurrences, at least one, and a two-to-four range.

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.

Master SQL patterns to check if a string starts or ends with a value using anchor operators, with examples starting with 6, 7, 9 and ending with road.

Learn how to escape meta characters with a backslash in Oracle to treat them as regular characters within search patterns in regular expressions.