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.

Comments

Privacy Policy of ShinStats: descriptives calc

Privacy Policy Shin Nix built the ShinStats app as an Ad Supported app. This SERVICE is provided by Shin Nix at no cost and is intended for use as is. This page is used to inform visitors regarding my policies with the collection, use, and disclosure of Personal Information if anyone decided to use my Service. If you choose to use my Service, then you agree to the collection and use of information in relation to this policy. The Personal Information that I collect is used for providing and improving the Service. I will not use or share your information with anyone except as described in this Privacy Policy. The terms used in this Privacy Policy have the same meanings as in our Terms and Conditions, which are accessible at ShinStats unless otherwise defined in this Privacy Policy. Information Collection and Use For a better experience, while using our Service, I may require you to provide us with certain personally identifiable information. The information that I request will be retaine...

Power BI Journey: Blog #6

In this lesson, the focus is on "Conditional Formatting" which is very much similar to the conditional formatting in MS Excel which I could relate to following the making of the joint reporting system for personnel during my second job. Basically, we click the data columns that we want to be displayed in a tabular visualization. Next, we select the columns that we will be applying conditional formatting to > Right-click on that column > Select Conditional Formatting > Select from among the options which is more appropriate for your application In this particular exercise, we utilized Background conditional formatting using gradient (applied on the first column of the first table) and rules (IF ELSE which was applied on the second column of the first table), Icons conditional formatting (applied on the second column of the first table), Data bars conditional formatting (applied on the second column of the first table and the fourth column of the second table). In the...

SQL Journey: Blog #3

I have reached the end of lesson 2. The final project is entitled "Data dig" where we are given a set of interesting data sets: NASA astronauts , Superbowl results , Pokemon stats , NBA players , Top movies , Top countries by population , Solar system objects by size , Marvel characters , Furniture store sales , Earned KA badges , Winston's donut logs , Card game results , and NFL draft picks . We are to pick one of those data sets and use advanced SELECT queries to discover things about the data. What sort of questions might one have about that data, like if they were using it for an app or a business idea? Here are some ideas:What are average, max, and min values in the data? What about those numbers per category in the data (using HAVING)? What ways are there to group the data values that don’t exist yet (using CASE)? What interesting ways are there to filter the data (using AND/OR)? Basically, the above given questions will serve as a guide on what we could dig from ...

Shin Nix

Search This Blog