SQL 걷기반 문제 통합

1) first

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

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

// 3. 테이블에서 연봉(salary)이 40000과 60000 사이인 직원들을 선택하는 쿼리를 작성해주세요.
// 정답
select *
from sparta_employees
where selary between 40000 and 60000;

// 4. 테이블에서 입사일(hire_date)이 2023년 1월 1일 이전인 모든 직원들을 선택하는 쿼리를 작성해주세요.
// 정답
select *
from sparta_employees
where hire_date < '2023-01-01';

---

2)

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

// 6. products 테이블에서 제품 이름에 '프로'가 포함된 모든 제품을 선택하는 쿼리를 작성해주세요.
// 정답
select *
from products
where product_name like '%프로%';

// 7. products 테이블에서 제품 이름이 '갤'로 시작하는 모든 제품을 선택하는 쿼리를 작성해주세요.
// 정답
select *
from products
where product_name like '갤%';

// 8. products 테이블에서 모든 제품을 구매하기 위해 필요한 돈을 계산하는 쿼리를 작성해주세요.
// 정답
select sum(price) as sum_price --별칭 사용해서 결과 깔끔하게 정리하기
from products;

---

3)

// 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)

// 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 in (2021, 2023);

// 16. sparta_students 테이블에서 Node.js 트랙 소속이고 학점이 ‘A’인 학생의 입학년도를 선택하는 쿼리를 작성해주세요!
//📌오답
select enrollment_year
from sparta_students
where track='Node.js' and grade='A'; --문자열에 ''

<오답노트>
14, 16. 문자열 사용 시 작은따옴표('') 사용하기

---

5)

//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 int(date(start_date, '%y')) = 2022;
//💡해설
select *
from team_projects
where year(start_date) = 2022; --year() 결과 타입: date(숫자로 인식)

//19.team_projects 테이블에서 현재 진행중인 프로젝트를 선택하는 쿼리를 작성해주세요. 단, 지금 시점의 날짜를 하드코딩해서 쿼리하지 말아주세요!
//📌오답
//💡해설
select *
from team_projects
where curdate() between start_date and end_date; --curdate(): 현재 날짜 반환

//20.team_projects 테이블에서 각 프로젝트의 지속 기간을 일 수로 계산하는 쿼리를 작성해주세요!
//📌오답
select date((int(end_date)-int(start_date)) + 1), '%d') as "프로젝트 지속 기간"
from team_projects
//💡해설
select name,
       datediff(day, start_date, end_date) as working_days --날짜(일) 차이 계산 함수
from team_projects;

<오답노트>
18. date_format('날짜값(문자열)' or 컬럼, '날짜 출력 포맷(문자열)') [결과 타입: 문자열]
     *date(): 날짜/시간 값에서 날짜 데이터만 추출 [결과 타입: date]
      year('날짜값(문자열)' or 컬럼) [결과타입: INT]
19. curdate(): 현재 날짜 출력 [결과 타입: DATE]
      now(): 현재 날짜/시간 출력 [결과 타입: DATETIME]
20. datediff(datepart, start date, end date) [결과 타입: INT]
       : dateapart에 따른 일자 차이를 반환

---

6)

//21.lol_users 테이블에서 각 유저의 레이팅(rating) 순위를 계산하는 쿼리를 작성해주세요! 전체 지역(region) 기준이고 순위는 레이팅이 높을수록 높아야해요. (e.g. rating 1400 유저의 순위 > rating 1350 유저의 순위)
//📌오답
select *
from lol_users
order by region asc, rating desc
//💡해설
select id,
       name,
       region,
       rating,
       rank() over(order by rating desc) as lol_ranking --rank() over(partition by(선택) order by(필수))
from lol_users;

//22.lol_users 테이블에서 가장 늦게 게임을 시작한(join_date) 유저의 이름을 선택하는 쿼리를 작성해주세요!
//정답
select name,
       join_date
from lol_users
where join_date is not null
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) as avg_rating
from lol_users
group by region;

<오답노트>
21. 랭킹 집계 함수
rank() over(partition by 순위를 나눠서 매길 기준(선택) order by 순위 기준(필수))
     - 동점 허용 / 같은 순위로 판단
     - partition by : 해당 기준마다 순위 리셋 (ex.각 국가 내에서 경쟁할 때)

---

7)

//25. lol_feedbacks 테이블에서 만족도 점수(satisfaction_score)에 따라 피드백을 내림차순으로 정렬하는 쿼리를 작성해주세요!
--정답
select id, user_name, satisfaction_score, feedback_date
from lol_feedbacks
order by satisfaction_score desc;

//26.lol_feedbacks 테이블에서 각 유저별로 최신 피드백을 찾는 쿼리를 작성해주세요!
--정답
select user_name,
       max(feedback_date) as latest_feedback_date
