SQL250 - Transact-SQL for Developers

During this 5-day course, students will learn Transact-SQL as implemented in SQL Server 2008, 2012 and 2014. The course starts by establishing a foundation understanding of database concepts and terminology. Students are then prepared to use various Microsoft tools to submit queries and view the result.

Following a logical process for creating SQL queries from business requirements, students learn how to write a query based on the way SQL Server processes the SQL statement. This differs from most courses, which present SQL as a set of features. This approach uses the natural way of breaking down the problem into a logical set of steps. Each step can be validated before moving to the next step.

Finally, the course teaches the student how to use T-SQL statements inside common database objects like Views, Stored Procedures and User-Defined Functions.

Lectures that highlight and explain T-SQL concepts are reinforced with extensive demonstrations and hands-on labs.

Audience Profile
This course is intended for SQL Server Developers, Database Administrators, and System Engineers who are responsible for writing T-SQL queries for an application

Student Testimonials

Instructor did a great job, from experience this subject can be a bit dry to teach but he was able to keep it very engaging and made it much easier to focus. Student
Excellent presentation skills, subject matter knowledge, and command of the environment. Student
Instructor was outstanding. Knowledgeable, presented well, and class timing was perfect. Student

Click here to print this page »


Before attending this course, students should have familiarity with programming and developing software using any language.

Detailed Class Syllabus

1. Database Concepts

Upon completion of this module, students will be able to describe the objects that make up a database, data relationships within the database and how databases guarantee uniqueness of defined objects.
Conceptual View
Logical View
Database Diagramming

2. Using the Tools

Upon completion of this module, students will be able to use SQL Server Management Studio to discover the database definition including tables and data relationships and how databases guarantee uniqueness of defined objects.
SQL Server Management Studio (SSMS) Overview
Viewing Basic Table Metadata
Viewing Other Table Constraints
SSMS Database Diagramming

3. Getting the Data

Upon completion of this module, students will be able to write a simple SELECT statement, returning all the columns and rows. Students will be able to formulate and write a meaningful join strategy to bring together all necessary data. The students will also be able to utilize a subquery to reshape data before using it in a query, write joins using the old and new form join syntax and write subqueries using Common Table Expressions.
SQL Language
The SELECT Statement
The FROM clause
Joining Tables
Using Views and Subqueries
Reformulating Subqueries using Common Table Expressions
Old Form Join Syntax

4. Filtering the Data

Upon completion of this module, students will be able to write a WHERE clause to filter the rows based on equality, a list of values, a range of values, a wildcard character condition and the existence of values in a subquery. The student will also be able to consider the impact on filtering based on implicit data conversions using data type precedence and how to create a proper search argument.
When is the WHERE clause processed?
The WHERE Clause
Filtering Data with Equality
Using a List of Values
Using Subqueries to Filter Data
Data Type Precedence
Proper Search Arguments

5. What to Display

Upon completion of this module, students will be able to control the display of data out of a SQL SELECT, rename column names, write expressions using standard operators and built-in function to change output values, conditionally change the output values using CASE expressions, sort the result set, eliminate duplicate rows and control the number of rows returned when necessary.
When is the Column List Processed?
Identifying Columns
Renaming Columns through a Column Alias
When and How to use Built-in Functions
Using Subqueries in the Column List
Using the CASE Expression
Ordering Rows
Controlling Row Display

6. Aggregating the Data

Upon completion of this module, students will be able to write Transact-SQL queries that add up values across rows based by group, filter the result set based on aggregated values, include aggregated values with detail rows, pivot rows into columns and define rank and row numbers.
When is the Aggregation Performed?
Grouping and Aggregating
Pivoting Data
Aggregate Window Functions
Using Ranking Functions

7. Modifying the Data

Upon completion of this module, students will be able to define the attributes of a database transaction, control the start and end of a transaction, add rows to a table, delete rows from a table, update values in a table and merge a result set into a table. The student will also be able to generate a result set of changes made during an INSERT, UPDATE, DELETE or MERGE statement.
Defining Database Transactions
INSERT Statement
DELETE Statement
UPDATE Statement
MERGE Statement

8. Modules

Upon completion of this module, students will be able to create a database view and in-line table value functions that filter columns and rows. Students also will be able to create and execute a simple stored procedure with parameters and create and execute simple scalar/multi-statement user-defined functions.
Layers of Abstraction-Three Schema Architecture
Creating and Using Views
Creating and Using User-Defined Scalar Functions
Creating and Using Inline-Table Valued Function
Creating and using Stored Procedures
Creating and Using Multi-Statement Table-Valued Functions

9. Procedural Logic

Upon completion of this module, students will be able to write Transact-SQL scripts that loop, test conditions, handle exceptions and conditions and display messages to users. Students will also be able to write procedural logic for Stored Procedures and User-defined Functions.
SQL Batch
Niladic Functions
Local Variables
Conditional Processing
Iterative Processing
Exception Handling
Structured Exception Handling