사전 캠프 3일차

오늘은 먼저 달리기반 db 문제를 풀어볼 것이다.

 

Lv.1 데이터 속 김서방 찾기

 

사실 쉬운 문제라고 생각하고 별 생각하지 않고 like를 사용해서 풀었다.

내가 제출한답

select count(*) as name_cnt from users where name like "김%";

으로 풀었는데 결과 창도 같고 당연히 정답과 같을 거라고 생각했는데 혹시 모를 중복 제거와 substr을 사용해서 풀어서 substr 함수에 대해서만 공부하자

 

substr 사용 방법

// pos = 시작 위치 값 len = 길이 값
1. substr(str, pos);

2. substr(str, pos, len);

1번 방법은 시작 위치 값만 정해주고 끝까지  읽는 것이고

2번 방법은 시작 위치부터 len 길이까지 읽는 것이다.

 

두 방법 모두 기억해두자!

 

 

Lv.2 날짜별 획득 포인트 조회하기

 

레벨 2 문제도 상당히 간단했다 요지는 3개 였는데

1.시분초까지 있는 값을 date 형식으로 변경하자!  <- date 함수 사용

2. 평균 값을 출력하자 <- avg 함수 사용

3. 반올림하자 < -round 함수 사용

이 3가지만 알고 있으면 쉽게 풀리는 문제였다! date가 아니라 substr를 써도 잘 풀리지만 date가 더욱 잘 어울리는 것 같다.

 

내가 제출한 답

select date(created_at) as created_at, round(avg(point)) as average_points from point_users group by date(created_at);

 

 

lv 3 이용자의 포인트 조회하기

이 문제 역시 간단한 편에 속했다. 3가지만 해주면 되는 문제 였는데

1. join인데 user가 메인 테이블 point_user가 서브 테이블로 user 테이블을 기준으로 하려고 LEFT JOIN을 사용했다.

2. point_user 테이블의 point를 기준으로 내림 차순 정렬

3. point 값이 null 값 일 때 0으로 처리해주면 된다.

1번과 2번은 간단했는데 3번은 방법이 여러가지 였다.

 

1. if문 사용

if(pu.point is null, 0, pu.point) as point

이런 식으로 point 값이 null 이면 0 아니면 point 값을 그래도 출력해주는 방법이다

 

2. ifnull 문 사용

 ifnull(pu.point, 0) as point

if문 과 비슷 하게 point가 null 이면 뒤에 있는 값을 넣어주는 방법이다.

 

3. coalesce 함수

COALESCE(pu.point,0) as point

ifnull 문과 비슷하게 point가 null 이면 뒤에 있는 값을 넣는 방법이다.

 

제출한 답

SELECT u.user_id as user_id, u.email as email, ifnull(pu.point, 0) as point from users u left join point_users as pu on u.user_id = pu.user_id order by pu.point desc;

 

 

Lv.4 단골 고객님 찾기

 

쉬운 문제 였다 주문을 한 적이 없는 고객도 결과에 포함 되어야 되기 때문에 customers가 메인 테이블이 되어야한다. 따라서 right join을 사용

select c.CustomerName as CustomerName, count(c.CustomerID) as OrderCount, sum(o.TotalAmount) as TotalSpent from orders o right join customers c on o.CustomerID = c.CustomerID group by o.CustomerID;

 

 

select (c.Country) as Country, c.CustomerName, sum(o.TotalAmount) from orders o join customers c on o.CustomerID = c.CustomerID group by c.Country, c.CustomerName

 

 

 

여기까지는 쉽게 풀었으나 나라 별로? 묶는 방법이 도저히 생각나지 않았다 나라로 묶으면 값을 구할 방법이 없고 그렇다고 값으로 먼저 묶으면 나라로 묶기가 너무 어려웠다 

검색을 해본 결과 테이블을 만들어서 풀어야 했다 아직 서브 쿼리 문제는 확실히 약한거 같다 연습하자!

 

SELECT country,
totalspent Top_Spent ,
customername Top_Customer
FROM
    (
      SELECT cus.country,
      RANK() OVER(PARTITION BY cus. country ORDER BY totalspent DESC) rn,
      a.totalspent,
      cus.customername
      FROM customer cus LEFT join
          (
          SELECT c.customername,
          SUM(totalamount) AS totalspent
          FROM orders o LEFT JOIN customer c ON o. CustomerID = c. CustomerID
          GROUP BY 1
          ) a
      on cus. CustomerName =a. customername
      )b
WHERE rn =1 
ORDER BY 1 DESC

 

Lv.4 가장 높은 월급을 받는 직원은?

 

가장 어렵고 복잡했던 문제였던 것 같다. 레벨 4부터는 뭔가 만만한 문제가 없었다. 

맨처음엔 select문에서 서브 쿼리를 써서 풀려고 했는데 가장 높은 월급을 받는 직업의 월급은 조회했으나 이름이 조회가 되질 않았다. 30분 고민하고 검색해본 결과 from에 새로운 테이블을 만들어서 가장 높은 월급의 직원의 이름과 월급 그리고 부서를 테이블로 만들고 그 내용을 추가를 위해 employees 테이블에 rank와 partition by를 사용해서 해결했다.

select e.name, e.department, e. salary, b.Top_Earner, b. Top_Salary from employees e join   
               (select name as Top_Earner, department , salary as Top_Salary FROM 
               (select Name, Department, salary, rank() over(partition by department order by salary desc) as rn
                   from employees
                   )  as a
                where rn=1
                ) as b
on e.department = b.department

 

검색 후 코드를 보고 이해했기 때문에 나중에 다시 풀어보기로 하자!

rank() over (partition by 칼럼 order by 칼럼 desc)

이부분은 기억해두자 1위를 찾을 때 상당히 쉽게 찾을 수 있게 해주는 코드 이다.

 

생각보다 어렵진 않았지만 기대 결과엔 부서가 IT 였는데 답은 계속 Finance가 나와서 고민했다 사실 두 부서의 평균 월급은 같았기 때문에 상관 없다고 생각햇지만 조원들과 토의 결과 부서 이름에도 정렬을 해주면서 IT 값을 뽑아내며 마쳤다.

 

결과

select Department, avg(salary) from employees e group by Department order by Department desc ,avg(salary) desc limit 1;

 

 

생각보다 문제가 난이도가 있어서 5단계는 내일 풀어야할거 같다!