from lol_feedbacks
group by user_name;

//27.lol_feedbacks 테이블에서 만족도 점수가 5점인 피드백의 수를 계산하는 쿼리를 작성해주세요!
--정답
select count(*) as feedback_cnt
from lol_feedbacks
where satisfaction_score=5;

//28.lol_feedbacks 테이블에서 가장 많은 피드백을 남긴 상위 3명의 고객을 찾는 쿼리를 작성해주세요!
--📌오답
select user_name,
       count(user_name) as cnt_fb
from lol_feedbacks
order by count(user_name) desc
limit 3;
--💡해설
select user_name,
       count(user_name) as cnt_fb
from lol_feedbacks
group by user_name --분류 기준 꼭 적기
order by count(user_name) desc --order by cnt_fb과 같이 별칭 사용 가능
--order by가 select 보다 뒤에 실행되기 때문에 select의 별칭을 불러올 수 있음.
limit 3;

//29.lol_feedbacks 테이블에서 평균 만족도 점수가 가장 높은 날짜를 찾는 쿼리를 작성해주세요!
--정답
select feedback_date,
       avg(satisfaction_score) as avg_scor
from lol_feedbacks
group by feedback_date
order by avg_score desc
limit 1;

---

8)

// 30. doctors 테이블에서 전공(major)가 성형외과인 의사의 이름을 알아내는 쿼리를 작성해주세요!
--정답
select name, major
from doctors
where major='성형외과';

// 31. doctors 테이블에서 각 전공 별 의사 수를 계산하는 쿼리를 작성해주세요!
--정답
select major,
       count(name) --null값 없으니 count(*) 사용 가능
from doctors
group by major;

// 32. doctors 테이블에서 현재 날짜 기준으로 5년 이상 근무(hire_date)한 의사 수를 계산하는 쿼리를 작성해주세요!
--📌오답
select count(*) 
from doctors
where year(curdate())-year(hire_date)>=5; --정확한 5년을 계산하는 게 아니라 년도를 기준으로 '5년차'를 계산하는 함수. 정확도 떨어짐.
--💡해설
select count(*)
from doctors
where date_sub(curdate(), interval 5 year) >= hire_date --DATE_SUB(기준날짜, 특정 시간 간격)

// 33.doctors 테이블에서 각 의사의 근무 기간을 계산하는 쿼리를 작성해주세요!
--정답
select name,
       hire_date,
       datediff(curdate(), hire_date) as working_dates --datediff(끝나는 날짜, 시작 날짜): 두 날짜의 차이를 구하는 함수
from doctors;

<함수 오답노트>
32. year() 함수의 차이는 '연차'를 계산하는 함수로, 정확한 시간을 계산할 수 없다.
       DATE_SUB(기준 날짜, INTERVAL 시간 간격) : '기준 날짜'에서 '시간 간격'만큼 이전 (-)
       DATE_ADD(기준 날짜, INTERVAL 시간 간격) : '기준 날짜'에서 '시간 간격'만큼 이후 (+)
33. 두 날짜의 차이 구하기
      DATEDIFF(끝 날짜, 시작 날짜)

---

9)

//34. patients 테이블에서 각 성별(gender)에 따른 환자 수를 계산하는 쿼리를 작성해주세요!
--정답
select gender,
       count(gender) as gender_cnt --count(*)사용 가능
from patients
group by gender;

//35. patients 테이블에서 현재 나이가 40세 이상인 환자들의 수를 계산하는 쿼리를 작성해주세요!
--📌오답
select count(*) as "age_>=40"
from patients
where year(curdate())-year(birth_date)+1 >= 40
--💡해설
select count(*)
from patients
where birth_date <= date_sub(curdate(), interval 40 year);

//36. patients 테이블에서 마지막 방문 날짜(last_visit_date)가 1년 이상 된 환자들을 선택하는 쿼리를 작성해주세요!
--정답
select *
from patients
where last_visit_date <= date_sub(curdate(), interval 1 year);

//37. patients 테이블에서 생년월일이 1980년대인 환자들의 수를 계산하는 쿼리를 작성해주세요!
--정답
select count(*)
from patients
where year(birth_date) between 1980 and 1989;
--💡권장
select count(*)
from patients
where birth_date between '1980-01-01' and '1989-12-31';

<오답노트>
35. 생일 값을 구할 때 year() 함수로 연도만 계산하면 한국나이만 계산 가능.
      가능하면 날짜 계산은 date_sub() / date_add() 함수 이용하기
37. 연도계산에 year() 사용 가능
      단, 그러면 컬럼의 모든 date 값을 year로 재반환해야 함. 비효율적. date 포맷 그대로 사용하도록.

