Capability Development / Database & DataScience
Query and Modify Data with T-SQL (DP-080+)
This training module builds practical T-SQL skills for querying, modifying, and programming data across SQL Server, Azure SQL, Synapse Analytics, and Microsoft Fabric environments. Through demonstrations, guided exercises, and real-world scenarios, participants learn to write SELECT queries that filter, sort, join, and summarize data, then progress to subqueries, table expressions, and window functions for deeper analysis. The course also covers safely modifying data with INSERT, UPDATE, DELETE, and MERGE, core T-SQL programming constructs, and building stored procedures, functions, and views. Participants finish with error handling, transaction control, and performance-aware coding practices for reliable, maintainable T-SQL scripts.
Program Overview
This training module builds practical T-SQL skills for querying, modifying, and programming data across SQL Server, Azure SQL, Synapse Analytics, and Microsoft Fabric environments. Through demonstrations, guided exercises, and real-world scenarios, participants learn to write SELECT queries that filter, sort, join, and summarize data, then progress to subqueries, table expressions, and window functions for deeper analysis. The course also covers safely modifying data with INSERT, UPDATE, DELETE, and MERGE, core T-SQL programming constructs, and building stored procedures, functions, and views. Participants finish with error handling, transaction control, and performance-aware coding practices for reliable, maintainable T-SQL scripts.
Audience Profile
Aspiring data analysts and report developers; Database professionals working across Microsoft's data platform; IT professionals building DP-080-aligned querying skills; and Developers who need to query and modify data safely.
Course Syllabus
Module 1: Introduction to T-SQL and SQL Server
Topics
- Relational databases and core database concepts
- SQL vs. T-SQL within the SQL Server ecosystem
- Schemas, tables, data types, and NULL handling
- Query tools such as SSMS and Azure Data Studio
Module 2: Writing Basic SELECT Queries
Topics
- The SELECT statement and column aliases
- Expressions, DISTINCT, and TOP
- ORDER BY and basic predicates
- Formatting result sets for readability
Module 3: Filtering and Conditional Logic
Topics
- The WHERE clause and comparison operators
- Logical operators, BETWEEN, IN, and LIKE
- NULL filtering and conditional checks
- CASE expressions and data type conversions
Module 4: Working with Multiple Tables
Topics
- INNER JOIN and OUTER JOIN concepts
- LEFT, RIGHT, FULL OUTER, and CROSS JOIN
- Self joins and relationship concepts
- Join best practices for accurate outputs
Module 5: Aggregation and Built-in Functions
Topics
- Aggregate functions with GROUP BY and HAVING
- Scalar, date, and string functions
- Ranking and rowset functions
- Data summarization for reports and dashboards
Module 6: Subqueries and Table Expressions
Topics
- Scalar and multi-valued subqueries
- Correlated subqueries
- Derived tables and common table expressions (CTEs)
- Temporary result sets for complex queries
Module 7: Advanced Query Techniques
Topics
- Set operators: UNION, INTERSECT, and EXCEPT
- Window functions and the OVER clause
- Ranking, paging, and pivoting
- Grouping sets for advanced analysis
Module 8: Modifying Data with T-SQL
Topics
- INSERT, UPDATE, DELETE, and MERGE statements
- Identity values and constraints
- Transaction basics
- Safe data modification practices
Module 9: T-SQL Programming Fundamentals
Topics
- Batches, variables, and parameters
- Control-of-flow statements
- IF...ELSE and WHILE loops
- Comments and script organization
Module 10: Stored Procedures, Functions, and Views
Topics
- Views and stored procedures
- User-defined functions and reusable logic
- Parameters and object execution
- Execution plans and database object management
Module 11: Error Handling and Transactions
Topics
- TRY...CATCH and THROW
- Transaction control with COMMIT and ROLLBACK
- Isolation basics
- Handling failed operations safely
Module 12: Performance and Best Practices
Topics
- Query readability and naming standards
- Indexing awareness and execution plan basics
- Avoiding common performance issues
- Secure and maintainable T-SQL coding practices