Microsoft SQL Fundamentals Training (20461)

Level: Intermediate
RATING: 4.5/5 4.47/5 Based on 142 Reviews

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

Microsoft SQL Fundamentals Training (20461)

Key Features of this SQL Fundamentals Training:

  • Prepare for Microsoft certification exam 70-461
  • Microsoft Official Course (MOC) content
  • After-course instructor coaching benefit
  • After-course Sandbox benefit to practice skills learned in class
  • Eligible to use with your Microsoft Software Assurance Training Vouchers (SATVs)

You Will Learn How To:

  • Describe the basic architecture and concepts of Microsoft SQL Server 2014.
  • Understand the similarities and differences between Transact-SQL and other computer languages.
  • Write SELECT queries
  • Query multiple tables
  • Sort and filter data
  • Describe the use of data types in SQL Server
  • Modify data using Transact-SQL
  • Use built-in functions
  • Group and aggregate data
  • Use subqueries
  • Use table expressions
  • Use set operators
  • Use window ranking, offset and aggregate functions
  • Implement pivoting and grouping sets
  • Execute stored procedures
  • Program with T-SQL
  • Implement error handling
  • Implement transactions

Certifications/Credits:

CPE 29 Credits

Choose the Training Solution That Best Fits Your Individual Needs or Organizational Goals

ON DEMAND

On Demand + Instructor Coaching

  • The same high quality MOC content as the live event
  • Flexibility to take the course on your own time, at your own pace
  • Enjoy a blend of video, text, hands-on labs, and knowledge checks with on demand training
  • Forever access to the digital course materials – including future course content updates
  • 2 FREE hours of individual coaching from an MCT Learning Tree Instructor
View Details ›

Standard: $895

Government: $895

GET STARTED

PRODUCT #8463

LIVE, INSTRUCTOR-LED

In Class & Live, Online Training

View Details ›

Standard: $3190

Government: $2833

GET STARTED

PRODUCT #8463

TRAINING AT YOUR SITE

Team Training

  • Bring this or any training to your organization
  • Full - scale program development
  • Delivered when, where, and how you want it
  • Blended learning models
  • Tailored content
  • Expert team coaching

Contact Us for Team Pricing

GET STARTED

On Demand + Instructor Coaching

SQL Fundamentals On-Demand Training Information

  • On-Demand Training Description

    With a blend of video, text, hands-on labs, and knowledge checks, you will receive the same high quality content as the live event, but you can attend on your own time, at your own pace.

    PLUS, we include access to a Microsoft Certified Trainer(MCT) to help you prepare for your certification exam and help you apply your new skills immediately… Learning Tree knows how to bring learning to life!

    • Flexibility to take the course on your own time, at your own pace
    • Forever access to the digital course materials – for any refreshers
    • You will receive a code with your purchase.The code may be redeemed for online access to this On Demand course for up to six months
    • Upon course activation, the MOC On Demand videos and labs are available for three months
    • 2 FREE hours of individual coaching from an MCT Learning Tree Instructor
    • This delivery is also eligible for Microsoft Assurance Training Vouchers(SATVs)
    • NOTE: Only live, in-class training is eligible for NASBA CPEs; on-demand training is not eligible for CPE credit

