Complete Microsoft SQL Server Masterclass:Beginner to Expert

Master the where clause to filter records by conditions, using equals and greater-than, with birth date and year function examples, and apply it to select, update, and delete statements.

Master the comparison operators in the where clause, including =, <>, !=, >, >=, <, and <=. Explore examples with id and country region codes, and learn two-column comparisons.

Learn arithmetic operators in sql: plus, minus, multiply, and divide for use in select and where clauses. Understand aliases with as, the execution order, and division by zero pitfalls.

Learn how to use and, or, and not in the where clause to test multiple conditions, combine criteria with precedence and parentheses, and apply between and in operators.

Use the plus operator to concatenate title, first name, middle name, last name, and suffix into a full name, with spaces and null handling. Apply it in where clauses.

Learn to concatenate strings in SQL using the concat function, remove null values, add separators with concat underscore ws, and nest functions to build domains and emails.

Learn how to handle SQL Server null values using is null and is not null to filter records with missing or defined values, such as products where color is null.

Master how to filter records with the between and not between operators in a where clause, including inclusive numeric ranges and date-time ranges, with cast to date time when needed.

Learn how to use the cast function in SQL Server to convert date time to date and normalize date inputs, ensuring consistent results across complex date ranges.

Explore the in and not in operators in SQL where clauses, testing values in a fixed set with practical examples using product IDs and state province codes.

Master the like operator with the percent wildcard to match in SQL where clauses. See how starts with, contains, or ends with keywords apply on the human resources employee table.

Explore character classes in SQL Server's like operator, using square brackets to match single characters, and learn patterns with % wildcards and multiple class combinations.

Master SQL patterns with character ranges using square brackets to match specific letters and digits, including starts with l, i to n ranges, and hyphen-based formats.

Discover how to use the negation character with the SQL LIKE operator by applying caret inside square brackets to exclude specific characters or ranges, with practical examples.

Master the like operator in SQL using underscore as a single-character wildcard to match exact lengths. Implement patterns like starting with abc and four characters, or s_ for types.

Apply the distinct keyword to retrieve unique person types. Use not like with s_ to filter out records that start with s followed by one character, testing your SQL skills.

Learn to escape characters in SQL queries using the like operator, including handling commas and dots. Discover why single quotes are special and how doubling them prevents errors.

Master the order by clause to sort results in ascending or descending order, with default ascending and nulls treated as lowest values, and apply it to multiple columns.

Sort results with the order by clause, including outside select columns. Use ascending or descending order and the length function to sort by birth date or address line one length.

Use the order by clause with offset and fetch to limit or skip rows, compare offset with top, and apply ascending or descending hire date on the employee table.

Master ordering results with top, offset, fetch, and order by, and explore top with ties and top percent through examples like production.product and sales order detail.

Learn how to group records by columns using the group by clause, apply aggregate functions like sum to total order quantities per sales order ID, and handle grouping errors.

Learn how to use the group by clause with and without aggregate functions on a department table, highlighting unique group names and the proper use of count.

Use group by to sum values per category, such as total rate per pay frequency from the employee history table, and sort the results with order by.

Explore numerical and aggregate functions in SQL Server, including sum, avg, max, min, and count, with practical examples and a look at less-used numerical functions.

Learn how the sql sum() function computes the total of non-null values, use with all or distinct keywords, and compare sum(field), sum(all field), and sum(distinct field) for aggregate results.

Compute avg to find the average of non-null values, ignoring nulls, and use group by make flag with standard cost; count records with count, including distinct and color not null.

Explore the max() and min() aggregate functions in SQL Server, returning non-null maximum and minimum values. Learn aliases, group by, having versus where, and order by with these aggregates.

Explore less used numeric functions in SQL Server, including abs, ceiling, floor, rand, and round, with practical examples to generate integers and bounded random values.

Master string functions in sql server by using charindex to locate substrings in a main string with an optional start position, illustrated on a product table.

Discover SQL Server string functions such as concat, concat_underscore, data length function, and format function, with examples that count bytes, include leading and trailing spaces, and format dates.

Learn to manipulate strings with left, right, and length functions in SQL Server, extracting characters from the left or right and calculating string length with practical examples.

Explore string functions in SQL Server, including lower, upper, and trim variants, to convert case and remove leading or trailing spaces in table data.

Explore pat index to locate a pattern’s first position in a string, use replace to substitute substrings, and apply replicate to repeat a string multiple times with real examples.

Learn to use reverse, str, and substring in SQL Server to transform and extract strings, with examples of reversing first names and extracting last-name substrings using starting position and length.

Master sql date manipulation with date add and date diff, using interval tokens from year to nanosecond, adding years, quarters, months, days, hours, minutes, seconds, and milliseconds; filter null results.

Learn how to extract date parts using date part, day, month, and year functions in SQL Server, including examples that pull year, month, and day from a date field.

Explore date functions in SQL Server, including current timestamp, get date function, and Sis date time and Sis date time offset, highlighting precision and timezone differences.

Explore isdate, isnull, and isnumeric functions in Microsoft SQL Server to validate dates, handle nulls, and test numeric expressions using practical examples.

Learn how to use coalesce for the first non-null value, nullif for comparison, convert for type changes, and iif for conditional results.

Explore the having clause and its use with group by to filter aggregated results; learn why where cannot filter aggregates, with max and sum examples.

Explore sub queries inside a main query using the in operator in a select statement, demonstrated with business entity id from employee and the human resources employee department history table.

