Querying Microsoft SQL Server 2014 (M20461) | MSP Training

ABOUT THE PROGRAM

The Querying Microsoft SQL Server 2014 course provides the technical skills to the delegates and enables them to write basic Transact SQL queries. The course includes concepts of SQL Server components and essential capabilities of SQL Server data platform. The training program lays a strong foundation for all SQL Server related disciplines including administration and development of database and business intelligence. The course provides knowledge about sophisticated tools and techniques to the delegates to enhance their performance in online transaction processing and greater insight into the data for better analysis. During the course, the delegates will learn the techniques to perform SELECT statements, data sorting and filtering, grouping and combine data, error handling and data manipulation language.

Apply T-SQL queries to manage database effectively
Courses led by highly certified and well-experienced instructors
Perform data operations like sort, filter, and group and aggregate quickly
MSP Training offers the courses at an affordable price and better value for money
Create and execute dynamic SQL statements
Error handling using TRY/CATCH blocks
Courses delivered using best practices and real world examples

WHAT'S INCLUDED ?

Find out what's included in the training programme.

Key Learning Points

Clear and concise objectives to guide delegates through the course.

Exam Prepration Tips

It helps in preparation for the exam so that they can score well.

Tutor Support

A dedicated tutor will be at your disposal throughout the training to guide you through any issues.

Certificate

Delegates will get certification of completion at the end of the course.

PREREQUISITES

The delegates must have knowledge of core functions of Windows operating system and must be familiar with relational database management system.

TARGET AUDIENCE

Database Developers
Report Writers
Database Administrators
Client Application Developers
Business Intelligence Professionals
Business Analysts

WHAT WILL YOU LEARN?

Understand the basic concepts and structure of Microsoft SQL Server 2014
Classify the components of T-SQL and usage of sets and predicate logic in SELECT statements
Able to remove duplicates and write SELECT statements and CASE expressions
Learn how to consolidate and retrieve data from multiple sources
Techniques to resolve missing and unknown values
Recognize conversions between data types and identify importance of type precedence
Apply manipulation language to insert and modify data
Learn how to invoke system stored procedures

Enquire Program

Fill in the form below & we'd get back to you.

PROGRAM OVERVIEW

Nowadays the source of incoming data is not only restricted to few employees detail files or student details, it has been expanded much more than that. Previously, Organizations tend to store and maintain data in FoxPro, dBase for a longer period of time. Later on, Microsoft Access came into existence. However, these have insufficient capabilities to store the massive bulk of data.

Therefore, the need for applications handling a large amount of data increased with the influx of massive incoming data. Two major applications introduced in the market were SQL Server and Oracle. Oracle became successful overnight but SQL Server took some time to establish itself. Once it was all settled and organized, there was no point of looking behind and it became one of the successful software application across the world. Microsoft SQL Server became an efficient tool for managing database systems in the organization. Transact –SQL (T-SQL) language is extensively used by SQL Server for managing and manipulating data in relational database management system (RDBMS).

Being a Microsoft product SQL Server, it helps in delivering excellent performance and enable the delegates to create and modify data using T-SQL queries. The main aim of the course is to prepare the delegates for the below-mentioned exam and get them certified. The exam serves as a fundamental exam for database administration, business intelligence, and database development.

Key Information of Querying Microsoft SQL Server 2014 (M20461) Exam

In order to complete the course, it is mandatory for delegates to take the exam. The exam comprises below topics:

Creation of database objects: 20-25%

Working with data: 25-30%

Modifying data: 20-25%

Troubleshoot and optimise data: 25-30%

PROGRAM CONTENT

An Introduction to Microsoft SQL Server 2014

Explain SQL Server basic Architecture
SQL Server versions and editions
Work with SQL Server Management Studio

An Introduction to T-SQL Querying

Understand the elements of T-SQL
Describe their role in writing queries
Understand the use of sets
Determine the use of predicate logic in SQL Server
Study the logical order of operations in SELECT statements

Writing SELECT Queries

Understand the techniques to write simple SELECT statements
Exclude duplicates with DISTINCT
Apply table aliases and columns
How to write simple CASE expressions

Querying multiple tables

Apply joins to query multiple tables in a SELECT statement
Write queries with Inner and outer joins
Queries applying self-joins and cross joins

Sorting and filtering data

How to filter data in the WHERE clause using predicates
Sort data using ORDER BY
Use OFFSET FETCH and TOP options for filter data
Work with missing and unknown values

Functioning with SQL Server 2014 Data Types

Classify type precedence, data types, and type conversions
Work with character data types for writing queries
How to use time and date data types for writing queries

Applying DML to Change Data

Add data to your tables
Modify and delete existing records in tables

Handling Built-in Functions

Write queries with use of built in scalar functions
Describe conversion functions
Understand logical functions
Determine functions to work with NULL

Grouping and Aggregating data

Use built in aggregate functions to summarize data
Arrange rows into groups by using GROUP BY clause
Filter groups with HAVING clause based on a search condition

Using Subqueries

Write Self-contained and correlated subqueries
Explain scalar or multi valued results
Use EXISTS predicate with subqueries

Using Table Expressions

Applying Views
Use derived tables to write queries
Understand common table expressions
Explain Inline Table-Valued Functions
Create simple inline table valued functions

Applying Set Operators

Combining data using UNION operator
Use INTERSECT and EXCEPT operators to write queries
Manipulating rows using APPLY operators

Use of Window Ranking, Offset, and Aggregate Functions

Understand the benefits to use window functions
Use OVER for creating Windows
Analyse Window Functions

Pivoting and Grouping Sets