SQL Fundamentals On-Demand Training Outline

  • Module 1: Introduction to Microsoft SQL Server 2014

    This module introduces the SQL Server platform and major tools. It discusses editions, versions, tools used to query, documentation sources, and the logical structure of databases.

    Lessons

    • The Basic Architecture of SQL Server
    • SQL Server Editions and Versions
    • Getting Started with SQL Server Management Studio

    Lab : Working with SQL Server 2014 Tools

    After completing this module, students will be able to:

    • Describe the architecture and editions of SQL Server 2012.
    • Work with SQL Server Management Studio.
  • Module 2: Introduction to T-SQL Querying

    This module introduces Transact SQL as the primary querying language of SQL Server. It discusses the basic structure of T-SQL queries, the logical flow of a SELECT statement, and introduces concepts such as predicates and set-based operations.

    Lessons

    • Introducing T-SQL
    • Understanding Sets
    • Understanding Predicate Logic
    • Understanding the Logical Order of Operations in SELECT statements

    Lab : Introduction to Transact-SQL Querying

    After completing this module, students will be able to:

    • Describe the elements of T-SQL and their role in writing queries
    • Describe the use of sets in SQL Server
    • Describe the use of predicate logic in SQL Server
    • Describe the logical order of operations in SELECT statements
  • Module 3: Writing SELECT Queries

    This module introduces the fundamentals of the SELECT statement, focusing on queries against a single table.

    Lessons

    • Writing Simple SELECT Statements
    • Eliminate Duplicates with DISTINCT
    • Using Column and Table Aliases
    • Write Simple CASE Expressions

    Lab : Writing Basic SELECT Statements

    After completing this module, students will be able to:

    • Write simple SELECT statements.
    • Eliminate duplicates using the DISTINCT clause.
    • Use column and table aliases.
    • Write simple CASE expressions.
  • Module 4: Querying Multiple Tables

    This module explains how to write queries which combine data from multiple sources in SQL Server. The module introduces the use of JOINs in T-SQL queries as a mechanism for retrieving data from multiple tables.

    Lessons

    • Understanding Joins
    • Querying with Inner Joins
    • Querying with Outer Joins
    • Querying with Cross Joins and Self Joins

    Lab : Querying Multiple Tables

    After completing this module, students will be able to:

    • Describe how multiple tables may be queried in a SELECT statement using joins.
    • Write queries that use inner joins.
    • Write queries that use outer joins.
    • Write queries that use self-joins and cross joins.
  • Module 5: Sorting and Filtering Data

    This module explains how to enhance queries to limit the rows they return, and to control the order in which the rows are displayed. The module also discusses how to resolve missing and unknown results.

    Lessons

    • Sorting Data
    • Filtering Data with Predicates
    • Filtering with the TOP and OFFSET-FETCH
    • Working with Unknown Values

    Lab : Sorting and Filtering Data

    After completing this module, students will be able to:

    • Filter data with predicates in the WHERE clause.
    • Sort data using ORDER BY.
    • Filter data in the SELECT clause with TOP.
    • Filter data with OFFSET and FETCH.
  • Module 6: Working with SQL Server 2014 Data Types

    This module explains the data types SQL Server uses to store data. It introduces the many types of numeric and special-use data types. It also explains conversions between data types, and the importance of type precedence.

    Lessons

    • Introducing SQL Server 2014 Data Types
    • Working with Character Data
    • Working with Date and Time Data

    Lab : Working with SQL Server 2014 Data Types

    After completing this module, students will be able to:

    • Describe numeric data types, type precedence and type conversions.
    • Write queries using character data types.
    • Write queries using date and time data types.
  • Module 7: Using DML to Modify Data

    This module describes the use of Transact-SQL Data Manipulation Language to perform inserts, updates, and deletes to your data.

    Lessons

    • Inserting Data
    • Modifying and Deleting Data

    Lab : Using DML to Modify Data

    After completing this module, students will be able to:

    • Insert new data into your tables.
    • Update and delete existing records in your tables.
  • Module 8: Using Built-In Functions

    This module introduces the use of functions that are built in to SQL Server Denali, and will discuss some common usages including data type conversion, testing for logical results and nullability.

    Lessons

    • Writing Queries with Built-In Functions
    • Using Conversion Functions
    • Using Logical Functions
    • Using Functions to Work with NULL

    Lab : Using Built-In Functions

    After completing this module, students will be able to:

    • Write queries with built-in scalar functions.
    • Use conversion functions.
    • Use logical functions.
    • Use functions that work with NULL.
  • Module 9: Grouping and Aggregating Data

    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

    • Using Aggregate Functions
    • Using the GROUP BY Clause
    • Filtering Groups with HAVING

    Lab : Grouping and Aggregating Data

    After completing this module, students will be able to:

    • 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 10: Using Subqueries

    This module will introduce the use of subqueries in various parts of a SELECT statement. It will include the use of scalar and multi-result subqueries, and the use of the IN and EXISTS operators.

    Lessons

    • Writing Self-Contained Subqueries
    • Writing Correlated Subqueries
    • Using the EXISTS Predicate with Subqueries

    Lab : Using Subqueries

    After completing this module, students will be able to:

    • Describe the uses of queries which are nested within other queries.
    • Write self-contained subqueries which return scalar or multi-valued results.
    • Write correlated subqueries which return scalar or multi-valued results.
    • Use the EXISTS predicate to efficiently check for the existence of rows in a subquery.
  • Module 11: Using Table Expressions

    This module introduces T-SQL expressions which return a valid relational table, typically for further use in the query. The module discusses views, derived tables, common table expressions and inline table-valued functions.

    Lessons

    • Using Views
    • Using Inline Table-Valued Functions
    • Using Derived Tables
    • Using Common Table Expressions

    Lab : Using Table Expressions

    After completing this module, students will be able to:

    • Write queries which use derived tables.
    • Write queries which use common table expressions.
    • Create simple views and write queries against them.
    • Create simple inline table-valued functions and write queries against them.
  • Module 12: Using Set Operators

    This module introduces the set operators UNION, INTERSECT, and EXCEPT to compare rows between two input sets

    Lessons

    • Writing Queries with the UNION Operator
    • Using EXCEPT and INTERSECT
    • Using APPLY

    Lab : Using Set Operators

    After completing this module, students will be able to:

    • 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
  • Module 13: Using Window Ranking, Offset, and Aggregate Functions

    This module introduces window functions including ranking, aggregate and offset functions. Much of this functionality is new to SQL Server 2012. It will cover the use of T-SQL functions such as ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE and LAST_VALUE to perform calculations against a set, or window, of rows.

    Lessons

    • Creating Windows with OVER
    • Exploring Window Functions

    Lab : Using Window Ranking, Offset and Aggregate Functions

    After completing this module, students will be able to:

    • 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.
  • Module 14: Pivoting and Grouping Sets

    This module discusses techniques for pivoting data in T-SQL as well to introduce the fundamentals of the GROUPING SETS clause. It will also cover the use of GROUP BY ROLLUP and GROUP BY CUBE syntax in SQL Server.

    Lessons

    • Writing Queries with PIVOT and UNPIVOT
    • Working with Grouping Sets

    Lab : Pivoting and Grouping Sets

    After completing this module, students will be able to:

    • Write queries which pivot and unpivot result sets.
    • Write queries which specify multiple groupings with grouping sets.
  • Module 15: Querying data with Stored Procedures

    This module introduces the use of existing stored procedures in a T-SQL querying environment. It discusses the use of EXECUTE, how to pass input and output parameters to a procedure, and how to invoke system stored procedures.

    Lessons

    • Writing Queries with PIVOT and UNPIVOT
    • Passing Parameters to Stored Procedures
    • Creating Simple Stored Procedures
    • Working with Dynamic SQL

    Lab : Executing Stored Procedures

    After completing this module, students will be able to:

    • 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.
  • Module 16: Programming with T-SQL

    This module provides a basic introduction to T-SQL programming concepts and objects. It discusses batches, variables, control of flow elements such as loops and conditionals, how to create and execute dynamic SQL statements, and how to use synonyms.

    Lessons

    • T-SQL Programming Elements
    • Controlling Program Flow

    Lab : Programming with T-SQL

    After completing this module, students will be able to:

    • 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.
  • Module 17: Implementing Error Handling

    This module introduces the use of error handlers in T-SQL code. It will introduce the difference between compile errors and run-time errors, and will cover how errors affect batches. The module will also cover how to control error handling using TRY/CATCH blocks, the use of the ERROR class of functions, and the use of the new THROW statement.

    Lessons

    • Using TRY / CATCH Blocks
    • Working with Error Information

    Lab : Implementing Error Handling

    After completing this module, students will be able to:

    • Describe SQL Server's behavior when errors occur in T-SQL code.
    • Implement structured exception handling in T-SQL.
    • Return information about errors from system objects.
    • Raise user-defined errors and pass system errors in T-SQL code.
  • Module 18: Implementing Transactions

    This module introduces the concepts of transaction management in SQL Server. It will provide a high-level overview of transaction properties, cover the basics of marking transactions with BEGIN, COMMIT and ROLLBACK.

    Lessons

    • Transactions and the Database Engine
    • Controlling Transactions

    Lab : Implementing Transactions

    After completing this module, students will be able to:

    • Describe transactions and the differences between batches and transactions.
    • 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.
    • Describe the effects of isolation levels on transactions.
  • Module 19: Appendix 1: Improving Query Performance

    This module presents several key guidelines for writing well-performing queries, as well as ways to monitor the execution of your queries and their impact on Microsoft SQL Server

    Lessons

    • Factors in Query Performance
    • Displaying Query Performance Data

    Lab : Improving Query Performance

    After completing this module, students will be able to:

    • Describe components of well-performing queries.
    • Display and interpret basic query performance data
  • Module 20: Appendix 2: Querying SQL Server Metadata

    SQL Server provides access to structured metadata by using a variety of mechanisms, such as system catalog views, system functions, dynamic management objects, and system stored procedures. In this module, you will learn how to write queries to return system metadata using these mechanisms.

    Lessons

    • Querying System Catalog Views and Functions
    • Executing System Stored Procedures
    • Querying Dynamic Management Objects

    Lab : Querying SQL Server Metadata

    After completing this module, students will be able to:

    • Write queries that retrieve system metadata using system views and functions.
    • Execute system stored procedures to return system information.
    • Write queries that retrieve system metadata and state information using system dynamic management views and functions.

