Querying SQL Databases using T-SQL

Where possible, our experienced trainers will take the time to discuss the how and where to use each aspect of TSQL covered in the course.

• Accessing the TSQL language using SQL Server Management Studio (SSMS)
• Getting to use SSMS to write TSQL to write queries using a single table
• Using more than one table using joins such as inner, outer and cross
• Applying filters to the output to reduce the number of rows returned
• Analysing data to return aggregated results using group by and a few functions
• Using functions to convert and cast the data between data types.

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

Module 1: Introduction to T-SQL

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

Lab :

• Familiarize yourself with SSMS
• Introduction to the Query Editor

Module 2: Retrieving Data

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

Lab :

• Basic Select statements
• Using expressions in Select lists

Module 3: Filtering Rows

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

Lab :

• Basic equality filters
• Basic comparisons
• Logical comparisons
• String comparisons
• NULL comparisons

Module 4: 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.

Lab :

• Basic sorting
• Sorting on expressions
• Unique rows
• Limiting results

Module 5: 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 describes how to filter the grouped values.

Lab :

• Single aggregates
• GROUP BY
• HAVING

Module 6: 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

Lab :

• Inner joins
• Outer joins
• Unions

Module 7: Using Views and Stored Procedures

This module provides an introduction to the benefits of using pre-written queries which have been stored in the database.

Lab :

• Using views
• Using stored procedures

Module 8: 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.

Lab :

• Text functions
• Date functions
• NULL functions
• Formatting dates

Appendix : Modifying Data

If time allows and if enough delegates express an interest in the topic, this module gives a brief introduction to INSERT, UPDATE and DELETE statements.

Please note: for Attend from Anywhere customers an additional screen is required. The additional screen must have a minimum screen size of 19 inch and minimum resolution of 1280x1024, with the vertical resolution (1024) being the most critical.