T-SQL Expert: Complete Microsoft SQL Server Training

Install Windows Server in Hyper-V to prepare for SQL Server, create a virtual machine, enable Hyper-V features, and choose the GUI edition of Windows Server 2022.

Install and configure Microsoft SQL Server on a local server, including downloading SQL Server on premises, installing management tools, and setting up Git and Visual Studio Code.

Download the Adventureworks Lt 2022 sample database from the Microsoft SQL samples repository and restore it on your local server using SQL Server Management Studio.

Explore the basics of T-SQL and its set-based, declarative approach to writing queries for SQL Server. Identify predicates, operators, expressions, and commands as core language elements.

Explore the T-SQL language elements, including DML, DDL, DCL, predicates, operators, functions, expressions, and variables, and learn how to organize queries with select, insert, update, and delete.

Develop and practice fundamental T-SQL queries in Adventureworks by using select, where, like, in, and between clauses in SSMS, including executing queries and handling batches.

Master expressions, comments, and batch separators in t-sql, and learn to use variables and basic control flow to build robust queries and manage code in batches.

Explore T-SQL expressions and control flow basics to enhance queries on sales.orders, using year extraction, date add, and order by order date descending, while troubleshooting syntax errors.

Learn t-sql control flow with if-else and begin-end blocks in stored procedures or triggers, to categorize orders by year, while debugging schema errors and using date diff.

Learn to think in sets for T-SQL, using set-based processing over procedural code, ensuring unique identifiers and proper ordering with order by to optimize SQL Server queries.

Learn how sets drive efficient sql server queries with practical examples in the Adventureworks database, including top n selects, defining primary keys, and set based grouping and ordering.

Learn how T-SQL column aliases boost readability, compare aliasing methods using as, equals, or direct assignment, and apply best practices for descriptive, consistent names and comma placement.

Learn to structure a business report query by joining the sales order header with territory, salesperson, and person tables using select, join, group by, and order by.

Master practical business reporting in T-SQL by aliasing columns with as, concatenating salesperson names, and calculating total orders, subtotal, tax, and average order size on AdventureWorks 2022.

Explore simple case expressions in T-SQL to substitute values and apply conditional logic in select clauses, alias the result, and replace numeric codes with descriptive labels.

Master simple case expressions with the Adventureworks 2022 database to categorize total due as low, medium, or high in the sales order header table, using where and order by.

Learn basic T-SQL querying in the Adventureworks 2022 database by writing simple select statements and retrieving specific columns, using distinct, aliases, and case expressions to classify products and campaigns.

Practice writing basic T-SQL select statements against the AdventureWorks 2022 database to retrieve data, learning to specify columns, use distinct and aliases, and apply case expressions for data categorization.

Master basic select statements by applying distinct to remove duplicates, using table and column aliases, and implementing case expressions to categorize and clarify query results for readability.

Explore how the from clause creates virtual tables and use aliases for clean references. Distinguish inner, outer, self, and cross joins, and avoid cartesian products with ansi sql 92 syntax.

Explain the from clause and virtual tables, apply aliases, and build joins, including inner joins and self-joins on AdventureWorks employees, while avoiding cartesian products and using get ancestor for managers.

Explore cartesian products, join types, and T-SQL syntax choices for efficient multi-table queries. Learn inner, outer, and cross joins, and why SQL 92 on clauses reduce accidental cartesian products.

Compare Ansi SQL 89 and Ansi SQL 92 join syntax to avoid accidental cartesian products, using explicit on clauses and join keywords for clearer, safer, and more efficient queries.

Explore cartesian products and join types in T-SQL using the Adventureworks 2022 database, including inner, outer, and self-joins, with best practices for efficient multi-table queries.

Master inner joins to query data across multiple tables, using on and where clauses, ansi sql 92 syntax, aliases, and both single and composite joins for accurate results.

Explore inner joins in SQL using practical examples, including simple, composite, and multi-table joins; use aliases and distinct to retrieve only matching rows across customers, orders, and details.

Master inner joins using the Adventureworks 2022 database to join customer, orders, and product data. Learn simple and composite joins with on and where clauses to filter results.

Explore the purpose and function of outer joins, including left, right, and full joins, and learn to write queries that preserve rows and use null tests to reveal unmatched data.

