How To Take This Class

Course Overview

In this SQL Server 2012 Queries using T-SQL course students will learn about the origins and uses of Transact-SQL. Students will explore some of the built-in T-SQL functions. Students will also learn how to control program flow and analyze data using T-SQL’s ranking functions. In addition, students will learn about the SELECT statement, the WHERE, ORDER BY and GROUP BY clauses. Finally, attendees will learn to use T-SQL INSERT, UPDATE, and DELETE statements.

Course Prerequisites

Basic knowledge of database concepts is helpful, but not required.

Course Audience

This course is designed for Business Analysts, Database Designers and others who frequently access data from SQL Server 2012 and would like to improve their ability to build custom T-SQL queries to select, modify and update data.

What You'll Learn

After successful completion of this course, students will be able to:
  • Learn about the origins and uses of Transact-SQL.
  • Explore some of the built-in T-SQL functions.
  • Understand how to control program flow.
  • Analyze data using T-SQL’s ranking functions.
  • Learn about data selection queries in T-SQL.
  • Explore the SELECT statement.
  • Understand how to filter rows using the WHERE clause.
  • Use the ORDER BY clause to sort data.
  • Group rows of data using the GROUP BY clause.
  • See how to join rows from different tables.
  • Learn to use Transact-SQL INSERT, UPDATE, and DELETE statements.
  • Learn to use bulk copy to insert rows.
  • Understand how transaction isolation levels affect performance and concurrency.

Course Duration

1 Day

Course Outline

    • Overview of Transact-SQL (T-SQL)
    • Transact-SQL (T-SQL)
    • Batches and Scripts
    • Variables
    • Delimiters and Operators
    • Transact-SQL and Data Types
    • Using Built-In Functions
    • Working with Nulls
    • Handling Numbers
    • Manipulating Strings
    • Working with Date and Time Values
    • Using the @@ Functions
    • Controlling Program Flow
    • IF...ELSE Statements
    • GOTO, RETURN, and Label Statements
    • CASE Expression
    • WHILE Statement
    • WAITFOR Statement
    • Ranking Results
      • Lab 1 Overview
      • Convert Data Types
      • Use Built-In Functions
      • Use Control-of-Flow Constructs
    • Data Selection in SQL Server
    • Understanding Transact-SQL Data Selection
    • Schemas and Naming in SQL Server
    • The SELECT Statement
    • Selecting All Columns
    • Concatenating Columns
    • Naming Columns
    • Using DISTINCT to Limit Values
    • The WHERE Clause
    • Transact-SQL Comparison Operators
    • The LIKE Operator
    • The BETWEEN Operator
    • Using IS NULL to Test for Nulls
    • Multiple Conditions with the AND, OR, and NOT Operators
    • Operator Precedence
    • Using the IN Operator
    • Using ORDER BY to Sort Data
    • Sorting on a Single Column
    • Sorting by Multiple Columns
    • Sorting with Expressions
    • The GROUP BY Clause
    • Aggregate Functions
    • Counting Rows
    • Counting Column Data
    • Counting with a WHERE Clause
    • Using the GROUP BY Clause
    • Using ORDER BY with GROUP BY
    • Using HAVING with GROUP BY
    • TOP Values
    • Joining Tables
    • Cross Joins (Cartesian Products)
    • The Use of Keys in Joining
    • Join Notation
    • Inner Joins
    • Outer Joins
    • Combining Inner and Outer Joins
    • Self Joins
    • Lab 2 Overview
      • Build a Simple Select Query
      • Build an Aggregate Query
      • Join Tables with an Inner Join
      • Build an Aggregate Query with Multiple Inner Joins
      • Build an Aggregate Query with Inner and Outer Joins
    • Modifying Data with Transact-SQL
    • Inserting Data
    • Inserting a Single Value
    • Inserting Multiple Values
    • Inserting Multiple Rows
    • Creating a New Table with SELECT INTO
    • Temporary Tables
    • Inserting with Output
    • Using Bulk Copy to Insert Data
    • Updating Data
    • Updating a Single Row
    • Updating Multiple Rows and Columns
    • Updating from Another Table
    • Updating with TOP
    • Updating Large Value Types with UPDATE .WRITE
    • Deleting Data
    • Deleting a Single Row
    • Deleting Multiple Rows
    • Understanding Transaction Isolation
    • Isolation Levels
    • Blocking and Deadlocks
    • Using Snapshot Isolation
      • Lab 3 Overview
      • Add a Product
      • Edit a Product
      • Delete a Product

What Students Receive

Each student will receive:
  • A printed 200 page courseware manual for use during and after class, with hands-on SQL labs and exercises.
  • A PC headset for communicating with your instructor and fellow students during your SQL Queries Training Class
  • A course completion certificate upon successful completion of your SQL Queries Training course
  • Six months of post-class email support from a SQL Query Instructor