SQL Study 6차시 (SolveSQL, Leetcode 문제풀이)

1. 멘토링 짝꿍 리스트

Q1. https://solvesql.com/problems/mentor-mentee-list/

select
  e1.employee_id as mentee_id,
  e1.name as mentee_name,
  e2.employee_id as mentor_id,
  e2.name as mentor_name
from  employees e1, employees e2
where
  e1.join_date between '2021-09-01' and '2021-12-31'
  and e2.join_date <= '2019.12.31'
  and e1.department != e2.department
order by
  mentor_id

2. 데이터 그룹으로 묶기

Q2. https://solvesql.com/problems/group-by/

SELECT quartet
     , round(avg(x),2) AS x_mean
     , round(variance(x),2) AS x_var
     , round(avg(y),2) AS y_mean
     , round(variance(y),2) AS y_var
FROM points
GROUP BY quartet;

3. Exchange Seats

Q3. https://leetcode.com/problems/exchange-seats/

-- 1번 풀이
SELECT CASE
           WHEN MOD(id,2) = 0 THEN id - 1
           WHEN MOD(id,2) = 1 AND id < (SELECT COUNT(*) FROM seat) THEN id + 1
           ELSE id END AS id
     , student
FROM seat
ORDER BY id;

-- 2번 풀이
SELECT ROW_NUMBER() OVER() AS id, student
FROM seat
ORDER BY CASE WHEN MOD(id, 2) = 1 THEN id + 1 ELSE id - 1 END;

4. Customers Who Never Order

Q4. https://leetcode.com/problems/customers-who-never-order/

SELECT name AS Customers
FROM customers LEFT JOIN orders o ON customers.id = o.customerId
WHERE o.id is null

5. Not Boring Movies

Q5. https://leetcode.com/problems/not-boring-movies/

SELECT *
FROM Cinema
WHERE id % 2 = 1
AND description NOT LIKE '%boring%'
ORDER BY rating DESC