데이터 그룹화(GROUP BY, DISTINCT), 집계함수, 총계출산 WITH ROLLUP, GROUPING( ) 함수, HAVING 절과 JOIN을 배웠다. 테이블 간의 연결이 안 되어 해보고 싶던 걸 못해서 이번 JOIN을 배우며 매우 반가웠다.
-- 형태
select *
from 테이블1 as 별칭1
join 테이블2 as 별칭2
on a.컬럼명1 = b.컬럼명2
select a.Population, a.Name, b.Name, b.Continent
from city a
join country b
on a.CountryCode = b.code
where b.Continent = 'europe'
order by a.Population
limit 1;
#455 Città del Vaticano Holy See (Vatican City State)
-- 형태
select *
from 테이블1 as 별칭1
left join 테이블2 as 별칭2 #left or right
on a.컬럼명1 = b.컬럼명2
#박스오피스에서 2019년 개봉영화중 영화 유형별 최대/최소 매출액과 총매출액
select movie_type, max(sale_amt) as 최대매출액, min(sale_amt) as 최소매출액, sum(sale_amt) as 총매출액
from box_office where release_date like '2019%'
group by movie_type
order by sale_amt;
#대륙별 총면적이 넓고 총인구, 국가수가 많은 순으로
select continent, sum(SurfaceArea) as 총면적, sum(Population) as 총인구, count(*) as 국가수
from country group by continent order by 2 desc , 3 desc;
-- join과 활용
#부서별 평균봉급, 총 봉급, 인원수
select d.dept_name, avg(c.salary), sum(c.salary), count(a.emp_no)
from employees a
join dept_emp b on a.emp_no = b.emp_no
join salaries c on a.emp_no = c.emp_no
join departments d on b.dept_no = d.dept_no
group by b.dept_no;
#개봉 월별로 순위가 탑10에 있는 영화 수 구하기 # 그룹내 조건걸기 having 탑텐 영화가 2편이상
select extract(year_month from release_date) as 개봉년월, count(*) 개봉편수
from box_office where ranks between 1 and 10
group by extract(year_month from release_date)
having count(*) > 1;
-- 서브쿼리에서 활용
select year(release_date),movie_name,sale_amt
from box_office where ranks =1 having sale_amt > (select avg(sale_amt) from box_office where ranks =1);
select if(grouping(b.name) = 1,'전체도시수',b.name), count(A.Name) as '도시 개수' #if와 grouping을 활용하여 전체도시개수 별칭 지정하기
from city a
join country b
on a.CountryCode = b.code
group by b.name with rollup
order by 2 desc;
전체도시를 별칭으로 정하니 1267 에러가 난다. 영어로 바꾸면 이상없다.
Error Code: 1267. Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'if' 0.000 sec...
-- 2019년 개봉작 중 영화유형별 매출액과 총합
select if(grouping(movie_type) = 1, '전체 총합', movie_type) 영화유형, sum(sale_amt) #if(조건,조건이 맞을시반환값, 조건이 다를시 반환값)
from box_office where release_date like '2019%' and quarter(release_date) = 1 and sale_amt >= 10000000
group by movie_type with rollup #총계출산
order by 1, 2 desc;
#부서별 평균봉급, 총 봉급, 인원수
select d.dept_name, avg(c.salary), sum(c.salary), count(a.emp_no)
from employees a
join dept_emp b on a.emp_no = b.emp_no
join salaries c on a.emp_no = c.emp_no
join departments d on b.dept_no = d.dept_no
group by b.dept_no;
# 유럽에서 인구가 가장 작은 국가
select name, min(Population)
from country where Continent = 'europe';
#Holy See (Vatican City State) 1000 정답
# Albania 1000 오답: 가장 작은 인구는 맞지만 국가명과 매치하진 않는다, 단순위 맨 위 국가가 나왔다. 그래서 서브쿼리를 써야한다.
-- 서브쿼리 (한 테이블에서 집계쿼리 활용할 때)
select a.Name, b.min_Population
from (select min(Population) as min_Population from country where Continent = 'europe')b,
country a where a.Population = b.min_Population;
#Holy See (Vatican City State): 1000
-- join과 활용할 때도 서브쿼리가 필요하다.
# 세계에서 인구가 가장 작은 도시
select min(a.Population), a.Name, b.Name, b.Continent
from city a
join country b
on a.CountryCode = b.code
#42 Oranjestad Aruba North America (오답: 인구와 이름 미스매치)
# 파생 테이블 서브쿼리
select c.min_Population, a.Name, b.Name, b.Continent #인구와 이름 미스매치
from (select min(Population) as min_Population from city) c, city a
join country b
on a.CountryCode = b.code
where a.Population = c.min_Population;
#정답: 42 Adamstown Pitcairn Oceania