A Small Class Size is the Best Learning Environment*
Only F1 has no more than 6 Delegates in a Class
Everyone has Individual Attention from the Trainer
Niche
SQL Server 2012

Querying Microsoft SQL Server 2012
(Microsoft Training Course: 10774) - 5 days - £1850 exc VAT

Save up to 50% of the cost of some courses: check our Certification Packages or buy F1 Training Vouchers


> Target Audience
This course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2012. It is intended for Database Administrators, Database Developers, and Business Intelligence professionals.
> Course outline
  1. Introduction to Microsoft SQL Server 2012
  2. Getting Started with SQL Azure
  3. Introduction to T-SQL Querying
  4. Writing SELECT Queries
  5. Querying Multiple Tables
  6. Sorting and Filtering Data
  7. Using Built-In Functions
  8. Grouping and Aggregating Data
  9. Using Subqueries
  10. Using Table Expressions
  11. Use Set Operators
  12. Using Window Ranking, Offset and Aggregate Functions
  13. Pivoting and Grouping Sets
  14. Executing Stored Procedures
  15. Programming with T-SQL
  16. Implementing Error Handling
  17. Implementing Transactions
  18. Improving Query Performance
Module 1: Introduction to Microsoft SQL Server 2012
  • Lab: Working with SQL Server 2012 Tools
  • Creating and Organizing T-SQL scripts
  • Using Books Online
Skills
  • Describe the architecture and editions of SQL Server 2012
  • Work with SQL Server Management Studio
top
Module 2: Getting Started with SQL Azure
  • Overview of, and working with, SQL Azure
Skills
  • Describe the basic features of SQL Azure
  • Provision a SQL Azure server and configure its security
top
Module 3: Introduction to T-SQL Querying
  • Introducing Transact-SQL
  • Understanding Sets, and Predicate Logic
  • Understanding the Logical Order of Operations in SELECT Statements
  • Lab: Introduction to Transact-SQL Querying
  • Executing Basic SELECT Statements
  • Executing queries which filter data using predicates and sort using ORDER BY
Skills
  • Describe the elements of T-SQL and its role in writing queries
  • Describe the use of sets in SQL Server
  • Describe the use of predicate logic in SQL
  • Define the logical order of operations in SELECT statements
top
Module 4: Writing SELECT Queries
  • Writing Simple SELECT Statements
  • Eliminating Duplicates with DISTINCT
  • Using Column and Table Aliases
  • Writing Simple CASE Expressions
  • Lab: Writing Basic SELECT statements
  • Eliminate Duplicates using DISTINCT
  • Use table and column aliases
  • Use a simple CASE expression
Skills
  • Write Simple SELECT statements
  • Eliminate duplicates using the DISTINCT clause
  • Use column and table aliases
  • Write simple CASE expressions
top
Module 5: Querying Multiple Tables
  • Using JOINs in T-SQL queries for retrieving data from multiple tables.
  • Understanding Joins
  • Querying With Inner and Outer Joins
  • Querying Using Self and Cross Joins
  • Lab: Querying Multiple Tables
  • Write queries using inner and multi-table joins
  • Write queries using self and outer joins
Skills
  • Describe how multiple tables may be queried in a SELECT statement using joins
  • Write queries which use inner or outer joins,
  • and queries which use self-joins and cross joins
top
Module 6: Sorting and Filtering Data
  • Sorting Data, filtering Data with a WHERE Clause, & working with Unknown Values
  • Filtering with TOP and OFFSET-FETCH Options
  • Lab: Filtering and Sorting Data
  • Write queries that filter using a WHERE clause
  • Write queries that sort data with the ORDER BY clause
  • Write queries that filter data using the TOP option and the OFFSET-FETCH clause
Skills
  • Filter data with predicates in the WHERE clause and sort data using ORDER BY
  • Filter data in the SELECT clause with TOP
  • Filter data with OFFSET and FETCH
  • Understand and handle NULLs
top
Module 7: Using Built-In Functions
  • Writing Queries with Built-In Functions
  • Using Conversion, and Logical, Functions
  • Using Functions to Work with NULL
  • Lab: Using Built-In Functions
  • Write queries which use conversion functions
  • Write queries which use logical functions
  • Write queries which test for nullability
Skills
  • Write queries with built-in scalar functions
  • Use conversion functions
  • Use logical functions
  • Use functions that work with NULL
top
Module 8: Grouping and Aggregating Data
  • Introducing methods for grouping data within a query, aggregating the grouped data and filtering groups with HAVING.
  • Using Aggregate Functions
  • Using the GROUP BY Clause
  • Filtering Groups with the HAVING Clause
  • Lab: Grouping and Aggregating Data
  • 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
Skills
  • Write queries which summarize data using built-in aggregate functions
  • GROUP BY clause to arrange rows into groups
  • Use the HAVING clause to filter out groups based on a search condition
