DP-080: Querying Data with Microsoft Transact-SQL

Course Overview

Master Microsoft's Transact-SQL (T-SQL) language with this comprehensive 2-day instructor-led course. Learn to query and modify data in relational databases hosted on Microsoft SQL Server-based systems, including Microsoft SQL Server, Azure SQL Database, and Azure Synapse Analytics. This hands-on training provides essential foundational skills for anyone working with data in Microsoft's ecosystem.

Who Should Attend

This course is ideal for:

• Data Analysts extracting insights from databases
• Data Engineers building data pipelines and solutions
• Data Scientists requiring database querying skills
• Database Administrators managing SQL Server environments
• Database Developers creating data-driven applications
• Solution Architects working with data platforms
• IT Professionals transitioning to data roles
• Students and Technology Managers seeking foundational SQL skills

Prerequisites

• Basic understanding of relational database concepts
• Familiarity with Microsoft Windows operating system
• No prior T-SQL experience required

Course Duration & Format

• Duration: 2 days (14 hours)
• Level: Beginner
• Format: Instructor-led training with hands-on labs using Visual Studio Code
• Languages: English

Learning Objectives

Upon completion of this course, participants will be able to:

Write SELECT statements to retrieve data from single and multiple tables
Sort and filter data using ORDER BY, WHERE, and logical operators
Implement JOIN operations to combine data from related tables
Create subqueries for complex data retrieval scenarios
Utilize built-in functions for data calculations and transformations
Group and aggregate data using GROUP BY and HAVING clauses
Modify data using INSERT, UPDATE, DELETE, and MERGE statements
Handle NULL values and perform data type conversions
Use advanced techniques like PIVOT, window functions, and set operators
Write T-SQL programming constructs including variables, loops, and conditionals
Create stored procedures and functions for reusable code
Implement error handling with TRY/CATCH blocks
Use transactions to ensure data integrity

Detailed Course Modules

Module 1: Getting Started with Transact-SQL

• Introduction to SQL Server and relational databases
• Understanding T-SQL and its relationship to standard SQL
• Writing basic SELECT statements
• Using column and table aliases
• Working with data types and handling NULL values
• Using CAST, CONVERT, and TRY_CAST functions
• Implementing CASE expressions for conditional logic

Module 2: Sorting and Filtering Query Results

• Controlling query results with WHERE clause
• Using comparison and logical operators (AND, OR, NOT)
• Implementing pattern matching with LIKE operator
• Sorting results with ORDER BY clause
• Limiting results with TOP and OFFSET/FETCH
• Removing duplicates with DISTINCT
• Working with date ranges and NULL comparisons

Module 3: Querying Multiple Tables with Joins

• Understanding table relationships and foreign keys
• Implementing INNER JOIN operations
• Using LEFT, RIGHT, and FULL OUTER JOIN operations
• Creating CROSS JOIN for Cartesian products
• Performing self-joins for hierarchical data
• Chaining multiple joins in complex queries
• Understanding when to use joins vs. subqueries

Module 4: Using Subqueries

• Writing subqueries in SELECT, FROM, and WHERE clauses
• Understanding correlated vs. non-correlated subqueries
• Using subqueries for existence checks
• Implementing subqueries for data comparison
• Creating derived tables for intermediate results
• Performance considerations with subqueries

Module 5: Using Built-in Functions

• Scalar Functions: String, numeric, and date/time functions
• Aggregate Functions: SUM, COUNT, AVG, MIN, MAX
• Logical Functions: ISNULL, COALESCE, IIF, CHOOSE
• System Functions: GETDATE, USER, NEWID
• Grouping data with GROUP BY clause
• Filtering grouped data with HAVING clause
• Combining functions for complex calculations

Module 6: Modifying Data

• Inserting new records with INSERT statements
• Creating tables from query results with SELECT INTO
• Updating existing data with UPDATE statements
• Removing data with DELETE statements
• Using MERGE for advanced data modifications
• Working with IDENTITY columns and SCOPE_IDENTITY
• Understanding transaction basics for data integrity

Module 7: Using Table Expressions

• Creating and using views for reusable queries
• Implementing Common Table Expressions (CTEs)
• Working with recursive CTEs for hierarchical data
• Using table variables and temporary tables
• Creating derived tables within queries
• Understanding when to use different table expression types

Module 8: Grouping Sets and Pivoting Data

