회고

• SELECT, ORDER BY, JOIN, CASE문 등 다양한 SQL문을 실습하며 데이터 조회와 정렬, 조건 처리 능력이 향상됐다.
  실제 테이블 구조와 데이터를 분석하면서 쿼리 작성의 중요성과 재미를 느꼈다.
  특히 복잡한 조건과 집계함수 활용에 자신감이 붙었다.

---

Key_point

✅select from

• select column from table;
• 테이블에서 칼럼이름을 조회한다.

✅order by

• order by column asc; : 오름차순으로 정렬
• order by column desc; : 내림차순으로 정렬
• 정렬했을 때 null값은 판단불가라 맨 처음에 나온다.
• column is null asc; : null값을 맨뒤로 보낼 수 있다.

✅limit

• select * from table limit 5; : 자료의 크기가 어느정도인지 모르기에 꼭 limit를 걸어서 DB를 확인하는 습관을 들여라.

✅서브쿼리

• join을 쓰는 것이 더 빠르고 좋으나 서브쿼리를 사용해야하는 상황이 올 수 있으니 꼭 숙지해야한다.
• 서브쿼리의 내용 먼저 작성하고 메인쿼리를 만들어라.

✅case

• 대표적으로 사용법은 두가지가 있다. 아래의 사진을 참고하자.
• case column
  when 값1 then 반환값 1
  when 값2 then 반환값 2
  .
  .
  else 반환값 5
  end
• case
  when colume = 값1 then 반환값 1
  when 값2 then 반환값 2
  .
  .
  else colume = 반환값 5
  end

---

필기

mydb2

기본예제

use mydb2;

-- 문제. team_id = K01인 선수들의 명단을 확인
-- 아래는 김성재의 풀이이다.
select *
from player
where team_id = 'K01'

-- 문제. K01 팀의 정보를 입단연도, 이름으로 오름차순해서 player_name, join_yyyy, posit 조회하기
-- 아래는 김성재의 풀이이다.
select player_name, join_yyyy, posit
from player
where team_id = 'k01'
order by join_yyyy asc, player_name asc; -- null 값은 판단 불가라 맨 처음에 나온다.

-- 백현숙강사님의 추가풀이
-- null 값을 맨뒤로 정렬
select player_name, join_yyyy, posit
from player
where team_id = 'k01'
order by join_yyyy is null asc, player_name asc;

-- null 값을 제외하는 방법 1
select player_name, join_yyyy, posit
from player
where team_id = 'k01' and join_yyyy is not null
order by join_yyyy asc, player_name asc;

-- null 값을 다른숫자로 대체하여 뒤로 빼는 방법 2
select * from
(select player_name, ifnull(join_yyyy, 5000)join_yyyy, posit
from player
where team_id = 'k01') as AA
order by join_yyyy asc, player_name asc;

-- 문제. 울산지역에 있는 모든 팀이름, 각 팀에 속한 선수이름, 우편 번호(ex, 682-060), 주소를 조회하라.
-- 아래는 김성재의 풀이이다.
select * from team limit 10; -- 팀 테이블 확인
select * from player limit 10; -- 선수 테이블 확인

select 
t.team_name as "팀명", p.player_name as "선수명",concat(t.zip_code1, "-", t.zip_code2) as "우편번호", t.address as "주소"
from team t
join player p on t.team_id = p.team_id
where t.region_name = '울산'
order by p.player_name;

-- 문제. team_id가 K01 이거나 K03인 선수의 팀 이름, 팀 주소, 선수이름
-- 아래는 join과 where절을 활용한 풀이이다.
select t.team_name, t.address, p.player_name
from team t
join player p on t.team_id = p.team_id
where t.team_id in ('k01','k03')
order by t.team_name asc, p.player_name asc;

-- 아래는 서브쿼리 사용 풀이이다.
select t.team_name, t.address, p.player_name
from team t
join player p on t.team_id = p.team_id
where t.team_id in (
select p1.team_id
from player p1
where p1.team_id = 'k01' or p1.team_id = 'k03')
order by t.team_name asc, p.player_name asc;