---

10)

//38. 현재 존재하고 있는 총 부서의 수를 구하는 쿼리를 작성해주세요!
--정답
select count(*) as cnt_departments
from departments;

//39. 모든 직원과 그들이 속한 부서의 이름을 나열하는 쿼리를 작성해주세요!
--정답
select e.name,
       d.name --join으로 연결한 테이블이 꼭 select에 포함되지 않아도 됨.
FROM employees AS e INNER JOIN departments AS d ON e.department_id=d.id;

//40. '기술팀' 부서에 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!
--정답
select e.name
from employees e inner join departments d on e.department_id=d.id
where d.name='기술팀';

//41. 부서별로 직원 수를 계산하는 쿼리를 작성해주세요!
--📌오답
select d.name as department,
       count(*)
from employees e inner join departments d on e.department_id=d_id --inner join:직원이 있는 부서만 보여준다.(오답/모든 부서를 보여줘야 함.)
group by d.name;
--💡해설
select e.name as department,
       count(e.id) --count 할 때 null일 가능성(변동될 가능성)이 적은 컬럼을 적는 게 안전함.
from departments d --기준 테이블로 설정(문제가 '부서별로 직원 수~)
	left join employees e on d.id=e.department_id --부서에 직원이 0명인 부서도 포함이어서 'left join'사용.
group by d.name;

//42. 직원이 없는 부서의 이름을 찾는 쿼리를 작성해주세요!
--정답
select d.name as department
       count(e.id) as cnt_employee
from departments d
     left join employees e
     on d.id=e.department_id
group by d.name
having count(e.id)=0;
--💡권장
SELECT d.name
FROM departments d LEFT JOIN employees e ON d.id = e.department_id
WHERE e.id IS NULL; --단순 null값을 조회할 때는 where로 간단하게.

//43. '마케팅팀' 부서에만 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!
--정답
select e.name
from employees e
     inner join departments d
     on e.department_id=d.id
where d.name='마케팅팀';

<오답노트>
41. 문제에 따라 기준테이블로 설정하는 게 달라진다.
      푼 것처럼 employees 테이블을 기준테이블로 설정하면, 직원이 없는 부서는 조회되지 않는다.
      '부서별로 직원 수'를 구해야 하기 때문에 (1)모든 부서가 표시되어야 하며 (2)만약 직원이 없으면 그 부서의 직원수는 0으로 표시.
42. 단순 null값을 조회하면 될 때는 where로 가벼운 쿼리문 작성.
      **LEFT JOIN에서 NULL 조회**
      LEFT JOIN에서 매칭되지 않은 행을 찾기 위해서는 기준 테이블이 아닌 오른쪽 테이블의 컬럼을 검사해야 하며,
      그중에서도 NULL이 될 가능성이 없는 고유 식별자(PK)를 기준으로 NULL 여부를 판단하는 것이 가장 안전하다.

---

11) last

//44. 모든 주문의 주문 ID와 주문된 상품의 이름을 나열하는 쿼리를 작성해주세요!
--정답
select o.id, p.name
from orders o inner join products p on o.product_id=p.id;

//45. 총 매출(price * quantity의 합)이 가장 높은 상품의 ID와 해당 상품의 총 매출을 가져오는 쿼리를 작성해주세요!
--정답
select p.id,
       sum(p.price * o.quantity) as sum_price
from products p
     inner join orders o
     on p.id=o.product.id
group by p.id
order by sum(p.price * o.quantity) desc
limit 1;

//46. 각 상품 ID별로 판매된 총 수량(quantity)을 계산하는 쿼리를 작성해주세요!
--정답
select p.id,
       sum(o.quantity) as sum_qty --어떤 테이블에서 가져온 컬럼인지 꼭 표기!!
from products p
     left join orders o
     on p.id=o.product_id
group by p.id;

//47. 2023년 3월 3일 이후에 주문된 모든 상품의 이름을 나열하는 쿼리를 작성해주세요!
-정답
select p.name
from products p inner join orders o on p.id=o.product_id
where o.order_date > '2023-03-03';

//48. 가장 많이 판매된 상품의 이름을 찾는 쿼리를 작성해주세요!
--정답
select p.name,
       sum(o.quantity) as total_qty
from products p inner join orders o on p.id=o.product_id
group by p.name
order by sum(quantity) desc
limit 1;

//49. 각 상품 ID별로 평균 주문 수량을 계산하는 쿼리를 작성해주세요!
--정답
select p.id,
       avg(o.quantity) as avg_qty
from products p inner join orders o on p.id=o.product_id
group by p.id;

