Querying Microsoft SQL Server 2014 (M20461)

5 days

Select a date to register.

Course Description

In this course, you will learn the technical skills required to write basic Transact-SQL (T-SQL) queries for Microsoft SQL Server 2012 and 2014. This is the foundational course for all SQL server related disciplines: database administration, database development and business intelligence. Tools and skills you will learn include: SQL Server Management Studio, T-SQL, SELECT statements in querying multiple tables, data types, data sorting and filtering, data manipulation language (DML), grouping and aggregating data, table expressions, set operators, window functions, T-SQL programming, error handlers, and transaction management in SQL Server.

This course incorporates material from the Official Microsoft Learning Product 20461: Querying Microsoft SQL Server. It covers the skills and knowledge measured by Exam 70-461 and along with on-the-job experience, helps you prepare for the exam.

Your course includes a twelve-month subscription to these exclusive benefits*:

  • 12 months of anytime access to your course labs and lab environment
  • 12 months of 24/7 access to mentoring via chat, email, and phone
  • 12 months of on-demand access to indexed, searchable recordings of your Virtual Classroom Live or Virtual Classroom Fit class
  • 12 months of unlimited retakes of your class
  • A six-month subscription to Professional Edge

*Terms and conditions may apply. Learn more about our Microsoft training exclusives.

What You'll Learn

  • SELECT query writing
  • Query multiple tables
  • Sort and filter data
  • Data types in SQL Server
  • Data modification using T-SQL
  • Built-in functions
  • Group and aggregate data
  • Set operators
  • Window functions: ranking, offset, and aggregate
  • Pivot and group sets
  • T-SQL programming
  • Error handling and transaction implementation

Who Should Attend

  • Database administrators
  • Database developers
  • Business intelligence professionals
  • SQL power-users such as:
    • Report writers
    • Business analysts
    • Client application developers

Prerequisites

  • Working knowledge of relational databases
  • Basic knowledge of Microsoft Windows operation system and its core functionality

Course Outline

Lessons

Lesson 1: Microsoft SQL Server 2014

  • SQL Server Architecture
  • SQL Server Editions and Versions
  • SQL Server Management Studio

Lesson 2: Transact-SQL Querying

  • Transact-SQL
  • Sets
  • Predicate Logic
  • Logical Order of Operations in SELECT Statements

Lesson 3: Write SELECT Queries

  • Write Simple SELECT Statements
  • Eliminate Duplicates with DISTINCT
  • Column and Table Aliases
  • Write Simple CASE Expressions

Lesson 4: Querying Multiple Tables

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

Lesson 5: Sorting and Filtering Data

  • Sort Data
  • Filter Data with a WHERE Clause
  • Filter with the TOP and OFFSET-FETCH Options
  • Work with Unknown and Missing Values

Lesson 6: SQL Server 2014 Data Types

  • SQL Server 2014 Data Types
  • Work with Character Data
  • Work with Date and Time Data

Lesson 7: DML to Modify Data

  • Insert Data
  • Modify and Delete Data

Lesson 8: Built-In Functions

  • Write Queries with Built-In Functions
  • Conversion Functions
  • Logical Functions
  • Use Functions to Work with NULL

Lesson 9: Grouping and Aggregating Data

  • Use Aggregate Functions
  • Use the GROUP BY Clause
  • Filter Groups with HAVING

Lesson 10: Sub-queries

  • Write Self-Contained Sub-queries
  • Write Correlated Sub-queries
  • Use the EXISTS Predicate with Sub-queries

Lesson 11: Table Expressions

  • Use Derived Tables
  • Use Common Table Expressions
  • Use Views
  • Use Inline Table-Valued Functions

Lesson 12: Set Operators

  • Write Queries with the UNION Operator
  • Use EXCEPT and INTERSECT
  • Use APPLY

Lesson 13: Window Ranking, Offset, and Aggregate Functions

  • Create Windows with OVER
  • Explore Window Functions including Ranking, Aggregate and Offset Functions

