Querying Microsoft SQL Server with Transact-SQL

Querying Microsoft SQL Server with Transact-SQL (55366AC)

Course Length: 5 days
Delivery Methods: Multiple delivery options
Course Overview

This course provides a comprehensive introduction to Transact-SQL (T-SQL), the programming language used in SQL Server for managing and manipulating data. Designed for database professionals, developers, and anyone who works with SQL Server, this course covers the essential T-SQL skills needed to query, modify, and manage data efficiently in SQL Server databases.

The course begins with an Introduction to Transact-SQL, where you'll learn the basics of T-SQL, including how to use the SELECT statement, apply the WHERE clause, sort results, perform calculations, and utilize CASE expressions. This foundational module sets the stage for writing effective queries in SQL Server.

Next, in the Joining Tables with Transact-SQL module, you'll explore the JOIN clause, including inner joins, outer joins, self joins, and cross joins. Understanding how to join tables is crucial for combining data from multiple sources, making your queries more powerful and informative.

The Filtering and Sorting Results section delves into techniques for ordering and filtering data using the ORDER BY and WHERE clauses. You'll learn how to limit the number of rows returned, implement NULL logic, and effectively manage query results to meet specific data requirements.

In the SQL Server Data Types module, you’ll gain an understanding of different data types available in SQL Server, including string and temporal data types. This section helps you select the appropriate data types for your columns, ensuring accurate data storage and retrieval.

The Inserting, Updating, and Deleting Data section covers the essential DML (Data Manipulation Language) operations. You'll learn how to insert new records, update existing data, and delete records, enabling you to maintain and manage data within your SQL Server databases.

In Using SQL Server Functions with Transact-SQL, you'll explore various function types, including logical and conversion functions, and learn how to handle NULL values using functions. This module enhances your ability to perform complex data manipulations within your queries.

The Aggregating Data with Transact-SQL module focuses on implementing aggregation functions, grouping records, and filtering aggregated data. These skills are essential for summarizing and analyzing data, enabling you to generate meaningful insights from large datasets.

In Implement Subqueries with Transact-SQL, you'll learn to use scalar and multi-valued subqueries, correlated subqueries, and existence checks. Subqueries allow you to build complex queries that can solve more advanced data retrieval problems.

The Create Queries that Use Table Expressions section introduces views, table-valued functions, derived tables, and common table expressions (CTEs). You'll learn how to create and use these expressions to simplify and modularize your queries, making them easier to understand and maintain.

In the Use UNION, INTERSECT, EXCEPT, and APPLY on Multiple Sets of Data module, you'll learn to combine and compare result sets using these operators, enhancing your ability to work with data from different sources and manipulate query results to meet complex requirements.

The Implement Window Functions in Queries section introduces you to window functions, which allow you to perform calculations across a set of table rows related to the current row. These functions are powerful tools for advanced data analysis and reporting.

In Use PIVOT and Grouping Sets in Queries, you'll explore techniques for reshaping and summarizing data, including using PIVOT to rotate rows into columns and grouping sets for advanced aggregation scenarios, adding flexibility to your data analysis.

The Use Stored Procedures in Queries module covers creating and interacting with stored procedures, including passing parameters and executing dynamic SQL. Stored procedures help encapsulate business logic and improve query performance.

Implement Programming Features in Transact-SQL focuses on adding programming logic to your queries, such as loops and conditional statements. These elements allow you to build more dynamic and responsive queries.

In Add Error Handling to Queries, you'll learn how to manage errors using structured exception handling techniques, ensuring your T-SQL scripts can gracefully handle unexpected issues.

The course concludes with Use Transactions in Queries, where you'll understand and implement database transactions to maintain data integrity and manage changes effectively within your SQL Server environment.

By the end of this course, you will have developed a solid foundation in Transact-SQL, enabling you to write efficient, robust, and complex queries. You’ll be well-prepared to manage data, develop dynamic SQL scripts, and optimize database operations in SQL Server.

Course Benefits
  • Create single table SELECT queries
  • Create multiple table SELECT queries
  • Filter and sort data
  • Insert, update, and delete data
  • Query data using built-in functions
  • Create queries that aggregate data
  • Create subqueries
  • Create queries that use table expressions
  • Use UNION, INTERSECT, and EXCEPT on multiple sets of data
  • Implement window functions in queries
  • Use PIVOT and GROUPING SETS in queries
  • Use stored procedures in queries
  • Add error handling to queries
  • Use transactions in queries
Available Delivery Methods
Public Class
Public expert-led online training from the convenience of your home, office or anywhere with an internet connection. Guaranteed to run .
Private Class
Private classes are delivered for groups at your offices or a location of your choice.
Course Outline
  1. Introduction to Transact-SQL
    1. What is Transact-SQL
    2. The SELECT statement
    3. The WHERE clause
    4. Sorting results
    5. Calculations
    6. CASE expressions
  2. Joining tables with Transact-SQL
    1. The JOIN clause
    2. Inner joins
    3. Outer joins
    4. Self joins and cross joins
  3. Filtering and sorting results
    1. Implement the ORDER BY clause
    2. Filter data with the WHERE clause
    3. Limit the number of rows returned by a query
    4. Implement NULL logic
  4. SQL Server data types
    1. Understand data types
    2. Implement string data types
    3. Implement temporal data types
  5. Inserting, updating and deleting data
    1. Insert new records
    2. Update existing records
    3. Delete data
  6. Using SQL Server functions with Transact-SQL
    1. Understand function types in SQL Server
    2. Convert data using functions
    3. Implement logical functions
    4. Work with NULL data using functions
  7. Aggregating data with Transact-SQL
    1. Implement aggregation in SQL Server
    2. Group records in SQL Server
    3. Filter aggregated data
  8. Implement subqueries with Transact-SQL
    1. Implement scalar and multi-valued sub-queries
    2. Implement correlated subqueries
    3. Implement existence checks with subqueries
  9. Create queries that use table expressions
    1. Create views
    2. Create table-valued functions
    3. Implement derived tables
    4. Implement common table expressions
  10. Use UNION, INTERSECT, EXCEPT and APPLY on multiple sets of data
    1. Write queries with the UNION operator
    2. Write queries with the INTERSECT and EXCEPT operators
    3. Write queries with the APPLY operator
  11. Implement window functions in queries
    1. Understand window functions
    2. Implement window functions
  12. Use PIVOT and grouping sets in queries
    1. Implement PIVOT in queries
    2. Implement grouping sets in queries
  13. Use stored procedures in queries
    1. Query data with stored procedures
    2. Interact with stored procedures using input and output parameters
    3. Write simple stored procedures
    4. Pass dynamic SQL to SQL Server
  14. Implement programming features in Transact-SQL
    1. Understand T-SQL programming elements
    2. Implement loops and conditions in T-SQL queries
  15. Add error handling to queries
    1. Understand SQL Server error handling
    2. Implement structured exception handling
  16. Use transactions in queries
    1. Understand database transactions
    2. Implement transactions in T-SQL
Class Materials

Each student will receive a comprehensive set of materials, including course notes and all the class examples.

Class Prerequisites

Experience in the following is required for this Microsoft SQL Server class:

  • Basic understanding of relational databases.
  • Basic Windows knowledge.
Prerequisite Courses

Courses that can help you meet these prerequisites:

Live Public Class
$2,935.10 / student
Please select a class.
Live Private Class
  • Private Class for your Team
  • Live training
  • Online or On-location
  • Customizable
  • Expert Instructors
Request Proposal