Téléchargez notre brochure Formation en français :
|
Querying Microsoft SQL Server 2012
Description
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. This course helps people prepare for exam 70-461.
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.
Prérequis
Before attending this course, students must have: Working knowledge of relational databases. Basic knowledge of the Microsoft Windows operating system and its core functionality. Before attending this course, students should have: Basic understanding of virtualization technology (Classroom labs utilize virtual machines) To help you prepare for this class, review the following resources: Book: T-SQL Fundamentals for Microsoft SQL Server 2012 and SQL Azure MCTS Self Paced Training Kit 70-461
Objectifs
-
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
Programme
-
Module 1: Introduction to Microsoft SQL Server 2012
-
- Introducing Microsoft SQL Server 2012
-
- Getting Started with SQL Server Management Studio
-
* Lab : Working with SQL Server 2012 Tools
-
- Working with SQL Server Management Studio
-
- Creating and Organizing T-SQL scripts
-
- Using Books Online
-
Module 2: Getting Started with SQL Azure
-
- Overview of SQL Azure
-
- Working with SQL Azure
-
Module 3: Introduction to T-SQL Querying
-
- Introducing T-SQL
-
- Understanding Sets
-
- Understanding 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
-
- Executing queries which sort data using ORDER BY
-
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
-
- Write simple SELECT Statements
-
- Eliminate Duplicates Using Distinct
-
- Use Table and Column Aliases
-
- Use a Simple CASE Expression
-
Module 5: Querying Multiple Tables
-
- Understanding Joins
-
- Querying with Inner Joins
-
- Querying with Outer Joins
-
- Querying with Cross Joins and Self Joins
-
* Lab : Querying Multiple Tables
-
- 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 Loins
-
- Writing Queries That Use Cross Join
-
Module 6: 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
-
* Lab : Sorting and Filtering Data
-
- 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
-
Module 7: Working with SQL Server 2012 Data Types
-
- Introducing SQL Server 2012 Data Types
-
- Working with Character Data
-
- Working with Date and Time Data
-
* Lab : Working with SQL Server 2012 Data Types
-
- 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
-
Module 8: Using Built-In Functions
-
- Writing Queries with Built-In Functions
-
- Using Conversion Functions
-
- Using 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
-
Module 9: Grouping and Aggregating Data
-
- Using Aggregate Functions
-
- Using the GROUP BY Clause
-
- Filtering Groups with HAVING
-
* 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
-
Module 10: Using Subqueries
-
- Writing Self-Contained Subqueries
-
- Writing Correlated Subqueries
-
- Using the EXISTS Predicate with Subqueries
-
* Lab : Using 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
-
Module 11: Using Table Expressions
-
- Using Derived Tables
-
- Using Common Table Expressions
-
- Using Views
-
- Using Inline Table-Valued Functions
-
* Lab : Using Table Expressions
-
- 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
-
Module 12: Using Set Operators
-
- Writing Queries with the UNION Operator
-
- Using EXCEPT and INTERSECT
-
- Using 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 operators
-
- Write queries which use EXCEPT and INTERSECT operators
-
Module 13: Using Window Ranking, Offset and Aggregate 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
-
Module 14: Pivoting and Grouping Sets
-
- Writing Queries with PIVOT and UNPIVOT
-
- Working with Grouping Sets
-
* Lab : Pivoting and Grouping Sets
-
- Write queries which use the PIVOT operator
-
- Write queries which use the UNPIVOT operator
-
- Write queries which use the GROUPING SETS subclause
-
Module 15: Querying SQL Server Metadata
-
- Querying System Catalog Views and Functions
-
- Executing System Stored Procedures
-
- Querying Dynamic Management Objects
-
* Lab : Querying SQL Server Metadata
-
- Querying System Catalog Views and Functions
-
- Querying System Functions
-
- Querying System Dynamic Management Views
-
Module 16: 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
-
- Execute system stored procedures
-
Module 17: Programming with T-SQL
-
- T-SQL Programming Elements
-
- Controlling Program Flow
-
* Lab : Programming with T-SQL
-
- Declaring Variables and Delimiting Batches
-
- Using Control-of-Flow Elements
-
- Generating Dynamic SQL
-
- Using Synonyms
-
Module 18: Implementing Error Handling
-
- Using TRY / CATCH Blocks
-
- Working with Error Information
-
* Lab : Implementing Error Handling
-
- Redirecting Errors with TRY / CATCH
-
- Using THROW to Pass an Error Message Back to a Client
-
Module 19: Implementing Transactions
-
- Transactions and the Database Engine
-
- Controlling Transactions
-
* Lab : Implementing Transactions
-
- Controlling transactions with BEGIN, COMMIT, and ROLLBACK
-
- Adding error handling to a CATCH block
-
Module 20: 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
|
|