-- 문제. k05, k07, k12구단 player_name, team_name, stadium_name을 조회하라
-- team_name asc, back_no asc
select * from team limit 5;

select p.player_name, t.team_name, s.stadium_name
from team t
join stadium s on s.stadium_id = t.stadium_id
join player p on t.team_id = p.team_id
where t.team_id in ('k05', 'k07', 'k12')
order by t.team_name asc, p.back_no asc;

case문 활용

select * from player
order by team_id asc;

-- case 사용법 1
select distinct player_name as '선수명',
	case posit
		when 'GK' then '골키퍼'
		when 'DF' then '수비수'
		when 'FW' then '공격수'
		when 'MF' then '미드필더'
		else '없음'
	end as '포지션'
from player where team_id = 'k01'
order by player_name asc;

-- case 사용법 2
select distinct player_name as '선수명',
	case
		when posit = 'GK' then '골키퍼'
		when posit = 'DF' then '수비수'
		when posit = 'FW' then '공격수'
		when posit = 'MF' then '미드필더'
		else '없음'
	end as '포지션'
from player where team_id = 'k01'
order by player_name asc;

-- 문제. case 사용하여 조회하기 1
-- 키가 190 넘으면 A
-- 키가 180 넘으면 B
-- 나머지 C
select * from player limit 10;

select player_name as '선수명', height as '키',
	case 
		when height >= 190 then 'A'
		when height >= 180 then 'B'
		else 'C'
	end as '등급'
from player
where team_id = 'K03'
order by case 
		when height >= 190 then 'A'
		when height >= 180 then 'B'
		else 'C'
	end asc,  player_name asc;

use mydb;

-- 문제. case 사용하여 조회하기 2
-- 부서번호 10 : 총무부, 20 : 홍보부, 30 : 개발 1부, 40 : 개발 2부
-- 이름이랑 부서를 조회하라
select * from dept limit 5;
select * from emp limit 5;

select e.ename as '사원명', d.deptno as '부서번호',
	case
		when e.deptno = 10 then '총무부'
		when e.deptno = 20 then '홍보부'
		when e.deptno = 30 then '개발1부'
		when e.deptno = 40 then '개발2부'
	end as '부서명'
from dept d
join emp e on d.deptno = e.deptno
order by d.deptno asc, '부서명' asc, e.ename asc;

use mydb2;

추가예제

use mydb2;

-- 문제. 각 팀별로 키가 180 이상인 선수의 숫자를 출력하기
-- team_name, 인원수
select t.team_name, count(*)
from player p
join team t on p.team_id = t.team_id
where p.height >= 180
group by t.team_name;

-- gpt 유사예제 : 각 팀별로 포지션이 df 이면서 몸무게가 75kg 이상인 선수의 수를 구하시오.
-- 출력: team_name, 인원수
select t.team_name, count(*)
from player p
join team t on p.team_id = t.team_id
where p.posit = 'df' and p.weight >= 75
group by t.team_name;

-- 문제. Fc서울의 전체 일정(sche_date)을 출력하기, 홈, away 둘다
-- 아래는 김성재의 풀이이다.
select s.sche_date, t1.team_name as 'home_team', t2.TEAM_NAME as 'away team'
from schedule s
JOIN team t1 ON s.hometeam_id = t1.team_id
JOIN team t2 ON s.awayteam_id = t2.team_id
where s.HOMETEAM_ID = 'k09' or s.AWAYTEAM_ID = 'k09'
order by sche_date asc;

-- 아래는 백현숙 강사님의 풀이이다.
select b.team_name, a.sche_date
from schedule a
join team b
on a.hometeam_id = b.team_id or a.awayteam_id = b.team_id
where team_name = 'fc서울';