Discover how outer joins preserve unmatched rows and fill missing data with null values in T-SQL, and learn left and right outer joins, inner joins, using Adventureworks.

Demonstrate left, right, and full outer joins on the Adventureworks 2022 database to preserve unmatched rows with nulls and identify customers without orders using null tests.

Explore cross joins and self joins in SQL to generate all row combinations, support test data and lookup table creation, and compare rows within the same table.

Master cross joins and self joins in SQL Server with the Adventureworks 2022 database, using the cross join operator to generate cartesian products for test data and lookups.

Explore self joins and cross joins in T-SQL with practical examples that link employees to their managers and generate all row combinations, including unmatched cases.

Learn to control query result order with the order by clause, sorting by columns (asc or desc), handling nulls, and using multiple columns as tiebreakers with collate options.

Explore how to sort query results with the order by clause, including sorting by columns, calculated aliases, and multiple columns with ascending or descending order.

Explore how to filter data with predicates in the where clause, using logical conditions and operators like in, between, and like to return only rows that evaluate to true.

Learn to filter query results with the where clause using predicates and a range of operators, including or, in, and not in, with examples on dates and strings.

Learn to filter ranges of rows with the top option and offset fetch, using order by, with ties, and paging for deterministic results.

Use top to retrieve a fixed number or percentage of rows with a deterministic order by and with ties. Apply offset fetch for pagination by skipping and fetching ranges.

Master three-valued logic in sql, where true, false, and unknown govern comparisons with nulls. Test nulls with is null or is not null, and where and order by treat nulls.

Explore how null values behave in SQL Server and how to include or exclude them with is null and is not null, while understanding default sorting of nulls.

Explore SQL Server data types, including exact numeric, approximate numeric, binary strings, and special types, with emphasis on type precedence, conversions, storage, precision, and performance.

Explore approximate numeric types such as float and real for large ranges with reduced precision; review binary types binary and varbinary, and note raw version, unique identifier, and XML.

Explore SQL Server data types across numeric, character, and temporal categories using the Adventureworks 2022 database, and learn how type precedence drives implicit and explicit conversions.

Learn how SQL Server handles implicit conversions and when to use explicit conversions with cast or convert, guided by precedence rules. Ensure reliable operations and prevent errors in T-SQL.

Explore character data in SQL Server, covering non-Unicode and Unicode types, collation, and case sensitivity, plus string concatenation, and text functions like substring, charindex, replace, and format.

Learn to work with character data in SQL Server, covering collation and case sensitivity, string concatenation with plus and concat, and built-in string functions and like pattern matching.

Explore SQL Server date and time data types, literals, and built-in functions to store, manipulate, and query temporal data, including handling partial values, time zones, and range queries.

Explore how to work with date and time data in SQL Server by using current and UTC date functions, extracting parts, constructing values from components, calculating differences, and validating inputs.

Learn to add data to tables in SQL Server using T-SQL with insert, insert into, insert into select, and select into; leverage table value constructor for multi-row inserts.

Master the insert statement in SQL Server to add data to tables, from single rows to bulk inserts using insert into select; ensure matching columns and data types.

Modify and remove data in SQL Server using update, merge, delete, and truncate table with where clauses, joins, and upserts for precise data management.

Learn to modify and remove data in SQL Server using update, merge, delete, and truncate table, with practical examples and guidance on where clauses and upserts.

Explore generating numbers in SQL Server using the identity property and the sequence object, including syntax, use cases, constraints, and cross-table coordination for unique identifiers and sequential values.

Explore built in functions in SQL Server, focusing on scalar functions like abs and dbname, grouped aggregates, window functions, and rowset tools such as open query and open row set.

Master built-in SQL Server functions, including scalar, aggregate, and ranking functions, with practical examples on date parts, absolute values, data type casting, counts, sums, and rankings.

Explore implicit and explicit data type conversions in SQL Server, using cast, convert, parse, and try parse and try convert, with culture codes and formatting for robust, error-free queries.

Explore data type conversions in SQL Server using cast, convert, parse, and try_parse to format dates, convert strings, and handle invalid input gracefully.

