Download PDF
of this course

MS20761 - Querying Data with Transact-SQL (MS20761)

  • Overview
  • Who Should Attend
  • Certifications
  • Prerequisites
  • Objectives
  • Content
  • Schedule
Course Overview

Duration : 3 Days

The main purpose of this 3 day instructor led  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.

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.

Who Should Attend

  • This course is intended for Database Administrators, Database Developers, and Business Intelligence professionals. 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 Certifications

This course is part of the following Certifications:

Prerequisites

  • Working knowledge of relational databases.

  • Basic knowledge of the Microsoft Windows operating system and its core functionality.

Course Objectives

After completing this course, students will be able to:

  • 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

Course Content

Module 1: Introduction to Microsoft SQL Server 2016

This module introduces SQL Server , the versions of SQL Server , including cloud versions of SQL Server , and how to connect to SQL Server using SQL Server Management Studio.

Lessons

  • The Basic Architecture of SQL Server 
  • SQL Server Edition and Versions
  • Getting started with SQL Server Management Studio

Lab : Working with SQL Server 2016 Tools

After completing this module , you will be able to :

  • Describe the architecture and editions of SQL Server 2012
  • Work with SQL Server Management Studio

 

Module 2 : Introduction to T-SQL Querying

This module introduces the elements of T-SQL and their role in writing queries , describes the use of sets in SQL Server , describes the use of predicate logic in SQL Server , and describes the logic in SQL Server , and describes the logical order of operations in SELECT statements.

Lessons

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

Lab : Introduction to Transact-SQL Querying 

After completing this module , you will be able to :

  • Describe the elements of T-SQL and their role in writing queries
  • Describes the use of sets in SQL Server
  • Describes the use of predicate logic in SQL Server
  • Describe the logical order of operations in SELECT statments

Module 3: Writing SELECT Queries

This module introduces the fundamentals of the SELECT statement , focusing on queries against a single table.

Lessons

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

Lab : Writing Basic SELECT Statements

After completing this module , you will be able to :

  • Write simple SELECT statements
  • Eliminate duplicates using the DISTINCT clause
  • Use column and table aliases
  • Write simple CASE Expressions

 

Module 4 : Querying Multiple Tables

This module explains how to write queries which combine data from multiple sources in SQL Server . The module introduces the use of JOINS in T-SQL queries as a mechanism for retrieving data from multiple tables.

Lessons

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

Lab : Querying Multiple Tables

After completing this module , you will be able to :

  • Describe how multiple tables may be queried in a SELECT statements using joins.
  • Writing queries that use inner joins
  • Write queries that use outer joins 
  • Write queries that use self-joins and cross joins.

 

Module 5: Sorting and Filtering Data

This module explains how to enhance queries to limit the rows they return , and to control the order in wich the rows are displayed.The module also discusses how to resolve missing and unknown results.

Lessons 

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

Lab : Sorting and Filtering Data

After completing this module , you will be able to :

  • Filter data with predicates in the WHERE clause
  • Sort data using ORDER BY
  • Filter data in the SELECT clause with TOP
  • Filter data with OFFSET and FETCH

Module 6 : Working with SQL Server 2016 Data types

This module explains the data types SQL Server uses to store data. It introduces the many types of numeric and special-use data types. It also explains conversions between data types , and the importance of type precendence.

Lessons 

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

Lab : Working with SQL Server 2016 Data Types

After completing this module , you willbe able to ;

  • Describe numeric data types , type precendence and type conversions.
  • Write queries using character data types
  • Write queries using date and time data types.

 

Module 7 : Using DM to Modify Data

This module describes the use of Transact-SQL Data Manipulation Language to perform inserts , updates , and deletes to your data .

Lessons

  • Inserting Data
  • Modifying and Deleting Data

Lab : Using DML to Modify Data

After completing this module , you will be able to : 

  • Insert new data into your tables.
  • Update and delete existing records in your tables.

 

Module 8: Using Built-In Functions 

This modules introduces the use of functions that are built in to SQL Server Denali, and will discuss some common usages including data type conversion , testing for logical results and nullability.

Lessons

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

Lab : Using Built-In Functions 

After completing this module , you will be able to :

  • Write queries with built-In scalar functions.
  • Use a conversion functions
  • Use logical functions
  • Use functions that work with NULL

 

Module 9 : Grouping and Aggregating Data

This module introduces methods for grouping data within a query , aggreating the grouped data and filtering groups with HAVING.The module is designed to help the student grasp why a SELECT clause has restrictions placed upon column naming in the GROUP BY clause as well as which columns may be listed in the SELECT clause.

Lessons 

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

Lab : Grouping and Aggregating Data

After completing this module , you will e able to :

  • Write queries which summarize data using built-in aggreggate functions .
  • Use the GROUP BY clauseto arrange rows into groups.
  • Use the HAVING clause to filter out groups based on search confition.

 

Module 10 : Using Subqueries

This module will introduce the use of subqueries in various in various parts of a SELECT statement. IT will include the use of scalar and multi-result subqueries, and the use of the IN and EXISTS operators.

Lessons

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

Lab : Using Subqueries

After completing this module , you will be able to :

  • Describe the uses of queries which are nested within other queries .
  • Write self-contained subqueries which return scalar or multi-valued results
  • Write correlated subqueries which return scalar or multi-valued results 
  • Use the EXISTS predicate to efficeiently check for the exxistence of rows in a subquery.

 

Module 11 : Using Set Operators 

This module introdcues the set operators UNION, INTERSECT , and EXCEPT to compare rows between two input sets.

Lessons

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

Lab : Using SET Operators 

After completing this module , you will be able to :

  • Write queries using UNION , EXCEPT , and INTERSECT operators.
  • Use the APPLY operator .

Course ID: MS20761

3 Days Course
SGD 2100.00
 
Singapore

Show Schedule for 1 Month  3 Months  All 
Date Country Location Register
06 Jun 2018 - 08 Jun 2018 Singapore Singapore