Course HighlightsCOURSE

Querying Data with Transact-SQL

From querying and modifying data in SQL Server or Azure SQL to programming with Transact-SQL, learn essential skills that employers need.

Querying Data with Transact-SQL Highlights

Course Enrollment

Starts on

01 January 2019

Enrollment closes on
30 September 2019

  Course duration

Duration

  • Total 24 to 30 hours
  Course Fee

Fee

US$89 - US$149

Course Enrollment

Starts on

01 January 2019

Enrollment closes on
30 September 2019

Course duration

Duration

  • Total 24 to 30 hours
Course Fee

Fee

US$89 - US$149

Enrollment is Closed

About this course

This course is part of the Microsoft Professional Program Certificate in Data Science and the Microsoft Professional Program Certificate in Big Data.

Transact-SQL is an essential skill for data professionals and developers working with SQL databases. With this combination of expert instruction, demonstrations, and practical labs, step from your first SELECT statement through to implementing transactional programmatic logic.

Work through multiple modules, each of which explore a key area of the Transact-SQL language, with a focus on querying and modifying data in Microsoft SQL Server or Azure SQL Database. The labs in this course use a sample database that can be deployed easily in Azure SQL Database, so you get hands-on experience with Transact-SQL without installing or configuring a database server.

What you'll learn

  • Create Transact-SQL SELECT queries
  • Work with data types and NULL
  • Query multiple tables with JOIN
  • Explore set operators
  • Use functions and aggregate data
  • Work with subqueries and APPLY
  • Use table expressions
  • Group sets and pivot data
  • Modify data
  • Program with Transact-SQL
  • Implement error handling and transactions

Meet the instructors

Geoff Allix

Geoff Allix

Microsoft Certified IT Professional for SQL Server Content Master

Geoff Allix is a Microsoft SQL Server subject matter expert and professional content developer at Content Master — a division of CM Group Ltd. Geoff is a Microsoft Certified IT Professional for SQL Server with extensive experience in designing and implementing database and BI solutions on SQL Server technologies. He has provided consultancy services to organizations seeking to implement and optimize database solutions, and contributed as a writer and subject matter expert to many SQL Server courses including the SQL Server 2014 Microsoft Official Curriculum courses.

Graeme Malcolm

Graeme Malcolm

Senior Content Developer Microsoft Learning Experiences

Graeme has been a trainer, consultant, and author for longer than he cares to remember, specializing in SQL Server and the Microsoft data platform. He is a Microsoft Certified Solutions Expert for the SQL Server Data Platform and Business Intelligence. After years of working with Microsoft as a partner and vendor, he now works in the Microsoft Learning Experiences team as a senior content developer, where he plans and creates content for developers and data professionals who want to get the best out of Microsoft technologies.

Course Outline

Enrollment is Closed
Welcome
Pre-Course Survey
Course Introduction
Demo: Course Lab Environment
Preparing for the Labs
Suggested Resources
Getting Started with Transact-SQL
Key Points and Additional Reading
The SELECT Statement
Demo: Using SELECT
Key Points and Additional Reading
Working with Data Types
Demo: Working with Data Types
Key Points and Additional Reading
Working with NULLs
Demo: Working with NULLs
Key Points and Additional Reading
Summary
Removing Duplicates
Sorting Results
Demo: Eliminating Duplicates and Sorting Results
Key Points and Additional Reading
Filtering and Using Predicates
Demo: Filtering and Using Predicates
Key Points and Additional Reading
Summary
Introduction to Joins
Key Points and Additional Reading
Inner Joins
Demo: Using Inner Joins
Key Points and Additional Reading
Outer Joins
Demo: Using Outer Joins
Key Points and Additional Reading
Cross Joins
Key Points and Additional Reading
Self Joins
Key Points and Additional Reading
Summary
UNION Queries
Demo: Creating UNION Queries
Key Points and Additional Reading
INTERSECT and EXCEPT Queries
Demo: Creating INTERSECT and EXCEPT Queries
Key Points and Additional Reading
Summary
Introduction to Functions
Scalar Functions
Logical Functions
Window Functions
Aggregate Functions
Key Points and Additional Reading
Grouping Aggregated Data
Filtering Groups
Key Points and Additional Reading
Summary
Introduction to Subqueries
Demo: Using Subqueries
Key Points and Additional Reading
Correlated Subqueries
Demo: Using Correlated Subqueries
Key Points and Additional Reading
The APPLY Operator
Demo: Using Apply
Key Points and Additional Reading
Summary
Table Expressions Overview
Querying Views
Key Points and Additional Reading
Using Temporary Tables and Table Variables
Key Points and Additional Reading
Querying Table-Valued Functions
Key Points and Additional Reading
Using Derived Tables
Using Common Table Expressions
Key Points and Additional Reading
Summary
Overview of Grouping Sets and Pivoting Data
Grouping Sets
Demo: Grouping Sets
Key Points and Additional Reading
Pivoting Data
Demo: Pivoting Data
Key Points and Additional Reading
Summary
Overview of Modifying Data
Inserting Data into Tables
Demo: Inserting Data
Key Points and Additional Reading
Updating and Deleting Data
Demo: Updating and Deleting Data
Key Points and Additional Reading
Summary
Transact-SQL Programming Fundamentals
Batches
Comments
Variables
Key Points and Additional Reading
Conditional Branching
Key Points and Additional Reading
Looping
Key Points and Additional Reading
Stored Procedures
Key Points and Additional Reading
Summary
Errors and Transactions Overview
Errors and Error Messages
Raising Errors
Demo: Raising Errors
Key Points and Additional Reading
Catching and Handling Errors
Demo: Catching and Handling Errors
Key Points and Additional Reading
Transactions
Demo: Implementing Transactions
Key Points and Additional Reading
Summary
Exam Instructions
Question 1
Question 2
Question 3
Question 4
Question 5
Question 6
Question 7
Question 8
Question 9
Question 10
Question 11
Question 12
Question 13
Question 14
Question 15
Question 16
Question 17
Question 18
Question 19
Question 20
Question 21
Final Assessment
Section 1
Section 2
Section 3
Section 4
End of the Course
Course Certificate

Earn your certificate

Once you have completed this course, you will earn your certificate.