-- 문제.2012년 10월 19일날 c06스타디움과 c05스타디움에서 경기하는 선수들 이름과 팀명을 출력하시오
-- 아래는 김성재의 풀이이다.
select t.team_name, p.player_name as '선수명', s2.SCHE_DATE as '일정'
FROM schedule s2
JOIN stadium s ON s2.stadium_id = s.stadium_id
JOIN team ht ON s2.hometeam_id = ht.team_id
JOIN team at ON s2.awayteam_id = at.team_id
JOIN player p ON p.team_id IN (s2.hometeam_id, s2.awayteam_id)
JOIN team t ON p.team_id = t.team_id
where (s2.SCHE_DATE = '20121019' and s.STADIUM_ID = 'c05') or
		(s2.SCHE_DATE = '20121019' and s.STADIUM_ID = 'c06')
order by t.team_name asc, p.player_name asc;

-- 아래는 백현숙강사님의 풀이이다.
select a.player_name, team_name
from player a
join team b on a.TEAM_ID = b.TEAM_ID
join schedule C on c.HOMETEAM_ID = b.TEAM_ID or c.AWAYTEAM_ID  = b.TEAM_ID
where c.STADIUM_ID in ('c05', 'c06') and c.sche_date = '20121019'
order by a.team_id;

---

과제

use sakila;
-- 1. 모든 배우(actor) 정보를 조회하세요. 다음처럼 출력이 나오게 하세요
-- +----------------------+
-- | 배우이름             |
-- +----------------------+
-- | GUINESS PENELOPE     |
-- | WAHLBERG NICK        |
-- | CHASE ED             |
-- | DAVIS JENNIFER       |
-- | LOLLOBRIGIDA JOHNNY  |
-- | NICHOLSON BETTE      |
-- | MOSTEL GRACE         |
-- | JOHANSSON MATTHEW    |
-- | SWANK JOE            |
-- | GABLE CHRISTIAN      |
-- | CAGE ZERO            |
-- | BERRY KARL           |
-- | WOOD UMA             |
-- | BERGEN VIVIEN        |
-- .........................  이하생략 
select concat(a.last_name, " ",a.first_name) as "배우이름"
from actor a;

-- 2. 성(last_name)이 'BERRY', 'HOFFMAN', 'DENCH' 인 배우를 조회하세요.  or연산자로 한번 in 연산자로 한번 두번 작성해보세요 
-- or 연산자
select a.last_name as "성"
from actor a
where a.last_name = 'BERRY' or a.last_name = 'HOFFMAN' or a.last_name = 'DENCH'
order by a.last_name asc;

-- in 연산자
select a.last_name as "성"
from actor a
where a.last_name in ('BERRY','HOFFMAN','DENCH')
order by a.last_name asc;

-- 3. 이름이 'SCARLETT'인 배우의 actor_id를 조회하세요.
select a.actor_id
from actor a
where a.first_name = 'SCARLETT';

-- 4. actor_id가 2,18,34,56,77,88,120, 199, 192, 191 인 배우의 정보를 조회하시오 출력방식은 1번 참조입니다.
select concat(a.last_name, " ",a.first_name) as "배우이름"
from actor a
where a.actor_id in (2,18,34,56,77,88,120,199,192,191)
order by concat(a.last_name, " ",a.first_name) asc;

-- 5.고객(customer) 테이블에서 이메일이 'KATHLEEN.ADAMS@sakilacustomer.org'인 고객의 전체 정보를 조회하세요.
select *
from customer c
where c.email = 'KATHLEEN.ADAMS@sakilacustomer.org';

select *
from customer c
where c.email in ('KATHLEEN.ADAMS@sakilacustomer.org');

-- 6. 고객 중에서 store_id가 1이고 last_name이 'MILLER'인 사람을 조회하세요.
select *
from customer c
where c.store_id = 1 and c.last_name = 'MILLER';

-- 7. 카테고리(category) 이름이 'Comedy'인 카테고리의 ID를 찾으세요.
select c.category_id as "카테고리ID"
from category c
where c.name = 'Comedy';

-- 8. 7일 이상 대여된(rental_duration > 7) 영화 정보를 조회하세요.
select *
from film f
where f.rental_duration > 7;

