The Advanced SQL Server Masterclass For Data Analysis

Explore how window functions extend aggregate calculations by performing sums, counts, and max across all rows or groups while preserving row-level detail. Apply over to add these calculations as a new column in your query output, retaining existing columns and enabling comparisons such as year-to-date sales.

Learn how partition by combines over with grouping to compute subtotals for groups while preserving row-level detail, using product id and order quantity to define groups.

Discover how the row_number window function assigns rankings within groups using over and partition by, and how order by determines these rankings in the sales order detail dataset.

Explore rank and dense_rank in SQL Server for data analysis in the advanced SQL Server masterclass.

Learn to use lead and lag window functions to fetch next or previous values in the same column. These enable time-series comparisons and partitioned analysis by customer id.

Explore the first_value window function, returning the first value per partition using over and order by, with examples of highest, lowest totals and first order amount per customer.

Master how to use subqueries to break complex SQL problems into multiple steps by turning a query's output into a virtual table, aliasing it, and applying outer filters.

Explore how rows between creates windows of two preceding rows relative to the current row for rolling totals and moving averages, using order by and order date.

Define scalar as a single value and show how scalar subqueries populate a constant across rows in the select clause and enable where clause filtering by the average list price.

Use correlated subqueries that run for each row of the outer query to derive fields. See how they count items in sales order details where quantity is greater than one.

Explore the exists operator and correlated subqueries to filter data without expanding results, compare exists with joins in one-to-many scenarios, and learn when not exists improves queries.

Master advanced correlated subqueries by using for xml path and stuff to flatten multi-row details into a single row, producing comma-separated line totals for each order.

Explore how to flatten data with SQL pivot by turning rows into columns, using sum as the aggregation, and compare to Excel pivot tables.

Master advanced pivot techniques in SQL Server, including replacing column references with select star, controlling pivot headers with product category name, and aliasing non-pivot fields like order quantity.

Master complex multi-stage data analysis with common table expressions, replacing nested subqueries; identify the top ten sales orders per month, sum them, and compare to the previous month.

Master common table expressions to simplify multi-step data transformations, using the with and as keywords to create sequential virtual tables, including recursive capabilities for generating series and dates.

Explore recursive cte techniques to generate series in SQL, including numbers and dates, using anchor and recursive members with a termination condition to build a calendar table.

Discover how temp tables overcome CTE scope limits, enabling reuse and easier debugging across a session, by converting CTEs to temp tables with select into and the pound sign.

Learn how temp tables persist beyond a select, how to drop them to save memory, and when to choose temp tables over CTEs for large joins and multi-step scripts.

Learn to create temporary tables with explicit create table and insert into, gaining fine-grained control of data types and derived fields over select into.

Learn how the truncate command clears data from tables while preserving structure, enabling reuse of temporary tables and streamlined code for combining sales and purchases in a single orders table.

Learn how truncating the orders table clears data while preserving structure, then reuse it for sales and purchases using an order_type field and minimizing temp tables.

Update existing table data in place to compute tax rate percent and bucket total due into small, medium, and large categories, using update statements, case logic, and selective field population.

Learn to chain update statements that populate derived fields from other fields using temp tables, case expressions, and targeted where clauses for tasks like tax rate buckets and holiday orders.

Explore the delete command in SQL to surgically remove rows with a where clause, contrast it with truncate, and note its danger even in top ten sales orders example.

Optimize SQL queries by using update statements to populate a temp table from one source at a time, after filtering data early to avoid many large-table joins.

Learn to update a table from another table using an update with a from clause and join, setting product name, subcategory ID, and category ID from related tables.

Learn how to use update to replicate exists and not exists, returning data from the many side of a one-to-many relationship, with examples like line totals over 10,000.

Learn how indexes speed up queries by sorting data, exploring clustered and non-clustered indexes, and applying them to temp tables and joins for faster data retrieval.

Apply clustered and non-clustered indexes to temp tables to speed up complex updates and joins with sales and product data, and learn indexing strategies.

Learn how to convert temporary into permanent tables with calendar tables, using DDL and DML, create table, insert, and recursive CTEs to generate date ranges.

Populate the calendar table's date fields and flags using simple updates and case statements, then join to the lookup table to provide weekend and holiday attributes for dates.

Views encapsulate complex queries, including derived columns, into a virtual table, simplifying analytics and enabling consistent logic and access from a single table.

Learn to use SQL variables to streamline code by declaring and setting values. See how pulling subquery results into variables reduces repetition and improves query maintainability.

Develop layered variable logic to compute the first and last days of the previous month and apply them in a calendar table query.

Learn how to create user defined functions in sql, defining scalar valued functions that return a date and are stored on the database for reuse.

Master flexible user defined functions by adding parameters, using a calendar table to count business days between order and ship dates, and replacing complex logic with simple function calls.

Learn to create table valued functions that return a table and accept parameters, enabling modular, reusable, and maintainable SQL for color-based product queries.

Explore stored procedures as flexible database objects for on-demand execution, parameterized customization, and heavy lifting on the server, contrasting them with user defined functions.

Learn how to implement control flow in SQL with if statements, using begin end blocks and else to branch queries in stored procedures and functions.

Learn to replace if else with multiple distinct if statements in SQL, handle three conditions, and obtain top ten products from a combined sales and purchase orders dataset.

Explore dynamic SQL, which constructs and executes a SQL statement at runtime to replace long chains of if statements. Use exec to build input-driven top-n queries with varying aggregates.

Learn to build dynamic sql by concatenating query fragments, executing the resulting code with exec, and parameterizing top n and aggregate functions (sum, avg, max, min) in a stored procedure.

Develop precise prompts to guide AI tools like ChatGPT and Claude in writing SQL Server queries, using clear objectives, data details, and output structure.

Learn how few-shot prompting uses example-driven templates to guide AI through data transformations, including unpivoting in SQL Server for tall and narrow data.

Be bold with prompts and practice prompt engineering through iterative rounds to refine responses. Start new conversations, compare ai assistants, and try 'let's think step by step' to boost quality.

Structure prompts with xml style tags to create clear sections such as context, data, instructions, and output format, enabling self-documenting, unambiguous, and nested prompts for clearer ai responses.

Explore when to use concise versus detailed prompts with ai assistants, focusing on uncertainty in sql data analysis and starting with broad prompts before refining.

Explore role reversal prompting, a flipped interaction that has the AI ask one clarifying question at a time to tailor a monthly revenue by product category sql query.