Microsoft SQL Server 2012: Manipulate Data Using Operators and Functions

This course is included in our On-demand training solution.


Microsoft SQL Server 2012 Transact-SQL offers advanced query techniques, such as built-in aggregate, analytic, scalar, and ranking functions, as well as common table expressions, or CTEs and derived tables to manipulate the result set for a query. To manipulate the result sets and generate summarization information, such as totals, the CUBE, GROUPING SETS, and ROLLUP operators can be used with the GROUP BY statement. The UNION, INTERSECT, and EXCEPT operators can be used to combined the result set of multiple queries. SQL Server 2012 also offers the use of CTEs and derived tables to obtain a result set, temporarily store it, and then pass it to a query to return another result set based on that information. This course demonstrates how to perform advanced query techniques to manipulate data using the SQL Server 2012 built-in functions, operators, and table expressions. This course is one of a series in the SkillSoft learning path that covers the objectives for the exam 70-461: Microsoft SQL Server 2012: Querying. This exam is one of the requirements for the Microsoft Certified Solutions Associated (MCSA): SQL Server 2012 certification.

Target Audience

Candidates for this path should have two or more years of experience using SQL Server 2012 or a previous version of SQL Server, and they are seeking to prove their proficiency in writing queries. Candidates for this exam are IT professionals who have jobs in which database administration is their main area of responsibility, or they work as database developers.


Expected Duration

120 min.

Course Objectives

Aggregate, Analytic, and Ranking Functions

  • describe the features of functions in SQL Server 2012
  • use built in functions in SQL Server 2012
  • Built-in Scalar Functions

  • determine the kind of scalar function required in a given scenario
  • Using Built-in Functions to Manipulate Data

    Combining Result Sets Using Operators and Expressions

  • manage results sets
  • return a single value by evaluating conditions
  • Summarizing Data using Operators

  • describe the considerations involved in creating summary reports
  • change a table-valued expression using the PIVOT and UNPIVOT operators in a given scenario
  • Using Derived Tables and CTEs

  • use the results returned from a subquery in an outer query
  • Manipulating Data using Operators and Table Expressions