MOC 20461 – Querying Microsoft SQL Server

MOC 20461 – Querying Microsoft SQL Server

This certified Microsoft Training course, Querying Microsoft SQL Server (MOC 20461), teaches students to write basic Transact-SQL queries for Microsoft SQL Server 2014. Attendees learn the foundation for all SQL Server-related disciplines including Database Administration, Database Development, and Business Intelligence. This course is preparation for exam 70-461.

SQL SERVER 2014 TRAINING 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

SQL SERVER 2014 TRAINING PREREQUISITES

All students should have:

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

SQL SERVER 2014 TRAINING MATERIALS

All Microsoft training students receive Microsoft official courseware.

SOFTWARE NEEDED FOR EACH PC:

Attendees will not need to install any software on their computer for this class. The class will be conducted in a remote environment that Accelebrate will provide; students will only need a local computer with a web browser with a stable Internet connection. Any recent version of Internet Explorer, Mozilla Firefox, or Google Chrome will be fine.

When you call us about purchasing this class, we will provide a live demo of the online lab environment so that you may explore the web browser interface in more detail.

For classes delivered online, all participants need either dual monitors or a separate device logged into the online session so that they can do their work on one screen and watch the instructor on the other. A separate computer connected to a projector or large screen TV would be another way for students to see the instructor’s screen simultaneously with working on their own.

SQL SERVER 2014 TRAINING OUTLINE

• Introduction to Microsoft SQL Server 2014
  • The Basic Architecture of SQL Server
  • SQL Server Editions and Versions
  • Getting Started with SQL Server Management Studio
• Introduction to T-SQL Querying
  • Introducing T-SQL
  • Understanding Sets
  • Understanding Predicate Logic
  • Understanding the Logical Order of Operations in SELECT statements
• Writing SELECT Queries
  • Writing Simple SELECT Statements
  • Eliminating Duplicates with DISTINCT
  • Using Column and Table Aliases
  • Writing Simple CASE Expressions
• Querying Multiple Tables
  • Understanding Joins
  • Querying with Inner Joins
  • Querying with Outer Joins
  • Querying with Cross Joins and Self Joins
• Sorting and Filtering Data
  • Sorting Data
  • Filtering Data with a WHERE Clause
  • Filtering with the TOP and OFFSET-FETCH Options
  • Working with Unknown and Missing Values
• Working with SQL Server 2014 Data Types
  • Introducing SQL Server 2014 Data Types
  • Working with Character Data
  • Working with Date and Time Data
• Using DML to Modify Data
  • Inserting Data
  • Modifying and Deleting Data
• Using Built-In Functions
  • Writing Queries with Built-In Functions
  • Using Conversion Functions
  • Using Logical Functions
  • Using Functions to Work with NULL
• Grouping and Aggregating Data
  • Using Aggregate Functions
  • Using the GROUP BY Clause
  • Filtering Groups with HAVING
• Using Subqueries
  • Writing Self-Contained Subqueries
  • Writing Correlated Subqueries
  • Using the EXISTS Predicate with Subqueries
• Using Table Expressions
  • Using Derived Tables
  • Using Common Table Expressions
  • Using Views
  • Using Inline Table-Valued Functions
• Using Set Operators
  • Writing Queries with the UNION Operator
  • Using EXCEPT and INTERSECT
  • Using APPLY
• Using Window Ranking, Offset, and Aggregate Functions
  • Creating Windows with OVER
  • Exploring Window Functions
• Pivoting and Grouping Sets
  • Writing Queries with PIVOT and UNPIVOT
  • Working with Grouping Sets
• Executing Stored Procedures
  • Querying Data with Stored Procedures
  • Passing Parameters to Stored Procedures
  • Creating Simple Stored Procedures
  • Working with Dynamic SQL
• Programming with T-SQL
  • T-SQL Programming Elements
  • Controlling Program Flow
• Implementing Error Handling
  • Using TRY / CATCH Blocks
  • Working with Error Information
• Implementing Transactions
  • Transactions and the Database Engine
  • Controlling Transactions
  • Isolation Levels
• Improving Query Performance
  • Factors in Query Performance
  • Displaying Query Performance Data
• Querying SQL Server Metadata
  • Querying System Catalog Views and Functions
  • Executing System Stored Procedures
  • Querying Dynamic Management Objects
• Conclusion