Training Courses

search courses

20761A: Querying Data with Transact-SQL (3 Days)

Guaranteed to Run Scheduled Fully Booked
Location
Wellington
Feb 2021
1
Mar 2021
15
Apr 2021
27
Jun 2021
8
Jul 2021
19
Aug 2021
30
Oct 2021
11
Nov 2021
22
Voucher
Voucher: 3-day voucher
Code: 20761A
Length: 3 Days
Overview

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

Delivery format(s)
On-Site
On-Site
Classroom
Classroom
Anywhere
Anywhere
OBJECTIVES
The main purpose of this 3-day icourse 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.This is a cut-down version of 5-day class 20761.
PREREQUISITES
  • Working knowledge of relational databases.
  • Basic knowledge of the Microsoft Windows operating system and its core functionality.
WHAT YOU’LL LEARN
  • Describe the basic architecture and concepts of Microsoft SQL Server 2016.
  • Understand the similarities and differences between Transact-SQL and other computer languages.
  • Write SELECT queries
  • Query multiple tables
  • Sort and filter data
  • Describe the use of data types in SQL Server
  • Modify data using Transact-SQL
  • Use built-in functions
  • Group and aggregate data
  • Use subqueries
  • Use table expressions
  • Use set operators
  • Use window ranking, offset and aggregate functions
  • Implement pivoting and grouping sets
  • Execute stored procedures
  • Program with T-SQL
  • Implement error handling
  • Implement transactions
WHO SHOULD ATTEND

The course will very likely be well attended by SQL power users who aren't necessarily database-focused; namely, report writers, business analysts and client application developers.

COURSE OUTLINE

Introduction to Microsoft SQL

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

Introduction to T-SQL Querying

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

Writing SELECT Queries

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

Querying Multiple Tables

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

Sorting and Filtering Data

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

Working with SQL Server 2016 Data Types

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

Using DML to Modify Data

  • Inserting Data
  • Modifying and Deleting Data

Using Built-In Functions

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

Grouping and Aggregating Data

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

Using Subqueries

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

Using Set

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

Enquiry Form