Leetcode: SQL Interview Prep Guide

Q1. Employees Earning More Than Their Managers

Ans:

# Write your MySQL query statement below

SELECT EMP.name AS Employee FROM Employee EMP,Employee MGR
WHERE EMP.managerId=MGR.id AND EMP.salary>MGR.salary

Q2. Managers with at Least 5 Direct Reports

Ans:

# Method1 :

SELECT E1.name
FROM Employee E1
JOIN (
    SELECT managerId, COUNT(*) AS directReports
    FROM Employee
    GROUP BY managerId
    HAVING COUNT(*) >= 5
) E2 ON E1.id = E2.managerId;


# Method 2

SELECT e.name
FROM Employee AS e 
INNER JOIN Employee AS m ON e.id = m.managerId 
GROUP BY e.name
HAVING COUNT(m.managerId) >= 5;


# Method 3

SELECT name 
FROM Employee 
WHERE id IN (
    SELECT managerId 
    FROM Employee 
    GROUP BY managerId 
    HAVING COUNT(*) >= 5)

# Method 4

SELECT a.name 
FROM Employee a 
JOIN Employee b ON a.id = b.managerId 
GROUP BY b.managerId 
HAVING COUNT(*) >= 5

# Method 5
SELECT e1.name
FROM employee e1
LEFT JOIN employee e2 ON e1.id=e2.managerId
GROUP BY e1.id
HAVING COUNT(e2.name) >= 5;

### Initial `Employee` Table:

| id | name       | managerId |
|----|------------|-----------|
| 1  | Alice      | NULL      |
| 2  | Bob        | 1         |
| 3  | Charlie    | 1         |
| 4  | David      | 1         |
| 5  | Eve        | 1         |
| 6  | Fiona      | 1         |
| 7  | George     | 3         |
| 8  | Hannah     | 3         |

### Step 1: Result After Join Operation
This result shows the pairing of each manager with their direct reports. We're joining the `Employee` table with itself based on `e.id = m.managerId`.

**Resultant Table:**

| ManagerName | EmployeeName | managerId |
|-------------|--------------|-----------|
| Alice       | Bob          | 1         |
| Alice       | Charlie      | 1         |
| Alice       | David        | 1         |
| Alice       | Eve          | 1         |
| Alice       | Fiona        | 1         |
| Charlie     | George       | 3         |
| Charlie     | Hannah       | 3         |

### Step 2: Result After `GROUP BY` and `HAVING` Clause
Now, we will group this result by the manager's name and count how many direct reports each manager has. We will then filter to show only those managers with 5 or more direct reports.


**Final Resultant Table:**

| ManagerName |
|-------------|
| Alice       |

This table shows that Alice is the only manager with 5 or more direct reports based on the `HAVING COUNT(m.managerId) >= 5` condition, which matches the description provided. This result effectively filters out managers like Charlie, who only manages 2 employees.

Q3. Department Top Three Salaries

Ans:

-- Write your MySQL query statement below
SELECT Department, Employee, Salary
FROM (
    SELECT 
        d.name AS Department,
        e.name AS Employee,
        e.salary AS Salary,
        DENSE_RANK() OVER (PARTITION BY d.name ORDER BY Salary DESC) AS rnk
    FROM Employee e
    JOIN Department d
    ON e.departmentId = d.id
) AS rnk_tbl
WHERE rnk <= 3;

Q4. Nth Highest Salary

Ans:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
    WITH RankedSalaries AS 
    (
      SELECT salary, 
             DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
      FROM employee
    )
    SELECT salary
    FROM RankedSalaries
    WHERE rank = N
    LIMIT 1
  );
END

To use this function, you would call it like so:

SELECT getNthHighestSalary(3);

Q5. Department Highest Salary : Write a solution to find employees who have the highest salary in each of the departments.

# Write your MySQL query statement below
SELECT 
    Department,
    Employee,
    Salary
FROM (SELECT 
    d.name AS Department,
    e.name AS Employee,
    e.salary AS Salary,
    DENSE_RANK() OVER (PARTITION BY d.id ORDER BY e.salary DESC) AS rnk
FROM Employee e
JOIN Department d
ON e.departmentId = d.id) AS t
WHERE rnk = 1;

Q6. Duplicate Emails

/* Write your PL/SQL query statement below */
SELECT DISTINCT P1.Email FROM Person P1,Person P2 

WHERE P1.id <> P2.id AND P1.Email=P2.Email

Q7. Rank Scores

Ans:

``sql SELECT score, dense_rank() OVER(ORDER BY score DESC) as "rank" FROM Scores

``

Q8. Customer Who Visited but Did Not Make Any Transactions

Ans:

# Write your MySQL query statement below

SELECT v.customer_id, COUNT(v.visit_id) AS count_no_trans 
from Visits v 
LEFT JOIN Transactions t 
ON v.visit_id = t.visit_id  
WHERE t.transaction_id IS NULL 
GROUP BY v.customer_id; 


#UPDATE DEC 1ST : we take the t.amount as null
SELECT v.customer_id, COUNT(v.visit_id) AS count_no_trans 
from Visits v 
LEFT JOIN Transactions t 
ON v.visit_id = t.visit_id  
WHERE t.amount IS NULL 
GROUP BY v.customer_id; 

Q9. Delete Duplicate Emails

Ans:

delete p1 from person p1,person p2 
where p1.email=p2.email and p1.id>p2.id;

Q10. Customers Who Never Order

SELECT name as Customers
from Customers
where id not in (
    select customerId
    from Orders
);

Q11. Replace Employee ID With The Unique Identifier

Ans:

# Write your MySQL query statement below
select 
eu.unique_id as unique_id, e.name as name
from Employees e left join EmployeeUNI eu on e.id = eu.id

Q12. The Number of Employees Which Report to Each Employee

Ans:

SELECT
    emp1.employee_id,
    emp1.name,
    COUNT(emp2.employee_id) AS reports_count,
    ROUND(AVG(emp2.age)) AS average_age
FROM Employees emp1
INNER JOIN Employees emp2 ON emp1.employee_id = emp2.reports_to
GROUP BY emp1.employee_id
ORDER BY emp1.employee_id