Knowledge Enhancement / Database & Data Science
Query and Modify Data with T-SQL (DP-080)
Build practical T-SQL skills to query, modify and program data across SQL Server, Azure SQL, Synapse Analytics and Microsoft Fabric.
Program Overview
This training 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.
Learners progress from basic SELECT queries through joins, aggregation, subqueries and window functions to safe data modification, stored procedures, error handling and performance-aware scripting.
Learning Objectives
- Understand relational database concepts and the role of T-SQL in SQL Server
- Write SELECT queries to retrieve, filter, sort, join and summarize data
- Use subqueries, table expressions, functions and window functions for complex analysis
- Modify data safely with INSERT, UPDATE, DELETE, MERGE, transactions and error handling
- Create readable, reusable and performance-aware T-SQL scripts
Audience Profile
Data analysts, report developers, database beginners, developers and students who need to query and work with data in Microsoft SQL Server, Azure SQL or Microsoft Fabric.
Course Syllabus
Module 1: Introduction to T-SQL and SQL Server
Topics
- Relational databases and core concepts
- SQL vs T-SQL and the SQL Server ecosystem
- Schemas, tables, data types and NULL handling
- Query tools such as SSMS or Azure Data Studio
Module 2: Writing Basic SELECT Queries
Topics
- SELECT statement and column aliases
- Expressions, DISTINCT and TOP
- ORDER BY and basic predicates
- Result-set formatting and readability
Module 3: Filtering and Conditional Logic
Topics
- 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 and OUTER JOIN concepts
- LEFT, RIGHT, FULL OUTER and CROSS JOIN
- Self joins and relationships
- Join best practices
Module 5: Aggregation and Built-in Functions
Topics
- Aggregate functions with GROUP BY and HAVING
- Scalar, date and string functions
- Ranking and rowset functions
- Summarizing data for reports and dashboards
Module 6: Subqueries and Table Expressions
Topics
- Scalar and multi-valued subqueries
- Correlated subqueries
- Derived tables and common table expressions
- Temporary result sets
Module 7: Advanced Query Techniques
Topics
- UNION, INTERSECT and EXCEPT
- Window functions and the OVER clause
- Ranking, paging and pivoting
- Grouping sets
Module 8: Modifying Data with T-SQL
Topics
- INSERT, UPDATE, DELETE and MERGE
- 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 object management
Module 11: Error Handling and Transactions
Topics
- TRY...CATCH and THROW
- COMMIT and ROLLBACK
- Isolation basics
- Handling failed operations safely
Module 12: Performance and Best Practices
Topics
- Readability and naming standards
- Indexing awareness and execution plan basics
- Avoiding common performance issues
- Secure, maintainable T-SQL coding