• Using PIVOT operator to rotate rows into columns
• Implementing UNPIVOT for data normalization
• Working with GROUPING SETS for flexible aggregation
• Using ROLLUP for hierarchical subtotals
• Implementing CUBE for all possible grouping combinations
• Creating dynamic pivot queries

Module 9: Combining Query Results

• Using UNION and UNION ALL to combine result sets
• Implementing INTERSECT for common rows
• Using EXCEPT for difference operations
• Understanding requirements for set operations
• Applying ORDER BY to combined results
• Working with CROSS APPLY and OUTER APPLY

Module 10: Window Functions

• Understanding window function syntax with OVER clause
• Implementing ranking functions (RANK, DENSE_RANK, ROW_NUMBER)
• Using PARTITION BY for grouped calculations
• Creating running totals and moving averages
• Implementing LAG and LEAD for row comparisons
• Working with frame specifications (ROWS, RANGE)

Module 11: Programming with Transact-SQL

• Declaring and using variables and table variables
• Implementing conditional logic with IF...ELSE
• Creating loops with WHILE statements
• Using PRINT for debugging and output
• Understanding variable scope and batches
• Combining programming constructs in scripts

Module 12: Creating Stored Procedures and Functions

• Stored Procedures: Creating reusable SQL code blocks
• Input and Output Parameters: Passing values in and out
• Scalar Functions: Returning single values
• Table-Valued Functions: Returning result sets
• Using EXECUTE to call procedures
• Understanding when to use procedures vs. functions

Module 13: Implementing Error Handling

• Understanding T-SQL error handling with TRY...CATCH
• Using THROW vs. RAISERROR for custom errors
• Capturing error details with ERROR_* functions
• Implementing nested error handling
• Creating robust error handling patterns
• Logging errors for troubleshooting

Module 14: Implementing Transactions

• Understanding ACID properties (Atomicity, Consistency, Isolation, Durability)
• Using BEGIN TRANSACTION, COMMIT, and ROLLBACK
• Implementing proper transaction error handling
• Using XACT_STATE() to check transaction status
• Working with savepoints for partial rollbacks
• Ensuring data integrity in multi-statement operations

Hands-On Lab Environment

Throughout the course, participants will work with:

• Visual Studio Code as the query editor
• AdventureWorksLT sample database for realistic scenarios
• Step-by-step guided exercises with detailed instructions
• Challenge problems to reinforce learning
• Real-world scenarios from retail and manufacturing contexts

Key Lab Activities

• Writing progressively complex SELECT queries
• Building reports using joins and aggregations
• Creating data modification scripts with proper error handling
• Developing stored procedures for business logic
• Implementing transaction-safe data updates
• Building pivot tables for data analysis
• Creating window functions for advanced analytics

Tools and Technologies Covered

• Microsoft SQL Server (various versions)
• Azure SQL Database cloud platform
• Azure Synapse Analytics for data warehousing
• Visual Studio Code with SQL Server extensions
• T-SQL language comprehensive features
• SQL Server Management Studio (SSMS) overview

Assessment and Certification Path

• Hands-on exercises with immediate feedback
• Challenge problems to test comprehension
• Progressive skill building through practical scenarios
• Foundation for advanced certifications:
  • Microsoft Azure Data Fundamentals (DP-900)
  • Microsoft Azure Database Administrator Associate (DP-300)
  • Microsoft Azure Data Engineer Associate (DP-203)

Business Benefits

Organizations investing in this training will benefit from staff who can:

• Extract valuable insights from corporate databases efficiently
• Create automated reports and data summaries
• Maintain data integrity through proper modification techniques
• Support data-driven decision making across departments
• Reduce IT dependency for routine data queries
• Improve productivity in data analysis tasks
• Implement robust data solutions with proper error handling

Course Materials Included

• Official Microsoft courseware and comprehensive lab guides
• Digital reference materials and documentation links
• AdventureWorksLT sample database for practice
• Code templates and best practice examples
• Access to online resources and community forums
• Course completion certificate

Advanced Topics Covered

Beyond basic querying, this course includes:

• Advanced JOIN techniques including self-joins and complex multi-table scenarios
• Subquery optimization and performance considerations
• Window functions for analytical processing
• Data pivoting and unpivoting for flexible reporting
• Set operations for combining query results
• Stored procedure development with parameters and error handling
• Transaction management for data integrity
• Programming constructs for automated processing