I registered and scheduled my 70-461 exam today. I was able to cover a few topics today from the Querying SQL Server 2012 Training Kit . I spent most of my study time watching some exam prep videos from Youtube just to get an idea of what the exam will be like. One of the videos was the one below by TechEd North America. This video was actually in the Microsoft 70-461 Exam page also.
It was very similar to the video below by Microsoft Learning (published in 2013).
They both give a nice overview of the topics that will be covered in the exam and gave me some tips on what areas I need to focus on for my review.
Wednesday, July 29, 2015
Monday, July 27, 2015
Logical Query Processing Order
One of the interesting things that I have learned during my 70-461 review is the Logical Query Processing order. I have been writing 'SELECT' statements for a while but it's only now that I really understood the order in how the statement is actually interpreted. So, a typical SELECT statement goes like this:
Before, I thought the main query clauses were processed in the order they were entered (also known as the "keyed-in order"):
1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY
No wonder I used to get the occasional
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
Whoa! Wait, so the SELECT statement is actually processed as the last (if no ORDER BY clause is present)?? Yes! I actually asked the same thing. Each of the steps above (phases) operates on one or more tables as inputs and the result is a virtual table that becomes that input of the next step. It's almost similar to method chaining. That explains why we get the Invalid column name error if for example, we used an alias defined in the SELECT clause in the WHERE clause. This is because the WHERE clause is actually evaluated before the SELECT clause, so it doesn't know anything about the alias yet.
SELECT country, YEAR(hiredate) AS yearhired, COUNT(*) AS numemployees
FROM HR.Employees
WHERE hiredate >= '20030101'
GROUP BY country, YEAR(hiredate)
HAVING COUNT(*) > 1
ORDER BY country, yearhired DESC;
Before, I thought the main query clauses were processed in the order they were entered (also known as the "keyed-in order"):
1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY
No wonder I used to get the occasional
Invalid column name '<insert your bad column name here>'. That's because I didn't understand the logical query processing. The conceptual interpretation order actually goes like this:1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
Whoa! Wait, so the SELECT statement is actually processed as the last (if no ORDER BY clause is present)?? Yes! I actually asked the same thing. Each of the steps above (phases) operates on one or more tables as inputs and the result is a virtual table that becomes that input of the next step. It's almost similar to method chaining. That explains why we get the Invalid column name error if for example, we used an alias defined in the SELECT clause in the WHERE clause. This is because the WHERE clause is actually evaluated before the SELECT clause, so it doesn't know anything about the alias yet.
Friday, July 24, 2015
Ready, Set...GO!
My name is Noel. I created this blog to document and blog my way to becoming a DBA. I'm currently a Web developer. I develop primarily in Java (GWT Framework) for my current job (Well currently, I'm assigned to work on a .NET project, so I'm currently developing in C#), and I also enjoy doing front-end development and web designing during my spare time (HTML/CSS/jQuery). Check out my past works in my portfolio page at nre4ma.com.
In my 8 years of development, I have experience in developing front-end, server-side, and database. In other words I've already seen and worked on the full-stack. So what has made me think about becoming a DBA? I have no straight answer, but I guess it started about 5-6 years ago when I first started writing SQL scripts (Oracle DB) as part of my job function. I didn't have a clue about SQL, so I basically learned it while doing the job. I was fascinated at how you can manipulate the data and the different ways of accessing it. Since then I've been kind of obsessed about data. I remember I used to write little programs/web apps that does inventory, or some kind of record keeping apps back then for myself using free DBs like MySQL.
Now, at this point in my career, I really feel like it's about the right time to really take the plunge and become a full pledge DBA. I have to be honest, but the salary that a DBA job demands is also a huge part of the reason why I want to become one. I still love being a front-end developer but I'm looking for some new challenges.
So hopefully, this blog will allow me to have a deeper understanding in the many topics that I will encounter along the way and also share my knowledge to the world and other people like me who have started their journey to become a DBA. And also, I will use this as a reference/reviewer for my certification tests.
I have decided to take the SQL Server (Microsoft) route. Basically, just because there is a higher demand for in the job market where I live. And also, their site and certification seems more organized compared to Oracle.
Right now, I'm reading the Querying Microsoft SQL Server 2012 Training Kit. I'm only in the early chapters but in my next few posts, Hopefully after a couple of months I will be ready to take the Exam 70-461. I will be sharing some topics and try to teach some of the stuff that I learned from the book.
In my 8 years of development, I have experience in developing front-end, server-side, and database. In other words I've already seen and worked on the full-stack. So what has made me think about becoming a DBA? I have no straight answer, but I guess it started about 5-6 years ago when I first started writing SQL scripts (Oracle DB) as part of my job function. I didn't have a clue about SQL, so I basically learned it while doing the job. I was fascinated at how you can manipulate the data and the different ways of accessing it. Since then I've been kind of obsessed about data. I remember I used to write little programs/web apps that does inventory, or some kind of record keeping apps back then for myself using free DBs like MySQL.
Now, at this point in my career, I really feel like it's about the right time to really take the plunge and become a full pledge DBA. I have to be honest, but the salary that a DBA job demands is also a huge part of the reason why I want to become one. I still love being a front-end developer but I'm looking for some new challenges.
So hopefully, this blog will allow me to have a deeper understanding in the many topics that I will encounter along the way and also share my knowledge to the world and other people like me who have started their journey to become a DBA. And also, I will use this as a reference/reviewer for my certification tests.
I have decided to take the SQL Server (Microsoft) route. Basically, just because there is a higher demand for in the job market where I live. And also, their site and certification seems more organized compared to Oracle.
Right now, I'm reading the Querying Microsoft SQL Server 2012 Training Kit. I'm only in the early chapters but in my next few posts, Hopefully after a couple of months I will be ready to take the Exam 70-461. I will be sharing some topics and try to teach some of the stuff that I learned from the book.
Subscribe to:
Posts (Atom)