📚 Easy and Simple Four Solutions With Explanation 🚀 | Beginner-Friendly 🎓 | Beats 98.88% ⚡
🎯 Approach 1: Using WITH Clause and DENSE_RANK📌🖥️Code🧠Complexity Analysis

CTE Execution (WITH Clause):


The MaxSalaryCTE computes a DENSE_RANK() for each empl
#  🎯 Approach 1: Using WITH Clause and DENSE_RANK📌

#  🖥️Code
```mysql []
With MaxSalaryCTE AS
				   (
					select *,
						   dense_rank() Over(Partition By DepartmentId order by Salary Desc) RN
					from employee 
					)
Select 
      d.name Department,
      m.name Employee,
      m.salary Salary
From MaxSalaryCTE as m
Join Department as d
On m.DepartmentId =d.id
Where rn =1
```
#  🧠Complexity Analysis
- **CTE Execution (WITH Clause):**

  - The MaxSalaryCTE computes a DENSE_RANK() for each employee within their department, sorted by salary in descending order.

  - The CTE scans the entire employee table, which is **O(N)**, where N is the number of employees.

  - The DENSE_RANK() function assigns a rank to each row, ensuring that employees with the same salary receive the same rank.
- **Join Operation:**

   - After the CTE, a JOIN operation between MaxSalaryCTE (aliased as m) and Department (d) is performed based on DepartmentId.

   - This join requires scanning through **MaxSalaryCTE** and **Department** tables, with a time complexity of **O(N + M)**, where N is the number of rows in the CTE, and M is the number of rows in the Department table.

- **Filtering on RN = 1:**

   - After the join, the query filters only the rows where RN = 1 (i.e., the employee with the highest salary in each department).

   - The filtering is efficient, as it only involves a direct comparison of the RN value.
#  📈Overall 
- **Time Complexity**: 
   - **Worst Case: O(N + M)**(due to the scan of both MaxSalaryCTE and Department tables and the join operation)

   - **Best Case: O(N)** (if an index on DepartmentId or Salary is available, reducing the scan time)

time)


- **Space Complexity**: O(N)

#  🎯 Approach 2: Using WITH Clause and MAX() Aggregation📌

#  🖥️Code
```mysql []
WITH MaxSalaryCTE AS 
                    (
					 SELECT 
							DepartmentId,
							MAX(Salary) AS Salary
					 FROM employee 
					 GROUP BY DepartmentId
                     )
SELECT 
	  d.name AS Department,
	  e.name AS Employee, 
	  e.salary AS Salary
FROM MaxSalaryCTE AS m
JOIN employee AS e
ON m.Salary = e.salary AND m.DepartmentId = e.DepartmentId
JOIN Department AS d
ON m.DepartmentId = d.id;
```
#  🧠Complexity Analysis
- **CTE Execution (WITH Clause):**

  - The MaxSalaryCTE calculates the highest salary (MAX(Salary)) for each department by grouping rows based on DepartmentId.

  - This involves scanning the employee table and performing a group-by operation. The time complexity for this is O(N), where N is the number of employees.
- **Join Operations:**
  - After the CTE, the query performs **two joins**:

      - **Employee Join:** It joins MaxSalaryCTE with the employee table, matching both Salary and DepartmentId.

         - The join compares each row in the CTE with employees having the same department and highest salary.
         - The time complexity of this join is O(N + M), where N is the number of rows in the employee table and M is the number of rows in the CTE.
  - **Department Join:** The second join connects the MaxSalaryCTE with the Department table based on DepartmentId.

     - This is a straightforward join that requires scanning the Department table.

     - The time complexity of this join is **O(M)**, where M is the number of departments.




- **Filtering and Output:**

   - The filtering occurs implicitly through the join conditions. There's no additional filtering step after the joins, so it doesn't add to the time complexity.
#  📈Overall 
- **Time Complexity**: 
   - **Worst Case: O(N + M)** (due to scanning the employee and Department tables and performing the join operations)

   - **Best Case: O(N)** (if an index on DepartmentId or Salary is available, reducing lookup time)


