SQL Interview questions | Data Analyst | Part - 1

this video is going to be the first part of our Series where I'll try to cover most commonly asked SQL questions in any job interview now with that let's get started with the first question okay so the first question says we have to write an SQL query to find the nth highest salary of the employee from the given table employees so on the left hand side you can see we have a table employees with three columns ID name and salary and based on the third column which is a salary column we have to find the nth highest salary of the employee now it could be the second highest third highest or fifth highest based on the interviewer to solve this query I will be using MySQL workp so I'll be solving this in MySQL and I have already created my database I have uh activated the database I have created the table as well with three columns and all the values are inserted as given into the given table okay so don't worry about these commands I've already provide them into the video description if you want to try it on your own you can just copy these commands and try it on your own in solving the problem okay so let us first see the values inside my table so if I just use the simple command select star from employees okay it will show me the output of all salaries now whenever you want to find the N highest salary it is always better to order the salary column so that you understand what we are talking about okay now I have uh ordered this in descending order so the top highest salary would be on the top and the lowest salary would be on the bottom okay now we can better see the table now let us assume your interviewer asked to find the second highest salary from this table okay so the second highest salary would be of Sage which is 90,000 salary okay so in order to do that I'll just first type in this command now I can use limit command for that now I'll explain you what is limit command so limit command limits the number of values which are need to be shown in the output so if I use limit three it will limit the values to three if I use limit two it will limit the values to two and it is limiting the values from the top okay so it will start from the top moan and from the and it will go for two values whenever I use limit command I have the option to use offset command with the limit command in my SQL so for that what is that is uh I can offset the count from which the limit is counting so if limit is uh to show only two values and if I offset it by one it will skip the first row and then it will count from the next row so it will show me the output of sish and the next employee below sish okay so it's showing me SES and hea you understood if I do offset two it will skip somes as well and it will show me hea and Vina so that's how limit and offset works now you understood that uh if you want to find the second highest salary we can simply do a limit of one because I need to Output only one row and I need to offset by one because I want to skip the row with the highest salary which is on the top and I want to show the next uh row so the next row would be of the SES which is the second highest salary now if you want to find the third highest salary I'll do it offset of two and it will give me the third highest salary okay so now you understood how we solve this but this is not a complete solution remember let's say I want to find the sixth highest salary okay I'll show you first what was the original table and if in this table we see what is the sixth highest salary it's uh so first one is moan SES hea and then Vin and Prashant are fourth highest salary they are not the separate because they have duplicate salary values okay and then the fifth one is shangi and sixth one is PR so if I use this command to find the sixth highest salary by offsetting it by five I want to skip the five rows it will show me shivangi and not Priya and and why is that so because whenever offset counts the number of rows the command doesn't know if the salary is duplicate or not so it will count the row of v and Prashant as two separate roles but we don't want to do that okay so that is why this is not a complete solution and do not provide this solution in your interview instead you have to add one more thing to uh this solution which is using distinct keyword so I can use distinct keyword on the salary column com s and if I just show you the output of this query what it will do is it will find the distinct values or the unique values in my Sal salary column okay so now if you see the salary with the 70,000 uh Rupees is now uh just uh like duplicate values of this uh 70,000 are removed and only one value of this salary is shown now I can use my offset command on this particular column to give me the output which is 40,000 and which is correct because priia had the salary of 40,000 which is the sixth highest salary so this is the solution that you need to provide so you need to use distinct to avoid the duplicate salary values and then you can use the limit and offset values to find the sixth highest salary okay now let's say the interviewer says that you don't have to use limit and offset command but you have to use rank function to solve the same okay so for this I can write first the same command here okay now I can add one more column to it which is dense rank over which column so it's a dense rank function that I'm using over a column which is salary now I need to arrange this column order by salary in descending order so that uh the function knows that the rank should be provided into descending order and not in ascending order okay now if I run this command you see okay I can provide a temporary name to this column as best rnk now if you see we have another command which have rank based on the salary now you see the person with the highest salary has been given rank one the second highest salary has been given rank and so on and if you see the person with the fourth highest salary is been given rank four and for both V and Prashant because both values are similar okay and now you are have similarly for shivangi and prya now one alternate that you can see here is you can use rank instead of dense rank but there's an issue with the rank command because when you have the D when you have the rank for the fourth highest salary if you have duplicate values it will provide the rank of six to the next row okay so if you had three duplicate values it will provide 4 44 and the next rank would be of seven so that's not what we want okay so always remember to use dense rank over instead of rank over function here okay so this will give you the correct column values now I can just use my wear Clause with this column and I can select for the fourth highest salary where this column value is four simple right now if you see I use the we Clause here it will not work it will give me an error and why is that so whenever we use rank over dense rank over Su over or row number over these type of functions we can't use their values directly in the same query we have to use another query to fetch those values okay so there are two ways to do it okay so method one is to use subqueries so what I'll do is I'll just copy this command here and put this command into a from clause and I'll use another query which will be just select star from this table that we have created using our this query okay so now this is a subquery inside this main query and now I can provide this table name as table one and now I can now I can uh use my V clause on my rank function and I can just simply say where the dense rank is equal to four I want that row okay so I need to run this command so now you see it's giving me the fourth highest salary of both Vin and Prashant as the same salary values okay so in these cases you have to use subqueries and there is another way also that you can use for the same uh solution which is to use a CTE expression so a CT is nothing but a Common Table expression and then you can just put this query from here and put it in this table okay and now you can use this name as your table now I can just use it in my query select star from CTE where dense rank equal 4 simple so now you can see the same output so both the methods are similar you are just using this query and it's output to another query okay so this is a subquery and its output is used in a main query here and you are using this query and then you are using its output to be stored into another table which is a CTE you can also use any name that you want to use and then you can just change the name here as well and then it will give you the same output okay now the only difference is that with this command you can use this table name now in multiple times in the same query while here you count this query multiple times because it's a written query in a subquery format okay so this is how you solve these questions in your interview and just to summarize in the first part I discussed how you can solve this problem using limit and offset command and how you have have to use distinct on your salary column because of the repeated salary values okay so that's the correct solution and that's not a solution that you should use okay and in the second part when we have to use a ranko function just remember to use dance rank instead of rank over function and we saw how uh it changes the output and then we can use this query as a subquery or as a common table expression okay before ending this video I would like to request if you have been into some situation where you have given some SQL based interview or know some questions which are asked in any job interview then please do write them in the comment section below and I'll try to make a separate video on that question so that we can help the community together and thank you for watching

Channel: The ML Mine

Views: 2,595

Keywords: sql questions, sql interview questions for freshers, sql, Rank function, CTE, Common table expression, limit, offset, mysql, mysql workbench, solution, step-by-step, data analyst, data scientist, business analyst, senior business analyst, sql interview, sql interview questions for beginners, sql interview questions and answers, data scientist interview, data analyst interview, business analyst interview

Id: b8r3Ep8oVDM

Length: 11min 56sec (716 seconds)

Published: Sun Jun 02 2024