Querying Microsoft SQL Server® Training Course

Learn How To Write Basic Transact-SQL Queries For Microsoft SQL Server 2014

Introduction

The five-day Querying Microsoft SQL Server® training course will provide you with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2014. This Querying Microsoft SQL Server® training course is the foundation for all SQL Server related disciplines; namely, Database Administration, Database Development and Business Intelligence.

Please Note

You can book this course and hold it in credit until you have decided on a specific course date. Alternatively, please view our other course dates.
Exam Not Included
5 Days
£2345Excl. VAT

Course Outline

Module 1: Introduction To Microsoft SQL Server 2014

The Basic Architecture Of SQL Server

SQL Server Editions And Versions

Getting Started with SQL Server Management Studio

Lab: Working With SQL Server 2014 Tools

Module 2: Introduction To T-SQL Querying

Introducing T-SQL

Understanding Sets

Understanding Predicate Logic

Understanding The Logical Order Of Operations In SELECT Statements

Lab: Introduction To Transact-SQL Querying

Module 3: Writing SELECT Queries

Writing Simple SELECT Statements

Eliminate Duplicates With DISTINCT

Using Column And Table Aliases

Write Simple CASE Expressions

Lab: Writing Basic SELECT Statements

Module 4: Querying Multiple Tables

Understanding Joins

Querying With Inner Joins

Querying With Outer Joins

Querying With Cross Joins And Self Joins

Lab: Querying Multiple Tables

Module 5: Sorting And Filtering Data

Sorting Data

Filtering Data With Predicates

Filtering With The TOP And OFFSET-FETCH

Working With Unknown Values

Lab: Sorting And Filtering Data

Module 6: Working With SQL Server 2014 Data Types

Introducing SQL Server 2014 Data Types

Working With Character Data

Working With Date And Time Data

Lab: Working With SQL Server 2014 Data Types

Module 7: Using DML To Modify Data

Inserting Data

Modifying And Deleting Data

Lab: Using DML To Modify Data

Module 8: Using Built-In Functions

Writing Queries With Built-In Functions

Using Conversion Functions

Using Logical Functions

Using Functions To Work With NULL

Lab: Using Built-In Functions

Module 9: Grouping And Aggregating Data

Using Aggregate Functions

Using the GROUP BY Clause

Filtering Groups with HAVING

Lab: Grouping and Aggregating Data

Module 10: Using Subqueries

Writing Self-Contained Subqueries

Writing Correlated Subqueries

Using The EXISTS Predicate With Subqueries

Lab: Using Subqueries

Module 11: Using Table Expressions

Using Views

Using Inline Table-Valued Functions

Using Derived Tables

Using Common Table Expressions

Lab: Using Table Expressions

Module 12: Using Set Operators

Writing Queries With The UNION Operator

Using EXCEPT And INTERSECT

Using APPLY

Lab: Using Set Operators

Module 13: Using Window Ranking, Offset, And Aggregate Functions

Creating Windows With OVER

Exploring Window Functions

Lab: Using Window Ranking, Offset and Aggregate Functions

Module 14: Pivoting And Grouping Sets

Writing Queries With PIVOT And UNPIVOT

Working With Grouping Sets

Lab: Pivoting and Grouping Sets

Module 15: Querying Data With Stored Procedures

Writing Queries with PIVOT And UNPIVOT

Passing Parameters To Stored Procedures

Creating Simple Stored Procedures

Working With Dynamic SQL

Lab: Executing Stored Procedures

Module 16: Programming With T-SQL

T-SQL Programming Elements

Controlling Program Flow

Lab: Programming With T-SQL

Module 17: Implementing Error Handling

Using TRY / CATCH Blocks

Working With Error Information

Lab: Implementing Error Handling

Module 18: Implementing Transactions

Transactions And The Database Engine

Controlling Transactions

Lab: Implementing Transactions

Module 19: Appendix 1: Improving Query Performance

Factors In Query Performance

Displaying Query Performance Data

Lab: Improving Query Performance

Module 20: Appendix 2: Querying SQL Server Metadata

Querying System Catalog Views And Functions

Executing System Stored Procedures

Querying Dynamic Management Objects

Lab: Querying SQL Server Metadata

Description

