Querying Microsoft SQL Server (20461)

Quantity

$2,895.00

Add to Cart

New Age Technologies has been delivering Authorized Training since 1996. We offer Microsoft’s full suite of authorized courses including courses pertaining to Querying Microsoft SQL Server, SharePoint 2013 Business Intelligence, Programming in HTML5 with JavaScript and CSS3, Microsoft Office, Microsoft Exchange Server, and more. If you have any questions or can’t seem to find the Microsoft class that you are interested in, contact one of our Microsoft Training Specialists. Invest in your future today with Microsoft training from New Age Technologies.

✉ Microsoft Training Specialist | ☏ 502.909.0819

---

EXCLUSIVE BENEFIT:

This course includes a twelve-month subscription to these exclusive benefits:

• 12 months of anytime access to your course labs and lab environment
• 12 months of 24/7 access to mentoring via chat, email, and phone
• 12 months of on-demand access to indexed, searchable recordings of your Online Live class
• 12 months of unlimited retakes of your class

---

COURSE INFORMATION

Querying Microsoft SQL Server (20461) Overview:

In the Querying Microsoft SQL Server (20461) 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 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
• Transaction management in SQL Server

This course covers the skills and knowledge measured by Exam 70-461 and along with on-the-job experience, helps you prepare for the exam.

Who Should Attend:

• Database administrators
• Database developers
• Business intelligence professionals
• SQL power-users such as:
• Report writers
• Business analysts
• Client application developers

Querying Microsoft SQL Server (20461) Prerequisites:

Before attending this course, you must have:

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

Querying Microsoft SQL Server (20461) Objectives:

After successfully completing this course, you will be able to:

• 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

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)

Querying Microsoft SQL Server (20461) Outline:

Module 1: Microsoft SQL Server 2014

• SQL Server Architecture
• SQL Server Editions and Versions
• SQL Server Management Studio
• Module 1 Labs: Work with SQL Server 2014 Tools
• SQL Server Management Studio
• Create and Organize T-SQL Scripts
• Books Online

Module 2: Transact-SQL Querying

• Transact-SQL
• Sets
• Predicate Logic
• Logical Order of Operations in SELECT Statements
• Module 2 Labs: Transact-SQL Querying
• Execute Basic SELECT Statements
• Execute Queries which filter data using predicates and sort data using ORDER BY

Module 3: Write SELECT Queries

• Write Simple SELECT Statements
• Eliminate Duplicates with DISTINCT
• Column and Table Aliases
• Write Simple CASE Expressions
• Module 3 Labs: Write Basic SELECT Statements
• Write Simple SELECT Statements
• Eliminate Duplicates using Distinct
• Table and Column Aliases
• Simple CASE Expression

Module 4: Querying Multiple Tables

• Joins
• Query with Inner Joins and Outer Joins
• Query with Cross Joins and Self Joins
• Module 4 Labs: Query Multiple Tables
• Write Queries that use Inner Joins, Multiple-Table Inner Join, Self Joins, Outer Joins, and Cross Joins

Module 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
• Module 5 Labs: Sort and Filter Data
• Write Queries that filter data using a WHERE Clause, ORDER BY Clause, TOP Option and OFFSET-FETCH Clause

Module 6: SQL Server 2014 Data Types

• SQL Server 2014 Data Types
• Work with Character Data
• Work with Date and Time Data
• Module 6 Labs: 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

Module 7: DML to Modify Data

• Insert Data
• Modify and Delete Data
• Module 7 Labs: Modify Data using DML
• Insert, Update and Delete Data

Module 8: Built-In Functions

• Write Queries with Built-In Functions
• Conversion Functions
• Logical Functions
• Use Functions to Work with NULL
• Module 8 Labs: Built-In Functions
• Write Queries which use conversion and logical functions
• Write Queries which test for nullability

Module 9: Grouping and Aggregating Data

• Use Aggregate Functions
• Use the GROUP BY Clause
• Filter Groups with HAVING
• Module 9 Labs: 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

Module 10: Sub-queries

• Write Self-Contained Sub-queries
• Write Correlated Sub-queries
• Use the EXISTS Predicate with Sub-queries
• Module 10 Labs: Sub-Queries
• Write Queries which use self-contained, scalar and multi-result sub-queries
• Write Queries which use correlated sub-queries and EXISTS predicate

Module 11: Table Expressions

• Use Derived Tables
• Use Common Table Expressions
• Use Views
• Use Inline Table-Valued Functions
• Module 11 Labs: Table Expressions
• Write Queries which use Views, Derived Tables and Common Table Expressions
• Write Queries which use Inline Table-Valued Functions

Module 12: Set Operators

• Write Queries with the UNION Operator
• Use EXCEPT and INTERSECT
• Use APPLY
• Module 12 Labs: 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

Module 13: Window Ranking, Offset, and Aggregate Functions

• Create Windows with OVER
• Explore Window Functions including Ranking, Aggregate and Offset Functions
• Module 13 Labs: Windows Ranking, Offset and Aggregate Functions

Module 14: Pivoting and Grouping Sets

• Write Queries with PIVOT and UNPIVOT
• Work with Grouping Sets
• Module 13 Labs: Pivoting and Grouping Sets

Module 15: Execute Stored Procedures

• Query Data with Stored Procedures
• Pass Parameters to Store Procedures
• Create Simple Stored Procedures
• Work with Dynamic SQL
• Module 15 Labs: Execute Stored Procedures
• Invoke stored procedures using the EXECUTE statement
• Pass parameters to stored procedures
• Execute system stored procedures

Module 16: Programming with T-SQL

• Module 16 Labs: Programming with T-SQL

Module 17: Implement Error Handling

• Use TRY/CATCH Blocks
• Work with Error Information
• Module 17 Labs: Implement Error Handling
• Redirect Errors with TRY/CATCH
• Pass an Error Message Back to a Client using THROW

Module 18: Implement Transactions

• Transactions and the Database Engine
• Control Transactions
• Isolation Levels
• Module 18 Labs: Implement Transactions
• Control transactions with BEGIN, COMMIT and ROLLBACK
• Add error handling to a CATCH block

Module 19: Improve Query Performance

• Factors in Query Performance
• Display Query Performance Data
• Module 19 Labs: Improve Query Performance
• View Query Execution Plans
• View Index Usage and Using SET STATISTICS Statements

Module 20: Query SQL Server Metadata

• Query System Catalog Views and Functions
• Execute System Stored Procedures
• Query Dynamic Management Objects
• Module 20 Labs: Query SQL Server Metadata
• Query System Catalog Views
• Query System Functions
• Query System Dynamic Management Views

---

Average Salary for Skill: Microsoft SQL Server