sql 달리기반 3~5번

3번

SELECT u.user_id, u.email,
          COALESCE(p.point,0) 'point'
FROM users u
JOIN point_users p ON u.user_id = p.user_id
order by p.point desc;

 

4번

SELECT 
    c.CustomerName,
    COUNT(o.OrderID) AS OrderCount,
    SUM(o.TotalAmount) AS TotalSpent
FROM 
    Customer c
JOIN 
    Orders o ON c.CustomerID = o.CustomerID
GROUP BY 
    c.CustomerID, c.CustomerName
HAVING 
    (SELECT COUNT(*) 
     FROM Orders o2 
     WHERE o2.CustomerID = c.CustomerID) > 0

 

5번 

SELECT 
    e.Name,
    e.Department,
    e.Salary,
    top_earner.Name 'Top_Earner',
    top_earner.Salary 'Top_Salary'
FROM 
    Employees e
JOIN 
    (SELECT 
         Department,
         Name,
         Salary
     FROM 
         Employees
     WHERE 
         (Department, Salary) IN (
             SELECT 
                 Department, 
                 MAX(Salary)
             FROM 
                 Employees
             GROUP BY 
                 Department
         ))  as top_earner
ON 
    e.Department = top_earner.Department
ORDER BY 
    e.EmployeeID;  


SELECT 
    e.Department,
    AVG(e.Salary) AS Avg_Salary
FROM 
    Employees e
JOIN 
    (SELECT 
         Department, 
         AVG(Salary) AS AvgSalary
     FROM 
         Employees
     GROUP BY 
         Department) AS DeptAvg 
ON 
    e.Department = DeptAvg.Department
WHERE 
    e.Department = 'IT'
GROUP BY 
    e.Department
HAVING 
    AVG(e.Salary) = (SELECT MAX(AvgSalary) FROM (SELECT AVG(Salary) AS AvgSalary FROM Employees GROUP BY Department) AS MaxAvg);