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.
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.
Microsoft SQL Server 2012: Manipulate Data Using Operators and Functions
- describe the features of functions in SQL Server 2012
- use built in functions in SQL Server 2012
- determine the kind of scalar function required in a given scenario
- manipulate data using built-in functions
- manage results sets
- return a single value by evaluating conditions
- describe the considerations involved in creating summary reports
- change a table-valued expression using the PIVOT and UNPIVOT operators in a given scenario
- use the results returned from a subquery in an outer query
- use operators and table expressions to work with data in given scenarios
- determine which expression should be used in a given scenario