The Ultimate T-SQL and Microsoft SQL Server Bootcamp

Describe SQL Server as a relational database management system (RDBMS) with a database engine that stores and retrieves data and creates databases and components such as tables, views, stored procedures.

Master the basics of SQL, a language for manipulating and defining data across databases. Explore statements, clauses, and CRUD operations to write effective queries.

SQL skills are in high demand in IT, opening career opportunities in data analysis and software development. Learn how SQL enables database management, data integration, and efficient querying across tables.

Explore SQL alternatives such as prequel, GraphQL, Malloy, and Nickel, highlighting how each aims to simplify data querying and JSON handling, including Couchbase integration.

Install SQL Server on Windows using the free express edition for development, and set up SQL Server Management Studio while learning about instances to connect to databases.

Run SQL Server on macOS or Linux with Docker, pulling the SQL Server image, setting an SA password, and port mapping; connect with Azure Data Studio.

Learn to navigate SQL Server Management Studio (SSMS), connect to instances, write and run SQL queries, and save or create databases using both scripting and the UI.

Discover how Azure Data Studio compares with SSMS, offering dark mode, multi-engine connections (including Postgres), and streamlined querying and scripting for database development.

Set up the Adventureworks database with pre-populated schemas and data, and learn how to back up and restore the database throughout this course.

Learn how to set up the AdventureWorks database by restoring a compatible backup in SQL Server, including checking server version, downloading the right OLTP backup, and resolving version mismatches.

Explore the Adventureworks 2019 database structure, including tables, views, and schemas; learn to access objects via schema qualifiers, the default dbo, and user-defined schemas.

Connect to a sql server, open a new query window, create database Adventureworks with create database, then execute to verify the database appears, using camelcase or underscores and avoiding spaces.

Drop databases with the drop database command and manage repeatable SQL scripts by creating multiple databases with different names, executing with F5, and commenting out lines with double dashes.

Create an employees table in Adventureworks database with an identity primary key and not null varchar(50) for first and last names, plus a nullable birth column using date time too.

Alter existing tables and create new ones to establish relationships between employees and job titles using primary keys and foreign keys in SQL scripts.

Explore adding unique and default constraints to a SQL Server table, beyond not null. See how to enforce a unique employee ID with varchar(10) and use getdate() for employment date.

Learn to alter tables by dropping columns and constraints, change data types like date, and name or reapply constraints such as unique employee ID using alter table.

Disconnect all queries from the Adventureworks database, then drop the database to clean up the environment. Refresh the database list to confirm removal and save scripts for future reference.

Learn to master select statements for read operations on the Adventureworks database, using use and from, with schemas, then retrieve all or top records and select specific columns.

Learn to filter data with where clauses, using literal strings and multiple conditions, then sort results with order by on hire date and other columns.

Learn to filter data with wild cards using the like operator, matching patterns in job titles such as marketing, and use starts with, ends with, and contains to refine results.

Alias columns in T-SQL to improve readability by renaming with spaces using square brackets, and choose between using as or omitting it for clear aliases.

Explore how to join data from multiple tables to create a single view, using foreign keys like department ID and business entity ID, with a focus on normalization and analysis.

Learn to join multiple tables with inner joins and aliases to build a comprehensive employee report, linking employee, department history, and department tables.

Master left joins, inner joins, and mixing join types to link work orders, products, and scrap reasons in a production schema, using aliases and handling nulls.

Explore how union joins two datasets from employees and customers into one, using the same selected columns, and see how union removes duplicates while union all preserves every row.

Learn how distinct eliminates duplicates in select queries and how group by clusters records, counts aggregated occurrences, and requires non-aggregated columns to be in the group by.

Explore aggregate functions in SQL, including count, sum, avg, min, and max, and learn to use group by, having, and aliases to analyze sales data from the order header table.

Join the person, salesperson, and sales order header to compute per-salesperson totals with aggregate functions including sum, average, min, max, and count, grouped by first and last name for display.

Learn string manipulation in SQL by building a full name column with concatenation or the concat function, and format currency values using format with currency.

Export data from the management studio to Excel by saving results as a CSV or by using copy with headers for clean reporting.

Explore subqueries and inner selects to compute averages and filter employees by vacation hours. Use derived tables, joins, and aliases to compare against job-title averages.

Explore common table expressions (CTEs) to define a temporary named result set that you can query, and learn the syntax, scope, and practical use in SQL statements.

Explore window functions in SQL, using an over clause to define a window of rows and compute aggregates, ranking, and value functions with partition by and order by.

Explore windows aggregate functions to compute max and min unit prices, using over instead of subqueries, and apply partition by for sums by sales order ID.

Learn to use row_number, rank, and dense_rank with over clauses, including partition by and order by vacation hours, and see how cte enables ranking results.

Learn how lead and lag window functions access upcoming and previous rows in a product table, using an order by clause with optional partition by, offset, and default values.

Compare Isnull and coalesce in SQL Server, showing how to replace null values with meaningful defaults in reports, and noting two-parameter Isnull versus multi-parameter coalesce and cross database behavior.

Master the full insert in t-sql with insert into schema.table, a complete column list, and values aligned by order, using getdate for the modified date.

Explore partial inserts in SQL Server, where you provide values for selected columns while defaults fill gaps, and understand constraints that govern non-null columns and defaults.

Insert related data by ensuring foreign keys exist, using composite primary keys, partial inserts, and default constraints to generate ids.

Learn how to use select into to copy tables, back up data, create empty table shells, and snapshot specific columns across databases, while noting constraints aren’t replicated.

Master full and partial insert statements, including schema, table, and column alignment, multi-row inserts, and foreign key considerations. Explore select into to create new tables and databases from existing data.

Learn how to use the update and delete statements in SQL Server to modify and remove data. Practice updating rows, using joins and table expressions, in Management Studio.

Master the update statement in t-sql, updating single or multiple columns with where clauses, joins, and from clauses, and optimize with common table expressions.

Explore deleting data with the delete statement and when to use truncate, including where predicates, transaction log implications, identity behavior, and referential integrity constraints.

Explore cascade on delete to automatically remove related records and preserve data integrity across foreign key relationships, using a departments and employees example in SQL Server bootcamp.

Create reusable views to centralize complex joins across multiple tables, such as employee data from Adventureworks, by wrapping a select query and exposing a single snapshot via a view.

Learn how to manage views in SQL Server by creating, altering, and dropping views, and aliasing first and last name columns to tailor results.

Learn how to declare and use variables in T-SQL, store values with set or select into, and print results. Apply variables for calculations, such as a multiplier to scale figures.

Explore scalar valued functions in SQL Server, using built-in functions like year, month, day, and get date, plus creating your own average sales by year function.

Learn to build table valued functions that return a table, compare to scalar valued functions, and query products and inventory by location and quantity.