SQL Development with MS SQL Server Beginner to Master 2026

Learn the theory behind the basics by creating databases, schemas, and tables, defining data types, defaults, and nullability, and performing inserts, alterations, and selects.

Learn to connect to a local SQL Server on Windows using Windows authentication in Azure Data Studio, configure the server name localhost, and handle the trust server certificate.

Create and manage a dedicated test database in MS SQL Server using create database, drop if exists, and use, while avoiding the master database and learning go for multi-query execution.

Create an inventory schema to organize data within a test database, using go statements, drop if exists, and batch sequencing for future tables and scalar variables.

Explore scalar variables in MS SQL Server, including declare with @ names, set and select usage, and core data types like int, varchar, and date time.

Create your first furniture table in the inventory schema, defining an id with identity and fields for furniture type, name, price, quantity, release date, create date, and update date.

Learn to define null and not null constraints and apply default values in SQL Server, using a furniture table and quantity default of zero to ensure valid inserts.

Insert a new row into a table by using insert into with explicit fields, omit identity columns, and provide values including ISO dates and proper decimals.

Master how to write and run a basic select against the furniture table, use intellisense and aliases to qualify fields, and return only needed columns.

Learn the basics of the update statement by changing a table’s quantity from 34 to 40 using set, and note that without a where clause all rows update.

Master the alter table statement in MS SQL Server to add, drop, or alter columns, set null or not null, and manage default constraints.

Create and use custom types in SQL Server by defining a dbo string from varcar 255, then apply it to variables and tables, while inspecting user defined versus system types.

Restore the AdventureWorks 2019 backup to set up database, then explore top, where, and operators (=, <, >, not equal), plus like, in, between, delete, update, into, and truncate.

Move the AdventureWorks backup file from downloads to the proper backup location so Azure Data Studio can access it, using two Windows Explorer tabs to copy and paste the path.

Restore the Adventureworks 2019 database on Windows by using a restore file list to verify the backup, then place the MDF and LDF files in the correct SQL Server path.

Learn how to copy a backup file into a docker container on mac or linux, using docker ps, container id, and docker cp to place AdventureWorks 2019 in /var/opt/mssql/data.

Learn to restore a dockerized sql server database by placing the backup in the correct data folder, verifying file names with a restore file list only, and restoring Adventureworks 2019.

Run your first select on the AdventureWorks 2019 database, pull all from Human Resources.Department, and use top seven to view the first seven results; discuss cross apply and outer apply.

Explore the where clause to filter sql results using comparisons, like, in, and is null. Learn to apply begins with and between ranges on department id, name, and group name.

Learn to use the delete keyword in SQL Server with a where clause, validate with a predelete select, and safely remove related rows while handling foreign key constraints.

Use the update statement with a where clause to modify a targeted row or rows identified by a unique id, such as renaming research and development to R and D.

learn how into keyword turns a select into create statement to copy a table into a new one, like department copy, with caveats about insert and drop table reuse.

Explore how the truncate keyword empties a table and resets the identity seed, contrasting it with delete, and observe how inserts start with the seed again.

Sort records with order by, remove duplicates using distinct, and summarize groups with group by and aggregates (count, sum, min, max). Use aliases for columns and understand the having clause.

Use the order by clause to return results sorted by group name, then by name within each group. Sort by multiple fields with ASC or DESC per field.

Demonstrates using the distinct keyword to return unique value sets and remove duplicates in a select, focusing on group name and department fields and introducing the group by concept.

Learn to use the group by clause with aggregate functions to count, sum, and average values by group, apply distinct counts, and order results with department examples.

Alias aggregates with as keyword, such as min and max department id. Rename fields and tables, view fully qualified field names, and avoid aliases in group by or order by.

Explains the having clause, filters using aggregate functions after group by, contrasts it with where, and shows the proper order of where, group by, having, and order by.

Master SQL built-in functions for null handling, is null, coalesce, and nullif, casting and converting data types, plus date functions like getdate, dateadd, datediff, case, and string_agg.

Explore how the isnull and coalesce functions handle nulls in MS SQL Server, showing how to compare nulls, provide defaults, and coalesce multiple fields using the person table.

Explore how nullif negates specific values to produce nulls and how is null and coalesce set defaults. Wrap nullif in is null to replace values with a title.

use the cast function to explicitly convert different data types so a single search value can compare with both an integer and a unique identifier, avoiding implicit conversions and errors.

Explore the convert function in SQL, showing how numeric keys format dates and money as text, including ISO strings and two- or four-decimal placements.

Explore the getdate function in SQL Server that returns the current date and time, and learn how to use it to timestamp a row's creation.

Learn the date add function in SQL Server, using the date part, the amount, and the base date to add or subtract time.

Explore how datediff calculates the difference between two dates in years using getdate and dateadd, showing the second minus the first and leap year effects.

Learn to use the case statement in SQL Server to derive has middle name from the middle name field in the person table, including nested cases and ordering.

