In this article, we will try to understand the top 4 SQL interview questions for developers and frequently used functions that an interviewer can ask in the interviews. Below are the top 4 SQL interview questions for developers which we 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 the top 4 SQL interview questions for developers with practical examples and queries.
In order to understand these four functions, you can see that I have created a very simple table called 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 I would like 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 a 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 numbers like 1,2,3,4 and 5 for these orders. Now many people say just go ahead and create an identity column, but I would like to avoid the word identity column Probably I can say that I want to go and generate these numbers 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 each row. I can write a below query to achieve this unique number for each order placed.
If you can execute the above query it will generate one extra column on the fly and here you can see the order number.
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 in different contexts.
You can also 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 the Row Number function actually generates a unique number for each row.
So good till here,
Now what I would like to do is I would like to generate a unique number as per Vendor Name
In the table, you can see how this order number is actually on the complete row irrespective of Archies or 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 numbers 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.
Now her we can partition on vendor Name.
We have two partitions here Archies and Bata. Now we can start numbering as per the partition.
So the Syntax is the same as row number (). But now we want to go and generate a unique number based on Vendor Name.
Let us write a query to give the 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
- For Archies 1, 2 and 3
- For Bata 1 and 2
Here in the table below 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 the records and then we can apply a 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.
We have generated Vendor Order number partition by Vendor Name that works fine.
But now I would like to go and generate unique numbers as per customer. Here you can see Rajendra and Rajendra that are not correct right?. 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.
What I would like to see is the same customer number allocated to the same customer irrespective of Row Number. That’s 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.
What is the problem with a Rank function when there is a repeated data?
In Rank what happens, it again gives a unique number but if the data repeats right, 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 it 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 to 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 continuous 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 continuous numbers. So in Rank what happens it skips continuous numbers depending on how much-repeated data we have.
Now we understood Row Number, Partition, Rank and Dense Rank. These are the top 4 SQL interview questions for developers which are commonly asked during interviews.
Would you like to know more about SQL functions? You can check here.
I hope you enjoyed this article’s top 4 SQL interview questions for developers asked in the interviews. If you feel it is really useful please leave your comments in the comment section.
Thank You. Have a good day.