Description

This course will teach you how to write Transact SQL queries for SQL Server 2012/2014. You will see how to perform simple queries, use built-in functions and also, how to group and aggregate data. This course covers all the topics necessary to prepare for the Microsoft 70-461 exam.

Who is this training for ?

For whom ?

Database administrators, developers.

Prerequisites

Training objectives

Write Transact SQL queries for SQL Server 2012/2014

Know how to use built-in functions

Learn to query data with stored procedures

Training program

• The basic structure of T-SQL queries
  • The architecture of SQL Server.
  • The SQL Server Management Studio application.
  • The basis of the language.
  • Predicate logic .
  • The SELECT statement.
  • Using column and table aliases.
  • Hands-on Running queries with SELECT.
  • Sort data with ORDER BY.
  • Use CASE expression.
• Joins and sorting
  • Query multiple tables with joins.
  • Inner and outer joins.
  • Cross joins and free joins.
  • Sorting and data filtering.
  • The WHERE clause.
  • Filtering with TOP and OFFSET-FECTCH.
  • Practical work Example of join and sorting data.
• Data types
  • Working with SQL Server 2014 data types.
  • Character data.
  • Using DML to modify data.
  • Insert, modify and delete data.
  • Practical work Example of using different types of joins.
• Built-in functions
  • Queries with built-in functions.
  • Conversion and logic functions.
  • Functions and the NULL value.
  • Practical work rnApplication examples of built-in functions.
• Data grouping and aggregation
  • Use aggregate functions.
  • The GROUP BY and HAVING clauses.
  • Included table views and functions.
  • Use aggregate functions. game operators (UNION, EXCEPT.
  • ).
  • Pivot and grouping.
  • The PIVOT and UNPIVOT clauses.
  • Practical work Examples of grouping and data aggregation.
  • Working with grouping sets.
• Stored Procedures and Programming with Transact-SQL
  • Querying data with stored procedures.
  • Passing parameters to stored procedures.
  • The basics of T-SQL programming.
  • Program flow control.
  • Error handling.
  • Transaction control and isolation levels.
  • Practical work r nCreate simple stored procedures.
  • Work with dynamic SQL.
  • Redirect errors with TRY/CATCH.
• Improve query performance
  • Write efficient queries.
  • Monitor execution.
  • Evaluate the impact on SQL Server.
  • Practical work r nQuery SQL server metadata.
  • Query catalog and system function views.