DP-080: Querying Data with Microsoft Transact-SQL
Course Overview
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
🤖 On-Demand + AI Assist Learning Experience
Intelligent Learning Support
AI agents provide 24/7 personalized and expert instruction, adapting to your learning pace and style.
Personalized Practice
Receive customized exercises and scenarios based on your progress and areas for improvement.
Continuous Assessment
Real-time feedback and progress tracking help you stay on track and achieve your learning goals.
Hands-On Labs
This course includes practical, hands-on laboratory exercises to reinforce your learning:
Module 1: Getting Started with Transact-SQL
Overview of SQL Server and T-SQL
Module 2: Filtering and Sorting Query Results
WHERE clause for filtering data
Module 3: Querying Multiple Tables Using Joins
INNER JOIN basics
Module 4: Writing Subqueries
Using subqueries in SELECT, FROM, and WHERE clauses
Module 5: Using Built-in Functions
Scalar functions: string, numeric, date/time
Module 6: Modifying Data
INSERT statements
Module 7: Using Table Expressions
Views overview
Module 8: Combining Query Results
UNION and UNION ALL
Module 9: Transforming Data Using Pivot and Unpivot
Using PIVOT to rotate rows into columns
Module 10: Programming with Transact-SQL
Variables and control-of-flow constructs (IF, WHILE)
Module 11: Creating Stored Procedures
Syntax for creating stored procedures
Module 12: Implementing Error Handling
TRY...CATCH blocks in T-SQL
Module 13: Implementing Transactions in T-SQL
BEGIN TRANSACTION, COMMIT, ROLLBACK
Ready to Get Started?
Join thousands of professionals who have advanced their careers with our training programs.
Join Scheduled Training
Find upcoming sessions for this course and register for instructor-led training with other professionals.
View ScheduleCustom Training Solution
Need training for your team? We'll create a customized program that fits your organization's specific needs.
Get Custom Quote