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 Heathrow

In London, Heathrow airport is a major international airport. Heathrow airport is the world’s second busiest airport by International passenger traffic. In 2016, Heathrow airport has a 75.7 million passengers travelling record. Heathrow airport renders scheduled services which is one of the six airports along with Gatwick, Luton, London City, Stansted and Southend. Heathrow airport is located 23 km west of Central London. This airport has two parallel east-west runways and four operational terminals that cover 4.74 sq. mi. From Heathrow airport, above 80 airlines fly to 180 locations (approx.) worldwide carrying more than 72 million travellers and 1.4 million tonnes of cargo every year. Heathrow airport is enclosed by the Hillingdon, Longford, Cranford and Harmondsworth from the north side and by Hatton and Hounslow from the east side. Heathrow airport offers many facilities such as for business, handicapped travellers and children. Heathrow airport has a splendid rail and road links to London and other cities of the country. From Heathrow airport, there are many public means of transport such as coaches, taxis, trains and Heathrow express, ease the passengers to travel to the main city.

History

Heathrow airports in its started life are known by the name of Great-West Aerodrome and occasionally known as Heathrow Aerodrome. It is originated in 1946 as London Airport for commercial air travel and renamed as Heathrow airport in 1966. Sir Frederick Gibberd has designed the Heathrow airport. He has designed the original terminals and buildings. In the 1980s and 90s Heathrow develop into a major employer with 70,000 staff.

Security

At the Heathrow airport, full body scanners are used which exhibits a cartoon-style figure of passengers’ body and exhibits all the items on the screen which are restricted to carry while travelling. Aviation security which is the unit of Metropolitan Police holds the responsibility of airport police. It also has armoured vehicles of Household Cavalry, which has been sporadically deployed during the time of heightened security.

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

Crewe

Darlington

Derby

Dereham

Doncaster

Dublin

Dundee

Dunfermline

Eastleigh

Edinburgh

Exeter

Fleet

Gatwick

Glasgow

Gloucester

Guildford

Harrogate

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