55366 Querying Data with Transact-SQL

Agenda

Module 1: Introduction to Transact-SQL

• What is Transact-SQL
• The SELECT statement
• The WHERE clause
• Sorting results
• Calculations
• CASE expressions

Module 2: Joining tables with Transact-SQL

• The JOIN clause
• Inner joins
• Outer joins
• Self joins and cross joins

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

Module 4: SQL Server data types

• Understand data types
• Implement string data types
• Implement temporal data types

Module 5: 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

Module 7: 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

Module 9: 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

Module 11: Implement window functions in queries

• Understand window functions
• Impement window functions

Module 12: Use PIVOT and grouping sets in queries

• Implement PIVOT 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

Module 14: Implement programming features in Transact-SQL

• Understand T-SQL programming elements
• Implement loops and conditions in T-SQL queries

Module 15: Add error handling to queries

• Understand SQL Server error handling
• Implemet structured exception handling

Module 16: Use transactions in queries

• Understand database transactions
• Implement transactions in T-SQL