Microsoft SQL Server 2019 - Querying SQL Server

Explore how T-SQL extends ANSI SQL with server-specific features and dialects. Learn naming rules with spaces using brackets, plus how Management Studio can highlight and execute selected statements.

Explore the logical order of operations in select statements and how the data engine executes them, including select, from, where, group by, having, and order by.

Learn to write basic select queries, remove duplicates with distinct, use column and table aliases, and apply a simple case expression with case when then else.

Master writing simple select statements by using select and from, pulling all columns with select star or listing specific columns from a table, including the human resources schema.

Use the distinct keyword to remove duplicates in sql queries, whether selecting one column or multiple. Observe unique job titles and distinct title and gender combinations.

Learn to use column and table aliases with the as keyword to assign alternative names, enabling output with spaces, and clearer labeling in joins, self joins, and subqueries.

Explore querying multiple tables by using joins to combine data, and learn inner, outer, cross, and self joins in practical SQL Server 2019 techniques.

Explore how inner, left, right, and full joins combine employee and department tables by matching department ids, returning matched rows and nulls for orphans.

Explore inner joins to intersect two or more tables and return only matching rows, using from and inner join on syntax to build accurate, multi-table queries.

Explore cross joins and self joins in SQL Server 2019, including Cartesian products, testing data generation, and self-join patterns to link employees with managers; learn aliasing and multi-level relationships.

Sort data with the order by clause and filter rows with the where clause. Implement paging with top, offset, and fetch, and handle null and empty string checks in predicates.

Learn to sort data with the order by clause, using primary and secondary keys to break ties, and understand how data types influence ascending or descending results.

Learn to filter data with predicates using a where clause, and/or logic, and the between operator, with parentheses to control evaluation, and like for flexible string matches.

Learn how to filter results using top and offset fetch to paginate data, applying order by, top N or top N percent, and fetching next rows for page-based queries.

Explore how to work with unknown values by distinguishing null from empty string, using is null versus equals, and filtering with where clauses, including numeric nulls in aggregates.

Explore SQL server data types, focusing on date/time and character data, and master the special functions that accompany these types.

Understand how to query date and time data in SQL Server, compare dates with between and equals, and choose data types like date, time, datetime, and datetimeoffset for accurate storage.

Explore how to manipulate dates in SQL Server 2019 using getdate, datepart, datediff, cast, convert, and format to compute ages, format dates for output, and filter results.

Analyze writing queries that return character data, comparing CHAR's fixed width with padding to VARCHAR's variable length, and contrast ASCII with Unicode, including the N prefix and plus concatenation.

Explore character functions in SQL Server 2019, including left, right, substring, ltrim, rtrim, trim, lower, upper, len, replace, str, and space, with practical examples using the national id.

Explore how DML enables modifying data in SQL Server, covering insert, update, and delete statements to add, modify, and remove records in the database.

Explore inserting records into a table with insert into, using a column list or not, and learn how identity, default, and null columns are handled, including inserting from a select.

Update records with the SQL Server syntax, using set, where, and optional joins to pull data from another table, then apply a 10% list price increase.

Delete records with the delete statement using a where clause, compare to truncate that resets the identity column, and perform actions via T-SQL or GUI.

Explore built-in functions, their classifications, and how conversion and logical functions handle null values, pick the first non-null value, and support meaningful aggregates.

Discover built-in functions in SQL Server, including aggregate and analytical (lag, lead, first_value, percentile_rank), conversions, data-type, mathematical, and logical functions. Apply them in column lists, where clauses, and global variables.

Learn how conversion functions work in SQL Server, including cast, convert, and pass, plus try_cast and try_convert, to turn strings into numbers or dates with culture-aware handling.

Explore four logical functions (choose, greatest, least, and immediate if) and their NULL handling, with examples on hire-date month and quarter in Azure versus on-premises.

Explore how is null and coalesce handle null data in SQL queries, substituting default values and selecting the first non-null field to simplify filtering and reporting.

Explore aggregate functions such as average, sum, count, max, min, and standard deviation in SQL Server, with and without grouping. See examples with where clauses and distinct.

Learn how to use the SQL group by clause to group rows by columns, include non-aggregates in the group by, and apply aggregates like sum and average while avoiding errors.

Filter groups with the having clause after grouping and aggregation, using aggregate predicates and avoiding column aliases; apply where-clause style logic alongside group by and aggregates.

Explore sub queries, including self-contained ones that run once per query, and outer and correlated sub queries that reference outer data, using the where clause and the exist keyword.

Explore self-contained subqueries, using the in operator and from statements to build dynamic lists or ad hoc tables, and compare with joins and self-joins for performance.

Learn how correlated subqueries reference the outer query and run for each row, with examples using a sales person, employee, and bonus.

Learn how the exists predicate works with subqueries, including correlated cases, performance comparisons with in, and practical examples using subcategories such as wheels and stored procedures.