Transact-SQL 101

This series will introduce you to the Transact-SQL (T-SQL) language. T-SQL is used to interact with your relational database for such tasks as retrieving records from your tables, updating, inserting and deleting data. This series is geared towards the absolute beginner and will give you the foundation needed to becoming a T-SQL expert.

Total Time: 4 Hours 37 Minutes              Total Videos: 29

  • Lesson 1: SQL Server 2005 and Transact-SQL

    • Video 1.1: Introduction to SQL
      (Running Time: 6:34)

      In this introductory video, you will learn what SQL is and how it is used. You will also learn what T-SQL is and how it is specific to SQL Server 2005. In addition, you will see a quick demonstration of T-SQL using Microsoft's SQL Server Management Studio.


    • Video 1.2: Using SQL Server Management Studio
      (Running Time: 20:24)

      This video will introduce you to SQL Server Management Studio, which is used to manage all aspects of SQL Server 2005. SQL Server Management Studio (SSMS) replaces Enterprise Manager, Query Analyzer and Analysis Services from previous versions of SQL Server. You will learn how to use some of the new features included with SSMS as well as how to customize the look of the application to meet your needs.


    • Video 1.3: Installing the Sample Databases
      (Running Time: 4:58)

      This video will show you how to install the AdventureWorks and AdventureWorksLT Databases provided by Microsoft. These databases will be used for all of the examples in this video series.

  • Lesson 2: SQL Server Data Types

    • Video 2.1: Understanding and Using Data Types
      (Running Time: 16:02)

      This video will provide an overview to the Data Types that are available in SQL Server. Each of the most common data types will be explained and concepts will be reviewed to help you make the right data type choices when designing your database.


    • Video 2.2: Understanding and Enforcing Data Integrity
      (Running Time: 7:36)

      This video will introduce you to the concept of constraints and show you how to implemenent them in your database. You will also learn about other data properties that you can define that will help you maintain data integrity within your database.


  • Lesson 3: Retrieving Data from your Database (Part 1)

    • Video 3.1: Retrieving Data using the SELECT Statement
      (Running Time: 10:55)

      The SELECT statement is the most common SQL statement and is used to retreive data from your database. This video will show you how to write your first SELECT statement and will also provide several examples on how to use this simple, but powerful command.


    • Video 3.2: Limiting your Result Set using TOP
      (Running Time: 3:38)

      This video will show you how to use the TOP command with your SELECT statements to limit the number of rows returned in your result sets.


  • Lesson 4: Retrieving Data from your Database (Part 2)

    • Video 4.1: Filtering results using the WHERE clause
      (Running Time: 9:15)

      This video will demonstrate how to filter and find data that is stored in your database tables by using the WHERE clause. You will also be introduced to the Comparison Operators and learn how they are used.


    • Video 4.2: Using AND, OR and other Logical Operators
      (Running Time: 9:10)

      Logical Operators allow you to perform more sohpisticated searches to find data in your database and this video will provide you with an introduction to the AND, OR, BETWEEN, IN and LIKE operators and show you how they are used.


    • Video 4.3: Finding Data using the Like Operator
      (Running Time: 13:23)

      This video will provide an overview of how to perform pattern matching when searching for records in your database tables. You will see several examples of how to use wildcard characters and escape characters when performing searches using the LIKE operator.

  • Lesson 5: Joining Tables

    • Video 5.1: Joining tables using INNER Joins
      (Running Time: 10:22)

      Joining tables to find related data in your database is the true power of any relational database management system and SQL Server 2005. This video will show you the fundamentals of performing INNER JOINS and show you how to write them on your own.


    • Video 5.2: Using INNER Joins to More Than Two Tables
      (Running Time: 7:15)

      Joining multiple tables can be complicated, but this video will help you understand and write multi-table INNER JOINS quickly. You will see several examples and understand the concepts behind multi-table joins to retrieve data correctly and efficiently.


    • Video 5.3: Joining Tables using LEFT, RIGHT and FULL OUTER Joins
      (Running Time: 10:54)

      This video will introduce you to OUTER JOINS and show you the differences between LEFT, RIGHT and FULL OUTER JOINS. You will see several examples and will understand which type of join is right for you in any situation.

  • Lesson 6: Modifying Data in your Database

    • Video 6.1: Inserting Records using INSERT
      (Running Time: 10:56)

      This video will introduce you to the Data Manipulation Language (DML) and show you how to insert records in your tables using the INSERT command.


    • Video 6.2: Updating Records using UPDATE
      (Running Time: 9:13)

      After watching this video, you will understand the different ways to write an UPDATE statement and be able to write them on your own.


    • Video 6.3: Deleting Records using DELETE
      (Running Time: 3:36)

      This video will show you how to delete records in your tables by using the DELETE command. You will see several examples of the DELETE command and understand how to use them safely.

  • Lesson 7: Using String and Date Functions

    • Video 7.1: Introduction to SQL Server 2005 Functions
      (Running Time: 10:59)

      This video will provide an introduction to Functions in SQL Server 2005. Functions can be used in a variety of ways and are a powerful feature of T-SQL. After watching this video, you will understand what a function is and how they are used.


    • Video 7.2: Using the LEFT, RIGHT and Substring Functions
      (Running Time: 9:05)

      Being able to manipulate and combine character strings is a skill that everyone using T-SQL needs to understand. This video will introduce you to the functions that allow you to work with string data and show you several common examples of when to use them.


    • Video 7.3: Manipulating Strings using LTRIM, RTRIM and Other String Functions
      (Running Time: 8:47)

      This video will show you more functions that you can use when working with character strings. You will see several examples and become familiar with how to use the most common string functions.


    • Video 7.4: Understanding and Using SQL Server's Date Functions
      (Running Time: 5:14)

      Working with dates in SQL Server 2005 can be tricky, but this video will show you some of the most common functions you can use to make working with dates easier. You will learn how to work with dateparts, such as months, years and days and more.


    • Video 7.5: Performing Date Calculations using DATEADD and DATEDIFF
      (Running Time: 5:50)

      This video will show you how to use the DATEADD and DATEDIFF functions to perform date calculations. I will show you examples of each function and point out a few things to consider when performing these types of calculations.

  • Lesson 8: Aggregate, Ranking and Other Functions

    • Video 8.1: Using Aggregate Functions to Group Data
      (Running Time: 15:15)

      Calculating an average or determining the sum of multiple records can be achieved by using the Aggregate functions provided with SQL Server 2005. This video will show you how to use the most common aggregate functions and explain to you when they can be used.


    • Video 8.2: Understanding ROW NUMBER and RANK and the Other Ranking Functions
      (Running Time: 9:08)

      This video will introduce the Ranking functions that are a new category of functions introduced with SQL Server 2005. The ranking functions allow you to rank the records returned in your result sets, and more. You will see several examples on how they can be used.


    • Video 8.3: Changing Datatypes using CAST and CONVERT
      (Running Time: 11:31)

      In this video, you will learn how to use the CAST and CONVERT functions to change the data types associated with your data. You will also be introduced to the various formatting options available to you and understand how and when to use these functions.


  • Lesson 9: Subqueries

    • Video 9.1: Understanding and Using Subqueries (Part 1)
      (Running Time: 10:49)

      This video will introduce you to subqueries and show you how and when to use them in your SQL. You will see examples on how to use them into your SQL statements and even how to use them when writing INSERTS, UPDATES and DELETES.


  • Lesson 10: Control-Of-Flow Language

    • Video 10.1: Display Alternate Values using the CASE Function
      (Running Time: 9:38)

      The CASE function allows you to display alternate values in your result sets. In this video, you will see several examples of how to use the CASE function and how to use them in your SQL statements.


  • Lesson 11: Introduction to Temporary and Derived Tables

    • Video 11.1: Using Common Table Expressions
      (Running Time: 7:02)

      This video will introduce you to Common Table Expressions, which is a new feature included with SQL Server 2005. After watching this video, you will undertand how and when to use Common Table Expressions and be able to write them on your own.


    • Video 11.2: Writing Recursive Queries using Common Table Expressions
      (Running Time: 9:25)

      This video will walk you through how to write recursive queries using Common Table Expressions, a new feature in SQL Server 2005. I will provide examples and walk you through each one step-by-step.

  • Lesson 12: Understanding Variables

    • Video 12.1: Introduction to SQL Server Variables
      (Running Time: 10:43)

      This video will introduce you to the concept of variables within SQL Server 2005. You will see how variables are used and when you can take advantage of them in your SQL Statements.