Mastering Microsoft SQL Server [2025]

Master the cast and convert functions to change data types, such as casting decimal to integer, format dates, and extract numeric values from strings for accurate reporting and calculations.

Explore inner, left, right, and full outer joins to combine employee and department data. See how nulls appear when there is no match and how each join type affects results.

Master self joins and cross joins in Microsoft SQL Server by joining a table to itself to reveal hierarchical data and generate pairs, including filtering pairs with different managers.

Compare union and union all to merge data from multiple queries, noting that union yields distinct results by removing duplicates while union all keeps duplicates.

Explore cross apply and outer apply to sql server, applying a table-valued function to each employee to list projects and show behavior like inner and left joins.

Use not in to filter out customers from New York and like to find names starting with e, shown on the customers table.

Learn how to use SQL Server's order by to sort results in ascending or descending order, with single or multi-column criteria and calculated columns like length of product names.

Master common table expressions (CTEs) to create temporary named query results, reference them in larger queries, and simplify complex SQL while improving readability.

Explore window functions row_number, rank, and partition by to assign department-specific, salary-ordered sequences and ranks for analytical queries. Compare rank with dense_rank and see department-based sequencing using salary.

Learn how rank and dense_rank differ when ranking employees by salary in descending order: rank may skip numbers after ties, while dense_rank assigns consecutive ranks without gaps.

Explore how to use case statements for conditional logic in SQL queries, including classification, aggregation, nested cases, and calculations for salary and bonuses.

Pivot data to turn rows into columns and unpivot to turn columns into rows for reporting and analysis, using aggregation and IsNull to handle missing values.

Learn how to group data by one or more columns, compute aggregates like sum, count, average, min, and max, and filter results with having for meaningful summaries.

Learn how to use the having clause with group by to filter grouped data by aggregate conditions, applying sum and average thresholds on the sales table.

Discover how rollup and cube extend SQL group by to produce hierarchical subtotals for multidimensional analysis in data warehouses, with rollup for top-down totals and cube for comprehensive breakdowns.

Master data inside SQL tables by learning single and bulk insert methods, using insert into values and inserting from another table, and ensuring data integrity.

Learn to update and delete data in SQL using update and delete statements, with set and where clauses to adjust IT salaries in the employee table, and prevent unintended deletions.

Master the merge statement to upsert data by updating, inserting, or deleting in one operation for synchronizing data between the employee table and the employee update source.

Use begin transaction, commit, and rollback to transfer 5000 from Kathy to Bob as a single unit, ensuring atomicity and data integrity.

Learn how stored procedures reuse SQL code to boost performance and security, encapsulating logic in a container and guiding you to create procedures with parameters, body, and return values.