SQL 달리기 퀘스트

👜lv4. 단골 고객님 찾기

2.나라별로 총 주문 금액이 가장 높은 고객의 이름과 그 고객의 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요.

SELECT 
    c.Country,
    c.CustomerName AS Top_Customer,
    SUM(o.TotalAmount) AS Top_Spent//Customer 연결한 테이블에서 나라, 고객명, 총액을 그룹별로 더한 값을 검색 
FROM 
    Customers c
JOIN 
    Orders o ON c.CustomerID = o.CustomerID //주문테이블과 고객테이블을 조인함
GROUP BY 
    c.Country, c.CustomerName //나라와 고객명을 그룹으로 묶어줌
HAVING 
    SUM(o.TotalAmount) = (//그룹별 총액과 같은 것만 그룹으로 묶어줌(
        SELECT 
            MAX(SumSpent)//그 중 가장 큰 총액
        FROM 
            (SELECT 
                 SUM(o2.TotalAmount) AS SumSpent//고객 아이디별로 총액 계산. 
             FROM 
                 Customers c2
             JOIN 
                 Orders o2 ON c2.CustomerID = o2.CustomerID//고객테이블과 주문테이블을 조인함
             WHERE 
                 c2.Country = c.Country//외부쿼리 나라 컬럼과 내부쿼리 나라 컬럼이 같다면
             GROUP BY 
                 c2.CustomerID) AS Subquery//고객 아이디별로 그룹으로 묶어줌
    );

<HAVING절>
Group by의 where 절 같은 역할로 Group화 한 후 조건을 더해 필터링한다.
where절은 Group화 하기 전 각 개별 행에 대해 조건을 적용한다면,
Having절은 Group화 한 후에 조건을 적용함.
having절에서도 함수(sum()..), 서브 쿼리, 논리연산(and,or...), 모두 가능

내가 막혔던 부분은

select Country,max(Total) 

 from 
 
    (select c.Country 'Country' ,c.CustomerName 'CustomerName' ,sum(o.TotalAmount) 'Total' 
      
      from new_schema.customers c left join new_schema.orders o on c.CustomerID=o.CustomerID
      
      group by 1,2 
      
      order by Total desc)s 
      
 group by Country;

여기서 CustomerName을 추가하려고 하면 실행이 안돼서 막힘.
답과 비교해보니 답은 having 문에서 서브쿼리를 추가했는데, 서브쿼리에서 특이한 점이 외부쿼리 고객 테이블과 서브쿼리 고객 테이블을 비교연산 한 것. ??->전혀 모르겠다..;
Group by에 포함된 열만 select할 수 있다는 거 잊지 말 것!

💸Lv4. 가장 높은 월급을 받는 직원은?

1.각 직원의 이름, 부서, 월급, 그리고 그 직원이 속한 부서에서 가장 높은 월급을 받고 있는 직원의 이름과 월급을 조회하는 SQL 쿼리를 작성해주세요.

SELECT ee1.Name,
       ee1.Department,
       ee1.Salary,
       ee2.Top_Earner,
       ee2.Top_Salary
FROM employees ee1
LEFT JOIN
  (SELECT DISTINCT Name AS 'Top_Earner',
                   Top_Salary,
                   Department
   FROM employees e1
   RIGHT JOIN
     (SELECT max(total) AS 'Top_Salary'
      FROM
        (SELECT Name,
                Department,
                max(Salary) AS 'total'
         FROM employees
         GROUP BY Name,
                  Department)kk
      GROUP BY Department)e2 ON e1.Salary=e2.Top_Salary)ee2 ON ee1.Department=ee2.Department;

<정답>

SELECT 
    e1.Name,
    e1.Department,
    e1.Salary,
    e2.Name AS Top_Earner,
    e2.Salary AS Top_Salary
FROM 
    Employees e1
JOIN 
    Employees e2 ON e1.Department = e2.Department
WHERE 
    e2.Salary = (
        SELECT MAX(Salary)
        FROM Employees e3
        WHERE e3.Department = e1.Department
    );

: 나한텐 너무 어려워서 어떻게든 답에 끼워맞추느라 이상하게 여러번 중첩된 코드가 되었다.
이번 답도 보니 외부쿼리랑 내부쿼리를 연결해서 코드를 훨씬 효율적으로 작성했는데,
이 부분이 어떻게 작용되는지 이해해야겠다.

2.부서별로 평균 월급이 가장 높은 부서의 이름과 해당 부서의 평균 월급을 조회하는 SQL 쿼리를 작성해주세요.

SELECT DISTINCT e1.Department,
                e2.Avg_Salary
FROM employees e1
RIGHT JOIN
  (SELECT Department,
          avg(Salary) AS 'Avg_Salary'
   FROM employees
   GROUP BY Department
   ORDER BY Avg_Salary DESC
   LIMIT 1)e2 ON e1.Department=e2.Department;

<정답>

SELECT 
    Department,
    AVG(Salary) AS Avg_Salary
FROM 
    Employees
GROUP BY 
    Department
HAVING 
    AVG(Salary) = (
        SELECT MAX(Avg_Salary)
        FROM 
            (SELECT AVG(Salary) AS Avg_Salary
             FROM Employees
             GROUP BY Department) AS subquery
    );

Workbench 코드 가독성 높이는 단축키: Ctrl+B

테이블을 엑셀 .cvs 파일로 저장해 Workbench 왼쪽 카테고리에서 Schemas-마우스 오른쪽 버튼-Table Data Import Wizard 클릭- 파일 불러오기 - Next3- Finish- 새로고침 한 번 누르면 테이블을 쉽게 생성할 수 있다.
(컬럼 맨 앞글자가 오류가 나서 한 번 수정해야하는데 이 부분은 어떻게 해결해야할지 모르겠다;
또 문자는 전부 TEXT형식으로 저장돼서 굳이는 아니지만 한 번 수정 필요.)

Workbench Error:1060
열이 중복된 경우 발생: 나같은 경우 세 테이블을 join으로 연결하려다 보니 두 테이블의 중복된 열 때문에 에러가 생김

• 해결방법 : 해당 중복 열의 이름을 바꾸거나, 제거