In Class & Live, Online Training

SQL Fundamentals Instructor-Led Training Information

  • Microsoft SQL Server Training Course Description

    This introduction to SQL and SQL query training training course includes 29 hours of Instructor-Led Training (ILT) or Virtual Instructor-Led Training (VILT) presented by a Learning Tree expert instructor who is a Microsoft Certified Trainer (MCT). This course can help you prepare for the Microsoft 70-461 certification exam, Querying Microsoft SQL Server 2012/2014,and is part of a Microsoft Certification Path and can help you prepare for your MCSA Certification, MCSE Certification, or MCSD Certification.

    Note: This course is designed for customers who are interested in learning SQL Server 2012 or SQL Server 2014. It covers the new features in SQL Server 2014, but also the important capabilities across the SQL Server data platform.

  • Requirements

    • Working knowledge of relational databases.
    • Basic knowledge of the Microsoft Windows operating system and its core functionality.
  • Exam Information

    • This course helps you prepare for the Microsoft 70-461 certification exam, Querying Microsoft SQL Server 2012/2014.
    • This exam will be the underlying exam for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence. As such, the primary target audience for this course is: Database Administrators, Database Developers and BI professionals.
  • Redeem Your Microsoft Training Vouchers (SATV)

    This course is eligible for Microsoft Software Assurance Training Voucher (SATV) redemption.

  • After-Course Computing Sandbox

    You'll be given remote access to a preconfigured virtual machine for you to redo your hands-on exercises, develop/test new code, and experiment with the same software used in your course. Note: The Sandbox is not available for the On-Demand option.

