SQL Server Development 2016 Training

Objectives :

  • 1- Learn to describe key capabilities and components of SQL Server.
  • 2- Learn to describe T-SQL, sets, and predicate logic.
  • 3- Learn to write a single table SELECT statement.
  • 4- Learn to write a multi-table SELECT statement.
  • 5- Learn to write SELECT statements with filtering and sorting.
  • 6- Learn to describe how SQL Server uses data types.
  • 7- Learn to write DML statements.
  • 8- Learn to write queries that use built-in functions.
  • 9- Learn to write queries that aggregate data.
  • 10- Learn to write subqueries.
  • 11- Learn to create and implement views and table-valued functions.
  • 12- Learn to use set operators to combine query results.
  • 13- Learn to create and implement stored procedures.
  • 14- Learn to add programming constructs such as variables, conditions, and loops to T-SQL code.

1. Introduction to Microsoft SQL Server 2016
  • 1- The Basic Architecture of SQL Server
  • 2- SQL Server Editions and Versions
  • 3- Getting Started with SQL Server Management Studio
  • 4- Lab: Working with SQL Server 2016 Tools
    • 1. Working with SQL Server Management Studio
    • 2. Creating and Organizing T-SQL Scripts

B. Introduction to T-SQL Querying
  • I. Introducing T-SQL
  • II. Understanding Set
  • III. Understanding Predicate Logic
  • IV. Understanding the Logical Order of Operations in SELECT statements
  • V. Lab: Introduction to T-SQL Querying
  • 1. Executing Basic SELECT Statement
    • 2. Executing Queries that Filter Data using Predicates
    • 3. Executing Queries That Sort Data Using ORDER BY

    B. Writing SELECT Queries
    • I. Writing Simple SELECT Statements
    • II. Eliminating Duplicates with DISTINCT
    • III. Using Column and Table Aliases
    • IV. Writing Simple CASE Expressions
    • V. Lab: Writing Basic SELECT Statements
  • 1. Writing Simple SELECT Statements
  • 1. Writing Simple SELECT Statements
  • 2. Eliminating Duplicates Using DISTINCT
  • 3. Using Column and Table Aliases
  • 4. Using a Simple CASE Expression

  • B. Querying Multiple Tables
    • I. Understanding Joins
    • II. Querying with Inner Joins
    • III. Querying with Outer Joins
    • IV. Querying with Cross Joins and Self Joins
    • V. Lab: Querying Multiple Tables
  • 1. Writing Queries that use Inner Joins
  • 2. Writing Queries that use Multiple-Table Inner Joins
  • 3. Writing Queries that use Self-Joins
  • 4. Writing Queries that use Outer Joins
  • 5. Writing Queries that use Cross Joins

  • B. Sorting and Filtering Data
    • I. Sorting Data
    • II. Filtering Data with Predicates
    • III. Filtering Data with TOP and OFFSET-FETCH
    • IV. Working with Unknown Values
    • V. Lab: Sorting and Filtering Data
  • 1. Writing Queries that Filter Data using a WHERE Clause
  • 2. Writing Queries that Sort Data Using an ORDER BY Clause
  • 3. Writing Queries that Filter Data Using the TOP Option

  • B. Working with SQL Server 2016 Data Types
    • I. Writing Queries that Return Date and Time Data
    • II. Writing Queries that use Date and Time Functions
    • III. Writing Queries That Return Character Data
    • IV. Writing Queries That Return Character Functions

    B. Using DML to Modify Data
    • I. Inserting Data
    • II. Modifying and Deleting Data
    • III. Lab: Using DML to Modify Data
    • 1. Inserting Data
    • 2. Updating and Deleting Data

    B. Using Built-In Functions
    • I. Writing Queries with Built-In Functions
    • II. Using Conversion Functions
    • III. Using Logical Functions
    • IV. Using Functions to Work with NULL
    • V. Lab: Using Built-In Functions

    • 1. Writing Queries That Use Conversion Functions
    • 2. Writing Queries that use Logical Functions
    • 3. Writing Queries that Test for Nullability

    B. Grouping and Aggregating Data
    • I. Using Aggregate Functions
    • II. Using the GROUP BY Clause
    • III. Filtering Groups with HAVING
    • IV. Lab: Grouping and Aggregating Data
    • 1. Writing Queries That Use the GROUP BY Clause
    • 2. Writing Queries that Use Aggregate Functions
    • 3. Writing Queries that Use Distinct Aggregate Functions
    • 4. Writing Queries that Filter Groups with the HAVING Clause

    B. Using Subqueries

    • I. Writing Self-Contained Subqueries
    • II. Writing Correlated Subqueries
    • III. Using the EXISTS Predicate with Subqueries
    • IV. Lab: Using Subqueries
    • 1. Writing Queries That Use Self-Contained Subqueries
    • 2. Writing Queries That Use Scalar and Multi-Result Subqueries
    • 3. Writing Queries That Use Correlated Subqueries and an EXISTS Clause

    B. Using Table Expressions

    • I. Using Views
    • II. Using Inline Table-Valued Functions
    • III. Using Derived Tables
    • IV. Using Common Table Expressions
    • V. Lab: Using Table Expressions

    • 1. Writing Queries That Use Views
    • 2. Writing Queries That Use Derived Tables
    • 3. Writing Queries That Use Common Table Expressions (CTEs)
    • 4. Writing Queries That Sue Inline Table-Valued Expressions

    A. Executing Stored Procedures

    • I. Querying Data with Stored Procedures
    • II. Passing Parameters to Stored procedures
    • III. Creating Simple Stored Procedures
    • IV. Working with Dynamic SQL
    • V. Lab: Executing Stored Procedures
    • 1. Using the EXECUTE statement to Invoke Stored Procedures
    • 2. Passing Parameters to Stored procedures
    • 3. Executing System Stored Procedures

    B. Programming with T-SQL

    • I. T-SQL Programming Elements
    • II. Controlling Program Flow
    • III. Lab: Programming with T-SQL
    • 1. Declaring Variables and Delimiting Batches
    • 2. Using Control-Of-Flow Elements
    • 3. Using Variables in a Dynamic SQL Statement
    • 4. Using Synonyms

    B. Implementing Error Handling

    • I. Implementing T-SQL error handling
    • II. Implementing structured exception handling
    • III. Lab: Implementing Error Handling
    • 1. Redirecting errors with TRY/CATCH
    • 2. Using THROW to pass an error message back to a client

    B. Implementing Transactions

    • I. Transactions and the database engines
    • II. Controlling transactions
    • III. Lab: Implementing Transactions
    • 1. Controlling transactions with BEGIN, COMMIT, and ROLLBACK
    • 2. Adding error handling to a CATCH blockck to a client