Live Chat
Monday - Friday 8am - 6pm EST Chat Now
Contact Us
Monday - Friday 8am - 8pm EST 1-800-268-7737 Other Contact Options
Checkout

Cart () Loading...

    • Quantity:
    • Delivery:
    • Dates:
    • Location:

    $

Querying Data with Transact-SQL (20761)

Learn basic Transact-SQL queries for Microsoft SQL Server 2016.

This course is designed to introduce students to Transact-SQL. It is designed in such a way that the first part of the content can be taught as a course to students requiring the knowledge for other courses in the SQL Server curriculum.  The remaining content focuses on the skills required to take exam 70-761.

*For more information on Microsoft SATVs, click here.

This course is also available in the On-Demand delivery format with digital Microsoft Official Courseware (dMOC). Click here to purchase.

 

Learn more about this topic. View the recorded webinar Overview of Microsoft SQL Server and High Availability Options

GK# 4395 Vendor# M20761
Vendor Credits:
  • Global Knowledge Delivered Course
  • Training Exclusives
  • GSA Eligible
No matching courses available.
Start learning as soon as today! Click Add To Cart to continue shopping or Buy Now to check out immediately.
Scheduling a custom training event for your team is fast and easy! Click here to get started.
$
Your Selections:
Location:
No available dates

Who Should Attend?

The main purpose of the course is to give students a good understanding of the Transact-SQL language which is used by all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence. As such, the primary target audience for this course is: Database Administrators, Database Developers and BI professionals.

What You'll Learn

  • Describe key capabilities and components of SQL Server.
  • Describe T-SQL, sets, and predicate logic.
  • Write a single table SELECT statement.
  • Write a multi-table SELECT statement.
  • Write SELECT statements with filtering and sorting.
  • Describe how SQL Server uses data types.
  • Write DML statements.
  • Write queries that use built-in functions.
  • Write queries that aggregate data.
  • Write subqueries.
  • Create and implement views and table-valued functions.
  • Use set operators to combine query results.
  • Write queries that use window ranking, offset, and aggregate functions.
  • Transform data by implementing pivot, unpivot, rollup and cube.
  • Create and implement stored procedures.
  • Add programming constructs such as variables, conditions, and loops to T-SQL code

Course Outline

1. Introduction to Microsoft SQL

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

2. Introduction to T-SQL Querying

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

3. Writing SELECT Queries

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

4. Querying Multiple Tables

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

5. Sorting and Filtering Data

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

6. Working with SQL Server 2016 Data Types

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

7. Using DML to Modify Data

  • Inserting Data
  • Modifying and Deleting Data

8. Using Built-In Functions

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

9. Grouping and Aggregating Data

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

10. Using Subqueries

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

11. Using Set Operators

12. Using Set

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

13. Using Windows Ranking, Offset, and Aggregate 

  • Creating Windows with OVER
  • Exploring Window Functions

14. Pivoting and Grouping Sets

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

15. Executing Stored 

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

16. Programming with T-SQL

  • T-SQL Programming Elements
  • Controlling Program Flow

17. Implementing Error Handling

  • Implementing T-SQL Error Handling
  • Implementing Structured Exception Handling

18. Implementing Transactions

  • Transactions and the Database Engines
  • Controlling Transactions
BUY NOW

Labs Outline

Lab 1: Working with SQL Server 2016 Tools
Lab 2: Introduction to Transact-SQL Querying
Lab 3: Writing Basic SELECT Statements
Lab 4: Querying Multiple Tables
Lab 5: Sorting and Filtering Data
Lab 6: Working with SQL Server 2016 Data Types
Lab 7: Using DML to Modify Data
Lab 8: Using Built-In Functions
Lab 9: Grouping and Aggregating Data
Lab 10: Using Subqueries
Lab 11: Using Table Expressions
Lab 12: Using SET Operators
Lab 13: Using Windows Ranking, Offset, and Aggregate 
Lab 14: Pivoting and Grouping Sets
Lab 15: Executing Stored 
Lab 16: Programming with T-SQL
Lab 17: Implementing Error Handling
Lab 18: Implementing Transactions

BUY NOW

Prerequisites

Students should have a working knowledge of relational databases and a basic knowledge of the Microsoft Windows operating system and its core functionality.

Related Certifications

Earning an MCSA: SQL 2016 Database Development certification validates your skills in building and implementing databases across organizations and will qualify you for a position as a database developer.

Vendor Credits

This course is eligible for Software Assurance Training Vouchers (SATVs).

BUY NOW

Follow-On Courses

The following courses will assist you in expanding your SQL Server database administration and development skills.