Loading...

M20461 – Querying Microsoft SQL Server 2014

Home / M20461 – Querying Microsoft SQL Server 2014

In this course, students will learn the technical skills required to write basic Transact-SQL (T-SQL) queries for Microsoft SQL Server 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 is designed for customers interested in learning SQL Server 2012 or SQL Server 2014. It covers the new features of SQL Server 2014 as well as the important capabilities across the SQL Server data platform.

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

Course Length: 5 day(s)

Course Price: $2995 CAD

Available Course Formats:

  • In-class
  • Instructor Led Online
  • Self-Paced Virtual Classroom

Enroll Now

Course: M20461 – Querying Microsoft SQL Server 2014

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. Query Data with 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

19. Appendix 1: Improve Query Performance

  • Factors in Query Performance
  • Display Query Performance Data

20. Appendix 2: Query SQL Server Metadata

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

Audience

  • 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

Instructor-Led In Classroom (5 days)

Newcomp can directly deliver courses through the use of in-class training facilities. Currently,  in-class courses are offered in Markham, Ottawa, Vancouver, Halifax, and Edmonton. Please note that classes can be added to new areas based on demand.

Instructor-Led Online (5 days)

Students receive the same quality as an in-class course, with a live instructor and the ability to participate in hands-on labs through real-life examples. ILOs help cut costs by reducing time and travel as they can be taken from home or the office and require only the use of a computer, high-speed wired internet and a headset.

Self Paced

Students can receive the same high-quality training, with the same courseware at their own speed and schedule with SPVC.  Individuals with busy schedules can complete a course over a 30-day timeframe at a lower price than in-class or ILO courses. Please note that there is no live interaction with an instructor in this format.