SQL Fundamentals Instructor-Led Training Outline

  • Module 1: Introduction to Microsoft SQL Server 2014

    This module introduces the SQL Server platform and major tools. It discusses editions, versions, tools used to query, documentation sources, and the logical structure of databases.

    Lessons

    • The Basic Architecture of SQL Server
    • SQL Server Editions and Versions
    • Getting Started with SQL Server Management Studio

    Lab : Working with SQL Server 2014 Tools

    After completing this module, students will be able to:

    • Describe the architecture and editions of SQL Server 2012.
    • Work with SQL Server Management Studio.
  • Module 2: Introduction to T-SQL Querying

    This module introduces Transact SQL as the primary querying language of SQL Server. It discusses the basic structure of T-SQL queries, the logical flow of a SELECT statement, and introduces concepts such as predicates and set-based operations.

    Lessons

    • Introducing T-SQL
    • Understanding Sets
    • Understanding Predicate Logic
    • Understanding the Logical Order of Operations in SELECT statements

    Lab : Introduction to Transact-SQL Querying

    After completing this module, students will be able to:

    • Describe the elements of T-SQL and their role in writing queries
    • Describe the use of sets in SQL Server
    • Describe the use of predicate logic in SQL Server
    • Describe the logical order of operations in SELECT statements
  • Module 3: Writing SELECT Queries

    This module introduces the fundamentals of the SELECT statement, focusing on queries against a single table.

    Lessons

    • Writing Simple SELECT Statements
    • Eliminate Duplicates with DISTINCT
    • Using Column and Table Aliases
    • Write Simple CASE Expressions

    Lab : Writing Basic SELECT Statements

    After completing this module, students will be able to:

    • Write simple SELECT statements.
    • Eliminate duplicates using the DISTINCT clause.
    • Use column and table aliases.
    • Write simple CASE expressions.
  • Module 4: Querying Multiple Tables

    This module explains how to write queries which combine data from multiple sources in SQL Server. The module introduces the use of JOINs in T-SQL queries as a mechanism for retrieving data from multiple tables.

    Lessons

    • Understanding Joins
    • Querying with Inner Joins
    • Querying with Outer Joins
    • Querying with Cross Joins and Self Joins

    Lab : Querying Multiple Tables

    After completing this module, students will be able to:

    • Describe how multiple tables may be queried in a SELECT statement using joins.
    • Write queries that use inner joins.
    • Write queries that use outer joins.
    • Write queries that use self-joins and cross joins.
  • Module 5: Sorting and Filtering Data

    This module explains how to enhance queries to limit the rows they return, and to control the order in which the rows are displayed. The module also discusses how to resolve missing and unknown results.

    Lessons

    • Sorting Data
    • Filtering Data with Predicates
    • Filtering with the TOP and OFFSET-FETCH
    • Working with Unknown Values

    Lab : Sorting and Filtering Data

    After completing this module, students will be able to:

    • Filter data with predicates in the WHERE clause.
    • Sort data using ORDER BY.
    • Filter data in the SELECT clause with TOP.
    • Filter data with OFFSET and FETCH.
  • Module 6: Working with SQL Server 2014 Data Types

    This module explains the data types SQL Server uses to store data. It introduces the many types of numeric and special-use data types. It also explains conversions between data types, and the importance of type precedence.

    Lessons

    • Introducing SQL Server 2014 Data Types
    • Working with Character Data
    • Working with Date and Time Data

    Lab : Working with SQL Server 2014 Data Types

    After completing this module, students will be able to:

    • Describe numeric data types, type precedence and type conversions.
    • Write queries using character data types.
    • Write queries using date and time data types.
  • Module 7: Using DML to Modify Data

    This module describes the use of Transact-SQL Data Manipulation Language to perform inserts, updates, and deletes to your data.

    Lessons

    • Inserting Data
    • Modifying and Deleting Data

    Lab : Using DML to Modify Data

    After completing this module, students will be able to:

    • Insert new data into your tables.
    • Update and delete existing records in your tables.
  • Module 8: Using Built-In Functions

    This module introduces the use of functions that are built in to SQL Server Denali, and will discuss some common usages including data type conversion, testing for logical results and nullability.

    Lessons

    • Writing Queries with Built-In Functions
    • Using Conversion Functions
    • Using Logical Functions
    • Using Functions to Work with NULL

    Lab : Using Built-In Functions

    After completing this module, students will be able to:

    • Write queries with built-in scalar functions.
    • Use conversion functions.
    • Use logical functions.
    • Use functions that work with NULL.
  • Module 9: Grouping and Aggregating Data

    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

    • Using Aggregate Functions
    • Using the GROUP BY Clause
    • Filtering Groups with HAVING

    Lab : Grouping and Aggregating Data

    After completing this module, students will be able to:

    • 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 10: Using Subqueries

    This module will introduce the use of subqueries in various parts of a SELECT statement. It will include the use of scalar and multi-result subqueries, and the use of the IN and EXISTS operators.

    Lessons

    • Writing Self-Contained Subqueries
    • Writing Correlated Subqueries
    • Using the EXISTS Predicate with Subqueries

    Lab : Using Subqueries

    After completing this module, students will be able to:

    • Describe the uses of queries which are nested within other queries.
    • Write self-contained subqueries which return scalar or multi-valued results.
    • Write correlated subqueries which return scalar or multi-valued results.
    • Use the EXISTS predicate to efficiently check for the existence of rows in a subquery.
  • Module 11: Using Table Expressions

    This module introduces T-SQL expressions which return a valid relational table, typically for further use in the query. The module discusses views, derived tables, common table expressions and inline table-valued functions.

    Lessons

    • Using Views
    • Using Inline Table-Valued Functions
    • Using Derived Tables
    • Using Common Table Expressions

    Lab : Using Table Expressions

    After completing this module, students will be able to:

    • Write queries which use derived tables.
    • Write queries which use common table expressions.
    • Create simple views and write queries against them.
    • Create simple inline table-valued functions and write queries against them.
  • Module 12: Using Set Operators

    This module introduces the set operators UNION, INTERSECT, and EXCEPT to compare rows between two input sets

    Lessons

    • Writing Queries with the UNION Operator
    • Using EXCEPT and INTERSECT
    • Using APPLY

    Lab : Using Set Operators

    After completing this module, students will be able to:

    • 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
  • Module 13: Using Window Ranking, Offset, and Aggregate Functions

    This module introduces window functions including ranking, aggregate and offset functions. Much of this functionality is new to SQL Server 2012. It will cover the use of T-SQL functions such as ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE and LAST_VALUE to perform calculations against a set, or window, of rows.

    Lessons

    • Creating Windows with OVER
    • Exploring Window Functions

    Lab : Using Window Ranking, Offset and Aggregate Functions

    After completing this module, students will be able to:

    • 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.
  • Module 14: Pivoting and Grouping Sets

    This module discusses techniques for pivoting data in T-SQL as well to introduce the fundamentals of the GROUPING SETS clause. It will also cover the use of GROUP BY ROLLUP and GROUP BY CUBE syntax in SQL Server.

    Lessons

    • Writing Queries with PIVOT and UNPIVOT
    • Working with Grouping Sets

    Lab : Pivoting and Grouping Sets

    After completing this module, students will be able to:

    • Write queries which pivot and unpivot result sets.
    • Write queries which specify multiple groupings with grouping sets.
  • Module 15: Querying data with Stored Procedures

    This module introduces the use of existing stored procedures in a T-SQL querying environment. It discusses the use of EXECUTE, how to pass input and output parameters to a procedure, and how to invoke system stored procedures.

    Lessons

    • Writing Queries with PIVOT and UNPIVOT
    • Passing Parameters to Stored Procedures
    • Creating Simple Stored Procedures
    • Working with Dynamic SQL

    Lab : Executing Stored Procedures

    After completing this module, students will be able to:

    • 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.
  • Module 16: Programming with T-SQL

    This module provides a basic introduction to T-SQL programming concepts and objects. It discusses batches, variables, control of flow elements such as loops and conditionals, how to create and execute dynamic SQL statements, and how to use synonyms.

    Lessons

    • T-SQL Programming Elements
    • Controlling Program Flow

    Lab : Programming with T-SQL

    After completing this module, students will be able to:

    • 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.
  • Module 17: Implementing Error Handling

    This module introduces the use of error handlers in T-SQL code. It will introduce the difference between compile errors and run-time errors, and will cover how errors affect batches. The module will also cover how to control error handling using TRY/CATCH blocks, the use of the ERROR class of functions, and the use of the new THROW statement.

    Lessons

    • Using TRY / CATCH Blocks
    • Working with Error Information

    Lab : Implementing Error Handling

    After completing this module, students will be able to:

    • Describe SQL Server's behavior when errors occur in T-SQL code.
    • Implement structured exception handling in T-SQL.
    • Return information about errors from system objects.
    • Raise user-defined errors and pass system errors in T-SQL code.
  • Module 18: Implementing Transactions

    This module introduces the concepts of transaction management in SQL Server. It will provide a high-level overview of transaction properties, cover the basics of marking transactions with BEGIN, COMMIT and ROLLBACK.

    Lessons

    • Transactions and the Database Engine
    • Controlling Transactions

    Lab : Implementing Transactions

    After completing this module, students will be able to:

    • Describe transactions and the differences between batches and transactions.
    • 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.
    • Describe the effects of isolation levels on transactions.
  • Module 19: Appendix 1: Improving Query Performance

    This module presents several key guidelines for writing well-performing queries, as well as ways to monitor the execution of your queries and their impact on Microsoft SQL Server

    Lessons

    • Factors in Query Performance
    • Displaying Query Performance Data

    Lab : Improving Query Performance

    After completing this module, students will be able to:

    • Describe components of well-performing queries.
    • Display and interpret basic query performance data
  • Module 20: Appendix 2: Querying SQL Server Metadata

    SQL Server provides access to structured metadata by using a variety of mechanisms, such as system catalog views, system functions, dynamic management objects, and system stored procedures. In this module, you will learn how to write queries to return system metadata using these mechanisms.

    Lessons

    • Querying System Catalog Views and Functions
    • Executing System Stored Procedures
    • Querying Dynamic Management Objects

    Lab : Querying SQL Server Metadata

    After completing this module, students will be able to:

    • Write queries that retrieve system metadata using system views and functions.
    • Execute system stored procedures to return system information.
    • Write queries that retrieve system metadata and state information using system dynamic management views and functions.

