Very Famous SQL Interview Question | Department Highest Salary

Рет қаралды 23,643

2 жыл бұрын

𝐖𝐚𝐧𝐭 𝐭𝐨 𝐌𝐚𝐬𝐭𝐞𝐫 𝐒𝐐𝐋? 𝐋𝐞𝐚𝐫𝐧 𝐒𝐐𝐋 𝐭𝐡𝐞 𝐫𝐢𝐠𝐡𝐭 𝐰𝐚𝐲 𝐭𝐡𝐫𝐨𝐮𝐠𝐡 𝐭𝐡𝐞 𝐦𝐨𝐬𝐭 𝐬𝐨𝐮𝐠𝐡𝐭 𝐚𝐟𝐭𝐞𝐫 𝐜𝐨𝐮𝐫𝐬𝐞 - 𝐒𝐐𝐋 𝐂𝐡𝐚𝐦𝐩𝐢𝐨𝐧𝐬 𝐏𝐫𝐨𝐠𝐫𝐚𝐦 𝐛𝐲 𝐒𝐮𝐦𝐢𝐭 𝐒𝐢𝐫!
"𝐀 8 𝐰𝐞𝐞𝐤 𝐏𝐫𝐨𝐠𝐫𝐚𝐦 𝐝𝐞𝐬𝐢𝐠𝐧𝐞𝐝 𝐭𝐨 𝐡𝐞𝐥𝐩 𝐲𝐨𝐮 𝐜𝐫𝐚𝐜𝐤 𝐭𝐡𝐞 𝐢𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰𝐬 𝐨𝐟 𝐭𝐨𝐩 𝐩𝐫𝐨𝐝𝐮𝐜𝐭 𝐛𝐚𝐬𝐞𝐝 𝐜𝐨𝐦𝐩𝐚𝐧𝐢𝐞𝐬 𝐛𝐲 𝐝𝐞𝐯𝐞𝐥𝐨𝐩𝐢𝐧𝐠 𝐚 𝐭𝐡𝐨𝐮𝐠𝐡𝐭 𝐩𝐫𝐨𝐜𝐞𝐬𝐬 𝐚𝐧𝐝 𝐚𝐧 𝐚𝐩𝐩𝐫𝐨𝐚𝐜𝐡 𝐭𝐨 𝐬𝐨𝐥𝐯𝐞 𝐚𝐧 𝐮𝐧𝐬𝐞𝐞𝐧 𝐏𝐫𝐨𝐛𝐥𝐞𝐦."
𝐇𝐞𝐫𝐞 𝐢𝐬 𝐡𝐨𝐰 𝐲𝐨𝐮 𝐜𝐚𝐧 𝐫𝐞𝐠𝐢𝐬𝐭𝐞𝐫 𝐟𝐨𝐫 𝐭𝐡𝐞 𝐏𝐫𝐨𝐠𝐫𝐚𝐦 -
𝐑𝐞𝐠𝐢𝐬𝐭𝐫𝐚𝐭𝐢𝐨𝐧 𝐋𝐢𝐧𝐤 (𝐂𝐨𝐮𝐫𝐬𝐞 𝐀𝐜𝐜𝐞𝐬𝐬 𝐟𝐫𝐨𝐦 𝐈𝐧𝐝𝐢𝐚) : rzp.io/l/SQLINR
𝐑𝐞𝐠𝐢𝐬𝐭𝐫𝐚𝐭𝐢𝐨𝐧 𝐋𝐢𝐧𝐤 (𝐂𝐨𝐮𝐫𝐬𝐞 𝐀𝐜𝐜𝐞𝐬𝐬 𝐟𝐫𝐨𝐦 𝐨𝐮𝐭𝐬𝐢𝐝𝐞 𝐈𝐧𝐝𝐢𝐚) : rzp.io/l/SQLUSD
𝐖𝐚𝐧𝐭 𝐭𝐨 𝐥𝐞𝐚𝐫𝐧 𝐁𝐢𝐠 𝐃𝐚𝐭𝐚 𝐛𝐲 𝐒𝐮𝐦𝐢𝐭 𝐒𝐢𝐫?
𝐜𝐡𝐞𝐜𝐤𝐨𝐮𝐭 𝐭𝐡𝐞 𝐛𝐢𝐠 𝐝𝐚𝐭𝐚 𝐜𝐨𝐮𝐫𝐬𝐞 𝐝𝐞𝐭𝐚𝐢𝐥𝐬
𝐖𝐞𝐛𝐬𝐢𝐭𝐞 : trendytech.in/?src=youtube&su...
I have trained over 20,000+ professionals in the field of Data Engineering in the last 5 years.
Very Famous SQL Interview Question | Department Highest Salary
In this video we will solve Problem number 184 on LeetCode
Department Highest Salary
This is a medium complexity SQL Interview Question
Do Like, Comment & Subscribe ..
𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
🔅Sumit LinkedIn - / bigdatabysumit
🔅Sumit Instagram - / bigdatabysumit
In this video, we will solve SQL question on LeetCode. LeetCode is an excellent platform for practicing SQL Queries. The SQL Questions on LeetCode are framed pretty similar to how they are asked on SQL Interviews hence solving SQL questions on LeetCode can give you a good hands on experience on solving real world SQL questions.
#sql #interview #leetcode

Пікірлер: 35

@sumitmittal07 Жыл бұрын

Checkout the Big Data course details here: trendytech.in/?referrer=youtube_lc5

@sonumahto1364 Жыл бұрын

select Department, Employee, Salary from ( select d.name as Department,e.name as Employee,e.salary, rank() over (partition by departmentid order by salary desc) rnk from Employee e join department d on e.departmentid=d.id ) x where x.rnk=1 It is more optimised

@himanchalchandra6202 Ай бұрын

