SQL Knowledge ALL Beginners Should Know

As you start your journey to become a competent SQL developer you are going to be overwhelmed with terminology like dml, ddl, stored procedures, views, normalization, and window functions. However, one simple and often overlooked aspect of SQL server that you should be aware of before you make the leap from beginner to intermediate, is having an understanding of how the database of your choice reads Sql queries. For example, as a Sql Server guy, I can tell you that SQL Server reads the query below:

Select top 1000 count(EmpName) as Total_Names_Starts_With_J, EmpName from Employees
where left(empname,1) = 'J'
group by EmpName
having count(EmpName) > 1
Order by Total_Names_Starts_With_J

Like this:

1. FROM Employees
2. WHERE EmpName left(empname,1) = 'J'
3. GROUP BY EmpName
4. WITH CUBE or WITH ROLLUP
5. HAVING count(EmpName) > 1
6. SELECT count(EmpName) as Total_Names_Starts_With_J, EmpName
7. ORDER BY Total_Names_Starts_With_J
8. TOP 1000

All this query does is find the top 1000 employees whose name starts with J and belongs to more than 1 employee. However, your focus should be on how the query is written in conjunction with how SQL server processes it. One key element of the query structure which stands out is the Order by clause. In the Order by clause we refer to our count function by using the column alias Total_Names_Starts_With_J. If we tried to refer to our count function by the alias in any other clause the compiler will fail, because the compiler does not see the column alias until after the Select statement is processed.

This is a simple aspect of database development that can vastly improve your skill set. Having a strong grasp of how queries are compiled is a huge advantage for all developers. It gives you better insight on how to structure your queries, avoid, errors, and increase productivity.