Call Us: +32 2 466 00 16
Email: info@u2u.be
Follow Us:

Querying SQL Server with Transact-SQL

3days
Training code
UTSQL
Book this course

Introduction to Transact-SQL and the Querying Tools

Before you can get started writing T-SQL code you must master the tool that we will mainly use throughout this classroom training: Management Studio. But there are other useful tools as well to discover in this module:

  • Object hierarchy
  • Configuratin manager
  • SQL Server Management Studio
  • Using the graphical query builder in Management Studio
  • Using the sqlcmd Utility
  • Using SSL Server Profiler
  • Brief history of SQL Server

SELECT Fundamentals

The SELECT statement is very important in T-SQL. It allows to retrieve data from tables and views. But that data may need to be filtered, both in the rows and columns we return, and sorted. Another crucial concept discussed in this module is that of expressions, which allow us to combine multiple columns into one computed column before we return the result.

  • Basic SELECT Statement
  • T-SQL Expressions
  • Formatting Result Sets
  • The CASE Statement
  • Fetching Data from Tables
  • Filtering Data
  • Sorting Data
  • Combining Multiple Result Sets: UNION, EXCEPT and INTERSECT

Aggregating Data

Often the data stored in the database is more detailed than the result we are interested in. This module shows how to aggregate multiple values into a group, and how to compute aggregated values on top of these groups. The following topics are covered in this module:

  • Using Aggregation Functions
  • GROUP BY Fundamentals
  • Filtering on Groups Using the HAVING Clause
  • Computing Subtotals Using the ROLLUP and CUBE Clauses

Joining Multiple Tables

Most operational databases are normalized: the data is spread over multiple tables. But when building reports we need to join these different tables to get the answer we need. That's why mastering the different techniques to combine tables in a single result is crucial. In this module we cover two important ways: via joins and via nested queries.

  • Normalization
  • Using Aliases for Table Names
  • Inner, outer, cross and self joins
  • Nested Queries

Advanced SELECT

Some queries are a bit more challenging to write. For instance, how do you retrieve for each country the three most important customers? How do you calculate the day of week from a date? These and other T-SQL challenges get solved in this module. The following topics are covered in this module:

  • Using Functions
  • Data Types
  • Conversion Between Data Types
  • The CROSS APPLY Statement

Modifying Data

Sometimes there is the need to insert new data into the database. Or old data needs an update or has to be deleted. This module shows you the basics of how this can be done. The following topics are covered in this module:

  • Using Transactions
  • Inserting Data
  • Updating Data
  • Deleting Data
  • Truncating a Table

Advanced Querying techniques

This module basically continuous on the theme of the Advanced SELECT module: Special T-SQL constructs that can make hard queries easy to write. Converting data stored in columns into rows, adding row numbers or figuring out which items are more than a certain number of levels deep in a hierarchy, that's the kind of problems we will look into in this module.
The following topics are covered in this module:

  • SQL Scripts
  • Common table expressions
  • Writing recursive CTEs
  • Using the OVER-clause
  • Windowing functions
  • Ranking functions
  • Analytic functions
  • PIVOT and UNPIVOT statement

Views, stored procedures and functions

Writing queries can also be the starting point for a database developer. Then you might want to share your queries with others via views, functions or stored procedures, which are exactly the topics covered in this module. Notice that we only cover an introduction into these things, for a in depth coverage you better attend the Developing and optimizing SQL Server databases training.

  • Introduction to Views
  • Introduction to Stored Procedures
  • Introduction to User-defined Functions
  • Functions and procedures

The goal of this course is to provide students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server. The course covers querying SQL Server 2008 and higher versions as well as Azure SQL Databases.

This course is intended for SQL Server database administrators and developers who are responsible for writing queries, but have no or very limited experience in writing T-SQL SELECT statements.

This introductory course requires no special skills.

© 2019 U2U All rights reserved.