Master SQL Server from Basic to Advanced - 25 hrs Training

Learn to implement iif in SQL select statements to create a new column from incentives, such as greater than 1000, using true and false parameters or a case alternative.

Master subqueries by finding the record with the maximum discount via a subquery that returns the highest value and feeds it to a second query to fetch the full record.

Utilize the lag window function with over to generate previous and previous-to-previous match date columns, ordered by match date, handling nulls and validating continuous date patterns.

Learn how to use subqueries with in and not in operators to filter records from related tables, ensuring distinct, non-null territory IDs.

Master subqueries with in and not in by using a select query to filter cities by country region, showing distinct usage and the where clause.

Learn to find the second highest salary in SQL Server using nested queries and max, without top or offset, and retrieve the full row via a subquery.

Master the left join in sql, where the left table drives results and all its rows appear, with matching right table data and nulls for unmatched rows.

Master the right join by selecting IDs from the right table, understanding alias usage, and comparing results with left join to handle null values.

Explore the full join to retrieve all IDs from both tables and handle nulls. Create a single ID column by choosing the first table’s ID when present, otherwise the second’s.

Explore the cross join by creating every combination between two tables, showing that it produces all pairings without an on clause and differs from lookup joins.

Explore derived tables in SQL Server by turning queries into a named table and selecting from it, while applying union all rules and data-type alignment.

Learn how a self join in sql uses the same table twice to find reporting managers, using aliases and left or inner joins, with an Excel vlookup analogy.

Demonstrates a three-way self-join on Adventure Works 2019 data to identify managers who appear in three consecutive records, using serial number offsets.

Master SQL techniques to extract three consecutive viewer IDs with counts over 50,000 using derived tables, row_number, lead, lag, and inner joins.

Identify each player's first game date, compute cumulative games over time with window functions, and count players with consecutive play dates using lead and date difference.

Explore interview-ready SQL Server: identify managers with at least three reportees using group by and having, join lookup for names, and analyze party seats and cumulative salaries with window functions.

Explore the differences between DML and DDL, and learn to create databases, schemas, and tables, then insert, update, delete, and drop data with practical school and hospital examples.

Learn how to perform update queries in SQL Server, including updating single tables and multi-table scenarios with joins, case expressions, and salary adjustments based on performance.

Learn to insert data into a table using insert into with or without column lists, handle text quotes and datatype checks, and insert multiple rows with examples.

Learn to import Excel data into SQL Server using SQL, and troubleshoot driver issues with ChatGPT-driven solutions in part 4.

Discover how primary keys and foreign keys enforce referential integrity in SQL Server table design, and learn how to create and relate keys in a multi-part series.

Explore how update queries behave under referential integrity with cascade on versus off, demonstrating updates on child and parent tables and the resulting auto-updates to related rows.

Master how insert queries enforce referential integrity by ensuring foreign keys exist in the parent table, and how cascade on/off and on delete set null affect related data.

Explore referential integrity and cascade behaviors in SQL Server, including insert validation, on update cascade, and on delete cascade, plus key constraints like primary key, foreign key, check, and unique.

Learn how to use common table expressions to write readable, maintainable SQL by defining temporary results with the with clause, comparing to derived tables, and composing complex queries.

Create two common table expressions for department names and incentives, then left join on department id to get department name and total incentive; compare with derived tables and group by.

Learn to use a common table expression to find the employee name with the highest incentive in each department, using ctes and a join; explore max over for subject scores.

Learn to build and validate a commutable expression to fetch recent orders from the last 30 days and identify customers who spent over 10,000, then join these results.

Discover recursive common table expressions (CTEs) to build hierarchical levels in SQL Server, part 5 of the series.

Explore building a recursive common table expression to generate a calendar from a start date to an end date, using anchor and recursive sections, union all, and max recursion settings.

Leverage recursive common table expressions to generate a complete range of invoice numbers and identify missing ones, using anchor and union all, and filter with not in to reveal gaps.

Discover how stored procedures pack multiple SQL tasks into a single executable program you run with one line, while macros and VBA help you structure reusable code.

Learn to create a stored procedure, connect Excel with a SQL table using M code, in part 2.

Master SQL Server techniques by learning how to use stored procedures to import Excel files into SQL Server tables, part 3 in this advanced training.

Master after update triggers to log old and new values into an audit log table, using the inserted and deleted tables joined on customer id.

Create an after delete trigger on the customer data table to capture deleted rows into an audit log via the deleted table.

Explore handling duplicate emails with an instead of insert trigger using a common table expression and row_number over (partition by email) to insert only the first occurrence.

Explore implementing an instead of update trigger to log inventory updates, discard negative quantities, use inserted and deleted tables to track old and new values, and update stock when valid.

Learn how SQL Server triggers auto execute code after insert, update, or delete on a table, with syntax, event names, and examples including drop and create trigger.

Create an after insert trigger on the customer data table to auto populate an audit log from the inserted pseudo table, capturing customer id, name, email, action type, and date.