- **Space Complexity**: O(N)

#  🎯 Approach 3: Using a co-related Subquery with MAX() to Find the Highest Salary📌

#  🖥️Code
```mysql []
SELECT 
    d.name AS Department,
    e.name AS Employee,
    e.salary AS Salary
FROM employee e
JOIN Department d 
ON e.DepartmentId = d.id
WHERE e.salary = (
				  SELECT MAX(Salary) 
				  FROM employee 
				  WHERE DepartmentId = e.DepartmentId
				 );
```
#  🧠Complexity Analysis
- **Subquery Execution:**

   - The subquery inside the WHERE clause calculates the maximum salary for each department.

  - For each employee in the employee table, the subquery runs once to get the maximum salary of the corresponding department.

  - This results in a nested loop structure, where for each employee, the subquery executes, scanning the employee table for matching DepartmentId.

   - The time complexity of the subquery is O(N) for each row in employee, leading to a total worst-case time complexity of O(N²) if no optimizations are in place.
- **Join Operation:**

   - After retrieving the maximum salary for each department, the query performs a join between the employee table (e) and the Department table (d) based on DepartmentId.

   - This join operation has a time complexity of O(N + M), where N is the number of employees, and M is the number of departments.

- **Filtering on MAX(Salary):**

   - The WHERE clause filters out only those employees whose salary matches the maximum salary for their respective department.

  - The filtering is efficient since it happens after the join and subquery evaluation, but it still requires checking each employee’s salary against the subquery result.
#  📈Overall 
- **Time Complexity**: 
   - **Worst Case: O(N²)** (if no index optimization is present, due to the subquery for each employee)

   - **Best Case: O(N log N)** (if an index on DepartmentId and Salary exists, reducing the subquery lookup time)


- **Space Complexity**: O(1) (the subquery only holds the result of MAX(Salary) for the current department, with no additional large data structures used)

#  🎯 Approach 4: Using EXISTS and HAVING to Find Employees with Maximum Salary📌

#  🖥️Code
```mysql []
SELECT 
    d.name AS Department,
    e.name AS Employee,
    e.salary AS Salary
FROM employee e
JOIN Department d 
ON e.DepartmentId = d.id
WHERE Exists
			(
			  Select
                    1 
			  from employee e1 
              where e1.departmentId=e.departmentId 
              Having Max(e1.Salary)=e.Salary
			)
```
#  🧠Complexity Analysis
- **Subquery Execution (EXISTS Clause):**

  - The EXISTS clause checks whether there is any row in the subquery where the maximum salary for the department (MAX(e1.Salary)) matches the salary of the current employee.

  - The EXISTS clause only returns TRUE or FALSE based on the existence of such rows, making it more efficient than returning actual results.

  - The subquery evaluates for each employee, checking if their salary is the highest in their department.
- **HAVING Clause:**
  - The HAVING clause filters groups by the maximum salary in the subquery, ensuring that only employees with the highest salary in their department are considered.

  - The use of HAVING after the aggregation (MAX(e1.Salary)) avoids unnecessary grouping in the outer query.
- **Join Operation:**

  - After filtering the employees through the EXISTS clause, the query performs a join between employee (e) and Department (d) based on DepartmentId.

  - This join operation has a time complexity of O(N + M), where N is the number of rows in the employee table and M is the number of rows in the Department table.

- **EXISTS Optimization:**

  - The EXISTS clause stops scanning once a matching row is found, which can optimize the execution compared to the subquery without EXISTS, especially when the number of employees in each department is small.

   - However, without an index on DepartmentId or Salary, the subquery can still require a full scan of the employee table for each employee in the outer query.
#  📈Overall 
- **Time Complexity**: 
   - **Worst Case: O(N²)** (if no index optimization is present, due to the nested subquery for each employee)

  - **Best Case: O(N + M)** (if an index exists on DepartmentId and Salary, the subquery is more efficient and only requires looking up the department for each employee)




- **Space Complexity**: O(1)
['MySQL']