Lesson 14: Pivoting and Grouping Sets

  • Write Queries with PIVOT and UNPIVOT
  • Work with Grouping Sets

Lesson 15: Execute Stored Procedures

  • Query Data with Stored Procedures
  • Pass Parameters to Store Procedures
  • Create Simple Stored Procedures
  • Work with Dynamic SQL

Lesson 16: Programming with T-SQL

Lesson 17: Implement Error Handling

  • Use TRY/CATCH Blocks
  • Work with Error Information

Lesson 18: Implement Transactions

  • Transactions and the Database Engine
  • Control Transactions
  • Isolation Levels

Lesson 19: Improve Query Performance

  • Factors in Query Performance
  • Display Query Performance Data

Lesson 20: Query SQL Server Metadata

  • Query System Catalog Views and Functions
  • Execute System Stored Procedures
  • Query Dynamic Management Objects

Labs

Lab 1: Work with SQL Server 2014 Tools

  • SQL Server Management Studio
  • Create and Organize T-SQL Scripts
  • Books Online

Lab 2: Transact-SQL Querying

  • Execute Basic SELECT Statements
  • Execute Queries which filter data using predicates and sort data using ORDER BY

Lab 3: Write Basic SELECT Statements

  • Write Simple SELECT Statements
  • Eliminate Duplicates using Distinct
  • Table and Column Aliases
  • Simple CASE Expression

Lab 4: Query Multiple Tables

  • Write Queries that use Inner Joins, Multiple-Table Inner Join, Self Joins, Outer Joins, and Cross Joins

Lab 5: Sort and Filter Data

  • Write Queries that filter data using a WHERE Clause, ORDER BY Clause, TOP Option and OFFSET-FETCH Clause

Lab 6: SQL Server 2014 Data Types

  • Write Queries that return date and time data and character data
  • Write Queries that use date, time, and character functions

Lab 7: Modify Data using DML

  • Insert, Update and Delete Data

Lab 8: Built-In Functions

  • Write Queries which use conversion and logical functions
  • Write Queries which test for nullability

Lab 9: Group and Aggregate Data

  • Write Queries which use the GROUP BY Clause
  • Write Queries which use aggregate and distinct aggregate functions
  • Write Queries which filter group with the HAVING Clause

Lab 10: Sub-Queries

  • Write Queries which use self-contained, scalar and multi-result sub-queries
  • Write Queries which use correlated sub-queries and EXISTS predicate

Lab 11: Table Expressions

  • Write Queries which use Views, Derived Tables and Common Table Expressions
  • Write Queries which use Inline Table-Valued Functions

Lab 12: Set Operators

  • Write Queries which use UNION set operators and UNION ALL multi-set operators
  • Write Queries which use CROSS APPLY and OUTER APPLY operators
  • Write Queries which use EXCEPT and INTERSECT operators

Lab 13: Windows Ranking, Offset and Aggregate Functions

Lab 14: Pivoting and Grouping Sets

Lab 15: Execute Stored Procedures

  • Invoke stored procedures using the EXECUTE statement
  • Pass parameters to stored procedures
  • Execute system stored procedures

Lab 16: Programming with T-SQL

  • Declare Variables and Delimiting Batches
  • Control-of-Flow Elements
  • Generate Dynamic SQL
  • Synonyms

Lab 17: Implement Error Handling

  • Redirect Errors with TRY/CATCH
  • Pass an Error Message Back to a Client using THROW

Lab 18: Implement Transactions

  • Control transactions with BEGIN, COMMIT and ROLLBACK
  • Add error handling to a CATCH block

Lab 19: Improve Query Performance

  • View Query Execution Plans
  • View Index Usage and Using SET STASTICS Statements

Lab 20: Query SQL Server Metadata

  • Query System Catalog Views
  • Query System Functions
  • Query System Dynamic Management Views