Introduction to Transact-SQL

At Course Completion

• Use the SQL Server Management Studio tool to browse the definition of the database, find necessary object names, create database diagrams, and execute a SQL query to return data rows.
• Write a simple SELECT statement, returning all the columns and rows and formulate meaningful join strategy to bring together all necessary data.
• Write a WHERE clause to filter the rows based on equality, a list of values, a range of values and a wildcard character condition.
• Understand the impact on filtering based on implicit data conversions using data type precedence and how to create a proper search argument.
• Control the display of data in a SQL SELECT including renaming columns, using simple expressions with 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.
• Write Transact-SQL queries that aggregate values across rows based a group and filter the result set based on aggregated values.

Audience Profile

This course is intended for Business Analysts, System Administrators, Database Administrators and other professionals with no programming or SQL experience. This course is valuable for anyone who needs to learn how to write a SELECT statement to access SQL Server databases.

Prerequisites

• Ability to work with the Windows operating system
• Windows login, startup of applications and programs, menu and toolbar manipulation skills
• Basic knowledge of the purpose of a database

Outline

1. Discovering the Database

• Using SQL Server Management Studio (SSMS)
• Identifying table relationships
• SSMS Diagram Support
• Preparing to Write a SQL SELECT

2. Accessing Tables

• The Basic SELECT
• Combining Two Tables with a Join
• Inner Join
• Outer Join
• Combining Multiple Tables with a Join

3. Filtering the Data

• Basic Filtering with WHERE clause
• Equality expressions
• Wildcard filtering
• Complex Filtering
• Range and List-based Filtering
• Using Built-in Functions

4. Displaying Data

• Defining the Result Set
• Manipulating Numbers and Data Types
• Using Conditional Column Output
• NULL Functions
• CASE Expressions
• Controlling Output Rows
• TOP operator

5. Aggregating Data

• Grouping and Summarizing Data
• Filtering Aggregate