Querying Microsoft SQL Server 2014 (70-461)

Disclaimer: Tech-Act is an independent training services provider. Any use of third party trademarks, brand names, products and services is only referential.Tech-Act disclaims any sponsorship, affiliation or endorsement of or by any third parties. Tech-Act is an authorized training partner only where explicitly stated and as listed here.


IT Certification Training Courses
mta certification
microsoft technology associate certification
mcsa certification

Querying Microsoft SQL Server 2014

About This Course

The Microsoft SQL Server 2014 training course is designed for candidates, who are enthusiasts about learning SQL Server 2014 or SQL Server 2012. The course covers all the latest features of Microsoft SQL Server 2014 along with critical capabilities across data platform of SQL Server. Most importantly, the course prepares you with essential knowhow and skills to help pass Exam 70-461.

Who Should Attend This Course

The course is a hot favorite among IT professionals, who are keen to pass the 70-461 Exam.
It is apt for:

  • Database developers
  • Database administrators
  • Business intelligence experts

The course is also well-suited for SQL power users, who plan to take up the 70-461 Exam. This includes application developers, business analysts and report writers.

Why This Course

The course will help you drive your SQL server understanding to a whole new level by helping you learn write queries and administer databases using T-SQL. Simply put, the course fast forwards you from a front-end noob to a back end ninja in the SQL Server administration domain. It covers the essential ins-and-outs of administering, programming and managing SQL Server 2014 whilst covering T-SQL language to help you learn create database objects using data definition language (DDL) and write queries using data manipulation language (DML).

Most importantly, Microsoft SQL Server 2014 certification course serves as a stepping stone to prepare for the 70-461 Exam followed by professional certifications for business intelligence and data platform.

Did you know a Microsoft SQL Server 2014 certified expert earns around $90,303 / annum (source: itcareerfinder.com)

Course Objectives

After completing the course, candidates can:

  • Implement transaction and handle errors
  • Use set operators
  • Aggregate and group data
  • Program with T-SQL
  • Data sorting
  • Query multiple tables
  • Write SELECT queries
  • Work with Data Manipulation Language (DML)
  • Work with Data Definition Language (DDL)
  • Use subqueries
  • Use built-in functions
  • Use table expressions

  • Basic understanding of Windows OS and its key functionalities
  • Adequate understanding of relational database

Course Benefits

After the successful completion of this course, a candidate can:

  • Query multiple tables
  • Execute data modification with the help of T-SQL
  • SQL Server 2014 programming
  • Create database objects
  • Work with Data Definition Language (DDL)
  • Work with Data Manipulation Language (DML)
  • Appear for the 70-461 exam
  • Administer business intelligence
  • Administer database
  • Develop databases

Skills and tools you will get to learn in this course include:

  • T-SQL
  • Filtering and sorting data
  • Setting up operators
  • T-SQL programming
  • Handling server errors
  • Managing transactions in the SQL Server
  • Aggregation and grouping of data


Create database objects


This section speaks about the overview of upcoming querying SQL . It includes scope of series ,also it covers who all would be the audience.

SQL Server Basics

This section talks about querying Microsoft SQL Server 2014, basics of MS SQL Server 2014 editions, primer on SQL databases in general, different default databases you’ll encounter after installation, which includes the System Databases (Master, MSDB, Model, TempDB) and User created databases .

SQL Querying Basics

This section speaks about basics of SQL querying It starts by defining what SQL is and how it differs from Transact SQL and explain the 3 categories of T-SQL statements which are Data Manipulation Language (DML), Data Definition Language(DDL), and Data Control Language(DCL).

Create and Alter Tables

This section covers table creation , alteration, compress tables and querying information from existing tables to new tables.

Create and Alter Views

This section explains how to restrict the information presented to a user or application by creating views and demonstrate how to view that has already been created. It explains inline table-valued function as well as creating indexed views to optimize performance. Further it demonstrates creating and altering views with querying Microsoft SQL Server 2014 and explains non-regression and data validation.

Create and Modify Constraints

This section covers how to maintain consistency within a database by using constrainsts, primary key, foreign key, unique and default constraints, their demonstration and implementation.

Create and Alter DML Triggers

This section demonstrate how to create and alter DML Triggers in SQL 2014. It covers the particulars of DML Triggers, the when/where/why/how they are used, AFTER trigger followed by the INSTEAD OF trigger type,nested triggers and how to use the UPDATE function.

Work with data

Basic SELECT Queries

This section covers SELECT SQL query, demonstrate how to perform a simple SELECT query to find set data form a database and pair the results by using WHERE and GROUP BY filter statements. It expalins filtering of data and removing duplicates from the results and demonstrate the decision making features of SQL using CASE command.

Query Using JOINS

This section cover the usage of JOINs function to return data from multiple databases, INNER JOIN command to filter the data, LEFT JOIN and RIGHT JOIN commands to return data sets that have matching values as well as all values from either the right or left tables respectively. It explains the concept and use of Temporary Tables, Views, and Derived Tables showing how to use the Temporary Tables and Views their benefits and problems.

Implement Subqueries

This section covers 3 different types of subqueries in SQL. It explains

  1. Scalar subquery, which is used to return a single value result.
  2. Multi-Valued subquery, which is used to return multiple results.
  3. Logical aptitude with Correlated Subqueries which works together with the outer query.
  4. Use APPLY operator, which is replacement for correlated subquieries and derived tables.
  5. Use of Common Table Expression(CTE).

