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 m.managerId 
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;

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 (
      # Write your MySQL query statement below.
        with cte as 
        (Select *, dense_Rank() over(order by salary desc) as getNthHighestSalary
        from employee)
        select salary
        from cte
        where getNthHighestSalary = N
        limit 1
  );
END

Q5. Department Highest Salary

# 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