[사전캠프 퀘스트] 걷기반 - SQL 문제 풀이

📑 목차

• 1) 돈을 벌기 위해 일을 합시다.
• 2) 이제 좀 벌었으니 flex 한 번 해볼까요?!
• 3) 상품 주문이 들어왔으니 주문을 처리해봅시다!
• 4) 이제 놀만큼 놀았으니 다시 공부해봅시다!
• 5) 공부하다보니 팀 프로젝트 시간이 왔어요!
• 6) 팀 프로젝트 열심히 했으니 다시 놀아볼까요?!
• 7) 랭크게임 하다가 싸워서 피드백 남겼어요… 
• 8) LOL을 하다가 홧병이 나서 병원을 찾아왔습니다.
• 9) 아프면 안됩니다! 항상 건강 챙기세요! 
• 10) 이젠 테이블이 2개입니다
• 마지막 연습 문제 ! 

---

1) 돈을 벌기 위해 일을 합시다.

아래와 같은 sparta_employees(직원) 테이블이 있습니다.

 

1. sparta_employees 테이블에서 모든 직원의 이름(name)과 직급(position)을 선택하는 쿼리를 작성해주세요.

SELECT name, position FROM sparta_employees;

 

2. sparta_employees 테이블에서 중복 없이 모든 직급(position)을 선택하는 쿼리를 작성해주세요.

SELECT DISTINCT position FROM sparta_employees;

 

3. sparta_employees 테이블에서 연봉(salary)이 40000과 60000 사이인 직원들을 선택하는 쿼리를 작성해주세요.

SELECT * FROM sparta_employees WHERE salary BETWEEN 40000 AND 60000;

 

4. sparta_employees 테이블에서 입사일(hire_date)이 2023년 1월 1일 이전인 모든 직원들을 선택하는 쿼리를 작성해주세요.

SELECT * FROM sparta_employees WHERE hire_date < '2023-01-01';

---

2) 이제 좀 벌었으니 flex 한 번 해볼까요?! 

여러분이 구매하고 싶은 상품들의 정보가 있는 products(상품) 테이블이 아래에 있습니다.

 

5. products 테이블에서 제품 이름(product_name)과 가격(price)만을 선택하는 쿼리를 작성해주세요.

SELECT product_name, price FROM product;

6. products 테이블에서 제품 이름에 '프로'가 포함된 모든 제품을 선택하는 쿼리를 작성해주세요.

SELECT * FROM product WHERE product_name LIKE '%프로%';

7. products 테이블에서 제품 이름이 '갤'로 시작하는 모든 제품을 선택하는 쿼리를 작성해주세요.

SELECT * FROM product WHERE product_name LIKE '갤%';

8. products 테이블에서 모든 제품을 구매하기 위해 필요한 돈을 계산하는 쿼리를 작성해주세요.

SELECT SUM(price) FROM product;

---

3) 상품 주문이 들어왔으니 주문을 처리해봅시다!

이제 상품 주문이 들어왔으니 어떤 고객에게 어떤 주문이 들어왔는지를 파악할 수 있는 orders(주문) 테이블이 아래에 있습니다.

 

9. orders 테이블에서 주문 수량(amount)이 2개 이상인 주문을 진행한 소비자의 ID(customer_id)만 선택하는 쿼리를 작성해주세요!

SELECT customer_id FROM orders WHERE amount >= 2;

10. orders 테이블에서 2023년 11월 2일 이후에 주문된 주문 수량(amount)이 2개 이상인 주문을 선택하는 쿼리를 작성해주세요!

SELECT * FROM orders WHERE order_date > '2023-11-02' AND amount >= 2;

11. orders 테이블에서 주문 수량이 3개 미만이면서 배송비(shipping_fee)가 15000원보다 비싼 주문을 선택하는 쿼리를 작성해주세요!

SELECT * FROM orders WHERE amount < 3 AND shipping_fee > 15000;

12. orders 테이블에서 배송비가 높은 금액 순으로 정렬하는 쿼리를 작성해주세요!

SELECT * FROM orders ORDER BY shipping_fee DESC;

---

4) 이제 놀만큼 놀았으니 다시 공부해봅시다!

아래와 같은 sparta_students(학생) 테이블이 있습니다.

 

13. sparta_students 테이블에서 모든 학생의 이름(name)과 트랙(track)을 선택하는 쿼리를 작성해주세요!

SELECT name, track FROM sparta_students;

14. sparta_students 테이블에서 Unity 트랙 소속이 아닌 학생들을 선택하는 쿼리를 작성해주세요!

SELECT * FROM sparta_students WHERE track != 'Unity';

15. sparta_students 테이블에서 입학년도(enrollment_year)가 2021년인 학생과 2023년인 학생을 선택하는 쿼리를 작성해주세요!

SELECT * FROM sparta_students WHERE enrollment_year = 2021 AND enrollment_year = 2023;