Implement Aggregate Queries

This section covers the concept and different implementations of aggregate queries. It demonstrates

  1. GROUPING SETS which will define the groups
  2. ROLLUP statement which will create a group with a hierarchy
  3. RANK() function, which assigns a value to data for sorting purposes
  4. NTILE() function for grouping data
  5. Use of Spatial aggregates to visualize data by using a map(Geographical) or a shape(Geometrical)
  6. 4 Spatial Aggregate types including: Union, Envelope, Collection, and Convex.

Query Using Synonyms

This section explains how to query SQL Server 2014 using Synonyms, Intersects and Except. It demonstrates Synonyms, which are similar to Aliases, use of the Intersect which returns distinct rows that are common between sets, Except which returns distinct rows that are in the first query but not the second.

Understand Data Access Technologies

This section covers different Data Access (DA) technologies. It demonstrates what Data Access technologies are and how they’re used. Further it explains DA’s like SQL Server Native Client(SNAC), ADO.NET, JDBC drivers, and Microsoft drivers for PHP.

Work with Conditions and Nulls

This tutorial covers how to work with conditional statements and NULL values. It demonstrates the CASE function to perform decisions based on matching conditions,IS NULL function to test for NULL values and COALESCE function to replace a NULL value with another value.

Work with Table Constraints

This section covers how to work with table constraints and demonstrates using the INSERT INTO function to select data from one table and insert it into an existing table.

Query and Manage XML Data

This section demonstrate how to query and manage XML data in a SQL 2014 database, how to create XML data from relational data and when and why to work with XML. It also explains “shredding” XML into tables,the process of XML indexing, creating and using both primary and secondary indexes.

Implement Data Types

This section covers different data types and database structure planning. It explains numeric, date and time data types and their characteristics. It demonstrates the skills and knowledge in writting Transact SQL queries. It also describes, discusses and demonstrates SQL query writing skills in multiple areas and covers the advanced SELECT statements, grouping sets, aggregates, SET operators, basic T-SQL programming and performance Improvement. It mainly focuses heavily on demonstrating the syntax and logic of writing the queries.

Modify data

Create and Alter Stored Procedures

This section covers using Stored Procedure in SQL 2014. It starts with introduction to store procedures, where and why they are used, how to create simple and complex store procedures with and without parameters. At the end it explains Branching Logic controls used as conditional elements.


This section covers the INSERT, UPDATE, and DELETE statements in SQL Server 2014. It explains to use INSERT command to insert one or more rows into a target table with single and multiple values. At the end it covers using the INSERT SELECT and EXEC statements.

Combine Datasets

This section covers Datasets in SQL Server 2014. It explains difference between UNION and UNION ALL and the rules that govern their use and modifying data with MERGE statements.

Work with Functions

This section demonstrates what are functions in SQL, how to create and work with functions, types of User Defined Functions(UDFs),Scalar and Table-Valued functions.

Troubleshoot and optimize

Optimize Queries

This section introduces optimization of queries. It explains query execution phases and two types of statistics (TIME and IO). It continues with exploring query optimization, Execution Plans (Actual and Estimated) and the ways in which they are presented (Textual, Graphical, and XML). It also covers exploring query optimization by looking at Dynamic Management Views, 3 Hint types (JOIN, QUERY, and Table), JOIN types and Nested Loops.

Manage Transactions

This section covers how SQL Server 2014 manages Transactions to ensure the database integrity. It explains isolation levels, how SQL 2014 uses Commits and Rollbacks and the difference between Implicit and Explicit transactions.

Evaluate Different Operations

This section evaluate the use of row-based operations vs set-based operations. It explains when to use the Cursors as well as the impact of Scalar User Defined Functions(UDFs). At the end it demonstrates combining multiple DML operations and using set-based logic rather than row-based logic.

Implement Error Handling

This section covers implementing errors and error handling in SQL Server 2014. It describes types of locks and their scope of use and implementing the TRY/CATCH/THROW functions.

Schedule & Fees

Online Instructor Led Querying Microsoft SQL Server 2014 (70-461) Certification Training

Please contact us for more information on course fees and upcoming batch schedule.

Course FAQ

How helpful the course is for MS Exam 70-461?

The course is beneficial when it comes to Exam 70-461. Trainers at Tech-Act make sure to explain you with all the essential components associated with this exam. Candidates get to learn with examples in T-SQL programming to help grab a good hold on the topic.

Why Tech-Act?

Tech-Act boasts of a splendid track record in the IT industry since the last two decades, well aware of the talent needs faced by the IT industry. We ensure to get onboard top-notch IT trainers certified to do the job. With that being said, we are authorized learning solutions partners for CompTIA, EC Council and various other industry leaders. Also, we are a Person Vue Authorized Test Center with traditional and online classroom.

What are the key benefits of Microsoft SQL Server 2014?

The IT industry is craving for expert database administrators and developers, which makes Microsoft SQL Server 2014 a perfect pick-me-up. The course helps you become an expert in managing, implementing, configuring and administering Microsoft SQL Server 2014 and prepares you up for Exam 70-461. In short, the course prepares you to become an expert system database administrator for SQL Server 2014.



Your Cart