Learn to use subqueries to find second maximum or minimum unit price in purchase order detail with top and min, and compare to the average list price from product table.

Uses exists with a subquery to return results in the outer query only when the inner query is true, illustrated with employee and department tables.

Learn to nest subqueries by placing one subquery inside another, matching product subcategory id to product categories and using distinct to return unique results.

Use a subquery as a column expression to fetch details from related tables, such as purchase order header and detail, and compute sum of order quantity per purchase order id.

Learn to use a subquery in the from clause to compute the average of the minimum unit price per purchase order ID, including proper aliasing and handling of nested aggregates.

Learn how any and all operators work with subqueries in sql, returning true when any or all values meet the condition, with examples using purchase order detail and header tables.

Explore how any operator works in subqueries, comparing due date with order date in purchase order detail, and why equals any excludes records while greater-than-or-equal-to includes more results.

Explore subqueries with the all operator in SQL Server, which returns true only if all subquery values meet the condition, and compare with any and not equal cases.

Combine the results of multiple select statements using union or union all. Union removes duplicates, while union all preserves them.

Master union and union all by joining purchase order detail and header, emphasizing data type compatibility (int to money) and how duplicates are removed.

Explore union and union all merging rows across tables, compare with join, and illustrate with employee, person, vendor, customer, and salesperson data to build a master business entity id list.

Explore how union and union all behave with compatible data types in SQL Server, highlighting pitfalls when combining integers with nvarchar, and learn strategies for concatenating names and ordering results.

Explore how union and union all differ. Union removes duplicates, union all preserves them; use order by to sort results, such as purchase order ID in descending order.

Learn how the intersect operator returns distinct rows common to two queries, ignoring nulls, and ensure equal numbers of expressions across selects.

The except operator returns distinct rows from the first query that do not appear in subsequent queries, demonstrated by comparing sales order detail against customer data.

Explore implicit joins by querying purchase order detail and header to fetch order id, date, ship date, and quantity using aliases, with a where clause.

Learn to use inner join to combine three or more tables: product, product subcategory, and product category, using aliases and on conditions to select product id, name, and category ids.

Explore left join and left outer join behavior, returning all records from left table and matching rows from right, with nulls for non matches, illustrated with syntax and examples.

Explore left join techniques in the complete Microsoft SQL Server Masterclass to retrieve all customers and match on customer ID and business entity ID, returning nulls when no match.

Modify a left join to compare person ID with business entity ID, handle nulls with is null and is not null, and note table order matters.

Explore right join in SQL Server, a right outer join opposite of left join, returning all records from the right table and matching left records, with syntax and examples.

Analyze full outer join by combining left and right joins, using table aliases, and interpreting nulls when records do not match, with a venn diagram intuition and example results.

Learn how cross join creates a cartesian product by pairing rows from two tables. Explore the comma and explicit cross join syntax and warn against large-table use.

Learn the essential data types in MS SQL Server, including numeric, string, and date/time types, plus special types like decimal, money, timestamp, and xml, with guidance before creating tables.

Learn how to create and drop databases in SQL Server, using both GUI (new database) and the create database command, including database files, ownership, and verification.

Apply a consistent naming convention for tables, columns, and procedures to avoid confusion, using plural names, no whitespace or special characters, and PK and key prefixes for keys.

Learn to create tables with the create table syntax, define a table name and columns with data types, including identity autoincrement and default dbo schema.

Create the stores table under the retail schema and drop the existing DBO.stores to prepare for the next lesson.

Master how to create tables with constraints, including not null, unique, check, default, primary key, and foreign key, using proper create table syntax and relationship concepts.

Explore check constraints in SQL Server, learn the syntax to create tables with min quantity checks, and see how to enforce conditions on single or multiple columns.

Explore how to apply a unique constraint in SQL Server to ensure each value in a column or across columns is unique, using create table syntax and optional constraint names.

Learn to use default constraints in SQL Server create table statements, defining default values by data types and schema, including using the get date function for current date.

Learn how a primary key uniquely identifies each row, and how it differs from a unique key constraint. See single and composite primary keys and their relation to foreign keys.

Explore foreign key constraints that link child and parent tables. Learn the syntax and on delete/update rules, including cascade, set null, set default, and no action for referential integrity.

Create a parent table with a primary key and a child table with a foreign key to link item status to product details, demonstrating constraints and references.

Learn how to link a child table to a composite primary key using a foreign key, and why a unique constraint on the referenced column is required.

Learn how unique indexes enforce non-duplication and accelerate query performance. Create a unique index to ensure unique values and support foreign key links between related tables.

Learn how to check for a unique index in SQL Server, identify non-clustered unique indexes on the store prods table, and verify the index status across tables.

Learn how to create non-unique and unique indexes in Microsoft SQL Server, using CREATE INDEX syntax on tables and columns like item name and item id, with naming conventions.

Learn how to use the insert statement in SQL Server to add records with or without specified columns, including identity auto-increment behavior.

Learn to insert records into a constrained table, enforcing not null and primary key rules, manage composite keys with store_id and prod_id, and handle nullable threshold values and unique indexes.

Explore changing the column order in an insert statement by listing columns in any order and providing matching values, such as item status and item quantity, regardless of creation order.

Insert records into a child table with a foreign key only after the parent record exists, demonstrated using item status and product details and autoincrement keys.

Use the update statement to modify existing records by setting column values, with an optional where clause to target rows, supporting updates to store number, email, and address.