M20461: Querying Microsoft SQL Server

Duration: 5 Days

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.

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 Needs to 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

Follow-On Courses

• Administering Microsoft SQL Server Databases (M20462)
• Implementing a Data Warehouse with Microsoft SQL Server (M20463)
• Developing Microsoft SQL Server Databases (M20464)
• Designing a Data Solution with Microsoft SQL Server (M20465)

Certification Programs and Certificate Tracks

This course is part of the following programs or tracks:

• MCSA: SQL Server 2012

Course Outline

1. Microsoft SQL Server 2014

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

2. Transact-SQL Querying

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

3. Write SELECT Queries

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

4. Querying Multiple Tables

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

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

6. SQL Server 2014 Data Types

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

7. DML to Modify Data

• Insert Data
• Modify and Delete Data

8. Built-In Functions

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

9. Grouping and Aggregating Data

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

10. Sub-queries

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

11. Table Expressions

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

12. Set Operators

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

13. Window Ranking, Offset, and Aggregate Functions

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

14. Pivoting and Grouping Sets

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

15. Execute Stored Procedures

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

16. Programming with T-SQL

17. Implement Error Handling

• Use TRY/CATCH Blocks
• Work with Error Information

18. Implement Transactions

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

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