-- 9. replacement_cost가 20 이상 25 이하인 영화 목록을 조회하세요.
select *
from film f
where f.replacement_cost >= 20 or f.replacement_cost <= 25;

-- 10. title에 'ACADEMY'라는 단어가 포함된 영화 제목만 검색하세요.
select f.title as "영화제목"
from film f
where f.title like '%ACADEMY%';

-- 11. 가장 최근에 등록된 고객 5명의 이름과 이메일을 조회하세요.
select concat(c.last_name, " ", c.first_name) as "이름", c.email as "이메일"
from customer c
order by c.last_update desc limit 5;

-- 12. 'Comedy' 카테고리에 속한 모든 영화 제목을 조회하세요.
select f.title as "영화제목"
from film f
join film_category fc on f.film_id = fc.film_id
join category ct on fc.category_id = ct.category_id
where ct.name = 'Comedy'

-- 13. 고객과 대여(rental)를 조인하여 고객 이름과 대여 일자를 10건만 조회하세요.
select concat(c.last_name, " ", c.first_name) as "고객이름", r.rental_date as "대여일자"
from customer c
join rental r on c.customer_id = r.rental_id limit 10;

-- 14. 'Action' 장르의 영화를 빌린 고객 이름과 영화 제목을 조회하세요.
select concat(c.last_name, " ", c.first_name) as "고객이름", f.title as "영화제목"
from customer c
join rental r on c.customer_id = r.customer_id
join inventory i on r.inventory_id = i.inventory_id
join film f on i.film_id = f.film_id
join film_category fc on f.film_id = fc.film_id
join category ct on fc.category_id = ct.category_id
where ct.name = 'Action';

-- 15. 'Alberta'에 사는 고객의 이름과 이메일을 조회하세요.
select concat(c.last_name, " ", c.first_name) as "고객이름", c.email as "이메일"
from customer c
join address a on c.address_id = a.address_id
where a.district = 'Alberta';

-- 16. 배우 이름과 그 배우가 출연한 영화 제목을 전체에서 10건만 조회하세요.
select concat(a.last_name, " ", a.first_name) as "배우이름", f.title as "영화제목"
from actor a
join film_actor fa on a.actor_id = fa.actor_id
join film f on fa.film_id = f.film_id
limit 10;

-- 17. 각 고객이 대여한 총 횟수를 고객 이름과 함께 조회하세요.
select * from rental;
select * from payment;

select c.customer_id, count(*) as "고객별 대여횟수", concat(c.last_name, " ", c.first_name) as "배우 이름"
from customer c
join rental r on c.customer_id = r.customer_id
group by c.customer_id;

-- 18. 각 배우가 출연한 영화 수를 조회하고, 가장 많이 출연한 배우 상위 5명을 조회하세요.
select a.actor_id, count(*) as "각 배우가 출연한 영화 수", concat(a.last_name, " ", a.first_name) as "배우 이름"
from film_actor fc
join actor a on fc.actor_id = a.actor_id
join film f on fc.film_id = f.film_id
group by a.actor_id
order by count(*) desc limit 5;

-- 19. 각 스토어별로 총 매출을 조회하세요.
select * from payment limit 5; -- 총 매출..? sum(amount)

select s.store_id, sum(p.amount) as "총 매출"
from payment p
join customer c on p.customer_id = c.customer_id
join store s on c.store_id = s.store_id
group by s.store_id;

-- 20. 월별 대여 건수를 조회하세요 (예: 2005-06, 2005-07 등).
select * from rental limit 5;

select substring(r.rental_date, 1, 7) as "년월", count(*) as "대여건수"
from rental r
group by substring(r.rental_date, 1, 7)
order by substring(r.rental_date, 1, 7) asc

---

복습

gpt

use sakila;

-- ✅ 중급 SQL 연습문제 10제 (Sakila DB)
-- 가장 많이 대여된 영화 제목과 해당 대여 횟수를 조회하세요.
select f.title as "영화제목", count(*) as "대여횟수"
from film f
join inventory i on f.film_id = i.film_id
join rental r on i.inventory_id = r.inventory_id
group by f.title
order by count(*) desc limit 1;

