Querying Data With Transact SQL (20761B) Course

This course is designed to introduce students to Transact-SQL. It is designed in such a way that the first three days can be taught as a course to students requiring the knowledge for other courses in the SQL Server curriculum.

Apply Today

Instructor Led Learning

Duration: 5 Days
Registration Open Now!

Video Learning

Duration: 5 Days
Registration Open Now!

What you will learn

• Introduction to Microsoft SQL Server 2016
•Introduction to T-SQL Querying
• Writing SELECT Queries
• Querying Multiple Tables
• Sorting and Filtering Data
• Working with SQL Server 2016 Data Types
• Using DML to Modify Data
• Using Built-In Functions
• Grouping and Aggregating Data
• Using Subqueries
• Using Table Expressions
• Using Windows Ranking, Offset, and Aggregate Functions
• Pivoting and Grouping Sets
• Executing Stored Procedures
• Programming with T-SQL
• Implementing Error Handling
• Implementing Transactions

Basic computer skills

Computers made easy course

FULL COURSE OUTLINE

Querying Data With Transact SQL (20761 B)

Lesson 1: Introduction to Microsoft SQL Server 2016

  • The Basic Architecture of SQL Server
  • SQL Server Editions and Versions
  • Getting Started with SQL Server Management Studio

Lab : Working with SQL Server 2016 Tools

  • Working with SQL Server Management Studio
  • Creating and Organizing T-SQL Scripts
  • Using Books Online

Lesson 2: Introduction to T-SQL Querying

  • Introducing T-SQL
  • Understanding Sets
  • Understanding Predicate Logic
  • Understanding the Logical Order of Operations in SELECT statements

Lab : Introduction to T-SQL Querying

  • Executing Basic SELECT Statements
  • Executing Queries that Filter Data using Predicates
  • Executing Queries That Sort Data Using ORDER BY

Lesson 3: Writing SELECT Queries

  • Writing Simple SELECT Statements
  • Eliminating Duplicates with DISTINCT
  • Using Column and Table Aliases
  • Writing Simple CASE Expressions

Lab : Writing Basic SELECT Statements

  • Writing Simple SELECT Statements
  • Eliminating Duplicates Using DISTINCT
  • Using Column and Table Aliases
  • Using a Simple CASE Expressions

Lesson 4: Querying Multiple Tables

  • Understanding Joins
  • Querying with Inner Joins
  • Querying with Outer Joins
  • Querying with Cross Joins and Self Joins

Lab : Querying Multiple Tables

  • Writing Queries that use Inner Joins
  • Writing Queries that use Multiple-Table Inner Joins
  • Writing Queries that use Self-Joins
  • Writing Queries that use Outer Joins
  • Writing Queries that use Cross Joins

Lesson 5: Sorting and Filtering Data

  • Sorting Data
  • Filtering Data with Predicates
  • Filtering Data with TOP and OFFSET-FETCH
  • Working with Unknown Values

Lab : Sorting and Filtering Data

  • Writing Queries that Filter Data using a WHERE Clause
  • Writing Queries that Sort Data Using an ORDER BY Clause
  • Writing Queries that Filter Data Using the TOP Option

Lesson 6: Working with SQL Server 2016 Data Types

  • Introducing SQL Server 2016 Data Types
  • Working with Character Data
  • Working with Date and Time Data

Lab : Working with SQL Server 2016 Data Types

  • Writing Queries that Return Date and Time Data
  • Writing Queries that use Date and Time Functions
  • Writing Queries That Return Character Data
  • Writing Queries That Return Character Functions

Lesson 7: Using DML to Modify Data

  • Inserting Data
  • Modifying and Deleting Data

Lab : Using DML to Modify Data

  • Inserting Data
  • Updating and Deleting Data

Lesson 8: Using Built-In Functions

  • Writing Queries with Built-In Functions
  • Using Conversion Functions
  • Using Logical Functions
  • Using Functions to Work with NULL

