Querying Microsoft SQL Server 2014 (M20461) (M20461)

Code: 6552
Length: 5 days
URL: View Online

In this course, you 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 you prepare for the
exam.

Skills Gained

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

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

Course Details

Lab 1: Work with SQL Server 2014 Tools

SQL Server Management Studio

Lab 2: Transact-SQL Querying

T-SQL elements and their role in writing queries

Use of sets ad predicate login in SQL Server

Logical order of operations in SELECT statements

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

Lab 19: Improve Query Performance

View Query Execution Plans

View Index Usage and Using SET STASTICS Statements

Lab 20: Query SQL Server Metadata

2016-02-25 08:23:40.673000000

Lab 1: Work with SQL Server 2014 Tools

SQL Server Management Studio

Lab 2: Transact-SQL Querying

T-SQL elements and their role in writing queries

Use of sets ad predicate login in SQL Server

Logical order of operations in SELECT statements

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

Lab 19: Improve Query Performance

View Query Execution Plans

View Index Usage and Using SET STASTICS Statements

Lab 20: Query SQL Server Metadata

Query System Catalog Views

Query System Functions

Query System Dynamic Management Views

Download Whitepaper: Accelerate Your Modernization Efforts with a Cloud-Native Strategy

Get Your Free Copy Now

ExitCertified® Corporation and iMVP® are registered trademarks of ExitCertified ULC and ExitCertified Generated 5
Corporation and Tech Data Corporation, respectively
Copyright ©2021 Tech Data Corporation and ExitCertified ULC & ExitCertified Corporation.
All Rights Reserved.