-- 모든 배우 중에서 'Comedy' 장르 영화에 출연한 배우의 이름을 중복 없이 조회하세요.
select c.name, concat(a.last_name, " ", a.first_name)
from actor a
join film_actor fa on a.actor_id = fa.actor_id
join film f on fa.film_id = f.film_id
join film_category fc on f.film_id = fc.film_id
join category c on fc.category_id = c.category_id
where c.name = 'Comedy'
group by concat(a.last_name, " ", a.first_name)
order by concat(a.last_name, " ", a.first_name) asc;

-- 2편 이상 영화를 빌린 고객 중에서 총 결제 금액이 가장 높은 상위 5명을 이름과 총 금액으로 조회하세요.
select concat(c.last_name, " ", c.first_name)as "이름", count(*) as "빌린 영화 수", sum(p.amount)
from customer c
join rental r on c.customer_id = r.customer_id
join payment p on r.rental_id = p.rental_id
group by c.customer_id
having count(*) > 2
order by sum(p.amount) desc limit 5;

-- 'Nick'이라는 이름을 가진 배우가 출연한 모든 영화 제목을 조회하세요.
select concat(a.last_name, " ", a.first_name) as "배우이름", f.title as "영화제목"
from actor a
join film_actor fa on a.actor_id = fa.actor_id
join film f on fa.film_id = f.film_id
where concat(a.last_name, " ", a.first_name) like '%Nick%';


-- 한 번이라도 영화를 빌렸지만, 2006년 이후에는 단 한 건도 대여하지 않은 고객의 이름을 조회하세요.
select concat(c.last_name, " ", c.first_name) as "고객이름", count(*)
from customer c
join rental r on c.customer_id = r.customer_id
group by c.customer_id
having count(*) >= 1 and max(r.rental_date) < '2006-06-01 00:00:00'
order by concat(c.last_name, " ", c.first_name) asc;


-- 영화를 빌리지 않은 고객 목록을 이름과 이메일과 함께 조회하세요.
select c.customer_id, concat(c.last_name, " ", c.first_name), count(*), c.email 
from customer c
join rental r on c.customer_id = r.customer_id
group by c.customer_id
having count(*) = 0;

-- 모든 영화의 평균 replacement_cost를 구하고, 이 평균보다 높은 비용을 가진 영화 제목을 조회하세요.
select * from payment;

select f2.title as "영화제목", avg(p2.amount) as "평균비용"
from film f2
join inventory i2 on f2.film_id = i2.film_id
join rental r2 on i2.inventory_Id = r2.inventory_id
join payment p2 on r2.rental_id = p2.rental_id
group by f2.title
having avg(p2.amount) > (
select avg(p.amount)
from film f
join inventory i on f.film_id = i.film_id
join rental r on i.inventory_Id = r.inventory_id
join payment p on r.rental_id = p.rental_id)
order by f2.title asc;

-- 한 도시(city)에 사는 고객 수가 5명 이상인 도시 이름과 고객 수를 조회하세요.
select * from city;

select ci.city as "도시명", count(*) as "고객 수"
from customer c
join address a on c.address_id = a.address_id
join city ci on a.city_id = ci.city_id
group by ci.city_id
having count(*) > 5;

-- 가장 많은 영화를 소장하고 있는 스토어의 ID와 해당 스토어가 보유한 영화 수를 조회하세요.
select s.store_id, count(*)
from store s
join inventory i on s.store_id = i.store_id
group by s.store_id
order by s.store_id desc limit 1;

-- 가장 많이 사용된 고객 이메일 도메인(@ 뒷부분)을 기준으로 그룹화하여,
-- 각 도메인별 고객 수를 내림차순으로 조회하세요.
select * from customer;

select substring_index(c.email, '@', -1) as domain, count(*)
from customer c
group by domain
order by count(*) desc;