55366 Querying Microsoft SQL Server with Transact-SQL

Course Details

• At Course Completion
• Create single table SELECT queries
• Create multiple table SELECT queries
• Filter and sort data, Insert, update, and delete data
• Query data using built-in functions, Create queries that aggregate data
• Create subqueries, Create queries that use table expressions
• Use UNION, INTERSECT, and EXCEPT on multiple sets of data
• Implement window functions in queries
• Use PIVOT and GROUPING SETS in queries
• Use stored procedures in queries
• Add error handling to queries
• Use transactions in queries
• Module 1: Introduction to Transact-SQL
• What is Transact-SQL
• The SELECT statement
• The WHERE clause
• Sorting results
• Calculations
• CASE expressions
• Lab 1: Introduction to Transact-SQL
• Run a query
• Search for text strings
• Sort the results
• Module 2: Joining tables with Transact-SQL
• The JOIN clause
• Inner joins
• Outer joins
• Self joins and cross joins
• Lab 1: Joining tables with Transact-SQL
• Query two tables
• Query many tables
• Implement an out join
• Implement a self join
• Implement a cross join
• Module 3: Filtering and sorting results
• Implement the ORDER BY clause
• Filter data with the WHERE clause
• Limit the number of rows returned by a query
• Implement NULL logic
• Lab 1: Filtering and sorting results
• Filter data with a WHERE clause
• Sort data with ORDER BY
• Limit the number of rows returned by a query
• Module 4: SQL Server data types
• Understand data types?
• Implement string data types
• Implement temporal data types
• Lab 1: SQL Server data types
• Create queries with string data
• Implement string functions
• Create queries with temporal data
• Implement temporal functions
• Module 5: Inserting, updating and deleting data
• Insert new records
• Update existing records
• Delete data
• Lab 1: Inserting, updating and deleting data
• Insert new records
• Update existing records
• Delete data
• Module 6: Using SQL Server functions with Transact-SQL
• Understand function types in SQL Server
• Convert data using functions
• Implement logical functions
• Work with NULL data using functions
• Lab 1: Using SQL Server functions with Transact-SQL
• Convert data using functions
• Implement logical functions
• Work with NULL data using functions
• Module 7: Aggregating data with Transact-SQL
• Implement aggregation in SQL Server
• Group records in SQL Server
• Filter aggregated data
• Lab 1: Aggregating data with Transact-SQL
• Implement aggregation in SQL Server
• Group records in SQL Server
• Filter aggregated data
• Module 8: Implement subqueries with Transact-SQL
• Implement scalar and multi-valued sub-queries
• Implement correlated subqueries
• Implement existence checks with subqueries
• Lab 1: Implement subqueries with Transact-SQL
• Implement scalar and multi-valued sub-queries
• Implement correlated subqueries
• Implement existence checks with subqueries
• Module 9: Create queries that use table expressions
• Create views
• Create table-valued functions
• Implement derived tables
• Implement common table expressions
• Lab 1: Create queries that use table expressions
• Create views
• Create table-valued functions
• Implement derived tables
• Implement common table expressions
• Module 10: Use UNION, INTERSECT, EXCEPT and APPLY on multiple sets of data
• Write queries with the UNION operator
• Write queries with the INTERSECT and EXCEPT operators
• Write queries with the APPLY operator
• Lab 1: Use UNION, INTERSECT, EXCEPT and APPLY on multiple sets of data
• Write queries with the UNION operator
• Write queries with the INTERSECT and EXCEPT operators
• Write queries with the APPLY operator
• Module 11: Implement window functions in queries
• Understand window functions
• Implement window functions
• Lab 1: Implement window functions in queries
• Implement window ranking functions
• Implement window offset functions
• Module 12: Use PIVOT and grouping sets in queries
• Implement PIVOT in queries
• Implement grouping sets in queries
• Lab 1: Use PIVOT and grouping sets in queries
• Implement PIVOT in queries
• Implement UNPIVOT in queries
• Implement grouping sets in queries
• Module 13: Use stored procedures in queries
• Query data with stored procedures
• Interact with stored procedures using input and output parameters
• Write simple stored procedures
• Pass dynamic SQL to SQL Server
• Lab 1: Use stored procedures in queries
• Query data with stored procedures
• Interact with stored procedures using input and output parameters
• Write simple stored procedures
• Module 14: Implement programming features in Transact-SQL
• Understand T-SQL programming elements
• Implement loops and conditions in T-SQL queries
• Lab 1: Implement programming features in Transact-SQL
• Implement variables in T-SQL
• Implement loops and conditions in T-SQL
• Implement variables in dynamic SQL statements
• Module 15: Add error handling to queries
• Understand SQL Server error handling
• Implement structured exception handling
• Lab 1: Add error handling to queries
• Implement structured exception handling
• Pass errors to client applications
• Module 16: Use transactions in queries
• Understand database transactions
• Implement transactions in T-SQL
• Lab 1: Use transactions in queries
• Implement transactions in T-SQL
• Combine transactions with error handling