16. sparta_students 테이블에서 Node.js 트랙 소속이고 학점이 ‘A’인 학생의 입학년도를 선택하는 쿼리를 작성해주세요!

SELECT * FROM sparta_students WHERE tack = 'Node.js' AND grade = 'A';

---

5) 공부하다보니 팀 프로젝트 시간이 왔어요!

공부를 한 결과를 점검하기 위해 팀 프로젝트를 수행해야 합니다! 이제, 아래와 같은 team_projects(프로젝트) 테이블이 있습니다.

 

17. team_projects 테이블에서 AWS 예산(aws_cost)이 40000 이상 들어간 프로젝트들의 이름을 선택하는 쿼리를 작성해주세요!

SELECT name FROM team_projects WHERE aws_cost > 40000;

18. team_projects 테이블에서 2022년에 시작된 프로젝트를 선택하는 쿼리를 작성해주세요! 단, start_date < ‘2023-01-01’ 조건을 사용하지 말고 쿼리를 작성해주세요!

SELECT * FROM team_projects WHERE YEAR(start_date) = 2022;

19. team_projects 테이블에서 현재 진행중인 프로젝트를 선택하는 쿼리를 작성해주세요. 단, 지금 시점의 날짜를 하드코딩해서 쿼리하지 말아주세요!

SELECT * FROM team_projects WHERE end_date > NOW();

20. team_projects 테이블에서 각 프로젝트의 지속 기간을 일 수로 계산하는 쿼리를 작성해주세요!

SELECT *, DATEDIFF(end_date, start_date) FROM team_projects;

---

6) 팀 프로젝트 열심히 했으니 다시 놀아볼까요?!

아래와 같은 lol_users(LOL 유저 테이블)이 있습니다.

 

21. lol_users 테이블에서 각 유저의 레이팅(rating) 순위를 계산하는 쿼리를 작성해주세요! 전체 지역(region) 기준이고 순위는 레이팅이 높을수록 높아야해요. (e.g. rating 1400 유저의 순위 > rating 1350 유저의 순위)

SELECT *, RANK() OVER (ORDER BY rating DESC) AS ranking FROM lol_users;

22. lol_users 테이블에서 가장 늦게 게임을 시작한(join_date) 유저의 이름을 선택하는 쿼리를 작성해주세요

SELECT name FROM lol_users ORDER BY join_date DESC LIMIT 1;

23. lol_users 테이블에서 지역별로 레이팅이 높은 순으로 유저들을 정렬해서 나열하는 쿼리를 작성해주세요!

SELECT * FROM lol_users ORDER BY region, rating DESC;

24. lol_users 테이블에서 지역별로 평균 레이팅을 계산하는 쿼리를 작성해주세요!

SELECT region, AVG(rating) FROM lol_users GROUP BY region;

---

7) 랭크게임 하다가 싸워서 피드백 남겼어요… 

아래와 같은 lol_feedbacks (LOL 피드백 테이블)이 있습니다.

 

25. lol_feedbacks 테이블에서 만족도 점수(satisfaction_score)에 따라 피드백을 내림차순으로 정렬하는 쿼리를 작성해주세요!

SELECT * FROM lol_feedbacks ORDER BY satisfaction_score DESC;

26. lol_feedbacks 테이블에서 각 유저별로 최신 피드백을 찾는 쿼리를 작성해주세요!

SELECT * FROM lol_feedbacks AS f1
WHERE feedback_date = (
	SELECT MAX(feedback_date) FROM lol_feedbacks AS f2 WHERE f2.user_name=f1.user_name 
);

27. lol_feedbacks 테이블에서 만족도 점수가 5점인 피드백의 수를 계산하는 쿼리를 작성해주세요!

SELECT COUNT(*) FROM lol_feedbacks WHERE satisfaction_score = 5;

28. lol_feedbacks 테이블에서 가장 많은 피드백을 남긴 상위 3명의 고객을 찾는 쿼리를 작성해주세요!

SELECT user_name, COUNT(*) FROM lol_feedbacks GROUP BY user_name ORDER BY feedback_count DESC LIMIT 3;

29. lol_feedbacks 테이블에서 평균 만족도 점수가 가장 높은 날짜를 찾는 쿼리를 작성해주세요!

SELECT feedback_date FROM lol_feedbacks GROUP BY feedback_date
ORDER BY AVG(satisfaction_score) DESC LIMIT 1;

---

8) LOL을 하다가 홧병이 나서 병원을 찾아왔습니다.

이제, 아래와 같은 doctors(의사) 테이블이 있습니다.

 

30. doctors 테이블에서 전공(major)가 성형외과인 의사의 이름을 알아내는 쿼리를 작성해주세요!

SELECT name FROM doctors WHERE major = '성형외과';

31. doctors 테이블에서 각 전공 별 의사 수를 계산하는 쿼리를 작성해주세요!