top
Module 9: Using Subqueries
  • Writing Self-Contained or Correlated Subqueries
  • Using the EXISTS Predicate with Subqueries
  • Lab: Write queries which use self-contained subqueries, correlated subqueries, scalar and multi-result subqueries
  • Write queries with the IN or EXISTS predicates
Skills
  • Use self-contained subqueries, correlated subqueries, scalar subqueries, and multi-valued subqueries, and use the IN and EXISTS predicates
top
Module 10: Using Table Expressions
  • Introducing T-SQL expressions which return a valid relational table.
  • Using Derived Tables and Views
  • Using Common Table Expressions
  • Using Inline Table-Valued Functions
  • Lab: Using Table Expressions
  • Write Queries Which Use Views & Derived Tables
  • Write Queries Which Use Common Table Expressions & Inline Table-Valued Functions
Skills
  • Write queries against views
  • Use derived tables & common table expressions
  • Use inline table-valued expressions
top
Module 11: Use Set Operators
  • Writing Queries with the UNION Operator
  • Using EXCEPT, INTERSECT, and APPLY
  • Lab: Using Set Operators
  • Write queries which use UNION set operators and UNION ALL multi-set operators
  • Write queries which use CROSS APPLY and OUTER APPLY, and EXCEPT and INTERSECT operators
  • Writing Queries with the UNION Operator
  • Using EXCEPT, INTERSECT, and APPLY
  • Lab: Using Set Operators
  • Write queries which use UNION set operators and UNION ALL multi-set operators
  • Write queries which use CROSS APPLY and OUTER APPLY, and EXCEPT and INTERSECT operators
Skills
  • 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
top
Module 12: Using Window Ranking, Offset and Aggregate Functions
  • Introducing window functions including ranking, aggregate and offset functions.
  • Creating Windows with OVER
  • Exploring Window Functions
  • Lab: Using Window Ranking, Offset and Aggregate Functions
  • Write queries which use ranking functions
  • Write queries which use offset functions
  • Write queries which use window aggregate functions
Skills
  • 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.
top
Module 13: Pivoting and Grouping Sets
  • Techniques for pivoting data in T-SQL
  • Lab: Pivoting and Grouping Sets
  • Write queries which use the PIVOT operator
  • Write queries which use the GROUPING SETS subclause
  • Write queries which use GROUP BY ROLLUP
  • Write queries which use GROUP BY CUBE
Skills
  • Use the basic form of PIVOT and UNPIVOT operators, and the fundamental form of GROUPING SETS clause
top
Module 14: Executing Stored Procedures
  • Querying Data with Stored Procedures
  • Passing Parameters to Stored Procedures
  • Creating Simple Stored Procedures
  • Working with Dynamic SQL
  • Lab: Executing Stored Procedures
  • Use the EXECUTE statement to invoke stored procedures
  • Pass parameters to stored procedures
  • Return results from a stored procedure using the OUTPUT clause
Skills
  • 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.
top
Module 15: Programming with T-SQL
  • T-SQL Programming concepts and Elements
  • Controlling Program Flow
  • Lab: Programming with T-SQL
  • Declaring variables and delimiting batches
  • Using control of flow elements
  • Generating dynamic SQL
  • Using synonyms
Skills
  • 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.
top
Module 16: Implementing Error Handling
  • The use of error handlers in T-SQL code.
  • Using TRY / CATCH Blocks
  • Working with Error Information
  • Lab: Implementing Error Handling
  • Redirecting errors with TRY/CATCH
  • Creating error handling routines in a CATCH block with ERROR functions
  • Using THROW to pass an error message back to a client
Skills
  • Describe the behavior of SQL Server when errors occur in T-SQL code
  • Implement structured exception handling in T-SQL and return information about errors from system objects
  • Raise user-defined errors and pass system errors in T-SQL code.
top
Module 17: Implementing Transactions
  • Transactions and the Database Engine
  • Controlling Transactions
  • Lab: Implementing Transactions
  • Controlling transactions with BEGIN and COMMIT, and using XACT_ABORT
  • Adding transaction handling logic to a CATCH block
Skills
  • Describe transactions and the differences between batches and transactions, and 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
top
Module 18: Improving Query Performance
  • Factors in Query Performance
  • Displaying Query Performance Data
  • Lab: Improving Query Performance
  • Viewing Query Execution Plans
  • Viewing Index Usage and Using SET STATISTICS Statements
Skills
  • Describe components of well-performing queries, and the role of indexes and statistics in SQL Server
  • Display and interpret basic query plans and basic query performance data
top
> Pre-Requisites
Before attending this course, students must have the following pre-requisites:
  • Working knowledge of relational databases.
  • Basic knowledge of the Microsoft Windows operating system and its core functionality
  • Basic understanding of virtualization technology (Classroom labs utilize virtual machines)
> Purpose
After completing this course, students will be able to: write SELECT queries; query multiple tables; use built-in functions; use subqueries; execute stored procedures; use set operators; implement error handling; implement transactions; use table expressions; sort and filter data; use window ranking, offset and aggregate functions; query SQL Server metadata; program with T-SQL; improve query performance.