use dense_rank over rank as it will allow more than 1 highest salary in each dept

@rokibhasan5184 4 ай бұрын

Finished the whole playlist. Really helpful. I've learned How to approach any SQL problem.

@sathiyanr3992 Жыл бұрын

More Useful Sir. Kindly upload these kinds of problems continuously.

@subhajitroy5850 2 жыл бұрын

Please bring few more problem statements showing the usage of full outer join & Cartesian product as well

@arunsundar3739 3 ай бұрын

'where' clause with more than one column, along with 'in' clause is a combination i am learning first time, very interesting, gaining more confidence in sql questions now :)

@RITURAJRANJAN-st7dv 2 ай бұрын

Thanks for the wonderful solution Sir. I have tried to solve in a different way. select Department,Employee,salary from (select Department,Employee,salary,dense_rank() over(partition by Department order by salary desc) as denserank from (select e.name as Employee,salary,d.name as Department from Employee e join Department d on e.departmentId = d.id) temp) temp1 where denserank=1;

@angelnadar6451 2 жыл бұрын

Thanks @ helpful !!!

@sumitmittal07 2 жыл бұрын

Glad that it was helpfull

@florincopaci6821 2 жыл бұрын

Thank you for all the videos! can you please please do a video about leetcode sql question 2153-number of passengers in each bus 2?Please.Thank you

@venkataram6460 2 жыл бұрын

Please upload more videos to this playlist

@ashitosh501 2 жыл бұрын

sir waiting for next session

@sumitmittal07 2 жыл бұрын

Very soon

@faizraina9917 Жыл бұрын

why are we using department id in sub-query . why do we need to include it , can't we just use max(salary) only

@razeenahmed7334 Жыл бұрын

No, we can't use only max(salary) in the sub-query, because that will return only one maximum salary, but here we want to retrieve max salaries for both of the departments!

@dikshagupta2795 2 жыл бұрын

Hello Sir, If we are using sql server then what will be the alternative for "WHERE (departmentId, salary)" as we can't use two columns in WHERE in sql server

@AnweshDash 2 жыл бұрын

use window functions

@103himajapoluri6 Жыл бұрын

Can anybody help with identifying what is wrong in this query select d.name as Department,e1.name as Empolyee ,max(salary) as Salary from employee e1,department d where e1.departmentId = d.id group by d.name ;

@ShivaKumar-dj8bj Жыл бұрын

This will not work because here in each department for each employee there will be only one row for salary...your query gives result department, employee name and the highest salary of him, here there is only one row so basically no aggregation will happen considering there is only one row for each employee in each department. the logic is you need to first partition the data department wise then order the data by salary in descending order for each department and get the first row in each partitioned data set. you can use either Row number or dense rank to achieve this....check once.

@adnanmulla6640 6 ай бұрын

Alternate way: select Department,Employee,Salary from (select d.name as Department, e.name as Employee, dense_rank() over (partition by d.name order by salary desc) as Sal, Salary from Employee e inner join Department d on e.departmentId=d.id)x where x.Sal=1

@lucifieramit1 2 жыл бұрын

select temp.name as Department,temp.employeename as Employee ,temp.salary as Salary from (select d.name,e.name as employeename,salary,dense_rank() over ( partition by d.name order by salary desc ) as dr from employee e join department d on e.departmentId=d.id) temp where temp.dr=1

@thomsondcruz5456 2 жыл бұрын

Window functions are not always cost effective

@sukanyaiyer2671 2 жыл бұрын

pls solve prob 627

@sumitmittal07 2 жыл бұрын

Noted :) will cover that in the upcoming sessions

@sukanyaiyer2671 2 жыл бұрын

@@sumitmittal07 thank you sir

@lucifieramit1 2 жыл бұрын

@@sukanyaiyer2671 you need to use update with case statement in that problem

@slyroy7562 2 жыл бұрын

Select * from (Select employee.department, employee.name employee, salary, dense_rank() over(partition by employee.department order by salary desc) as r from employee, department where employee. Id = department. Id) where r=1; Will this works?

@sukanyaiyer2671 2 жыл бұрын

Yes. It works

@dikshagupta2795 2 жыл бұрын

There are some changes I have made and after that the query gives expected results: select Department, Employee, Salary from ( Select Department.name as Department, employee.name as Employee, Salary, dense_rank() over(partition by employee.departmentId order by salary desc) as r from employee join Department on employee.departmentId = Department.id ) as r where r = 1

@slyroy7562 2 жыл бұрын

@@dikshagupta2795 Thanks

@karthikbs8457 2 жыл бұрын

Sir I saw all the previous 5 video. This problem I paused and solved the problem on my own. Thanks. But I used Partition method involving 3 select statements. SELECT temp1.Department,temp1.name as Employee,temp1.Salary FROM( SELECT *, RANK() OVER(PARTITION by temp.departmentId ORDER BY temp.Salary DESC) AS rnk FROM( SELECT e.*,d.name as "Department" FROM Employee e INNER JOIN Department d ON e.departmentId=d.id) temp ) temp1 WHERE temp1.rnk=1;

@ShivaKumar-dj8bj Жыл бұрын

for this highest salary rank will work but if you want to get nth highest salary you need to use dense rank...correct me if I'm wrong

@Foodie_Cooking_Loverz Жыл бұрын

Looks complicated querry

@vishwamgupta1329 2 жыл бұрын

WITH Solution as (SELECT Employee.name as Employee, Employee.salary as Sal ,Department.name as Department, DENSE_RANK() OVER(PARTITION BY Department.name ORDER BY Employee.salary DESC) as re FROM Employee INNER JOIN Department ON Employee.departmentID=Department.id) SELECT Department, Employee ,Sal as Salary FROM Solution WHERE re=1; #This one also worked using Dense rank window function