Introduction to T-SQL (Online)

The Introduction to T-SQL virtual class is an intensive four-day class that is designed to get you developing with T-SQL very quickly. You will first learn about the structure of a SQL Server database, then you will dive right in learning how to query the tables. Next, we will cover how to sort and filter the results. Then we will dive into how to write queries that involve multiple tables and summary queries. You will also learn how to modify data and finally about the logical programming aspects of T-SQL.

IMPORTANT: When you purchase a seat in this class you also receive 2 hours of our BI Virtual Mentor service for assistance with YOUR project work. This remote service lets you work ONE on ONE with our BI mentors to accelerate YOUR project and further improve YOUR skills. Your Virtual Mentor hours can be used during business hours anytime during the 12 months following your class. To set up a session, download this simple questionnaire, fill it out, and send it as an attachment to mentor@pragmaticworks.com. This unique Pragmatic Works offering insures that your training will translate into real world success for YOUR projects.

All virtual training students are provided a previously recorded version of the class that are available for 7 days after the end of the class.

Outline

    Day 1 – Writing queries to return data

  1. Writing queries to return data
    • What is a Database?
    • Tables 
    • Data types
    • Normalization
  2. SQL Server Management Studio (SSMS)
    • Connecting
    • Query window
    • Query designer
  3. The SELECT statement
    • Returning data from a table
    • Specifying the columns
    • Aliasing
    • Sorting the results
    • SSMS tricks
    • Practice: Write SELECT Queries
  4. Expressions
    • Concatenating columns
    • Mathematical expressions
    • Functions
    • NULL
    • TOP and DISTINCT
    • Hands-on Lab: More Practice outside of class

    Day 2 – Filtering the Results

  5. Indexes
    • Two types of table structures
    • Clustered vs.Non-clustered
    • Key columns
  6. The WHERE clause
    • Predicates (Conditions)
    • Operators
    • Like and Wildcards
    • The IN list
  7. Complex WHERE clauses
    • Multiple predicates
    • Using NOT
    • Working with NULL
    • Practice: Write queries with filters
  8. Using a sub-query to filter data
    • IN
    • NOT IN
    • NOT EXISTS
    • Hands-on Lab: Practice writing queries with a WHERE clause

    Day 3 – Writing queries with more than one table

  9. Joining tables
    • Inner join
    • Joining Three or More Tables
    • Hands-on Lab: Practice writing queries with joins
  10. Outer Joins
    • Left Outer Join
    • Right Outer Join
    • Outer Join with Three or More Tables
    • Hands-on Lab: Write queries with outer joins
  11. Other Query Patterns
    • UNION
    • Common table expressions
    • Sub-Queries
    • Hands-on Lab: Practice writing queries with virtual tables

    Day 4 – Modifying Data and Aggregate Queries

  12. Aggregate queries
    • Aggregate functions
    • GROUP BY
    • HAVING
    • Order of Operations
    • Hands-on Lab: Practice writing aggregate queries
  13. Modifying Data
    • CREATE tables
    • INSERT
    • SELECT INTO
    • UPDATE
    • DELETE
    • Hands-on Lab: Practice modifying data
  14. T-SQL Programming
    • Variables
    • Logic
    • Loops
    • Scripts
    • Stored procedures
    • Hands-on Lab: Using stored procedures

Prerequisites

This class is targeted for business analysts, report writers, developers or database administrators who need to write queries against SQL Server databases. Previous SQL Server knowledge is not required. Ideally, the students should be proficient using the Windows operating system and performing typical tasks like uploading or zipping files.

SystemRequirements

  • Microsoft Windows XP Professional, Microsoft Windows Server 2003, Microsoft Vista, Windows 7 or Windows 8
  • 1 GHz or faster CPU
  • 1 GB of available RAM (recommended 2 GB)
  • 20 GB of free hard drive space
  • SQL Server Developer, Standard or Enterprise Edition 2008/2008R2/2012/2014
    • SQL Server Database Engine
    • SQL Server Management Studio
    • Follow this installation guide to ensure all items are installed correctly
  • AdventureWorks2008R2 or AdventureWorks2012 databases installed on the instance.
  • AdventureWorksDW2008R2 or AdventureWorksDW2012 databases installed on the instance.

Some of our clients