20461 - Querying Microsoft SQL Server (2014)

20461 - Querying Microsoft SQL Server (2014)

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


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

Course Outline

  1. Microsoft SQL Server 2014
    SQL Server Architecture
    SQL Server Editions and Versions
    SQL Server Management Studio
  2. Transact-SQL Querying
    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
    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 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


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

Class Dates:


Apr 29, 2019 - May 3, 2019

Register Now...


May 6, 2019 - May 10, 2019

Register Now...

On Site Class Available

ExpertONE Learning Solutions Provider Microsoft Certified Partner Logo GSA Advantage