Microsoft
SQL
General
SQL250: Transact-SQL for Developers (SQL250)

In this 5-day course, you will establish a solid foundational understanding of database concepts and terminology. Then you will master the use of various Microsoft tools to submit queries to quickly and efficiently get the results you want.

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

Finally, you will learn to harness the power of using T-SQL statements inside common database objects like Views, Stored Procedures and User-Defined Functions.

Class lectures highlight and explain Transact-SQL concepts which are reinforced with extensive follow-along demonstrations and hands-on labs.

Download pdf
About the course

Prerequisites:

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

Course Objectives:

Upon completing this course, the learner will be able to meet these overall objectives:

  • Describe the objects that make up a database, data relationships within the database and how databases guarantee uniqueness of defined objects.
  • Understand different sublanguages of SQL.
  • Use the SQL Server Management Studio
  • Write a simple SELECT statement, returning all the columns and rows.
  • Formulate and write a meaningful join strategy to bring together all necessary data.
  • Utilize a subquery to reshape data before using it in a query
  • Write a WHERE clause to filter the rows
  • Control the display of data
  • Write Transact-SQL queries and aggregated values
  • Define the attributes of a database transaction and generate a result set
  • Create a database view and in-line table value functions and execute simple scalar/multi-statement user-defined functions
  • Write procedural logic for Stored Procedures and User-defined Functions
Course content

Module 1: Database Concepts

  • Conceptual View
  • Logical View
  • Database Diagramming

Module 2:Using the Tools

  • SQL Server Management Studio (SSMS) Overview
  • Viewing Basic Table Metadata
  • Viewing Other Table Constraints
  • SSMS Database Diagramming

Module 3: Getting the Data

  • SQL Language
  • The SELECT Statement
  • The FROM clause
  • Joining Tables
  • Using Views and Subqueries
  • Reformulating Subqueries using Common Table Expressions
  • Old Form Join Syntax

Module 4: Filtering the Data

  • 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

Module 5: What to Display

  • 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

Module 6: Aggregating the Data

  • When is the Aggregation Performed?
  • Grouping and Aggregating
  • Pivoting Data
  • Aggregate Window Functions
  • Using Ranking Functions

Module 7: Modifying the Data

  • Defining Database Transactions
  • INSERT Statement
  • DELETE Statement
  • UPDATE Statement
  • MERGE Statement

Module 8: Modules

  • 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

Module 9: Procedural Logic

  • SQL Batch
  • Niladic Functions
  • Local Variables
  • Conditional Processing
  • Iterative Processing
  • Branching
  • Exception Handling
  • Structured Exception Handling
  • PRINT and RAISERROR Statement
Who Should Attend

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