Explore how to use SQL Server logical functions to validate data, test conditions, and map values from a predefined list with is numeric, iif, and choose.

Explore SQL Server logical functions for data validation and conditional testing with is numeric, iif, and choose. Learn to map numeric indices to labels and handle non-numeric inputs in queries.

Learn to handle null values in SQL Server by using isnull to replace nulls, coalesce to return the first non-null value, and nullif to conditionally generate nulls in queries.

Learn to manage null values in SQL Server with isnull, coalesce, and nullif. Replace nulls with a value, return the first non-null value from expressions, and generate nulls when appropriate.

Explore built-in aggregate functions in SQL Server to summarize data across rows, including sum, avg, min, max, and count, while handling nulls and using distinct and group by.

Master using aggregate functions in SQL Server to summarize data across rows, calculating totals, averages, and counts while handling group by, nulls, and distinct.

Explore null handling in aggregate functions, comparing sum, count, and avg and contrasting arithmetic versus non-null averages. Replace nulls with zero before aggregating, noting distinct implications.

Learn how to use the group by clause to group rows by columns, apply aggregates like count, sum, and max, and analyze data per group with correct query ordering.

Learn to use the group by clause in SQL Server to group rows, apply aggregate functions, and analyze data at multiple granularities for reporting and trends.

Understand how the having clause filters groups after group by, based on aggregate values like count and sum. Distinguish where versus having for row level and group level filtering.

Discover how the having clause filters grouped data after group by, using aggregates such as count, sum, and average to return only meaningful groups.

Master self-contained subqueries in t-sql by writing scalar and multi-valued examples, testing them independently, and comparing with joins for clearer, efficient queries.

Explore scalar and multi-valued subqueries in T-SQL, including self-contained subqueries that run independently. Use max to fetch most recent orders, and apply in predicates or joins for readability and maintainability.

Explore correlated subqueries in T-SQL by contrasting them with self-contained queries, understanding outer and inner query dependencies, and learning testing, debugging, and performance considerations.

Discover how correlated subqueries in T-SQL depend on the outer query and execute per row for dynamic calculations, with notes on testing, debugging, and performance.

Explore how the exists predicate accelerates existence tests in subqueries, compare it to count and not exists, and learn practical queries for matching and non-matching rows in SQL Server.

Demonstrates the exists predicate with subqueries in SQL Server to test row existence. Shows not exists and compares exists with count for efficient filtering of customers with or without orders.

Learn how to use views in SQL Server to query underlying tables, create reusable views with the create view statement, and leverage modularity, security, and API-like consistency.

Learn to create and query views, from simple employee phone lists to complex multi-table sales by employee and year. Set context, reuse logic, and drop views to stay clean.

Master inline TVFs to encapsulate reusable logic and return parameterized virtual tables in SQL Server, enabling dynamic, modular queries that combine the reusability of views with function flexibility.

Learn to use and create user defined functions in SQL Server, including inline table valued functions that return a virtual table and compute order line totals, enabling modular, reusable queries.

Master how to use derived tables to modularize complex queries by creating temporary virtual tables in the from clause, aliasing columns, passing arguments, and avoiding deep nesting.

Explore derived tables in T-SQL and learn to use inline and external column aliases. Pass arguments to derived tables and nest queries for modular, readable code.

Leverage with clause defined CTEs to modularize complex queries, reuse the CTE multiple times in the outer query, and compare their readability and flexibility with derived tables.

Explore common table expressions (CTEs) to simplify complex SQL queries by creating a temporary result set, with non-recursive CTEs for modular queries and recursive CTEs for hierarchical data.

Learn how to use the union and union all operators to combine rows from multiple queries, handle duplicates, and optimize performance in SQL Server.

Learn to use union and union all in SQL Server to combine queries, control duplicates, and produce distinct versus all results for merging data from multiple sources.

Explore how intersect and except operators compare data sets in SQL Server, returning distinct rows that are common to both or unique to one set.

Explore the intersect and except operators in SQL Server to compare datasets, identify overlaps and differences, and understand how order affects results and duplicate elimination.

Learn to use the apply operator in SQL Server, applying a TVF or derived table to each left row with cross apply or outer apply, and understand their filtering behavior.