//50. 판매되지 않은 상품의 ID와 이름을 찾는 쿼리를 작성해주세요!
--정답.
select p.id, p.name
from products p left join orders o on p.id=o.product_id
where o.quantity is null; --o.quantity는 단순 기입 누락일 수 있어서 실무에서는 오류 발생 가능성 있음.
--주문 자체의 고유값인 o.id를 사용하는 게 안전함.

<오답노트>
- select, group by, count() 등 해당 컬럼의 고유값으로 변동 가능성이 없는 컬럼을 사용하는 것이 안전함.
- group by에서 A컬럼을 사용, 컬럼A와 컬럼B가 '함수 종속성(1:1 매칭)'이 있다면 select에 컬럼B를 사용해도 됨.(고유값인 컬럼으로 사용)

---

오답노트 총합

• 문자열 사용 시 작은따옴표('') 사용
• select, group by, count() 등 해당 컬럼의 고유값으로 변동 가능성이 없는 컬럼을 사용하는 것이 안전함.
• group by에서 A컬럼을 사용, 컬럼A와 컬럼B가 '함수 종속성(1:1 매칭)'이 있다면 select에 컬럼B를 사용해도 됨.
  (고유값인 컬럼으로 사용)

# 날짜 관련 함수

• DATE_FORMAT('날짜값(문자열)' or 컬럼, '날짜 출력 포맷(문자열)')
  : 날짜(Date/Datetime)를 사람이 원하는 “문자열 형식”으로 바꿔주는 함수
  [Data Type: Str/Varchar]

포맷	의미	예시
%Y	연도(4자리)	2025
%y	연도(2자리)	25
%m	월(01~12)	12
%d	일(01~31)	27
%Y-%m	연-월	2025-12
%Y-%m-%d	연-월-일	2025-12-27
%H:%i:%s	시:분:초	14:30:00

• DATE('날짜값 시간값' or 컬럼)
  : 날짜/시간 값에서 날짜 데이터만 추출
  [Data Type: DATE]
• YEAR('날짜값(문자열)' or 컬럼)
  : 날짜(Date / Datetime)에서 ‘연도(년도)’만 추출하는 함수
  [Data Type: INT]
• CURDATE()
  : 현재 날짜 출력
  [Data Type: DATE]
• NOW()
  : 현재 날짜/시간 출력
  [Data Type: DATETIME]
• DATEDIFF(date1, date2)
  : 두 날짜 사이의 차이를 ‘일(day)’ 단위로 계산해서 반환하는 함수
  계산: date1 - date2
  **단순 일차 계산 / 정확한 날짜 기준의 계산이 필요할 때는 'DATE_SUB() 함수 사용'
  [Data Type: INT]
• DATE_SUB(기준 날짜, INTERVAL 시간 간격)
  : '기준 날짜'에서 '시간 간격'만큼 이전 (-)
  ** 연차 / 근속 / 나이 계산 등 정확한 일자를 기준으로 계산할 때는 이 함수 사용.
  [Data Type: DATE]
  
• DATE_ADD(기준 날짜, INTERVAL 시간 간격)
  : '기준 날짜'에서 '시간 간격'만큼 이후 (+)
  ** 연차 / 근속 / 나이 계산 등 정확한 일자를 기준으로 계산할 때는 이 함수 사용.
  [Data Type: DATE]

# 랭킹 집계 함수

• rank() over(partition by 순위를 나눠서 매길 기준(선택) order by 순위 기준 [ASC | DESC](필수))
  :RANK()는 정렬 기준에 따라 순위를 매기되, 같은 값은 같은 순위를 부여하고 다음 순위는 건너뛴다.
  - partition by : 해당 기준마다 순위 리셋 (ex.각 국가 내에서 경쟁할 때)
• DENSE_RANK()
  : 동첨허용, 다음순위 안 건너뜀.
• ROW_NUMBER()
  : 동점 무시, 무조건 1씩 증가

# JOIN

• 문제에 따라 기준테이블로 설정하는 게 달라진다.
  푼 것처럼 employees 테이블을 기준테이블로 설정하면, 직원이 없는 부서는 조회되지 않는다.
  '부서별로 직원 수'를 구해야 하기 때문에 (1)모든 부서가 표시되어야 하며 (2)만약 직원이 없으면 그 부서의 직원수는 0으로 표시.
• 단순 null값을 조회하면 될 때는 where로 가벼운 쿼리문 작성.
  **LEFT JOIN에서 NULL 조회**
  LEFT JOIN에서 매칭되지 않은 행을 찾기 위해서는 기준 테이블이 아닌 오른쪽 테이블의 컬럼을 검사해야 하며,
  그중에서도 NULL이 될 가능성이 없는 고유 식별자(PK)를 기준으로 NULL 여부를 판단하는 것이 가장 안전하다.