SELECT major, COUNT(*) FROM doctors GROUP BY major;

32. doctors 테이블에서 현재 날짜 기준으로 5년 이상 근무(hire_date)한 의사 수를 계산하는 쿼리를 작성해주세요!

SELECT COUNT(*) FROM doctors WHERE TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) >= 5;

33. doctors 테이블에서 각 의사의 근무 기간을 계산하는 쿼리를 작성해주세요!

SELECT name, DATEDIFF(NOW(), hire_date) FROM doctors;

---

9) 아프면 안됩니다! 항상 건강 챙기세요! 

의사가 있으면 당연히 의사에게 진료받는 환자가 있겠죠? 아래와 같은 patients(환자) 테이블이 있습니다.

 

34. patients 테이블에서 각 성별(gender)에 따른 환자 수를 계산하는 쿼리를 작성해주세요!

SELECT gender, COUNT(*) FROM patients GROUP BY gender;

35. patients 테이블에서 현재 나이가 40세 이상인 환자들의 수를 계산하는 쿼리를 작성해주세요!

SELECT COUNT(*) FROM patients WHERE TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 40;

36. patients 테이블에서 마지막 방문 날짜(last_visit_date)가 1년 이상 된 환자들을 선택하는 쿼리를 작성해주세요!

SELECT * FROM patients WHERE TIMESTAMPDIFF(YEAR, last_visit_date, CURDATE()) >= 1;

37. patients 테이블에서 생년월일이 1980년대인 환자들의 수를 계산하는 쿼리를 작성해주세요!

SELECT COUNT(*) FROM patients WHERE YEAR(birth_date) BETWEEN 1980 AND 1989;

---

10) 이젠 테이블이 2개입니다

다음과 같은 직원(employees) 테이블과 부서(departments) 테이블이 있습니다.

• employees 테이블

• departments 테이블

 

38. 현재 존재하고 있는 총 부서의 수를 구하는 쿼리를 작성해주세요!

SELECT COUNT(*) FROM departments;

39. 모든 직원과 그들이 속한 부서의 이름을 나열하는 쿼리를 작성해주세요!

SELECT *, d.name AS department_name FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

40. '기술팀' 부서에 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!

SELECT e.name FROM employees e, departments d WHERE e.department_id = d.id AND d.name = '기술팀';

41. 부서별로 직원 수를 계산하는 쿼리를 작성해주세요!

SELECT d.name, COUNT(*) FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.name;

42. 직원이 없는 부서의 이름을 찾는 쿼리를 작성해주세요!

SELECT name FROM departments 
WHERE id NOT IN (
    SELECT DISTINCT department_id 
    FROM employees
);

43. '마케팅팀' 부서에만 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!

SELECT e.name FROM employees e, departments d WHERE e.department_id = d.id AND d.name = "마케팅팀";

---

마지막  연습 문제 ! 

다음과 같은 상품(products) 테이블과 주문(orders) 테이블이 있습니다.

• products 테이블

• orders 테이블

 

44. 모든 주문의 주문 ID와 주문된 상품의 이름을 나열하는 쿼리를 작성해주세요!

SELECT o.id, p.name FROM orders o
LEFT JOIN products p ON o.product_id = p.id;

45. 총 매출(price * quantity의 합)이 가장 높은 상품의 ID와 해당 상품의 총 매출을 가져오는 쿼리를 작성해주세요!

SELECT p.id, SUM(p.price * o.quantity) AS total_price FROM products p
LEFT JOIN orders ON p.id=o.product_id
GROUP BY p.id
ORDER BY total_price DESC LIMIT 1;

46. 각 상품 ID별로 판매된 총 수량(quantity)을 계산하는 쿼리를 작성해주세요!

SELECT p.id, SUM(o.quantity) FROM products p
LEFT JOIN orders ON p.id=o.product_id
GROUP BY p.id;

47. 2023년 3월 3일 이후에 주문된 모든 상품의 이름을 나열하는 쿼리를 작성해주세요!

SELECT p.name FROM orders o
LEFT JOIN products p ON o.product_id = p.id
WHERE o.order_date > '2023-03-03';

48. 가장 많이 판매된 상품의 이름을 찾는 쿼리를 작성해주세요!

SELECT p.name FROM products p
LEFT JOIN orders o ON p.id = o.product_id
GROUP BY p.name
ORDER BY SUM(o.quantity) DESC LIMIT 1;

49. 각 상품 ID별로 평균 주문 수량을 계산하는 쿼리를 작성해주세요!

SELECT p.id, AVG(o.quantity) FROM products p
LEFT JOIN orders o ON p.id = o.product_id
GROUP BY p.id;

50. 판매되지 않은 상품의 ID와 이름을 찾는 쿼리를 작성해주세요!

SELECT id, name FROM products
WHERE id NOT IN (
    SELECT product_id FROM orders
);