Lab : Using Built-In Functions

  • Writing Queries That Use Conversion Functions
  • Writing Queries that use Logical Functions
  • Writing Queries that Test for Nullability

Lesson 9: Grouping and Aggregating Data

  • Using Aggregate Functions
  • Using the GROUP BY Clause
  • Filtering Groups with HAVING

Lab : Grouping and Aggregating Data

  • Writing Queries That Use the GROUP BY Clause
  • Writing Queries that Use Aggregate Functions
  • Writing Queries that Use Distinct Aggregate Functions
  • Writing Queries that Filter Groups with the HAVING Clause

Lesson 10: Using Subqueries

  • Writing Self-Contained Subqueries
  • Writing Correlated Subqueries
  • Using the EXISTS Predicate with Subqueries

Lab : Using Subqueries

  • Writing Queries That Use Self-Contained Subqueries
  • Writing Queries That Use Scalar and Multi-Result Subqueries
  • Writing Queries That Use Correlated Subqueries and an EXISTS Clause

Lesson 11: Using Table Expressions

  • Using Views
  • Using Inline Table-Valued Functions
  • Using Derived Tables
  • Using Common Table Expressions
  • Writing Queries That Use Views
  • Writing Queries That Use Derived Tables
  • Writing Queries That Use Common Table Expressions (CTEs)
  • Writing Queries That Sue Inline Table-Valued Expressions

Lab : Using Table Expressions

Lesson 12: Using Set Operators

  • Writing Queries with the UNION operator
  • Using EXCEPT and INTERSECT
  • Using APPLY

Lab : Using Set Operators

  • Writing Queries That Use UNION Set Operators and UNION ALL
  • Writing Queries That Use CROSS APPLY and OUTER APPLY Operators
  • Writing Queries That Use the EXCEPT and INTERSECT Operators

Lesson 13: Using Windows Ranking, Offset, and Aggregate Functions

  • Creating Windows with OVER
  • Exploring Window Functions

Lab : Using Windows Ranking, Offset, and Aggregate Functions

  • Writing Queries that use Ranking Functions
  • Writing Queries that use Offset Functions
  • Writing Queries that use Window Aggregate Functions

Lesson 14: Pivoting and Grouping Sets

  • Writing Queries with PIVOT and UNPIVOT
  • Working with Grouping Sets

Lab : Pivoting and Grouping Sets

  • Writing Queries that use the PIVOT Operator
  • Writing Queries that use the UNPIVOT Operator
  • Writing Queries that use the GROUPING SETS CUBE and ROLLUP Subclauses

Lesson 15: Executing Stored Procedures

  • Querying Data with Stored Procedures
  • Passing Parameters to Stored procedures
  • Creating Simple Stored Procedures
  • Working with Dynamic SQL

Lab : Executing Stored Procedures

  • Using the EXECUTE statement to Invoke Stored Procedures
  • Passing Parameters to Stored procedures
  • Executing System Stored Procedures

Lesson 16: Programming with T-SQL

  • T-SQL Programming Elements
  • Controlling Program Flow

Lab : Programming with T-SQL

  • Declaring Variables and Delimiting Batches
  • Using Control-Of-Flow Elements
  • Using Variables in a Dynamic SQL Statement
  • Using Synonyms

Lesson 17: Implementing Error Handling

  • Implementing T-SQL error handling
  • Implementing structured exception handling

Lab : Implementing Error Handling

  • Redirecting errors with TRY/CATCH
  • Using THROW to pass an error message back to a client

Lesson 18: Implementing Transactions

  • Transactions and the database engines
  • Controlling transactions

Lab : Implementing Transactions

  • Controlling transactions with BEGIN, COMMIT, and ROLLBACK
  • Adding error handling to a CATCH block

Join Over 10,000 Students that have studied with MasterGrade IT Now

Become Part of MasterGrade IT to Further Your Career.