Study Notes for 70-461: Querying Microsoft SQL Server 2012 – Part 1
As I mentioned before I like certification because it gives me a chance to cover all topics related to a subject no matter how trivial. During my preparations for this exam I learned some useful stuff. I believe they can be very useful in real-life scenarios. A compiled a list of items that I learned while studying for this exam:
01. Common Table Expressions
Not a new feature in SQL 2012 actually. I have used it a few times but I didn’t know its name back then. A Common Table Expression (CTE) is basically a temporary result set that you can use in the scope statement. For example
WITH DepartmentCTE (Name, GroupName)
AS
(
SELECT Name, GroupName
FROM HumanResources.Department
WHERE GroupName = 'Executive General and Administration'
)
SELECT NationalIDNumber
FROM DepartmentCTE cte
INNER JOIN HumanResources.Employee e ON cte.Name = e.JobTitle
This is not a very good example because a simple join could be used instead. But for the demonstration purposes it should be enough to layout the syntax and usage.
Source: http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
02. Pivot / Unpivot
I had one question about Pivot and that was just the definition of it so it was easy. Basically, pivot converts row values to columns. For example, take this query:
SELECT o.TerritoryID, YEAR(OrderDate) AS SaleYear, SUM(SubTotal) AS Total
FROM Sales.SalesOrderHeader o
WHERE o.TerritoryID = 1
GROUP BY o.TerritoryID, YEAR(OrderDate)
ORDER BY SaleYear ASC, o.TerritoryID ASC
which yields this output:
Say we want to get all years as columns and the totals as the values in the row. Using pivots we can rewrite the query as follows:
SELECT 'Total Sales' AS TotalSales, [2005], [2006], [2007], [2008]
FROM
(
SELECT o.TerritoryID, YEAR(OrderDate) AS SaleYear, SubTotal
FROM Sales.SalesOrderHeader o
WHERE o.TerritoryID = 1
) AS SourceTable
PIVOT
(
SUM(SubTotal)
FOR SaleYear IN ([2005], [2006], [2007], [2008])
) AS PivotTable;
and we get this resultset:
Source: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
03. FOR XML
I think XML lost a lot of ground after the rise of JSON over the last few years but still it is used very commonly. I never used FOR XML in a real-life project but I was aware the Microsoft cares about it and not surprisingly I got 4 questions. It’s is very simple and intuitive actually. The keywords you need to know are: RAW, AUTO, EXPLICIT, PATH and ELEMENTS
Raw: Returns a single
Query:
SELECT TOP 1 *
FROM HumanResources.Department
FOR XML RAW
Output:
<row DepartmentID="1" Name="Engineering" GroupName="Research and Development" ModifiedDate="2002-06-01T00:00:00"/>
Auto: Generates the name of the elements from the query
Query:
SELECT TOP 1 *
FROM HumanResources.Department
FOR XML AUTO
Output:
<HumanResources.Department DepartmentID="1" Name="Engineering" GroupName="Research and Development" ModifiedDate="2002-06-01T00:00:00">
Elements: Returns the values in elements rather than attributes.
Query:
SELECT TOP 1 *
FROM HumanResources.Department
FOR XML AUTO, ELEMENTS
Output:
<HumanResources.Department>
<DepartmentID>1</DepartmentID>
<Name>Engineering</Name>
<GroupName>Research and Development</GroupName>
<ModifiedDate>2002-06-01T00:00:00</ModifiedDate>
</HumanResources.Department>
Path: Gives more control over the path of the nodes
Query:
SELECT TOP 1 *
FROM HumanResources.Department
FOR XML PATH('Department'), ROOT('HumanResources')
Output:
<HumanResources>
<Department>
<DepartmentID>1</DepartmentID>
<Name>Engineering</Name>
<GroupName>Research and Development</GroupName>
<ModifiedDate>2002-06-01T00:00:00</ModifiedDate>
</Department>
</HumanResources>
Source: http://msdn.microsoft.com/en-us/library/ms178107.aspx
