Microsoft SQL for Beginners (SQL Server and T-SQL)

Download and install the Microsoft SQL Server developer edition locally for development and training, not for production, using the SQL Server downloads page, the web installer, and a basic install.

Download and install SQL Server Management Studio to connect to your local SQL Server, run the installer, accept defaults, and launch SSMS to access the database engine.

Download the AdventureWorks sample database and restore it to your local SQL Server using SQL Server Management Studio, then explore its tables and run queries.

Learn basic SQL syntax and select statements, query tables using star or specific fields, apply aliases, and follow formatting and commenting practices in SQL Server.

Learn to write a select statement using distinct to return unique account type values and see how nulls are treated as their own distinct value.

Use the where clause to filter queries, retrieving a subset from the fact Internet sales by order day 2010-12-29 and exploring operators =, >, >=, and <=

Master how to filter data with where clauses and, or, not operators using currency key and promotion key, including distinct and parameterized queries in SQL Server.

Sort query results with the order by command, sorting by first name and last name. Switch between ascending and descending, or sort by multiple columns or column positions for clarity.

Use the top command after select to return a limited number of rows, such as top 5 or top 10, often with order by for sorted data.

Write a query to select the top 50 black products from the product table, filter by color, and order by english product name.

Explore max and min functions to identify the most expensive and cheapest products in the Jim Brady dataset, filtering out nulls and aliasing the standard_cost for clear aggregation.

Explore how the like command extends the where clause with wildcards to filter data in a currency table, using ampersand and underscore to match patterns and noting case insensitivity.

Learn to replace multiple or statements with a where clause using the in operator to filter by listed currency names and get the same results.

Learn to use the between operator in SQL Server to filter results within a range, shown as equivalent to >= lower bound and <= upper bound with reorder point examples.

Learn to use aggregation functions such as count, distinct count, average, and sum to summarize data, including the sum of standard cost and the average list price.

Understand null values in SQL, how they differ from empty strings and zeros, filter them with where clauses, and replace nulls with a chosen value using is null.

Explore how to use group by to aggregate data, apply max, average, and other functions, and group by single or multiple columns to create meaningful summaries from a product table.

Explore how the having clause filters grouped results after a group by, using max of safety stock level by product to illustrate conditions: max <= 4 or > 10.

Build nested subqueries and use them inside larger queries to filter data, such as returning product keys for a color filter in dim product, and note upcoming temporary tables.

Apply the case statement in SQL to transform query results, replacing values based on conditions, using distinct for readability, and chaining multiple cases to handle colors and numbers.

Create and use temporary tables in SQL Server by selecting into a #temp table, storing results in memory for the current session, enabling faster, modular queries.

Learn how to create common table expressions with the with statement, define in-memory CTEs, and use multiple CTEs in subsequent selects for streamlined queries.

Master sql date functions like getdate, datepart, dateadd, and datediff to compute days, hours, and formatted outputs from current date/time, while converting dates for reports.

Explore inner, left, right, and full outer joins to combine data from two tables, with where clauses and sample code; run a create table script to set up lab data.

Explore inner join concepts by combining two tables with the on clause to return only overlapping data, using select star and qualified names like table1.person_name = table2.person_name.

Left join returns all rows from the left-hand table and matching rows from the right table, with nulls for non-matches, and you can use a where clause to eliminate others.

Learn how the right join returns data from right table in SQL, including matches, and eliminates not present in both, with tips to flip from left join and add conditions.

Explore the full outer join, combining left and right joins to return all rows from both tables, including non-matches, enabling you to identify existing or missing relationships in your queries.

Learn how to join three tables using left joins and on conditions, expanding from two-table joins. Build complex queries by chaining joins across table1, table2, and table3, preserving unmatched rows.

Demonstrate how to write a 2014 sales query by joining fact internet sales with dim date and product tables, then grouping by product category to identify best selling items.

Learn how the union command stacks multiple tables vertically to form a single result set, removing duplicates when the tables share the same number of columns.

Learn to insert data into SQL tables with the insert into command, specifying columns like name and age, including a null name when no constraints exist.

Execute insert into select to copy rows from one table into another using select star, then verify by selecting from the target table to see the added records.

Update the table using the set command to change age values, apply a where clause to target only John rows, and verify results; use and clauses for more granular updates.

Learn how to safely delete data with the SQL delete command, using from and where clauses to remove specific rows like John without wiping the entire table.

Truncate table quickly wipes all rows from a table without removing the table itself. It cannot use a where clause, and this rapid command leaves the table empty.

Explore views as dynamic select statements that run at query time, not storing data; create or alter views, name them, and query them to return updated columns.

Learn to create and execute stored procedures in SQL Server, pass parameters like account type to filter results, and implement multiple steps or complex logic.