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 Carlisle

Carlisle, also known as the Great Border City as it has a common border with Scotland and England, is located in Cumberland at the convergence of the rivers Eden, Caldew and Petteril. It serves not only as the administrative centre for Cumbria County Council and Carlisle City Council but is also the largest settlement in the Cumbria County. Carlisle is known to have been a Roman settlement. Carlisle became an important military settlement during the Middle Ages as it enjoyed the closeness with the Kingdom of Scotland. One of the main attractions of Carlisle is the Carlisle Castle which was built by William Rufus in 1092 AD and was to be the prison of the Queen of Scotts, Mary. In the 12^th Century, by the order of Henry I, a nunnery came up in Carlisle. A diocese came into existence somewhere in the 1130’s and the priory became a cathedral that is now known as the Carlisle Cathedral. Carlisle is a place of historic importance as all the greats such as Celts, Romans, King Arthur and Bonnie Prince Charlie have either ruled or lived.

Later, the Industrial Revolution saw the rise of the textile industry and the beginning of a social as well as economical transformation in Carlisle.

Attractions

The Carlisle Castle: The Carlisle Castle has stood here for nine centuries. The Castle was home to the King’s Royal Border Regiment and even held Queen Mary of Scotts as a prisoner. As of today, it is a place of an exhibition that showcases its history to the generation of the 21^st Century in the form of exhibitions. The Castle also has Cumbria's Museum of Military Life that depicts the arms and other objects used by the military of the various kings in battles and wars.
The Carlisle Cathedral: Carlisle Cathedral is considered to be a magnificent piece that has been taken from the history of North West England. Having stood on the border for almost 900 years it seems to say a tale of its own. Even though it is not the biggest cathedral in England, it delights the visitors due to its religious connections. Visitors come here just not to see the cathedral but also to make worship to the Lord and praise Him. Here religion is no bound. Anybody from any religion can come and sit in the congregation at these services. Visitors come here to discover stories of faith, love and hope.
Our Lady and St Joseph's Church: Built in the early 1890's, this church is a Roman Catholic Parish church. The church has a building that is listed as Grade II.

Transport

Owned and managed by the Stagecoach, the bus station serves Carlisle. Located on the Dury Lane are the bus station that is served by a travel centre and seven stands. The present station was a replacement for a larger station at the same site. The replacement took place in the 1990s. Stagecoach in Cumbria and Arrestation at the iva North East are the main operators of the bus station in Carlisle.

Media

For around 48 years, 1961 to 2009, Carlisle played host to the Border Television that served the ITV Border region. After 1970’s Border Television suffered a decline in both the quantity and the range of its output. The premises of the Border Television were demolished after it shutdown operation in 2010. No programs were telecast till 2014. Border Television announced its return to Carlisle in 2014. As far as the printed news is concerned, it is taken care of by The Cumberland News. The newspaper is published every Friday.

TRAINING AVAILABILITY

Aberdeen

Bath

Bedford

Belfast

Birmingham

Bolton

Bournemouth

Bracknell

Brighton

Bristol

Bromley

Burton Upon Trent

Buxton

Cambridge

Canterbury

Cardiff

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

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