The Querying Microsoft SQL Server® training course is designed for those who are interested in learning SQL Server 2012 or SQL Server 2014. It covers the new features in SQL Server 2014 but also the important capabilities across the SQL Server data platform.

  • Describe The Basic Architecture And Concepts Of Microsoft SQL Server 2014.
  • Understand The Similarities And Differences Between Transact-SQL And Other Computer Languages.
  • Write SELECT Queries
  • Query Multiple Tables
  • Sort And Filter Data
  • Describe The Use Of Data Types In SQL Server
  • Modify Data Using Transact-SQL
  • Use Built-In Functions
  • Group And Aggregate Data
  • Use Subqueries
  • Use Table Expressions
  • Use Set Operators
  • Use Window Ranking, Offset And Aggregate Functions
  • Implement Pivoting And Grouping Sets
  • Execute Stored Procedures
  • Program With T-SQL
  • Implement Error Handling
  • Implement Transactions

About the Course

This interactive training session can be taken from any location, your office or home and is delivered by a tutor. Virtual students do not need to travel for the training course and Purple Griffon will send you all of the information needed before the start of the training course so you can test your login details.

Who Should Attend

The Querying Microsoft SQL Server® training course is intended for Database Administrators, Database Developers, and Business Intelligence Professionals.

The Querying Microsoft SQL Server® training course will very likely be well attended by SQL power users who aren’t necessarily database-focused or plan on taking the exam; namely, Report Writers, Business Analysts and Client Application Developers.

Materials Provided

  • Full Materials Including Comprehensive Course Workbook
  • Highly Skilled And Knowledgeable Tutors
  • Refreshments

Organisational Benefits

Security: SQL Server provides two security models including integration with Windows Authentication to provide robust security platform for managing your organisations information. Security can be managed across all database objects including tables, views, stored procedures and at a very granular level, to provide complete information security. Database encryption is now available as well to secure SQL Server database at rest.

Scalability: SQL Server is a highly scalable enterprise database platform capable of managing petabytes of data while delivery high performance. This allows organisations to deploy SQL Server databases with the confidence that SQL Server can continue to deliver superior performance even as their needs and businesses grow.

High Availability/Failover: SQL Server provides a variety of high availability features including fail over clustering available on the Standard and Enterprise editions, database mirroring, log shipping, and database snapshots to meet your organisations' needs.

Business Intelligence: SQL Server Standard and Enterprise Editions come with extensive business intelligence capabilities through SQL Server Analysis Services. Analytical models can be developed to breakdown business information to provide business insights for managing and forecasting business needs. SQL Server also comes with integrated reporting capabilities from SQL Server Reporting Services which allows organisations to access business critical information across the organisation. In more recent versions of SQL Server, PowerPivot for Microsoft Excel has been added to allow users another familiar tool for analysing business data.

Integration: SQL Server provides a robust set of integration tools call SQL Server Integration Services (SSIS) which can be used consolidate data from multiple heterogenous data sources for holistic data view and comprehensive data analysis. SSIS can also be used to migrate data from other database formats (Oracle, DBase, Paradox, legacy formats, Microsoft Access) to SQL Server.

Individual Benefits

The Querying Microsoft SQL Server® training course will keep your personal or professional skills up to date.

Prerequisites

Working knowledge of relational databases.

Basic knowledge of the Microsoft Windows operating system and its core functionality

Prereading

There is no official prereading with the Querying Microsoft SQL Server® training session.

Exam Overview

The main purpose of the Querying Microsoft SQL Server® training course is to prepare you for the examination '70-461: Writing Queries Using Microsoft® SQL Server® 2014 Transact-SQL'. The examination will be the underlying exam for all SQL Server related disciplines; namely, Database Administration, Database Development and Business Intelligence.

Exam Type

Classroom Exam

Professional Development Units - PDUs

The Querying Microsoft SQL Server® training course will give you credit towards the MCP, MCSA, MCSE certifications.

Qualifications

Querying Microsoft SQL Server® Certification

Next Steps

The five day Implementing A Data Warehouse With Microsoft SQL Server® (SSIS) training course will show you how to implement a data warehouse platform to support a BI solution. The training course is designed for those who are interested in learning SQL Server 2012 or SQL Server 2014 and covers the new features in SQL Server 2014 but also the important capabilities across the SQL Server data platform.

Conclusion

The Querying Microsoft SQL Server® training course will provide you with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2014 and to pass the examination.

Querying Microsoft SQL Server® Course Dates

Course Filter


DD/MM/YYY - e.g. 31/12/2015
Showing 0 to 0 of 0 Total Course Dates.

Oops!

There are currently no course dates that match your selected filter options. Please try another filter or contact one of our dedicated account managers on +44(0)1539 736 828 to discuss your requirements. Thanks.

Scroll Table to View Content