Querying Microsoft SQL Server 2014 (M20461)

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