SQL Server: T-SQL Fundamentals Training

SQL Server: T-SQL Fundamentals

• Introduction
  • Prerequisites
  • Installing the Practice Files
    • Software Requirements
    • Sample Databases
    • Sample Code Installation
  • About the Author
• Transact-SQL Programming
  • 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
    • BEGIN...END
    • GOTO, RETURN, and Label Statements
    • CASE Expression
    • WHILE Statement
    • WAITFOR Statement
  • Ranking Results
• Lab 1: Transact-SQL Programming
  • Lab 1 Overview
  • Convert Data Types
  • Use Built-In Functions
  • Use Control-of-Flow Constructs
• Data Selection Queries
  • 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 Null
    • 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 the 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: Data Selection Queries
  • 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
  • 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
    • 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: Modifying Data
  • Lab 3 Overview
  • Add a Product
  • Edit a Product
  • Delete a Product

Before Taking this Class

This course assumes that you have at least a basic familiarity with the concept of relational databases and a basic understanding of what SQL Server is and the high-level tools in it, as well as how to create and manage objects using Management Studio. You should also have a basic understanding of how SQL Server implements security, including its authentication and authorization schemes, and how to assign permissions on securable objects to principals.

Software/Setup For this Class

SQL Server 2012 or higher.