SQL 문제(코드카타)
- 상품 별 오프라인 매출 구하기
SELECT P.PRODUCT_CODE,
SUM(P.PRICE*O.SALES_AMOUNT) SALES
FROM PRODUCT P INNER JOIN OFFLINE_SALE O ON P.PRODUCT_ID=O.PRODUCT_ID
GROUP BY 1
ORDER BY SALES DESC, 1
SQL 걷기반 - 공부하다보니 팀 프로젝트 시간이 왔어요!
19.
SELECT *
FROM team_projects
WHERE CURDATE() BETWEEN start_date and end_date
SQL 걷기반 - 랭크게임 하다가 싸워서 피드백 남겼어요…
SELECT *
FROM lol_feedbacks
order by satisfaction_score desc
26.
SELECT *
FROM
(
SELECT *,
ROW() over (partition by user_name order by feedback_date desc) recent
FROM lol_feedbacks
) a
WHERE recent=1
27.
SELECT *,
count(1)
FROM lol_feedbacks
WHERE satisfaction_score=5
GROUP by id
28.
SELECT *
FROM
(
SELECT *,
count(1) f_count
FROM lol_feedbacks
GROUP by user_name
) a
order by f_count DESC
limit 3
29.
SELECT feedback_date
FROM
(
SELECT *,
AVG(satisfaction_score) score_avg
FROM lol_feedbacks
group by feedback_date
) a
order by score_avg DESC
limit 1
SQL 걷기반 - LOL을 하다가 홧병이 나서 병원을 찾아왔습니다.
30.
SELECT name
FROM doctors
WHERE major='성형외과'
31.
SELECT count(1)
FROM doctors
group by major
32.
SELECT count(1)
FROM
(
SELECT *,
DATE_FORMAT(CURDATE(), '%Y')-DATE_FORMAT(hire_date, '%Y') result
FROM doctors
) a
WHERE result>=5
33.
SELECT *,
DATE_FORMAT(CURDATE(), '%Y')-DATE_FORMAT(hire_date, '%Y') result
FROM doctors
SQL 걷기반 - 아프면 안됩니다! 항상 건강 챙기세요!
34.
SELECT count(1)
FROM patients
group by gender
35.
SELECT count(1)
FROM patients
WHERE age>=40
36.
SELECT *
FROM
(
SELECT *,
DATE_FORMAT(CURDATE(), '%Y')-DATE_FORMAT(last_visit_date, '%Y') result
FROM patients
) a
WHERE result>=1
37.
SELECT count(1)
FROM patients
WHERE birth_date like '198%'
SQL 걷기반 - 이젠 테이블이 2개입니다
38.
SELECT COUNT(distinct name)
FROM departments
39.
SELECT name,
CASE when department_id='101' then name='인사팀'
when department_id='102' then name='마케팅팀'
when department_id='103' then name='기술팀' end d_name
FROM
(
SELECT e.department_id, e.name, d.name d_name
FROM employees e left join departments d on e.id=d.id
) a
40.
SELECT name
FROM employees
WHERE department_id='103'
41.
SELECT count(1)
FROM employees
group by department_id
42.
SELECT d.name
FROM employees e left join departments d on e.id=d.id
WHERE e.name is null
43.
SELECT e.name
FROM employees e left join departments d on e.id=d.id
WHERE d.name='마케팅팀'
SQL 걷기반 - 마지막 연습 문제 !
44.
SELECT o.id, p.name
FROM products p left join orders o on p.id=o.product_id
45.
SELECT id,
SUM(price)
FROM
(
SELECT p.id, p.price, p.name,
MAX((SUM(p.price*o.quantity)) m_total
FROM products p left join orders o on p.id=o.product_id
group by p.name
order by MAX((SUM(p.price*o.quantity))
LIMIT 1
) a
group by name
46.
SELECT SUM(o.quantity)
FROM products p left join orders o on p.id=o.product_id
group by p.id
47.
SELECT p.name
FROM products p left join orders o on p.id=o.product_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
order by o.quantity DESC
limit 1
49.
SELECT AVG(o.quantity)
FROM products p left join orders o on p.id=o.product_id
group by p.id
50.
SELECT p.id, name
FROM products p left join orders o on p.id=o.product_id
WHERE o.quantity is null or o.quantity=0
*