Duration: 5 Days
In this course, you will develop the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2012. This course is the foundation for the all SQL Server-related disciplines, including database administration, database development, and business intelligence. Many of the labs are SQL Azure enabled, allowing you to perform the lab while connected to your own Windows Azure database in the cloud. This requires a Microsoft Windows Azure account.
This course incorporates materials from Querying Microsoft SQL Server 2012 (MS-10774) and covers the skills and knowledge measured by Exam 70-461. Along with on-the-job experience, this course helps prepare you for the exam.
Proven Impact Exclusive!
Continue developing your skills after class with 24/7 access to our live remote labs for six months, providing you with valuable extra time for hands-on practice.
What You Will Learn
- Write SELECT Queries
- Query Multiple Tables
- Use Built-In Functions and Subqueries
- Execute Stored Procedures
- Use Set Operators
- Implement Error-Handling and Transactions
- Use Table Expressions, Windows Ranking, Offset, and Aggregate Functions
- Sort and Filter Data
- Query SQL Server Metadata and Improve Query Performance
- Program with T-SQL
Audience
Database administrators, database developers, BI professionals, and SQL power-users such as report writers, business analysts, and client application developers
Prerequistes
- Working knowledge of relational databases
- Basic knowledge of Microsoft Windows operation system and its core functionality
Course Outline
1. Microsoft SQL Server 2012
- SQL Server 2012 Architecture
- Working with SQL Server 2012 Tools
- Working with SQL Server Databases
2. SQL Azure
3. Transact-SQL Querying
- Transact-SQL
- Sets
- Predicate Logic
- Logical Order of Operations in SELECT Statements
4. Writing SELECT Queries
- Writing Simple SELECT Statements
- Eliminating Duplicates with DISTINCT
- Using Column and Table Aliases
- Writing Simple CASE Expressions
5. Querying Multiple Tables
- Joins
- Querying with Inner Joins
- Querying with Outer Joins
- Querying Using Self and Cross Joins
6. Sorting and Filtering Data
- Sorting Data
- Filtering Data
- Filtering with the TOP and OFFSET-FETCH Options
- Working with Unknown Values
7. Working with SQL Server 2012 Data Types
- SQL Server 2012 Data Types
- Working with Character Data
- Working with Date and Time Data
8. Using Built-In Functions
- Writing Queries with Built-In Functions
- Conversion Functions
- Logical Functions
- Using Functions to Test for Nullability
9. Grouping and Aggregating Data
- Using Aggregate Functions
- Using the GROUP BY Clause
- Filtering Groups with the HAVING Clause
10. Using Subqueries
- Writing Self-Contained Subqueries
- Writing Correlated Subqueries
- Using the EXISTS Predicate with Subqueries
11. Using Table Expressions
12. Use Set Operators
13. Using Window Ranking, Offset, and Aggregate Functions
14. Pivoting and Grouping Sets
15. Querying SQL Server Metadata
16. Executing Stored Procedures
17. Programming with T-SQL
18. Implementing Error Handling
19. Implementing Transactions
20. Improving Query Performance
Course Labs
Lab 1: SQL Server 2012 Tools
Lab 2: Transact-SQL Querying
Lab 3: Write Basic SELECT statements
Lab 4: Query Multiple Tables
Lab 5: Filter and Sort Data
Lab 6: Work with SQL Server 2012 Data Types
Lab 7: Use Built-In Functions
Lab 8: Group and Aggregate Data
Lab 9: Use Subqueries
Lab 10: Use Table Expressions
Lab 11: Use Set Operators
Lab 12: Use Window Ranking, Offset, and Aggregate Functions
Lab 13: Pivot and Group Sets
Lab 14: Query SQL Server Metadata
Lab 15: Execute Stored Procedures
Lab 16: Program with T-SQL
Lab 17: Implement Error Handling
Lab 18: Implement Transactions
Lab 19: Improve Query Performance
