Course description

Learn basic Transact- SQL queries for SQL Server 2012 and 2014.

In this course, you will learn the technical skills required to write basic Transact-SQL (T-SQL) queries for Microsoft SQL Server 2014. This is the foundational course for all SQL server related disciplines: database administration, database development and business intelligence. Tools and skills you will learn include: SQL Server Management Studio, T-SQL, SELECT statements in querying multiple tables, data types, data sorting and filtering, data manipulation language (DML), grouping and aggregating data, table expressions, set operators, window functions, T-SQL programming, error handlers, and transaction management in SQL Server.

This course is designed for customers interested in learning SQL Server 2012 or SQL Server 2014. It covers the new features of SQL Server 2014 as well as the important capabilities across the SQL Server data platform.

This course incorporates material from the Official Microsoft Learning Product 20461: Querying Microsoft SQL Server 2014. It covers the skills and knowledge measured by Exam 70-461 and along with on-the-job experience, helps you prepare for the exam.

Each individual Microsoft Official Course On-Demand includes 3 months (or 12 months with purchase of Microsoft Total Access Collection) of 24x7 access to the following:

What You'll Learn in Class:

Who Needs to Attend:

Prerequisites:

Follow-On Courses:

Certification:

This course is part of the following programs or tracks:

Note: To reflect the inclusion of SQL Server 2014 content in exams 461, 462, and 463, Microsoft Learning has rebranded the MCSA certification to MCSA: SQL Server 2012/2014. As changes to each individual exam are modest in size and SQL Server 2012 skills remain relevant, all current MCSA: SQL Server 2012 certification holders will have their transcripts updated accordingly to reflect the new credential name. Please see https://www.microsoft.com/en-us/learning/mcsa-sql-certification.aspx for certification details.

Course Outline:

  1. Microsoft SQL Server 2014

  1. Transact-SQL Querying

  1. Write SELECT Queries

  1. Querying Multiple Tables

  1. Sorting and Filtering Data

  1. SQL Server 2014 Data Types

  1. DML to Modify Data

  1. Built-In Functions

  1. Grouping and Aggregating Data

  1. Sub-queries

  1. Table Expressions

  1. Set Operators

    • Write Queries with the UNION Operator

    • Use EXCEPT and INTERSECT

    • Use APPLY

  2. Window Ranking, Offset, and Aggregate Functions

    • Create Windows with OVER

    • Explore Window Functions including Ranking, Aggregate and Offset Functions

  3. Pivoting and Grouping Sets

    • Write Queries with PIVOT and UNPIVOT

    • Work with Grouping Sets

  4. Query Data with Stored Procedures

    • Query Data with Stored Procedures

    • Pass Parameters to Store Procedures

    • Create Simple Stored Procedures

    • Work with Dynamic SQL

  5. Programming with T-SQL

  6. Implement Error Handling

    • Use TRY/CATCH Blocks

    • Work with Error Information

  7. Implement Transactions

    • Transactions and the Database Engine

    • Control Transactions

    • Isolation Levels

  8. Appendix 1: Improve Query Performance

    • Factors in Query Performance

    • Display Query Performance Data

  9. Appendix 2: Query SQL Server Metadata

    • Query System Catalog Views and Functions

    • Execute System Stored Procedures

Query Dynamic Management Objects

Labs:

Lab 1: Work with SQL Server 2014 Tools

Lab 2: Transact-SQL Querying

Lab 3: Write Basic SELECT Statements

Lab 4: Query Multiple Tables

Lab 5: Sort and Filter Data

Lab 6: SQL Server 2014 Data Types

Lab 7: Modify Data using DML

Lab 8: Built-In Functions

Lab 9: Group and Aggregate Data

Lab 10: Sub-Queries

Lab 11: Table Expressions

Lab 12: Set Operators

Lab 13: Windows Ranking, Offset and Aggregate Functions

Lab 14: Pivoting and Grouping Sets

Lab 15: Execute Stored Procedures

Lab 16: Programming with T-SQL

Lab 17: Implement Error Handling

Lab 18: Implement Transactions

Lab 19: Improve Query Performance

Lab 20: Query SQL Server Metadata