SQL101: Introduction to Transact-SQL

About this Course

During this 4-day course, students will learn Transact-SQL as implemented in SQL Server 2000, 2005 and SQL Server 2008 R2. The course is designed and paced for students that do not have a programming background.

The course provides students with knowledge to learn the technical skills needed to write Transact-SQL SELECT statements accessing one or more tables. The student will learn the various techniques for combining data, filtering the results as well as how to control and format the statement output.

The course starts by establishing a foundation understanding of a SQL Server database and use of the tools available to discover the database definition. Students are prepared to use various Microsoft tools to submit queries and view the results.

Following a step-by-step process for creating SQL queries from business requirements, students learn how to write a query based on the way SQL Server processes the SQL statement. This differs from most courses, which present SQL as a set of features. This approach uses the natural way of breaking down the problem into a logical set of steps. Each step can be validated before moving to the next step.

Lectures that highlight and explain Transact-SQL concepts are reinforced with extensive follow-along demonstrations and hands-on labs.

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 also valuable for anyone who needs to learn how to write just a SELECT statement to access a SQL Server database.

At Course Completion

After attending this course, students will be able to:

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.

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

Prerequisites

Before attending this course, students should have:

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