தமிழில் - Departments Highest Salary | Advanced SQL Interview Questions | Data SQL | FAANG Interview

Video 364: This is the 38th video of the SQL Interview Question series.

00:00 - Introduction to dataset and Question
02:35 - Approach 1: CTE (Common Table Expression) Approach
06:45 - Approach 2: Subquery Approach
07:50 - Approach 3: Correlated Subquery Approach
10:10 - Approach 4: Concatenated Values Approach
12:50 - Approach 5: Window Function Approach
17:00 - Conclusion

We are given two tables, Employee and Department

The Employee table has ID, name, and salary of an employee. It also contains the ID of their department.
The Department table has ID of a department and its name

We are asked to find employees who have the highest salary in each of the departments.

In this video, we dive into an essential SQL problem: how to find employees with the highest salary in each department.

** Approach 1: CTE Table Approach **
We'll start with using a Common Table Expression (CTE) to first calculate the maximum salary in each department and then join this result back to the main Employee table.

** Approach 2: Subquery Table Approach **
Next, we'll explore a similar solution using a subquery table to derive the maximum salary per department, showcasing how this approach compares to the CTE method.

** Approach 3: Correlated Subquery Approach **
Here, we leverage a correlated subquery to fetch the maximum salary directly within the main query, offering a more concise solution with a powerful SQL technique.

** Approach 4: Concatenated Values Approach **
This method combines department IDs and salaries in a way that allows for a simple yet effective solution to find the highest salary in each department.

** Approach 5: Window Function Approach **
Finally, we'll use a window function to rank salaries within each department, filtering out the top earners with a Dense Rank function. This approach is particularly useful for handling ties in salary.

For a comprehensive understanding of these SQL methodologies and their application, please refer to this explanatory video.

code: https://github.com/jeganpillai/adv_sq...

Follow me on,
Website : https://growwithdata.co/
YouTube :    / @growwithdata  
TikTok :   / growwithdata  
LinkedIn :   / growwithdata  
Facebook :   / growwithdata.co  
FB Group : facebook.com/groups/datainterviewpreparation
twitter :   / growwithdata_co  
Instagram :   / growwithdata.co  
WhatsApp : https://whatsapp.com/channel/0029VaF8...
TheWide : https://thewide.com/profile/891

#sql #dataengineers #tablejoins #ceil #floor #bucket #meta #google #facebook #apple #paypal #netflix #amazon #deinterview #sqlinterview #interviewquestions #leetcode #faang #maanga #mysql #oracle #dbms #query #sqlserver #mysql #coderpad #aggregates #aggregation #nonaggregation #database #placementpreparation #lead #lag #windowsfunction #nullcheck #coalesce #sqlperformance #ifnull #case #lead #lag #windowsfunction #tamil #tamilpython #tamilinterview #tamilinterviewlatest #tamilinterviewquestions #sqlintamil