Explore the built in string_agg function to aggregate department names into a single group row, using group by and separators, in SQL Server 2016 and later.

Explore how to use the stuff function with for xml path to replicate string_agg on older sql server versions, constructing comma-separated lists and removing headers for clean xml output.

Explore joining data from multiple sources with inner, left, right, and full joins, plus outer apply, cross-apply, union, and accept to shape results.

Explore inner joins in SQL Server by matching records across tables with a join key, aliases, and the on clause, illustrated using the employee department history and the person tables.

Explore how left join differs from inner join by including all records from the left table and only matching records from the right, producing nulls when no match exists.

Discover how the right join preserves the right table while matching from the left, contrasting it with left and inner joins, and note that right outer join equals right join.

Explore the full outer join and learn how it includes all records from both tables, with nulls where there is no match, unlike inner, left, or right joins.

Explore outer apply, a left-join-like technique that attaches query results to each department row, enabling aggregate data and single-row lookups.

Explore cross-apply and contrast it with outer-apply, showing cross-apply returns only matching rows like an inner join, unlike outer-apply's left join, and note right join is less common.

Explore how unions combine separate selects to add rows vertically, learn when to use union all to preserve duplicates, and align columns for reporting and data warehousing contexts.

Learn how the Accept clause filters out records by matching or not matching between two data sets, ideal for partial loads in data warehouses and reporting databases to avoid duplicates.

Explore how indexes organize data and speed searches in SQL Server, comparing clustered and nonclustered indexes, includes clause, unique constraints, primary keys, and composite keys.

Learn to use go to definition (F12) in Azure Data Studio to view a table's declaration, copy its create table script, and prep for a clustered index.

Create a clustered index on the department ID to organize data physically, learn that only one clustered index per table exists, and follow naming conventions like cix_department_id.

Learn how non clustered indexes differ from clustered indexes, enhance lookups, and use the includes clause to add fields like modified date for better query performance.

Apply a unique constraint to a field or a combination of fields to ensure values appear only once, explore how it ties to clustered and non clustered indexes and keys.

Explore the concept of a single-field primary key in SQL Server, its role as a clustered index, its uniqueness, and how it speeds joins and deletes.

Explore composite keys in SQL Server, learning how combining multiple fields creates a primary key with a clustered index and enforces uniqueness with constraints.

Discover the theory of window functions, how a top-down window exposes prior rows within partitions, and use row number, rank, dense rank, ntile, lag, lead, first value, and last value.

learn to use the row_number window function to assign sequential numbers. demonstrate how partitioning by person type and ordering by first name reveals the top-down window as rows accumulate.

Explore the rank window function and how it handles duplicates, contrasting with row_number in different partitions, and see why identical values share the same rank.

Learn how dense_rank differs from rank and when to use it to assign consecutive placements without gaps, handling ties and distinct names, and an intro to ntile.

Explore ntile, the end tile window function that splits a partition into N groups and numbers them by order, revealing partition sizes with row numbers.

Explore how the lag window function retrieves data from the previous row, such as the first name, within a partition and order, with the first row yielding null.

Discover how the lead function retrieves data from the next row in MS SQL Server, contrasting with lag, and explore partitioned and non-partitioned layouts and end-of-partition nulls.

This lecture introduces the FIRST_VALUE window function in SQL Server, pulling the first row in each partition based on an order by clause, with name examples.

Explore the last value window function, why it mirrors the first value, and how reversing the first value or using rows between unbounded preceding and unbounded following yields last value.

Explore advanced SQL concepts by building and calling stored procedures with parameters, apply offset-based pagination, use nested queries and temporary tables, and implement if/else and transactions.

Learn to create your first stored procedure in MS SQL Server using create or alter, with the Human Resources schema and a departments get query, enclosed by begin and end.

Learn how to add parameters to a stored procedure, pass them by name or position, and filter results by a group name parameter in a department context.

Enable nullable parameters in a stored procedure, use default values and is null or coalesce to filter by group name and department name.

Use the offset and fetch next clauses to paginate results in a stored procedure, driving pages by page number and rows to return with a required order by.

Learn to retrieve employee's recent pay rate from pay history using a row number window function in a nested query, partitioned by business entity ID and ordered by modified date.

Use into to create a temporary table that holds the most recent pay rate by business entity ID, then join to retrieve the data.

Learn best practices for temporary tables in stored procedures, including drop-if-exists checks, compatibility with older sql server versions, and adding a clustered index to speed joins.

Master if and else in SQL using a print statement to show outcomes based on a table's record count, with thresholds like 200 and 300 in the messages tab.

Learn how transactions begin, create isolated environments to commit or roll back changes in SQL Server, and how locking and blocking affect concurrent access.

Celebrate completing the course as you master MS SQL Server and T-SQL, ready to write real-world queries, build solutions that interact with databases, and explore API development or report development.