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 Glasgow

Glasgow, with a population of 1,057,600, is the biggest city in Scotland. Not only this, it is also the fourth biggest city in the whole of UK after London, Birmingham and Manchester in terms of population. Glasgow is situated on the River Clyde in the West Central Lowlands of the country. Citizens of Glasgow are known as “Weegithe es”.

Places of Interest

Glasgow is home to various places that attract visitors every year. Some of the famous attractions of Glasgow have been listed below:

Glasgow City Chambers: It is the most magnificent building that dictates the story of wealth and its industrial prosperity as the Second City of the Empire. It is a building that is an instant favourite among all who visit this beauty. The building was inaugurated by Queen Victoria in the late 1880’s and even today it is the headquarter of the city councils. There is a statue of the Queen with Truth above her. According to locals, the statue is their version of “The Statue of Liberty”.
Glasgow Cathedral: The Reformation that happened in 1560’s in Scotland was a troubled time for the country. Many buildings famed for their architecture, collapsed. However, it was the Glasgow Cathedral that stood the test of times. It is considered to be a superb building of the medieval era. The building is said to have been built somewhere between the 13^th and the 15^th Interestingly, the building was built at the place where the patron saint and founder of the city, St. Mungo was laid to rest. The place is his shrine.The Glasgow Cathedral is home to a crypt that was built in the 13^th century. The crypt houses the body of St. Kentigern.
Glasgow Museums: The city is home to eight museums each of which have a rich collection of various artworks from the past as well as the present. The list follows:
- St Mungo Museum of Religious Life and Art and Provand's Lordship
- Gallery of Modern Art
- Scotland Street School Museum
- Kelvingrove Art Gallery and Museum
- People's Palace and Winter Gardens
- The Burrell Collection
- Glasgow Museums Resources Centre
- Riverside Museum

The museums have abundant of rare artworks, collection of different type of furniture, arms and armaments, and what not. All the Glasgow Museums have free admission and their sections for the kids as well where they not only have fun, they also learn. The accompanying shopping centres in some of the museums also sell replicas of the artworks.

Glasgow Cross: The Glasgow Cross is medieval architecture building. It houses a Clock Tower that used to be a part of the City Chambers that was destroyed by fire in 1926. The building also houses another hexagonal shape building the Tolbooth.
Glasgow is also famous for its bridges. The Tradeston Pedestrian Bridge, Kingston Bridge, and the Clyde Arc are just some of them.The Tradeston crosses the River Clyde on the east of the M8 motorway while the Kingston Bridge crosses the River Clyde carrying the M8 motorway along with it.The Clyde Arc is a new bridge and also has a wonderful curved design as it crosses the bridge.
Parks: Besides museums and bridges forming the pride of Glasgow, the parks also help in promoting the city tourism. Some of the major parks that the visitors could come across in Glasgow are listed below:
- Bellahouston Park
- Botanic Gardens
- Glasgow Green
- Kelvingrove Park
- Mugdock Park
- Queen’s Park
- Strathclyde Country Park
- Victoria Park
  - Fossil Grove: Part of the Victoria Park, this is open in summers only.
- Tollcross Park

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

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