Team Training

SQL Fundamentals Training FAQs

  • Which Microsoft exam does this course prepare me for?

    This course can help you prepare for the Microsoft 70-461 certification exam, Querying Microsoft SQL Server 2012/2014

  • Is online training available?

    Yes! We know your busy work schedule may prevent you from getting to one of our classrooms which is why we offer convenient online training to meet your needs.

Microsoft SQL Training Reviews

David A. - New York Presbyterian Hospital

“The instructor was awesome!”


Maryanne N. - Department of Veterans Affairs

“The instructor was amazing.”


Tajah B. - Data Analyst, Montefiore Care Management

“Bobbie was a great instructor going over the material thoroughly. She was clear in her instructions and ensured the class was grasping all areas of the course material.”


Rondria L. - Litigation Support Specialist, Federal Trade Commission

“The course gave me a better understanding of how SQL queries are written and used. I have a better understanding of the ins and outs of queries and what they can do.”


Questions about which training is right for you?

call 888-843-8733
chat Live Chat




100% Satisfaction Guaranteed

Your Training Comes with a 100% Satisfaction Guarantee!*

  • If you are not 100 % satisfied, you pay no tuition!
  • No advance payment required for most products.
  • Tuition can be paid later by invoice - OR - at the time of checkout by credit card.

*Partner-delivered courses may have different terms that apply. Ask for details.

Denver / Online (AnyWare)
Online (AnyWare)
New York / Online (AnyWare)
Denver / Online (AnyWare)
Ottawa / Online (AnyWare)
New York / Online (AnyWare)
Herndon, VA / Online (AnyWare)
Denver / Online (AnyWare)
Online (AnyWare)
New York / Online (AnyWare)
Denver / Online (AnyWare)
Ottawa / Online (AnyWare)
Preferred method of contact:
Chat Now

Please Choose a Language

Canada - English

Canada - Français