dev certification, sql_server

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:

Query 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:

Query Output

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 element for row.

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

dev certification, sql_server

I believe I had a considerable amount of experience with SQL Server throughout my career. Recently I decided to get SQL Server MSCA title which requires taking 3 exams (70-461, 70-462 and 70-463 namely). I think it’s a good way of polishing existing knowledge and learning new stuff. Hands on experience is great but generally you only learn enough to get things done, which is the desired result of course but when studying for certification you have to cover all details. Some of them may be quite trivial and don’t have much practical usage but I think they all may come in handy sometime so it doesn’t hurt to learn more.

On to 70-461

The number of questions vary between 40 – 60 and test duration is between 2 – 3 hours. In my exam, I had 44 questions and the time given was 2 hours.

Total score is 1000 and the passing score is 700. I’ve got a whopping 906. Actually at the end of the day doesn’t really matter if you get a full 1000 but it feels better to pass with a high mark.

Study Notes

I decided to release my notes on a separate blog post as it became very lengthy very quickly.

Study Materials

70-461 Training Kit

I used several different sources preparing for the exam. One of the most important ones is the official Training Kit. Also, I found the CBT Nuggets video series quite useful. You can move at a much faster pace to cover the topics with a video. So I recommend those videos (despite the steep price of CBT Nuggets subscription)

There is another series in TrainSignal but I haven’t tried it.Finally I’ve found a short introductory video (around 1h 15m) to the exam. You can find the find link in the resorces section. It’s a very fast-paced introduction mainly underlining the exam topics that are not commonly used in real life.

Resources

hobby electronics

Playing with Gadgeteer fuelled my enthusiasm so I dusted off my solderless breadboard and basic electronics components that came with the basic kit I bought a while ago.

Electronics

I don’t know what most of those things are but I decided to start with a simple circuit that lights an LED. After burning a few LEDs I learned that it would be a good idea to use a resistor in the circuit in order not to fry the LED. Reading resistors turned out to be a pain though! They are colour-coded and you have to know the value of each colour. I found here a nice calculator to free myself from that unnecessary waste of time: http://www.csgnetwork.com/resistcolcalc.html

Yet there was another problem. All the sources I had found were talking about 3 bands. But my resistors had 5 colour bands! After spending some more time I learned that there is also a 5-colour version of resistors. Here is the calculator for the 5-band version: http://www.diyalarmforum.com/5-band-resistor-calc

The problem is they are so tiny that it’s not always easy to distinguish the colours. Anyway, I decided to pick one and hope for the best.

The next challenge is using the breadboard. Even though it’s meant to make life easier for circuit builders there’s a still a few things to learn about it which are not very intuitive. I found a nice video on Youtube to learn the basics: http://www.youtube.com/watch?v=k9jcHB9tWko

After playing around a little bit a finally managed to light a LED which was nice but didn’t feel like much of an accomplishment. It required too much time to achieve something so trivial.

Electronics

I think I’ll need various components to build something significant. The kit I have looks very limited. But before I invest more money into this I think I’ll keep playing with more high-level products like Gadgeteer, Netduino and Arduino. If I can incorporate breadboard and lower-level components into systems using those that would a bonus but without such powerful controllers I don’t think I’ll get satisfying results with circuits built on a solderless breadboard.