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.

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.

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.

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 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.

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 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.

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 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.

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.