Querying Microsoft SQL Server 2014

Schedule

Start	End	Duration	Location	Details

Course Details

Querying Microsoft SQL Server 2014

Course Code: 20461

Duration: 5 days

Prerequisite:

Working knowledge of relational databases.
Basic knowledge of the Microsoft Windows operating system and its core functionality

Course Description:

This 5-day course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2014. This course is the foundation for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence.

Course Objectives:

After completing this course, students will be able to:

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

Intended Audience:

This course is intended for Database Administrators, Database Developers, and Business Intelligence professionals. The 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.

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

Module 2: Introduction to T-SQL Querying

Introducing T-SQL
Understanding Sets
Understanding Predicate Logic
Understanding the Logical Order of Operations in
SELECT statements

Module 3: Writing SELECT Queries

Writing Simple SELECT Statements
Eliminate Duplicates with DISTINCT
Using Column and Table Aliases
Write Simple CASE Expressions

Module 4: Querying Multiple Tables

Understanding Joins
Querying with Inner Joins
Querying with Outer Joins
Querying with Cross Joins and Self Joins
Module 5: Sorting and Filtering Data
Sorting Data
Filtering Data with Predicates
Filtering with the TOP and OFFSET-FETCH
Working with Unknown Values

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

Module 7: Using DML to Modify Data

Inserting Data
Modifying and Deleting 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

Module 9: Grouping and Aggregating Data

Using Aggregate Functions
Using the GROUP BY Clause
Filtering Groups with HAVING

Module 10: Using Subqueries

Writing Self-Contained Subqueries
Writing Correlated Subqueries
Using the EXISTS Predicate with Subqueries

Module 11: Using Table Expressions

Using Views
Using Inline Table-Valued Functions
Using Derived Tables
Using Common Table Expressions

Module 12: Using Set Operators

Writing Queries with the UNION Operator
Using EXCEPT and INTERSECT
Using APPLY

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

Creating Windows with OVER
Exploring Window Functions

Module 14: Pivoting and Grouping Sets

Writing Queries with PIVOT and UNPIVOT
Working with Grouping Sets

Module 15: Querying data with Stored

Writing Queries with PIVOT and UNPIVOT
Passing Parameters to Stored Procedures
Creating Simple Stored Procedures
Working with Dynamic SQL

Module 16: Programming with T-SQL

T-SQL Programming Elements
Controlling Program Flow

Module 17: Implementing Error Handling

Using TRY / CATCH Blocks
Working with Error Information

Module 18: Implementing Transactions

Transactions and the Database Engine
Controlling Transactions

Module 19: Appendix 1: Improving Query Performance

Factors in Query Performance
Displaying Query Performance Data

Module 20: Appendix 2: Querying SQL Server Metadata

Querying System Catalog Views and Functions
Executing System Stored Procedures
Querying Dynamic Management Objects