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

📑 목차

• Lv1. 데이터 속 김서방 찾기
• Lv2. 날짜별 획득포인트 조회하기
• Lv3. 이용자의 포인트 조회하기
• Lv4. 단골 고객님 찾기
• Lv4. 가장 높은 월급을 받는 직원은? 
• Lv5. 가장 많이 팔린 품목은?
• Lv5. 예산이 가장 큰 프로젝트는? 

---

🌱 준비

엑셀보다 쉽고 빠른 SQL 강의 환경과 동일

• DBeaver 설치
• sparta DB 연결

2025.03.20 - [내일배움캠프(Spring 7기)/엑셀보다 쉽고 빠른 SQL] - 엑셀보다 쉽고 빠른 SQL - 1주차

엑셀보다 쉽고 빠른 SQL - 1주차

---

Lv1. 데이터 속 김서방 찾기

풀이

SELECT COUNT(*) name_cnt FROM users WHERE name LIKE '김%';

---

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

풀이

SELECT DATE(created_at) created_at, ROUND(AVG(point)) average_points
FROM point_users
GROUP BY 1;

---

Lv3. 이용자의 포인트 조회하기

 

풀이

SELECT u.user_id, email, COALESCE(point, 0) point FROM users u
LEFT JOIN point_users pu ON u.user_id=pu.user_id
ORDER BY 3 DESC;

---

Lv4. 단골 고객님 찾기

문제:

Orders 테이블:

 

Customers 테이블:

 

1. 고객별로 주문 건수와 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요.

• 출력 결과에는 고객 이름, 주문 건수, 총 주문 금액이 포함되어야 합니다. 단, 주문을 한 적이 없는 고객도 결과에 포함되어야 합니다.

SELECT CustomerName, COUNT(*) OrderCount, COALESCE(SUM(TotalAmount), 0) TotalSpent FROM customers c
LEFT JOIN Orders o ON c.CustomerID=o.CustomerID
GROUP BY 1
ORDER BY 1 ASC;

 

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

SELECT Country, CustomerName Top_Customer, TotalSpent Top_Spent
FROM (
	SELECT c.CustomerName, c.CustomerID, c.Country, SUM(o.TotalAmount) TotalSpent,
    	RANK() OVER (PARTITION BY c.Country ORDER BY SUM(o.TotalAmount) DESC) AS ranking
    	FROM customers c
	LEFT JOIN Orders o ON c.CustomerID=o.CustomerID
	GROUP BY c.CustomerName, c.CustomerID
) sub1
WHERE ranking=1;

---

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

문제:

Employees 테이블:

 

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

SELECT e1.Name, e1.Department, e1.Salary, sub1.Top_earner, sub1.Max_Salary FROM Employees e1
LEFT JOIN (
	SELECT e2.Name Top_earner, MAX(e2.Salary) Max_Salary, e2.Department FROM Employees e2
    	GROUP BY e2.Department
) sub1 ON e1.Department=sub1.Department;

 

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

SELECT Department, AVG(Salary) AS Avg_Salary FROM Employees
GROUP BY Department
ORDER BY Avg_Salary DESC
LIMIT 1;

---

Lv5. 가장 많이 팔린 품목은?

문제:

 

1. 각 고객이 구매한 모든 제품의 총 금액을 계산하고, 고객 이름, 총 구매 금액, 주문 수를 출력하는 SQL 쿼리를 작성해주세요.

SELECT 
	(SELECT CustomerName FROM Customers c WHERE c.CustomerID=o.CustomerID) AS CustomerName,
    SUM(p.Price*o.Quantity) TotalAmount, COUNT(o.OrderID) OrderCount
FROM Orders o
LEFT JOIN Products p ON o.ProductID=p.ProductID
GROUP BY o.CustomerID;

2. 각 제품 카테고리별로 가장 많이 팔린 제품의 이름과 총 판매량을 조회하는 SQL 쿼리를 작성해주세요.

SELECT Category, ProductName AS Top_Product, TotalSold
FROM (
	SELECT p.Category, p.ProductName,
           SUM(o.Quantity) AS TotalSold,
           RANK() OVER (PARTITION BY p.Category ORDER BY SUM(o.Quantity) DESC) AS ranking
    FROM Products p
    JOIN Orders o ON p.ProductID = o.ProductID
    GROUP BY p.Category, p.ProductName
) sub1
WHERE ranking = 1;

---

Lv5. 예산이 가장 큰 프로젝트는? 

문제:

 

1. 각 직원이 속한 부서에서 가장 높은 월급을 받는 직원들만 포함된 결과를 조회하는 SQL 쿼리를 작성해주세요.

SELECT e1.Name, e1.Department, e1.Salary FROM Employees e1
WHERE e1.Salary = (
    SELECT MAX(e2.Salary)
    FROM Employees e2
    WHERE e2Department = e1.Department
);

2. 직원이 참여한 프로젝트 중 예산이 10,000 이상인 프로젝트만을 조회하는 SQL 쿼리를 작성해주세요. 

SELECT e.Name, p.ProjectName, p.Budget FROM Employees e
JOIN EmployeeProjects ep ON e.EmployeeID = ep.EmployeeID
WHERE ep.ProjectID IN (
    SELECT p.ProjectID FROM Projects p
    WHERE p.Budget >= 10000
);