Querying Microsoft SQL Server 2014 (M20461)

Learn to write Transact-SQL (T-SQL) queries for managing data

Effectively manage database using T-SQL queries
Quickly perform data operations like sort, filter, group and aggregate
Course is designed by well experienced and certified trainers
Prepare well for the exam and gain the certification
Our instructors train delegates using best practices and real world examples
Pentagon training offers this course at an affordable price

As the amount of data is increasing day by day, it has become difficult for organisations to manage it. Microsoft SQL Server helps to manage and handle database systems in an organisation efficiently. SQL Server makes use of Transact-SQL (T-SQL) language. T-SQL is one of the major language used to manage and manipulate data in relational database management system (RDBMS). Along with this, SQL Server also supports various other languages like Microsoft Visual C# and Visual Basic. It delivers excellent performance and provides deeper insight into the data for better analysis.

In Pentagon Training’s Querying Microsoft SQL Server 2014 course learn to develop and manage databases. Understand how to add new data, update current data and delete data using Transact-SQL queries. This course covers all aspects of Querying Microsoft SQL Server 2014.

Who should take this course

Querying Microsoft SQL Server 2014 (M10461) course is intended for delegates who are into database administration, database development and business intelligence.

Delegates who are not database focused can take this course like report writers, client application developers and business analysts.

Prerequisites

For attending this course delegates must have good knowledge of relational database management system.

Basic knowledge of Windows operating system and its other functions can be beneficial.

Understand how to write queries using Transact-SQL (T-SQL).
Query many tables and perform operations on it like sort, filter, group and add data.
Understand SQL Server data types and change data using T-SQL.
Learn Built-in functions and operators.
Know window function ranking and offset.
Get knowledge of T-SQL programming.
Handle errors using TRY/CATCH statements and transaction implementation.

Key Learning Points

Exam Prepration Tips

Tutor Support

Certificate

Course Overview

Querying Microsoft SQL Server 2014 (M10461) course provide delegates with the technical skills required to write Transact-SQL queries to manage SQL Server. This course is a foundation for all disciplines related to SQL Server, namely database administration, development and business intelligence. During this course, understand about SQL Server Management Studio, T-SQL queries, SELECT statements, data sorting, and data types, grouping and aggregating data and managing transactions in SQL Server. This course also helps delegates to prepare well for the exam and gain the certification.

Course Content

Introduction to Microsoft SQL Server 2014

In this understand about SQL Server platform and its various tools. Also, learn about different tools used to query data and structure of databases.

Basic Architecture of SQL Server
SQL Server versions and editions
SQL Server Management Studio

Introduction to T-SQL Querying

In this module understand about Transact-SQL as primary SQL Server querying language.

T-SQL introduction
Understand sets
Understand predicate logic and logical order of operations in SELECT statements

Writing SELECT Queries

Learn about fundamentals of SELECT statement and queries.

How to write simple SELECT statements
Remove duplicates with DISTINCT
Use table aliases and columns
Write simple CASE expressions

Querying multiple tables

In this module learn how to write queries that merge data from many sources in SQL Server. Also, understand how to use JOINS in T-SQL queries for extracting data from many tables.

Joins overview
Query with Inner and outer joins
Query with self-joins and cross joins

Sorting and filtering data

In this module learn to enhance queries for limiting rows they return and control the order in which rows will be displayed. Also, find out how to resolve missing and unknown results.

Sort data
Using WHERE clause filter data
With TOP and OFFSET-FETCH options filter data
Work with missing and unknown values

Working with SQL Server 2014 Data Types

In this module understand about SQL Server data types used to store data. Get knowledge of various numeric and special use data types. Also, learn how to shift between data types and importance of type precedence.

SQL Server 2014 data types
Work with character data
Work with time and date data

Use DML to Change Data

In this module learn to use Transact-SQL Data Manipulation language for inserting, updating and deleting data.

Add data
Modify and delete data

Using Built-in Functions

In this module learn to use built-in functions present in SQL Server Denali and understand common usages like data type conversion, testing logical results and nullability.

With Built-in functions write queries
Use conversion and logical functions
Use functions to work with NULL

Grouping and Aggregating data

In this module understand about methods for gathering data using the query, aggregating grouped data and filtering groups with HAVING.

Use aggregate functions
Use GROUP BY clause
Filter groups with HAVING

Using Subqueries

In this module understand how to use subqueries in various parts of the SELECT statement including scalar and multi-select subqueries. Learn the use of EXISTS and IN operators.

Write Self-contained and correlated subqueries
Use EXISTS predicate with subqueries

Using Table Expressions

In this module understand T-SQL expressions that return logical relational table and using query further. Discuss views, derived tables, common table expressions and inline table-valued functions.

Use derived tables and common table expressions
Use views and Inline Table-Valued Functions

Using Set Operators

