Microsoft SQL - The Complete Guide for Beginners (TSQL)

Explore basic sql fundamentals by understanding instances, databases, schemas, and tables, with examples from Adventureworks and the dbo default, and learn to retrieve data using queries.

Learn to write the select statement in SQL Server, use the new query window, execute queries, and read results in the result and messages windows, including practicing with literals.

Learn to build select statements with multiple columns, alias columns (including spaces via brackets), and run queries with F5 or Alt X, noting five divided by two yields two.

Save and retrieve queries in the editor, then select from person table in person schema of AdventureWorks using first name, last name, and middle name, or * for columns.

Learn to use top to limit results and distinct to return unique rows in a select statement, with examples using first, middle, and last names.

Learn to alias columns and tables in SQL select statements using as, improve readability with capitalization, and apply optional table aliases like persons to clarify references.

Learn to use select top to fetch limited rows, explore schema and table naming like human resources.employee, and use right-click to auto generate top 1000 queries with optional database naming.

Filter records with where clauses to retrieve specific rows, e.g., business entity ID equals five, not equal to five, or less than five, and note double-hyphen and multi-line comments.

Explains using the where clause to filter rows by first name or modified date, showing equals and less-than comparisons and common date format pitfalls.

Use the where clause with and, or, and brackets to combine conditions and refine queries. Filter by first name, last name, email promotion, and business entity id for precise results.

Explore the between operator in SQL, used in the where clause to filter a range with endpoints; it works for numbers, dates, and text, such as between 10 and 20.

Learn the in operator in sql, a shorthand for multiple or conditions that matches a column value against a list, making queries like first name in (Michael, Amy) cleaner.

Learn how the not operator and not in clause exclude specific results in SQL queries, reverse conditions, and apply these techniques to simple selects from a person table.

Learn how null values differ from empty strings and how to use the is null and is not null operators to filter records in SQL queries.

Learn to use the like keyword, with percent and underscore wildcards for pattern matching, and how like differs from = for exact matches in SQL.

Learn how underscores and percent signs power pattern matching with the like operator in SQL, enabling precise character, position, and range searches, including starting with certain letters and range patterns.

Sort a query's results with the order by clause, using ascending by default or descending as needed; arrange by first_name or last_name in examples.

Learn how to sort data by multiple columns using order by, first sorting on last name ascending and breaking ties with first name ascending or descending, illustrated with Adams examples.

Learn how to order query results by column numbers in t-sql, using last name asc and first name desc, with where email promotion is zero before order by.

Explore aggregate functions by counting rows with count, count(*), and count(column), highlighting non-null versus distinct values and filtering results with where conditions.

Explore common aggregate functions in sql, including min, max, sum, and average on sales.tax_rate, with optional where clauses and aliases, preparing for group by analysis.

Learn to use the group by clause with aggregate functions to sum quantity by location ID. See a practical example yielding total quantity per location and an alias.

Filter data with a where clause on the modified date, then aggregate using group by, and finally order by location ID descending.

Learn to use multiple aggregate functions with group by in SQL, displaying location, shelf, and bin counts alongside total quantity, and adopt as aliases for readability.

Explore how the having clause filters group by aggregates, showing why where can't filter totals and how to apply conditions on sums like total quantity in SQL Server.

Learn how to optimize sql queries by using where and having, filter by location id, and ensure having filters apply only to aggregates for better performance.

Learn how to use the case statement in SQL to apply conditional logic, returning male, female, or unknown for gender, using when, then, else, and end.

Learn how to use case expressions to categorize vacation hours into low, mid, and high, then apply group by to count employees per category.

Apply case expressions in the where clause to filter rows by computed booleans, returning 1 for true and 0 for false, and combine with like and vacation hours checks.

Explore inner join theory by combining two tables on a common key and returning only matching records. See a customer and orders example to verify the results.

Explore inner join in action by joining production.product and production.inventory on product_id, using aliases p and pi; see how matching rows appear and understand inner join versus join.

Practice inner joins and column selection by joining human resources employee and person with aliases to resolve ambiguous business entity ID and display job title, first name, and last name.

Join three tables in SQL to retrieve product id, product name, location name, and inventory details by inner joining product, product inventory, location, and selecting shelf, bin, and quantity.

Join three tables using inner join to see how null product subcategory ids are excluded, then learn to display subcategory names when they exist while listing all products.

Explore left join (left outer join), which returns all rows from the left table and matching right-table rows, using nulls for unmatched records in examples with customers and orders.

Right join, also known as right outer join, returns all records from the right table and the matching records from the left, with nulls where there is no match.

Learn how to use joins (inner, left, outer) with where clauses, merge filtering with ordering, and order results by quantity when quantity is greater than 700.

Learn how to use self join with inner join to compare records within the same table by matching territory ids, and optionally display first and last names.

Discover cross join in SQL, which returns the cartesian product of two tables without on clauses, yielding all possible row pairs; use with care due to potentially enormous results.

Explore union and union all as SQL set operators that combine results from multiple selects; union removes duplicates, union all preserves them, with matching column counts and order.

Show how to union customer and salesperson by joining person and email address on business entity IDs, add a contact type to distinguish sources, and compare union with union all.

Explore how intersect and except filter query results by common and unique rows, compare product IDs, and contrast with union and union all.

Explore essential SQL string functions such as upper, lower, length, and trim, with practical examples using literals to show how to normalize case, measure length, and remove whitespace.

Learn to combine SQL functions, trim spaces before measuring length, and use left and right to extract the first or last characters.

Learn to extract parts of a string in SQL using substring: specify the expression, a 1-based starting position, and a length; spaces count as characters.

Learn to use the charindex function to find a comma's position, then apply substring with the found index to extract the following text, handling spaces and optional starting index.

Learn how to use SQL Server's replace and reverse functions to modify strings, including replacing a comma with a space and reversing 'hello'.