Querying Microsoft SQL Server (M20461)

In this course, you will learn the technical skills required to write basic Transact-SQL (T-SQL) queries for Microsoft SQL Server 2012 & 2014. This is the foundational course for all SQL server related disciplines: database administration, database...

Course Outline

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.

Our Microsoft Training Exclusives*
  • Six months of anytime access to your course labs and lab environment
  • Six months of 24/7 access to mentoring via chat, email, and phone
  • Six months of on-demand access to indexed, searchable recordings of your Virtual Classroom or Virtual Classroom Fit class
  • Six months of unlimited retakes of your class

* Terms and conditions may apply

Did You Know?

This class is available in our Virtual Classroom -- live online training that combines premium skills development technologies and expert instructors, content, and exercises to ensure superior training, regardless of your location.

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

Training Location

Online Classroom
your office

your city, your province
your country