Querying SQL Databases using T-SQL

Overview

This course provides you with the basic knowledge and skills to create queries using Transact-SQL. It will teach you how to select, filter, and sort data, perform aggregations, and use multiple tables.

Prerequisites

There are no technical pre-requisites for this course and it assumes no prior knowledge of SQL Server.

Target audience

This course is designed for IT professionals who write queries for use with Microsoft SQL Server 2005 or later. Non-technical 'power users' will also benefit from the course if they’re comfortable using sophisticated software tools like SQL Server Management Studio.

The course is aimed at both non-technical 'power users' and IT professionals who write queries for use with Microsoft SQL Server.

Objectives

By the end of this course, learners will be able to:

• Use fundamental aspects of the T-SQL language with SQL Server Management Studio (SSMS).
• Use SSMS to write T-SQL to write queries using a single table.
• Use SSMS to write T-SQL queries that use multiple queries with inner and outer joins.
• Apply filters to the output to reduce the number of rows and columns returned.
• Analyse data to return aggregated results using GROUP BY and HAVING.
• Use simple built-in functions to process and convert data.

Outline

Introduction to T-SQL

• This module provides essential background information on Relational Database Management Systems and Microsoft SQL Server. It introduces the main tool that will be used throughout the course — SQL Server Management Studio.

Retrieving Data

• This module describes the fundamental part of a T-SQL query: the SELECT statement.

Filtering Rows

• This module describes how you can filter unwanted data out of your results using the WHERE clause.

Sorting returned rows

• This module describes how to use the ORDER BY clause of a query to manage the order in which rows are returned.

Grouping and Aggregating data

• This module describes how to use the aggregate functions of SQL to calculate results for a number of records at a time and how to group results into a series of values. Finally, it examines how to filter the grouped values.

Using multiple tables

• This module describes how to use JOIN clauses to retrieve data from multiple tables. It also covers the UNION, INTERSECT, and EXCEPT keywords

Common Functions

• This module introduces you to some of the basic text, date, and null-value manipulation functions that are built in to SQL Server.

Using Views and Stored Procedures

• This module introduces the benefits of using pre-written queries stored in the database.

Appendix : Modifying Data

• This module is provided for additional post-course reading and gives a brief introduction to INSERT, UPDATE, and DELETE statements.

Appendix: Working with Tables

• This module is provided for additional post-course reading and gives a brief introduction into how tables are created, altered, and dropped.

Exams and assessments

There are no formal exams included in this course. Participants will complete interactive labs and knowledge checks to reinforce learning outcomes.

Hands-on learning

This course includes:

• Hands-on labs to apply key concepts in a real-world environment.