Explore the apply operator in SQL Server, using inline tvf with cross apply and outer apply to fetch top three products per supplier, and compare cross apply with auto apply.

Explore the over clause in SQL Server to define windows for window functions, apply partitioning, ordering, and framing, and generate running totals, rankings, and row comparisons without self joins.

Demonstrates using the over clause in t-sql to compute rankings and running totals within windows, then builds views to show ordering and partitioning by category and product data.

Explore how to create windows with the over clause in SQL Server, using partition by, order by, and framing to compute running totals and cumulative sums.

Master window functions in SQL Server with the over clause. Explore aggregate, ranking, distribution, and offset functions using partitioning, ordering, and framing, including rank, dense rank, lead, and lag.

Explore window functions for calculations across rows while preserving granularity and use views as reusable virtual tables to simplify complex queries in sql.

Create views to simplify complex queries by joining employees, orders, and order details to calculate total sales per employee by year, enabling reuse for streamlined data analysis.

Explore window functions in T-SQL by ranking products with rank and dense_rank over unit price, and compare gaps versus dense_rank for reporting.

Explore window functions in SQL Server, including row_number, ntile, lag, and first_value, with category-based partitioning and unit price ordering. Learn ranking, bucketing, year-over-year comparisons, and cleanup of views.

Learn to reshape data in T-SQL by applying pivot and unpivot to rotate rows into columns and back, using grouping, spreading, and aggregation for analysis.

Learn to transform data with pivot and unpivot in T-SQL, rotating rows into columns and back while working with a sales category quantity year view.

Learn to pivot and unpivot data in SQL Server by creating a staging table and producing a pivoted category sales view, then unpivot to restore rows across 2006–2008.

Learn how stored procedures encapsulate T-SQL logic to query data, execute them with exec and parameters, and deliver reusable, secure, and efficient result sets.

Learn how stored procedures in T-SQL are reusable, named collections of SQL statements that encapsulate logic for querying or manipulating data with a consistent interface and parameterized queries.

Enhance a stored procedure by using alter procedure to add a num rows parameter with a default, enabling top rows through parameterized execution, and finish by dropping the procedure.

Master passing input parameters to stored procedures and using output parameters to return scalar values in T-SQL, and explore parameter metadata with the system catalog or SQL Server Management Studio.

Explore how to work with stored procedure parameters in T-SQL, including input and output parameters, and discover definitions using SSMS and system catalog queries to write flexible, reusable queries.

Query SQL Server metadata to inspect parameters of functions and procedures using T-SQL, including variables and object joins, with guidance on input and output parameters.

Create and test a T-SQL stored procedure with an input parameter to filter products by supplier id, then modify it with alter proc and verify its existence.

Explore passing parameters to stored procedures in T-SQL, test with variable declarations and output parameters, handle batch scope with go, debug typos, and clean up by dropping procedures.

Learn how to create, alter, drop, and test stored procedures in t-sql, including organizing scripts, using separation lines, and verifying procedure existence.

Discover, define, and use input and output parameters in stored procedures, explore parameters graphically with SSMS, and retrieve parameter metadata from system catalog views.

Create simple stored procedures in T-SQL to encapsulate reusable SQL statements that return result sets and accept input parameters, enhancing performance, security, and maintainability.

Learn to construct and execute dynamic sql in t-sql, using sp_executesql for parameterized queries with improved security and performance, and compare it to the exec method for dynamic sql.

Master dynamic sql in t-sql by executing runtime queries, comparing the exact statement and sp execute sql stored procedure, and leveraging parameters and query plan reuse for security.

Explore dynamic sql using exec and sp execute sql with a parameterized string. Define parameter data types, pass values separately, and avoid input in query strings to prevent sql injection.

Learn to create and submit batches, declare and use variables, and work with synonyms in T-SQL, mastering batch boundaries, scope, and object aliases for flexible, efficient scripting.

Explore t-sql programming elements by working with batches, variables, and synonyms; learn to drop existing objects, create test tables, execute batches with go, and validate syntax to avoid errors.

Explore core T-SQL programming elements, including declaring and assigning variables with declare, set, and select, plus using synonyms and batch execution to manage scope and objects.

