SQL250: Transact-SQL Course for Developers
MOC: DP-080T00
MS Subscription
Interface Gold
SQL250 is a comprehensive, five-day course that takes students from foundational database concepts through advanced querying techniques to T-SQL development. Students learn how to write queries the same way SQL Server processes them – following a step-by-step approach that breaks business requirements into logical, verifiable steps. This differs from most courses, which present SQL as a set of disconnected features.
The course begins with database fundamentals, the relational model, and the tools used to interact with SQL Server. From there, students master the art of combining data from multiple tables using joins and subqueries, filtering results, controlling output, and aggregating data for decision support. The course culminates with a full module on T-SQL development – encapsulating queries in views, building reusable scalar and table-valued functions, and writing stored procedures with parameters, error handling, and transaction management.
SQL250 is a superset of SQL101: Introduction to Transact-SQL. It covers everything in SQL101 but moves at a faster pace, goes deeper into each topic, and adds material specifically for developers – including WHERE clause optimization, old join syntax, advanced aggregation with windowing functions, pivot queries, ranking functions, transactions, the MERGE statement, and the full development module. Designed for students who have programming experience in any language.
Class lectures highlight and explain Transact-SQL concepts, which are reinforced with extensive follow-along demonstrations and hands-on labs.
What you will learn
- With a focus on practical applications, the course covers:
- Create and organize solutions and projects in SSMS
- Navigate SSMS: Object Explorer, query editor, window docking, and keyboard shortcuts
- Distinguish between primary keys, foreign keys, and intersection tables
- Inner Joins
- Outer Joins
- Write GROUP BY queries
- Maintaining data (Inserting, deleting, updating data
- Troubleshooting
What products and services will you use and learn about in this course
- SQL Server
- SQL Server Management Studio
- Browser
Prepare for the Microsoft Certification
None
- $2,795
- 5 Days, Labs, Live Q&A
- Interface exclusive course
- Master T-SQL queries, scripts, etc
- Use your programming skills!
- Faster and deeper than SQL101
- Attend online with RemoteLive™
- Replay™ Class Recordings Included
- Included in “all-you-can-eat” Microsoft Live Training Subscription
Course Outline
Module 1: The basics
The material we cover in this module will be used throughout the course. Understanding objects and object names, the processing order of a query, literal values, identifiers and delimiters, SSMS basics, and more – these are things that you will use no matter what you do in SQL.
Lessons:
- Introducing databases
- The database table
- SQL basics
- SSMS basics
- Query basics
- The metadata
Lab:
- Database Concepts and SSMS
Module 2: The Relational Model
Any discussion about relational databases will inevitably require an understanding of the Relational Model. The RM describes the organization and structure of the database and will have an impact on almost everything in this course and back at work. Understanding this important topic is also the first step in understanding and applying joins.
Lessons:
- Introducing the Relational Model
- The need for joins
- Table relationships
- OLAP and OLTP databases (Optional – Time permitting)
Lab:
- Table Relationships
Module 3: Getting the data
The FROM clause always references a table. The table can be a database table, or it can be generated by table-type functions, views, subqueries, or joins. In this module, our focus will be on joins and subqueries.
Lessons:
- The FROM clause
- Single-join queries
- Multiple-join queries
- Derived-table Subqueries
- Old join syntax
Labs:
- Combining two tables with a join
- Combining multiple tables with a join
Module 4: Filtering the Data
Now that we can use the FROM clause to define the base table of our query, we need a way to select the rows we want to include in our results and the ones we want to exclude. In this module, we look at different ways we can use the WHERE clause to filter the data.
Lessons:
- Introduction
- Expressions
- Data type precedence
- Advanced filtering
- WHERE clause optimization
Lab:
- Defining the result set
Module 5: Displaying the Data
At this point, we have learned how to define the data to query using the FROM and WHERE clauses. We are ready to project the results from our query and look at ways that the SELECT clause can be used to affect the rows that the query returns. We will also examine several of the system functions.
Lessons
- Introduction
- The column list
- The ORDER BY Clause
- Functions
- CASE expression
Lab:
- Controlling output rows
Module 6: Aggregating the Data
All levels of management need information to provide decision support. Lower levels of management may need to make decisions about what products and how much to have in stock, and higher levels of management may need to understand industry trends to support strategic decisions about where to guide the company. In this module, you will learn how to create Aggregate queries; they provide this powerful type of high-level information.
Lessons
- Introduction
- Aggregation with GROUP BY
- Filtering with HAVING
- Advanced aggregation with OVER()
- Pivoting data
- Ranking data
Lab:
- Grouping data
Module 7: Maintaining the Data
Everything we’ve seen so far in this course has been about reading the data. In this module, we look at the three write operations that we collectively call data maintenance: Insert, delete, and update. We start with a look at transactions, and we end with a look at the Merge command that allows us to perform all three data maintenance operations in one pass of the data.
Lessons
- Transactions
- Inserting data
- Deleting data
- Updating data
- Merging data
Lab:
- Data maintenance operations
Module 8: Development in TSQL
So far in this course, we’ve seen how the conceptual schema works. In this module we turn to the external schema where we can encapsulate much of what we’ve learned. We will encapsulate our age calculation, see how a stored procedure can be used to save a 1:N relationship, process transactions, perform data validation, and more.
Lessons
- Basic Programming constructs
- Scalar functions
- Views
- Inline table-valued functions
- Stored procedures | Part 1
- Stored Procedures | Part 2
- Multi-statement table-valued functions
Lab:
- Final scenario
Audience
This course is intended for SQL Server Developers, Database Administrators, and System Engineers who are responsible for writing T-SQL queries for an application. It is also suitable for Business Analysts and other professionals who need to query SQL Server databases and have some programming background.
Prerequisites
Before attending this course, students should have:
- Familiarity with programming and developing software using any language.