Overview
5 Days $4,050 or FREE WITH SATVs (?)
This 5-day Microsoft 20461 training course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2014. This Microsoft 20461 training course is the foundation for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence. This is a Microsoft Official Course (MOC) and help helps people prepare for exam 70-461.
- The Basic Architecture of SQL Server
- SQL Server Editions and Versions
- Getting Started with SQL Server Management Studio
- Working with SQL Server Management Studio
- Creating and Organising T-SQL scripts
- Using Books Online
After completing this module, students will be able to:
- Describe the architecture and editions of SQL Server 2012.
- Work with SQL Server Management Studio.
- Introducing T-SQL
- Understanding Sets
- Understanding Predicate Logic
- Understanding the Logical Order of Operations in SELECT statements
- Executing Basic SELECT Statements
- Executing queries which filter data using predicates
- Executing queries which sort data using ORDER BY
After completing this module, students will be able to:
- Describe the elements of T-SQL and their role in writing queries
- Describe the use of sets in SQL Server
- Describe the use of predicate logic in SQL Server
- Describe the logical order of operations in SELECT statements
- Writing Simple SELECT Statements
- Eliminating Duplicates with DISTINCT
- Using Column and Table Aliases
- Writing Simple CASE Expressions
- Write simple SELECT Statements
- Eliminate Duplicates Using Distinct
- Use Table and Column Aliases
- Use a Simple CASE Expression
After completing this module, students will be able to:
- Write simple SELECT statements.
- Eliminate duplicates using the DISTINCT clause.
- Use column and table aliases.
- Write simple CASE expressions.
- Understanding Joins
- Querying with Inner Joins
- Querying with Outer Joins
- Querying with Cross Joins and Self Joins
- Writing Queries That Use Inner Joins
- Writing Queries That Use Multiple-Table Inner Join
- Writing Queries That Use Self Joins
- Writing Queries That Use Outer Joins
- Writing Queries That Use Cross Joins
After completing this module, students will be able to:
- Describe how multiple tables may be queried in a SELECT statement using joins.
- Write queries that use inner joins.
- Write queries that use outer joins.
- Write queries that use self-joins and cross joins.
- Sorting Data
- Filtering Data with a WHERE Clause
- Filtering with the TOP and OFFSET-FETCH Options
- Working with Unknown and Missing Values
- Writing Queries That Filter Data Using a WHERE Clause
- Writing Queries That Filter Data Using an ORDER BY Clause
- Writing Queries That Filter Data Using the TOP Option
- Writing Queries That Filter Data Using the OFFSET-FETCH Clause
After completing this module, students will be able to:
- Filter data with predicates in the WHERE clause.
- Sort data using ORDER BY.
- Filter data in the SELECT clause with TOP.
- Filter data with OFFSET and FETCH.
- Introducing SQL Server 2014 Data Types
- Working with Character Data
- Working with Date and Time Data
- Writing Queries That Return Date and Time Data
- Writing Queries That Use Date and Time Functions
- Writing Queries That Return Character Data
- Writing Queries That Use Character Functions
After completing this module, students will be able to:
- Describe numeric data types, type precedence and type conversions.
- Write queries using character data types.
- Write queries using date and time data types.
- Inserting Data
- Modifying and Deleting Data
- Inserting Data
- Updating and Deleting Data
After completing this module, students will be able to:
- Insert new data into your tables.
- Update and delete existing records in your tables.
- Writing Queries with Built-In Functions
- Using Conversion Functions
- Using Logical Functions
- Using Functions to Work with NULL
- Write queries which use conversion functions
- Write queries which use logical functions
- Write queries which test for nullability
After completing this module, students will be able to:
- Write queries with built-in scalar functions.
- Use conversion functions.
- Use logical functions.
- Use functions that work with NULL.
- Using Aggregate Functions
- Using the GROUP BY Clause
- Filtering Groups with HAVING
- Write queries which use the GROUP BY clause
- Write queries which use aggregate functions
- Write queries which use distinct aggregate functions
- Write queries which filter groups with the HAVING clause
After completing this module, students will be able to:
- Write queries which summarise data using built-in aggregate functions.
- Use the GROUP BY clause to arrange rows into groups.
- Use the HAVING clause to filter out groups based on a search condition.
- Writing Self-Contained Subqueries
- Writing Correlated Subqueries
- Using the EXISTS Predicate with Subqueries
- Write queries which use self-contained subqueries
- Write queries which use scalar and multi-result subqueries
- Write queries which use correlated subqueries and EXISTS predicate
After completing this module, students will be able to:
- Describe the uses of queries which are nested within other queries.
- Write self-contained subqueries which return scalar or multi-valued results.
- Write correlated subqueries which return scalar or multi-valued results.
- Use the EXISTS predicate to efficiently check for the existence of rows in a subquery.
- Using Derived Tables
- Using Common Table Expressions
- Using Views
- Using Inline Table-Valued Functions
- Write Queries Which Use Views
- Write Queries Which Use Derived Tables
- Write Queries Which Use Common Table Expressions
- Write Queries Which Use Inline Table-Valued Functions
After completing this module, students will be able to:
- Write queries which use derived tables.
- Write queries which use common table expressions.
- Create simple views and write queries against them.
- Create simple inline table-valued functions and write queries against them.
- Writing Queries with the UNION Operator
- Using EXCEPT and INTERSECT
- Using APPLY
- 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
After completing this module, students will be able to:
- Write queries which combine data using the UNION operator.
- Write queries which compare sets using the INTERSECT and EXCEPT operators.
- Write queries which manipulate rows in a table by using APPLY with the results of a derived table or function.
- Creating Windows with OVER
- Exploring Window Functions
- Write queries which use ranking functions
- Write queries which use offset functions
- Write queries which use window aggregate functions
After completing this module, students will be able to:
- Describe the benefits to using window functions.
- Restrict window functions to rows defined in an OVER clause, including partitions and frames.
- Write queries which use window functions to operate on a window of rows and return ranking, aggregation and offset comparison results.
- Writing Queries with PIVOT and UNPIVOT
- Working with Grouping Sets
- Write queries which use the PIVOT operator
- Write queries which use the UNPIVOT operator
- Write queries which use the GROUPING SETS subclause
After completing this module, students will be able to:
- Write queries which pivot and unpivot result sets.
- Write queries which specify multiple groupings with grouping sets.
- Querying Data with Stored Procedures
- Passing Parameters to Stored Procedures
- Creating Simple Stored Procedures
- Working with Dynamic SQL
- Use the EXECUTE statement to invoke stored procedures
- Pass parameters to stored procedures
- Execute system stored procedures
After completing this module, students will be able to:
- Return results by executing stored procedures.
- Pass parameters to procedures.
- Create simple stored procedures which encapsulate a SELECT statement.
- Construct and execute dynamic SQL with EXEC and sp_executesql.
- T-SQL Programming Elements
- Controlling Program Flow
- Declaring Variables and Delimiting Batches
- Using Control-of-Flow Elements
- Generating Dynamic SQL
- Using Synonyms
After completing this module, students will be able to:
- Describe the language elements of T-SQL used for simple programming tasks.
- Describe batches and how they are handled by SQL Server.
- Declare and assign variables and synonyms.
- Use IF and WHILE blocks to control program flow.
- Using TRY / CATCH Blocks
- Working with Error Information
- Redirecting Errors with TRY / CATCH
- Using THROW to Pass an Error Message Back to a Client
After completing this module, students will be able to:
- Describe SQL Server's behavior when errors occur in T-SQL code.
- Implement structured exception handling in T-SQL.
- Return information about errors from system objects.
- Raise user-defined errors and pass system errors in T-SQL code.
- Transactions and the Database Engine
- Controlling Transactions
- Isolation Levels
- Controlling transactions with BEGIN, COMMIT, and ROLLBACK
- Adding error handling to a CATCH block
After completing this module, students will be able to:
- Describe transactions and the differences between batches and transactions.
- Describe batches and how they are handled by SQL Server.
- Create and manage transactions with transaction control language statements.
- Use SET XACT_ABORT to define SQL Server's handling of transactions outside TRY / CATCH blocks.
- Describe the effects of isolation levels on transactions.
- Factors in Query Performance
- Displaying Query Performance Data
- Viewing Query Execution Plans
- Viewing Index Usage and Using SET STATISTICS Statements
After completing this module, students will be able to:
- Describe components of well-performing queries.
- Display and interpret basic query performance data
- Querying System Catalog Views and Functions
- Executing System Stored Procedures
- Querying Dynamic Management Objects
- Querying System Catalog Views
- Querying System Functions
- Querying System Dynamic Management Views
After completing this module, students will be able to:
- Write queries that retrieve system metadata using system views and functions.
- Execute system stored procedures to return system information.
- Write queries that retrieve system metadata and state information using system dynamic management views and functions.
- Working knowledge of relational databases.
- Basic knowledge of the Microsoft Windows operating system and its core functionality.
Instructors

Adam Keats
With over 24 years of real-world and training experience, Adam is ...

Newton Godoy
With over 17 years of in-class training experience and over 16 ...