SQL Server Transact-SQL // SQL Server Development // SQL250

SQL250: Transact-SQL for Developers

SQL Server Transact-SQLSQL Server Development

SQL250: Transact-SQL for Developers

This 5-day Interface-exclusive course is structured the same way the SQL executes a query, rather than teaching to a list of features. You’ll master the technical skills required to write T-SQL Queries, Scripts, Views, Stored Procedures and User-Defined Functions, and how to use T-SQL statements inside common database objects. If you have a programming background and you need to go faster and deeper than SQL101, this is the perfect course. This live class is available virtually with RemoteLive™ or locally at our Phoenix, AZ location.

Microsoft Transact-SQL for Developers images
x
Course:

Course Description

In this course, you’ll establish a solid foundational understanding of database concepts and terminology. Then you’ll 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, the you’ll 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.

Course Outline

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
  • Branching
  • Exception Handling
  • Structured Exception Handling
  • PRINT and RAISERROR Statement

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.

Prerequisites

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

What You Will Learn

  • 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

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
  • Branching
  • Exception Handling
  • Structured Exception Handling
  • PRINT and RAISERROR Statement

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

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

  • 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

$2,795.00

  • 5 Days
  • Interface exclusive course
  • Master T-SQL queries, scripts, etc
  • Use your programming skills!
  • Faster and deeper than SQL101
  • Attend online with RemoteLive™
  • Microsoft SA Voucher Eligible
  • Replay™ Class Recordings included with this course

Upcoming Dates:

RemoteLive Training at Interface
Interface Technical Training Partners - Miscrosoft - Cisco - PMI - Red Hat - ITIL - Citrix
Interface Technical Training Partners - Miscrosoft - Cisco - PMI - Red Hat - ITIL - Citrix