Querying Microsoft SQL Server 2012

(1)

Querying Microsoft SQL Server 2012

Duration: 5 Days Course Code: M10774

Overview:

Deze cursus wordt vanaf 1 juli vervangen door cursus M20461 Querying Microsoft SQL Server. This course will be replaced by course M20461 Querying Microsoft SQL Server from July 1st..

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

Target 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.

Objectives:

After completing this course, students will be able to: Implementing Transactions

Write SELECT Queries Use Table Expressions

Query Multiple Tables Sort and Filter Data

Use Built-In Functions Use Window Ranking, Offset and Aggregate Functions

Use Subqueries Query SQL Server Metadata

Execute Stored Procedures Program with T-SQL

Use Set Operators Improve Query Performance

Implement Error Handling

Prerequisites:

Testing and Certification

Working knowledge of relational databases. This course helps people prepare for exam 70-461 . Basic knowledge of the Microsoft Windows operating system and

its core functionality.

(2)

Content:

Module 1: Introduction to Microsoft SQL Server This module will introduce the use of This module introduces the use of existing 2012 subqueries in various parts of a SELECT stored procedures in a T-SQL querying

line statement. It will include the use of scalar and environment. It discusses the use of This module introduces the SQL Server multi-result subqueries, and the use of the IN EXECUTE, how to pass input and output platform and major tools. It discusses and EXISTS operators. parameters to a procedure, and how to invoke editions, versions, tools used to query, line system stored procedures.

documentation sources, and the logical Writing Self-Contained Subqueries line

structure of databases. Writing Correlated Subqueries

SQL Server Denali Architecture Using the EXISTS Predicate with Lab : Executing Stored Procedures

Working with SQL Server Denali Tools line

Write queries which use self-contained Use the EXECUTE statement to invoke Working with SQL Server Management subqueries stored procedures

Studio Write queries which use correlated Pass parameters to stored procedures Exploring with Database Objects subqueries Return results from a stored procedure Organizing and Creating T-SQL Scripts Write queries which use scalar and using the OUTPUT clause

multi-result subqueries

Describe SQL Server components Write queries which use the IN and Invoke existing stored procedures with

Create and save T-SQL scripts in SQL EXECUTE

Server Management Studio Use self-contained subqueries, correlated Pass input parameters and receive output Browse the objects in a database subqueries, scalar subqueries, parameters

Search T-SQL syntax definitions in Books andmulti-valued subqueries Generate dynamic SQL statements Online Use the IN and EXISTS predicates

Module 16: Programming with T-SQL Module 2: Introduction to Transact-SQL Module 10: Using Table Expressions line

Querying line

line This module provides a basic introduction to

This module introduces T-SQL expressions T-SQL programming concepts and objects. It This module introduces Transact SQL as the which return a valid relational table, typically discusses batches, variables, control of flow primary querying language of SQL Server. It for further use in the query. The elements such as loops and conditionals, how discusses the basic structure of T-SQL queries, modulediscusses views, derived tables, to create and execute dynamic SQL

the logical flow of a SELECT statement, and common table expressions and inline statements, and how to use synonyms. introduces concepts such as predicates and table-valued functions. line

set-based operations. line

line Views Lab : Programming with T-SQL

Introducing Transact-SQL line

Understanding Sets Write queries which use views Declaring variables and delimiting batches Understanding Predicate Logic Write queries which use derived tables Using control of flow elements

Understanding the Logical Order of Write queries which use common table Generating dynamic SQL expressions

Executing Basic SELECT Statements Write queries which use inline Understand where T-SQL programming Executing queries which filter data using objects can be used

predicates Write queries against views Declare variables and delimit batches Executing queries which sort data using Use derived tables Use control of flow elements such as IF,

Use common table expressions WHILE, BEGIN, END, RETURN Describe the elements of T-SQL and its role Use inline table-valued expressions Use synonyms

in writing queries

Describe the use of sets in SQL Server Module 11: Use Set Operators Module 17: Implementing Error Handling Describe the use of predicate logic in SQL line line

Server

Define the logical order of operations in This module introduces operations involving This module introduces the use of error SELECT statements multiple sets of data. It will cover the use of handlers in T-SQL code. It will introduce the

the UNION, UNION ALL, APPLY, CROSS difference between compile errors and Module 3: Writing SELECT Queries APPLY, OUTER APPLY operators as well as run-time errors, and will cover how errors

line the EXCEPT and INTERSECTS operators. affect batches. The module will also cover

line how to control error handling using This module introduces the fundamentals of the TRY/CATCH blocks, the use of the ERROR SELECT statement, focusing on queries Lab : Use Set Operators class of functions, and the use of the new

against a single table. line THROW statement.

line Write queries which use UNION set line

Writing Simple SELECT Statements operators and UNION ALL multi-set

Eliminating Duplicates with DISTINCT operators Lab : Implementing Error Handling Using Column and Table Aliases Write queries which use CROSS APPLY line

and OUTER APPLY operators Redirecting errors with TRY/CATCH Write simple SELECT statements Write queries which use APPLY with Creating error handling routines in a

(3)

Eliminate Duplicates using DISTINCT derived tables and functions CATCH block with ERROR functions Use table and column aliases Write queries which use EXCEPT and Using THROW to pass an error message Write Simple SELECT statements Describe the difference between a set Describe the behavior of SQL Server when Eliminate duplicates using the DISTINCT and a multi-set errors occur in T-SQL code

clause Use the UNION and UNION ALL Use TRY/CATCH blocks to handle error

Use column and table aliases operators flow

Write simple CASE expressions Use CROSS APPLY, OUTER APPLY Understand ERROR class functions and APPLY

Module 4: Querying Multiple Tables Use EXCEPT and INTERSECT operators Module 18: Implementing Transactions