Write queries with PIVOT and UNPIVOT
Specify multiple groupings
Work with grouping sets

Querying data with Stored Procedures

Classify PIVOT and UNPIVOT
Pass parameters to stored procedures
Design simple stored procedures
Perform Dynamic SQL with EXEC

Programming with T-SQL

Understand Programming elements of T-SQL
Controlling program flow using IF and WHILE blocks
Understand batches
Allocating synonyms and variables

Implementing Error Handling

Understand Behavior of SQL Server
Use TRY/CATCH block
Work with error information
Identify user defined errors
Explain pass system errors in T-SQL code

Implementing Transactions

Classify Transactions and Database engine
Understand differences between batches and transactions
Use transaction control language statements to manage transactions
Apply SET XACT_ABORT
Explore Isolation Levels and its effect on transactions

Improving Query Performance

Classify the elements of well-performing queries
Describe basic query performance data

Querying SQL Server Metadata

Performing System Stored Procedures
Understand functions of Querying System Catalog
Describe Dynamic Management Objects

Querying Microsoft SQL Server 2014 (M20461) Enquiry

Enquire Now

----- OR -------

Reach us at 0121 368 7851 or info@msptraining.com for more information.

ABOUT Crewe

Located within the borough of Cheshire East, 158 miles north of London, Crewe is a railway town of Cheshire, England. With a population of 72,863, Crewe is known for its large railway station. It is also known for Crewe Works – an organisation that used to manufacture and recondition locomotives. Crewe is also known as the birthplace of Rolls-Royce. Rolls-Royce remained in Crewe from 1946 to 2002 i.e. for a span of 56 years. Now it is the Bentley motor cars that are being manufactured in Crewe.

A mention of Creu is found in the Domesday Book - a record of the "Great Survey" of much of England & Wales that was completed in 1086 to determine the taxes that were owed to the King. However, it was not until 1843 that Crewe had its first modern settlement owing to a development in the railway system that had started in the early 1840’s. During the Second World War, Crewe became a target of the Nazi forces because of the railway junction as well as the presence of Rolls-Royce engineering works.

Developments

In 2008, the development of the current bus station and main shopping area were given up due to a shortage of funds. The railway station was also to be modified and as a part of the plan, it was to be moved to Basford. Anyhow, no such things happened and the current station underwent only some maintenance work instead. A new plan was developed by the Cheshire East Council. A Lifestyle Centre, furnished with swimming pool, a library and a gym was to be a part of the new plan. A new transport hub has been planned for Crewe that would be completed by 2027.

People

Blaster Bates - an expert with explosives and demolitions
William Cooper - novelist
Harold Hankins - Vice-Chancellor of the University of Manchester Institute of Science and Technology.
William Hope (1863 – 1933) - A leader in the field of spirit photography
Chris Hughes (born 1947) – One of the top quizzers’ from Britain
Jimmy MacDonald(1906 – 1991) - During his lifetime he lent his voice to the cartoon character Mickey Mouse.
Sir Philip Craven (born 1950) – International Paralympic Committee President
Neil Brooks - Gold medalist, Australian Olympic swimming
John Edward Morris - former cricketer from England
Carl Ashmore, - children's author
Craig Jones - Motorcycle racer, England
Shanaze Reade - born in 1988 Shanaze is a world BMX and track cycling champion.
Muthu Alagappan - a medical student but known in the USA for his analytical skills in professional basketball.

Things To Do

Crewe has various attractions one can not afford to miss. The visitors and the locals alike love to visit the following attractions:

Crewe Lyceum Theatre
- Located at the Lyceum Theatre Heath Street in Crewe this is a small and a beautiful theatre. A great theatre at a good location provides a positive feedback from the visitors.
Queens Park
- Beautiful park with a relatively newly renovated café. It is also home to a playing area that is well-taken care. Very friendly area. He kids would find it a great place. Free parking, boat rides, basketball hoops, fitness equipment, trails, band stands, lake and a café are the other features that it comes along with.
Crewe Heritage Centre
- The Crewe Heritage Center is located between two main railway lines that are still active with trains constantly passing by, this heritage centre as the fore-runner of the Virgin tilting trains such as the Flying Scotsman that used to stop here for a whole night. Visitors love to have a ride on trains on a small route.

TRAINING AVAILABILITY

Aberdeen

Bath

Bedford

Belfast

Birmingham

Bolton

Bournemouth

Bracknell

Brighton

Bristol

Bromley

Burton Upon Trent

Buxton

Cambridge

Canterbury

Cardiff

Carlisle

Chatham

Chelmsford

Chester

Chichester

Chorley

Cirencester

Colchester

Corby

Coventry

Darlington

Derby

Dereham

Doncaster

Dublin

Dundee

Dunfermline

Eastleigh

Edinburgh

Exeter

Fleet

Gatwick

Glasgow

Gloucester

Guildford

Harrogate

Heathrow

High Wycombe

Hinckley

Inverness

Ipswich

Jersey

Kingston upon Hull

Leeds

Leicester

Lincoln

Liverpool

London

Luton

Maidstone

Manchester

Middlesbrough

Milton Keynes

Newcastle

Northampton

Norwich

Nottingham

Oxford

Peterborough

Plymouth

Poole

Portsmouth

Preston

Reading

Sheffield

Slough

Solihull

Southampton

Stoke-on-Trent

Sunderland

Swansea

Swindon

Teesside

Tonbridge

Tredegar

Wakefield

Warrington

Warwick

Watford

Windsor Town

Wokingham

Wolverhampton

Worcester

Wrexham

York