Describe relational databases and Transact-SQL queries.
Describe the on-premise and cloud-based editions and versions of SQL Server.
Describe how to use SQL Server Management Studio (SSMS) to connect to an instance of SQL Server, explore the databases contained in the instance, and work with script files that contain T-SQL queries.

Describe the role of T-SQL in writing SELECT statements.
Describe the elements of the T-SQL language and which elements will be useful in writing queries.
Describe the concepts of the set theory, one of the mathematical underpinnings of relational databases, and to help you apply it to how you think about querying SQL Server.
Describe predicate logic and examine its application to querying SQL Server.
Explain the elements of a SELECT statement, delineate the order in which the elements are evaluated, and then apply this understanding to a practical approach to writing queries.

Describe the structure and format of the SELECT statement, as well as enhancements that will add functionality and readability to your queries.
Describe how to eliminate duplicates using the DISTINCT clause.
Describe the use of column and table aliases.
Understand and use CASE expressions.

Explain the fundamentals of joins in SQL Server.
Write inner join queries.
Write queries that use outer joins.
Use additional join types.

Explain how to add an ORDER BY clause to your queries to control the order of rows displayed in your query’s output.
Explain how to construct WHERE clauses to filter out rows that do not match the predicate.
Explain how to limit ranges of rows in the SELECT clause using a TOP option.
Explain how to limit ranges of rows using the OFFSET-FETCH option of an ORDER BY clause.
Explain how three-valued logic accounts for unknown and missing values, how SQL Server uses NULL to mark missing values, and how to test for NULL in your queries.

Explore many of the data types SQL Server uses to store data and how data types are converted between types.
Explain the SQL Server character-based data types, how character comparisons work, and some common functions you may find useful in your queries.
Describe data types that are used to store temporal data, how to enter dates and times so they will be properly parsed by SQL Server, and how to manipulate dates and times with built-in functions.

Use INSERT and SELECT INTO statements.
Use UPDATE, MERGE, DELETE, and TRUNCATE.

Describe the types of functions provided by SQL Server, and then focus on working with scalar functions.
Explain how to explicitly convert data between types using several SQL Server functions.
Describe how to use logical functions that evaluate an expression and return a scalar result.
Describe additional functions for working with NULL.

Describe the built-in aggregate function in SQL Server and write queries using it.
Write queries that separate rows using the GROUP BY clause.
Write queries that use the HAVING clause to filter groups.

Describe where subqueries may be used in a SELECT statement.
Write queries that use correlated subqueries in a SELECT statement.
Write queries that use EXISTS predicates in a WHERE clause to test for the existence of qualifying rows.
Use the EXISTS predicate to efficiently check for the existence of rows in a subquery.

Write queries that return results from views.
Use the CREATE FUNCTION statement to create simple inline TVFs.
Write queries that create and retrieve results from derived tables.
Write queries that create CTEs and return results from the table expression.

Write queries that use UNION to combine input sets.
Write queries that use UNION ALL to combine input sets.
Write queries that use the EXCEPT operator to return only rows in one set but not another.
Write queries that use the INTERSECT operator to return only rows that are present in both sets.
Write queries using the CROSS APPLY operator.
Write queries using the OUTER APPLY operator.

Describe the T-SQL components used to define windows, and the relationships between them.
Write queries that use the OVER clause, with partitioning, ordering, and framing to define windows.
Write queries that use window aggregate functions.
Write queries that use window ranking functions.
Write queries that use window offset functions.

Describe how pivoting data can be used in T-SQL queries.
Write queries that pivot data from rows to columns using the PIVOT operator.
Write queries that unpivot data from columns back to rows using the UNPIVOT operator.
Write queries using the GROUPING SETS subclause.
Write queries that use ROLLUP AND CUBE.
Write queries that use the GROUPING_ID function.

Describe stored procedures and their use.
Write T-SQL statements that execute stored procedures to return data.
Write EXECUTE statements that pass input parameters to stored procedures.
Write T-SQL batches that prepare output parameters and execute stored procedures.
Use the CREATE PROCEDURE statement to write a stored procedure.
Create a stored procedure that accepts input parameters.
Describe how T-SQL can be dynamically constructed.
Write queries that use dynamic SQL.

Describe how Microsoft SQL Server treats collections of statements as batches.
Create and submit batches of T-SQL code for execution by SQL Server.
Describe how SQL Server stores temporary objects as variables.
Write code that declares and assigns variables.
Create and invoke synonyms.
Describe the control-of-flow elements in T-SQL.
Write T-SQL code using IF…ELSE blocks.
Write T-SQL code that uses WHILE.

Implement T-SQL error handling.
Implement structured exception handling.

Describe transactions and the differences between batches and transactions.
Describe batches and how they are handled by SQL Server.
Create and manage transactions with transaction control language (TCL) statements.
Use SET XACT_ABORT to define SQL Servers handling of transactions outside TRY/CATCH blocks.