line line

Module 12: Using Window Ranking, Offset

This module explains how to write queries and Aggregate Functions This module introduces the concepts of which combine data from multiple sources in line transaction management in SQL Server. It will SQL Server.The module introduces the use of provide a high-level overview of transaction JOINs in T-SQL queries as a mechanism for This module introduces window functions properties, cover the basics of marking retrieving data from multiple tables. including ranking, aggregate and offset transactions with BEGIN, COMMIT and

line functions. Much of this functionality is new to ROLLBACK. Understanding Joins SQL Server 2012. It will cover the use of line

Querying With Inner Joins T-SQL functions such as ROW_NUMBER,

Querying With Outer Joins RANK, DENSE_RANK, NTILE, LAG, LEAD, Lab : Implementing Transactions FIRST_VALUE and LAST_VALUE to perform line

Write queries using inner joins calculations against a set, or window, of rows. Controlling transactions with BEGIN and Write queries using multi-table joins line COMMIT

Write queries using self joins Using XACT_ABORT

Lab : Using Window Ranking, Offset and Adding transaction handling logic to a Describe how multiple tables may be Aggregate Functions

queried in a SELECT statement using joins line Describe the benefits of using transactions Write queries which use inner joins Write queries which use ranking functions Control transactions with BEGIN,

Write queries which use outer joins Write queries which use offset functions COMMIT, ROLLBACK

Write queries which use self-joins and cross Write queries which use window Describe error handling behavior in

joins transactions

Use window ranking functions

Module 5: Sorting and Filtering Data ROW_NUMBER, RANK, DENSE_RANK, Module 19: Improving Query Performance

line NTILE line

Use offset functions LAG, LEAD,

This module explains how to enhance queries FIRST_VALUE, LAST_VALUE This module introduces the concepts of to limit the rows they return, and to control the Use window aggregate functions system resource usage and the performance order in which the rows are displayed.The Use the basic forms of window impact of querying SQL Server 2012. It will module also discusses howto resolvemissing partitioning, window ordering and window cover, at a high level, the use of indexes in and unknown results. framing SQL Server, the use of execution plans in

line SQL Server Management Studio, and the use

Sorting Data Module 13: Pivoting and Grouping Sets of SET options to view system resource Filtering Data line usage when executing queries. It will also Filtering with the TOP and OFFSET-FETCH compare set-based operations with Options This module discusses techniques for pivoting cursor-based operations.

data in T-SQL as well to introduce the line

Write queries that filter data using a WHERE fundamentals of the GROUPING SETS

clause clause. It will also cover the use of GROUP Lab : Improving Query Performance Write queries that sort data with the ORDER BY ROLLUP and GROUP BY CUBE syntax in line

BY clause SQL Server 2012. Viewing query execution plans

Write queries that filter data using the TOP line Using SET STATISTICS statements

option Viewing index usage

Write queries that filter data using the Lab : Pivoting and Grouping Sets Comparing cursors to set-based

line

Filter data with predicates in the WHERE Write queries which use the PIVOT Describe how SQL Server uses indexes to

clause operator support query execution

Sort data using ORDER BY Write queries which use the GROUPING Enable the display of execution plans in Filter data in the SELECT clause with TOP SETS subclause SQL Server Management Studio Filter data with OFFSET and FETCH Write queries which use GROUP BY Enable system IO and time information in

Understand and handle NULLs ROLLUP query results

Write queries which use GROUP BY Describe the differences between Module 6: Working with SQL Server Denali accessing data in sets versus via cursors Data Types Use the basic form of PIVOT and

line UNPIVOT operators

(4)

This module explains the data types SQL SETS clause Server uses to store data. Itintroduces the

many types of numeric and special-use data Module 14: Querying SQL Server Metadata types.It also explains conversions between data line

types, and the importance of type precedence.

line This module introduces the use of SQL Introducing SQL Server Denali Data Types Server system objects in T-SQL queries. It Working with Character Data will cover the use of system catalog views, system stored procedures, system functions, Write queries which return date and time and dynamic management objects.

data line

Write queries which use date and time

functions Lab : Querying SQL Server Metadata Write queries which return character data line

Write queries which use character Querying system catalog views Querying system functions

Describe numeric data types, type Querying system dynamic management precedence and type conversions

Write queries using character data types Write queries against system catalog Write queries using date and time data views and functions

types Execute system stored procedures Identify and query system dynamic Module 7: Using Built-In Functions management objects

line Describe security permissions necessary to query system objects

This module introduces the use of functions

that are built in to SQL Server Denali, and will Module 15: Executing Stored Procedures discuss some common usages including data line

type conversion, testing for logical results and nullability.

line

Writing Queries with Built-In Functions Using Conversion Functions

Using Logical Functions

Write queries which use conversion functions

Write queries which use logical functions Write queries with built-in scalar functions Use conversion functions

Use logical functions

Use functions that work with NULL Module 8: Grouping and Aggregating Data

line

This module introduces methods for grouping data within a query, aggregating the grouped data and filtering groups with HAVING. The module is designed to help the student grasp why a SELECT clause has restrictions placed upon column naming in the GROUP BY clause as well as which columns may be listed in the SELECT clause.Lessons

line

Using Aggregate Functions Using the GROUP BY Clause Filtering Groups with the HAVING Write queries which use the GROUP BY clause

Write queries which use aggregate functions Write queries which use distinct aggregate functions

(5)

Write queries which summarize 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

Module 9: Using Subqueries

line

Additional Information:

The Beta version of this course (10774AB) utilizes pre-release software in the virtual machine for the labs. Many of the exercises in this course are SQL Azure enabled.

This course will be delivered with digital courseware. In order to have the best learning experience you are asked to bring your own second screen to view the courseware. A second screen includes: tablets and laptops.