In this module know about Microsoft SharePoint Server as Business Intelligence platform and focus on building BI dashboards and scorecards with PerformancePoint Services.

With UNION operator write queries
Use INTERSECT, EXCEPT and APPLY

Using Window Ranking, Offset, and Aggregate Functions

This module introduces window functions including aggregating, offset and ranking functions. Understand new features of SQL Server 2014.

With OVER create windows
Explore Window Functions

Pivoting and Grouping Sets

In this module understand techniques for data pivoting in T-SQL and fundamentals of GROUPING SETS clause along with the use of GROUP BY ROLLUP and GROUP BY CUBE syntax in SQL Server.

Write queries with PIVOT and UNPIVOT
Work with grouping sets

Executing Stored Procedures

Understand the use of present stored procedures in T-SQL querying environment. Learn the use of EXECUTE and how to pass input and output parameters to a procedure, and how to invoke system stored procedures.

Query data with stored procedures
Pass parameters to stored procedures
Create simple stored procedures
Work with Dynamic SQL

Programming with T-SQL

Understand about basic of T-SQL programming concepts and objects. Also know about batches, variables, control of flow elements like loops and conditionals and learn to create and execute dynamic SQL statements.

Programming elements of T-SQL
Controlling program flow

Implementing Error Handling

Understand T-SQL error handlers and difference between compile errors as well as run-time errors. Learn how to handle errors using TRY/CATCH block and using THROW statement.

Use TRY/CATCH block
Work with error information

Implementing Transactions

In this module understand concepts of transaction management and its properties. Cover basic marking transactions with COMMIT, ROLLBACK and BEGIN.

Transactions and Database engine
Control transactions
Isolation Levels

About Wakefield

Wakefield

In West Yorkshire, England, Wakefield is located. Wakefield is on the River Calder and Pennines eastern edge. In 2001 Wakefield had a population of around 77,512. It increased for five Wakefield wards called East, North, South, West and Rural to 77,512 according to 2011 census. Wakefield is also dubbed as ‘ Merrie City’ in Middle Ages. John Leland in 1538 described it as ‘ A quick market town and large and meately large. It is also a well-served market of fish and flesh from sea and rivers so that vital is good and cheap there. Wakefield Battle took place in Wars of the Roses. Wakefield became a famous centre for wool and a market town. In the 18th century, Wakefield made a trade in corn and textiles. In 1888 parish church of Wakefield acquired Cathedral status. It also became a county town of West Riding of Yorkshire. It was the seat of West Riding County Council from 1889 till 1974.

History

Along with railroad, many streams and lakes also played a significant role in economic growth of Wakefield. There were many damn and around twenty mill sites that include fulling mills, gristmills and carding mills along these waterways. Due to this development growing population expanded in seven separate villages, East Wakefield, South Wakefield, North Wakefield, Burleyville Wakefield corner, Sanbornville and Union. Sanbornville villages are now the primary business centre in Wakefield. The new town hall was constructed in Sanbornville in 1895. From Lovell lake , ice was shipped and harvested by two companies with the help of 16 to 20 train carloads to Boston and beyond it every day. At the beginning of 1900’s railroading was to the extreme with 25 trains in and out of Sanbornville every day.

In 1911, due to fire various rail yard buildings burned and operations centre shifted to Dover. After the emergence of electrification, need for ice reduced. The Later popularity of automobiles further reduced the need for rail travel. Finally, in 1969, Snow train which was a passenger train, made its final run.

During Second half of 20th century, a major industry in Wakefield was the development of 11 lakes. Development of summer homes and services needed to be provided. This helped later to provide incomes to many Wakefield residents. It helped to retain the rural character of Wakefield for which Wakefield is known.

Education

Oldest school Surviving in Wakefield is Queen Elizabeth Grammar School, a boys school established in 1591 by Queen Elizabeth by Royal Charter. The original building is in Brook Street that is now the Elizabethan Gallery. In 1854, QEGS school was moved to Northgate. The school was administered by Governors of Wakefield Charities who also opened Wakefield Girls High School ( WGHS) located at Wentworth-street in 1878. Church of England opened National Schools that include St Mary’s in the 1840s and St Johns in 1861. Original St Austin’s Catholic School was opened in 1838. In 1846 Methodist School was opened on Thornhill Street. Eastmoor School previously Pinders Primary School is only opened by Education Act 1870 which is still open.

Wakefield College has origins in School of Art and Craft of 1868. It is today the primary provider of 6th form and further education in the area with around 10,00 part-time and 3000 full-time students. It has campuses in the surrounding towns as well as in the city. In 2007 Wakefield College and Wakefield City Council announced plans to create a University Centre of Wakefield but bid for funding failed in 2009. Other schools with sixth forms include QEGS, Cathedral High School which is now an Arts College for age 11 to 18 and Wakefield High Girls School.