Explore T-SQL control flow to implement conditional logic and loops with if else, while, and begin end, using break, continue, wait for, and return to control execution.

Explore control flow in T-SQL by using if-else and exists to conditionally execute blocks and handle procedure creation, drops, and messaging.

Learn to control T-SQL programs with if and else statements and a while loop, using begin blocks, declared variables, and break or continue to manage iteration and avoid infinite loops.

Raise errors and use throw in T-SQL to signal issues, capture error details, and set up alerts for critical conditions. Distinguish system and custom errors; custom numbers start at 50,000,001.

Master t-sql error handling by capturing the system error number immediately, raising and throwing errors, logging custom errors with sp_add_message, using with log, and setting up alerts for high-severity events.

Learn robust T-SQL error handling by raising custom errors with raise error, capturing details via the error system variable, and defining reusable messages with ESP_add_message.

Learn structured exception handling in T-SQL with try catch blocks to encapsulate error prone code, log error_number, error_message, error_severity, error_state, and error_line, and use throw for rethrows.

Explore structured exception handling in T-SQL with try catch blocks to isolate error prone code, handle errors gracefully, and retrieve error information like error number and message.

Explore how transactions extend batches to ensure all operations succeed or fail as a unit, preserving acid properties. Learn autocommit and explicit transactions with begin, commit, rollback, and try/catch.

Explore how transactions enforce atomicity and maintain data integrity in a multi-statement batch, using try catch for error handling with two simple tables: orders and order details.

Demonstrates executing a multi-statement batch inside a try-catch block, where some inserts succeed and others fail. Illustrates that transactions ensure operations succeed or fail together, preventing partial commits and inconsistencies.

Master how to control transactions in t-sql using begin transaction, commit transaction, rollback transaction, and exact abort to ensure atomic, consistent database changes.

Master explicit transaction control in T-SQL using begin transaction, try-catch, and commit or rollback to guarantee atomicity, consistency, isolation, and durability for multi-statement operations, and report error details.

Improve server query performance by writing selective queries, using clustered and non-clustered indexes, avoiding selecting all columns, and favoring set-based operations over cursors, while maintaining distribution statistics up to date.

Explore non-clustered indexes, their impact on query performance, and when to use or avoid them, including distribution statistics, execution plans, and the superiority of set-based methods over cursors.

Explore cursors versus set-based operations in SQL Server by calculating the maximum order date per employee with a cursor, then compare it to a more efficient set-based solution.

Compare cursors and set-based operations to optimize T-SQL queries for performance. The demo shows calculating max order date per employee with a cursor and contrasts it with a set-based solution.

Demonstrates using set-based queries to compute the maximum order date per employee and compares with cursors, highlighting faster, scalable performance in SQL Server.

Learn how SQL Server uses execution plans to analyze query performance, compare estimated and actual plans, view graphical plans in SSMS, and identify bottlenecks to inform indexing and tuning.

Explore how SQL Server execution plans and query performance data reveal how queries run and where to optimize with estimated and actual plans, statistics time, and statistics IO.

Learn to query system catalog views in the sys schema and use information schema views for standard metadata, plus system metadata functions to retrieve object_id and server_property values.

Explore SQL Server metadata by querying system views, information schema views, and system metadata functions to inspect server settings, database properties, and user tables, and resolve schema IDs with schema_name.

Query system catalog views and functions to access SQL Server metadata, including data types and columns, information schema views for standardized queries, and server property function outputs.

Learn how to execute system stored procedures in SQL Server using exec or shortcut, pass parameters by name, and retrieve metadata with system procedures like sys.databases, system tables, and sp_columns.

Learn to execute system stored procedures in SQL Server to retrieve metadata, manage databases, and filter results by schema or parameters, using exec and parameterized calls.

Explore dynamic management objects in SQL Server to retrieve real-time metadata and state information. Learn how DMOs are categorized and queried as views and table-valued functions for monitoring and troubleshooting.

Discover how to query dynamic management objects, including DMVs and DMFs, to monitor SQL Server performance, browse system objects, and inspect active user connections with Sysadm Exec Sessions.

Explore querying dynamic management objects to monitor active user sessions, group by login name, and identify dependencies using referencing entities, enabling performance monitoring and change-safe administration.