In this article, we will try to understand the top 6 SQL queries and frequently used functions which an interviewer can ask in the interviews. Below are the top 4 functions which you must answer when the interviewer asked.
- Row Number
- Dense Rank
If you can completely read this article, you will be able to understand the most important SQL functions and frequently asked top 6 SQL queries during interviews.
In order to understand these four functions, you can see that I have created a very simple table called as the Sales Table and the sale table has four columns Customer Name, Product Name, Amount and Vendor Name.
You can see some records here
- Shiv has bought Shoes from Bata
- Raju has bought Bags from Bata
- Sukesh has bought Perfume from Archies
- Rajendra has bought Books and Pens from Archies etc.
Now the data over here is very nice what I would like to do is to go and generate a unique order number for each one of these orders placed.
In other words, I would like to go and generate running number over here as given below.
- Shiv has bought Shoes from Bata which is Order Number 1
- Raju has bought Bags from Bata which is Order Number 2
- Sukesh has bought Perfume from Archies which is Order Number 3
- Rajendra has bought Books from Archies which is Order Number 4
- Rajendra has bought Pens from Archies which is Order Number 5
I would like to go and generate unique number 1,2,3,4 and 5. Now many people can say just go ahead and create an identity column but I would like to avoid the identity column and probably let us say that I want to generate these number on the fly.
In other words, I would like to generate these numbers while I do the select of the SQL and that’s where we can use the Row Number function.
The Row Number Function actually generates a unique number for row what I can do here is I can go and write the query like this.
If you can execute the above query it will generate one extra column on the fly and here as an order number.
So, in other words, Row Number function helps you to create a unique number by using a simple select statement and then you can use this unique number.
You can use this unique number for page nation. I have seen a lot of developers using this unique number for page nation. So whatever the use it is but the Row Number function actually generates a unique number.
So good till here,
Now what I would like to do is I would like to generate a unique number as per Vendor Name
For example, here you can see how this order number is actually on the complete row. In other words irrespective it is Archies or it is Bata it is just numbering them 1,2,3,4 and 5.
Now what I would like to do is, I would like to say that if it is Bata then give the order number like 1 and 2. If it is Archies then start with new order number like 1, 2 and 3.
How do I go now and generate order number vendor wise? That’s where we can go and use partitions.
So what we can do is we can go and partition on vendor Name.
We have two partitions here Archies and Bata and then we can start numbering as per the partition.
So the Syntax is row number () exactly the same, we want to generate the unique number but we want to generate on Vendor Name.
So I would like to now give order number as per vendor
row_number () over (partition by Vendor Name Order By Vendor Name)
So our query would be something like this.
You can see over here Vendor Order Number has generated two partitions
Archies 1, 2 and 3
Bata 1 and 2
First Order Number is generic Order Number whereas Second Order Number is Vendor Order Number Partition by Vendor Name.
row_number () over (partition by vendor name order by vendor name)
So by using a partition, we can group records and then we can apply Row Number function on it.
The first column is Order Number 1,2,3,4 and 5. Now, look at the second column vendor order number for Archies it has given 1, 2 and 3. For Bata, it has started numbering them differently as 1 and 2.
So by using partition we can actually go and group records and then we can apply the Row Number function on it.
When I use row number it will just give unique values throughout the rows and second, when I use a Partition with the Row Number it will actually create groups and then give Row Numbers according to the groups.
Now we want to generate unique customer ID. We have generated Unique Order Number. All fine.
We have generated Vendor Order number partition by Vendor Name even that works fine.
But now I would like to go and generate unique numbers as per customer. In other words you can see Rajendra and Rajendra. I would like to generate a unique number for Rajendra.
If you can see the below table here it has generated 1 and 2 for Rajendra. which is not correct right?
What I would like to see is the same customer number allocated to the same customer irrespective of Row Number and that where we have the Rank Function.
rank () over (order by customer name) as customer order number
Now every customer name has assigned a unique number. In Rank what happens, it again gives a unique number but if the data repeats right it for the same kind of data it does not go and generate a unique number.
In other words for Sukesh, it has generated a unique number 5 and for Raju, it generated a unique number 3. For Rajendra, it generated a unique number but as soon as he found Rajendra again it did not generate a unique number.
So if there are repeated data right the Rank Function will use the same number for the repeated data.
Now there is a small problem here especially in Rank you can see over here
1 for Rajendra that is good and again 1 for Rajendra that is also good but where has the 2 gone but suddenly I have 3 for Raju and 4 for Shiv and 5 for Sukesh.
I would like see rank number 2 for Raju why he has skipped a value right?
I want him to use a second value and I want him to use a value in continues passion. that’s where we can use something called Dense Rank.
dense_rank () over (order by customer name) as customer order number
Now he is using continues numbers. So in Rank what happens it skips continuous numbers depending on how much-repeated data we have.
Now we understand Row Number, Partition, Rank and Dense Rank and we also covered top 6 SQL queries commonly asked during interviews.
I hope you enjoyed this article on top 6 SQL queries asked in the interviews. If you feel it is really useful please leave your comments in the comment section.
You can also check What is the difference between Union and Union ALL – Interview questions on SQL here.
Would you like to know more about SQL functions ? You can check here.
Thank You. Have a good day.