SQL Journey: Blog #13

I just would like to pass by and state that:

WHERE comes first before GROUP BY

while

GROUP BY comes first before HAVING.

We cannot use WHERE if the condition is an AGGREGATE function i.e. COUNT, AVG, MAX. In these cases, we use HAVING.

Also, when using CASE statement, bear in mind that the result of a case statement is ANOTHER COLUMN. In other words, it is like creating a new column in MS Excel while using an IF-ELSE statement to populate the contents of that column.

Since it is another column, it is written as one of the columns that needs to be on the output of the select statement. For example,

Notice that we put a comma before we used the case statement and we named the column output of the case statement as IncreasedSalary.

Here is also a trick when one wants to delete data in the table. Deleting values in a table is a very crucial thing that must be taken with caution because one misstep and one can delete the whole table. So to do away with this and ensure that you are deleting the correct row in the table, we use SELECT statement first and then use the DELETE statement next.

Next, erase the "*" symbol indicating all columns and then change SELECT to DELETE as shown below.

We also have the PARTITION BY statement which is a subclause of the the OVER clause as shown in lines 67 to 69.

We can also observe that the PARTITION BY subclause will create a new column in our output table which will be labelled as TotalGender and it will be arranged in such a manner that the Gender column is in ascending order since we partitioned by Gender.

It is like we are performing a GROUP BY AND ORDER BY clause.

The only things is now it is applied in every row which in a way informs us that Pam is a Female worker and there are two other female workers in the company working together with Pam. 

Another thing that we can notice in the PARTITION BY exercise is ALIASING by using "AS". By using aliasing, instead of writing [SQL Project 1].dbo.EmployeeDemographics which is quite long, we can now call it by using Demo. It is very important though that we name the table with a unique identifier for us to easily query on the appropriate table especially in cases where we are using big databases consisting of many tables.