스터디노트 (SQL 8~10)

zoe·2023년 4월 17일
sql
0

실습환경 만들기

  • zerobase 사용(이동)
use zerobase;
  • 테이블 정보 확인
desc celeb;
select * from celeb;

  • 테이블 생성
create table test1( no int );
create table test2( no int);
show tables;
  • 테스트 데이터 추가
insert into test1 values (1);
insert into test1 values (2);
insert into test1 values (3);
insert into test2 values (5);
insert into test2 values (6);
insert into test2 values (3);
  • 데이터 확인
select * from test1;
select * from test2;



Union

  • union : 여러 개의 SQL문을 합쳐서 하나의 SQL문으로 만들어주는 방법(주의. 컬럼의 개수가 같아야 한다)
  • union : 중복된 값을 제거하여 알려준다
  • union all : 중복된 값도 모두 보여준다
select column1, column2, ... from tableA
union | union all
select column1, column2, ... from tableB

  • 예제1 union all test1 의 모든 데이터와 test2 의 모든 데이터를 중복된 값을 포함하여 검색
select * from test1 union all select * from test2;
  • 예제1 union test1 의 모든 데이터와 test2 의 모든 데이터를 중복된 값을 제거하여 검색
select * from test1 union select * from test2;
  • 예제2 union all 성별이 여자인 데이터를 검색하는 쿼리와 소속사가 YG엔터테이먼트인 데이터를 검색하는 쿼리를 UNION ALL 로 실행
 select name, sex, agency from celeb where sex = 'F' union all select name, sex, agency from celeb where agency='YG엔터테이먼트';
  • 예제2 union 성별이 여자인 데이터를 검색하는 쿼리와 소속사가 YG엔터테이먼트인 데이터를 검색하는 쿼리를 UNION으로 실행
select name, sex, agency from celeb where sex = 'F' union select name, sex, agency from celeb where agency='YG엔 터테이먼트';
  • 💡 예제3 union 가수가 직업인 연예인의 이름, 직업을 검색하는 쿼리와,
    1980년대에 태어난 연예인의 이름, 생년월일, 나이를 검색하는 쿼리를 UNION으로 실행
select name, job_title from celeb where job_title like '%가수%' union select name, birthday, age from celeb where birthday between '1980-01-01' and '1980-12-31';

문제 1. 직업이 가수인 (가수를 포함하는) 데이터를 검색하는 쿼리와 직업이 텔런트인 (텔런트를 포함하는) 데이터를 검색하는 쿼리를 중복을 제거하여 합쳐서 실행

 select name, birthday, age from celeb where job_title like '%가수%' union select name, birthday, age from celeb where job_title like '%텔런트%';

💡 문제 2. 성이 이씨인 데이터를 검색하는 쿼리와 1970년대생을 검색하는 쿼리를 중복을 포함하여 합쳐서 실행

select name, birthday from celeb where name like '이%' union all select name, birthday from celeb where birthday between '1970-01-01' and '1979-12-31';



실습환경 만들기

  • zerobase 사용(이동)
use zerobase;
  • 테이블 정보 확인
desc celeb;
select * from celeb;
  • 새로운 테이블 추가
create table snl_show(
	ID int not null auto_increment primary key,
	SEASON int not null,
    EPISODE int not null,
    BROADCAST_DATE date,
    HOST varchar(32) not null);
  • 추가한 테이블 정보 확인
 desc snl_show;
  • 추가한 테이블에서 데이터 추가
insert into snl_show values (1, 8, 7, '2022-09-05', '강동원');
insert into snl_show values (2, 8, 8, '2020-09-12', '유재석');
 insert into snl_show values (3, 8, 9, '2020-09-19', '차승원');
insert into snl_show values (4, 8, 10, '2020-09-26', '이수현');
insert into snl_show values (5, 9, 1, '2021-09-04', '이병헌');
insert into snl_show values (6, 9, 2, '2021-09-11', '하지원');
insert into snl_show values (7, 9, 3, '2021-09-18', '제시');
insert into snl_show values (8, 9, 4, '2021-09-25', '조정석');
insert into snl_show values (9, 9, 5, '2021-10-02', '조여정');
insert into snl_show values (10, 9, 6, '2021-10-09', '옥주현');
  • 추가한 테이블의 데이터 확인
select * from snl_show;



JOIN

  • join : 두 개 이상의 테이블을 결합하는 것
    - inner join, full outer join, left join, right join

  • 두개의 실습 테이블 확인
select * from snl_show;



INNER JOIN

  • inner join : 두 개의 테이블에서 공통된 요소들을 통해 결합하는 조인방식 (교집합)
select column1, column2, ...
from tableA
inner join tableB
on tableA.column = tableB.column
where condition;

  • 예제1 snl_show 에 호스트로 출연한 celeb 을 기준으로 celeb 테이블과 snl_show 테이블을 INNER JOIN
select celeb.id, celeb.name, snl_show.id, snl_show.host from celeb inner join snl_show on celeb.name = snl_show.host;
<br><br>

LEFT JOIN

  • left join : 두 개의 테이블에서 공통영역을 포함해 왼쪽 테이블의 다른 데이터를 포함하는 조인방식
select column1, column2, ...
from tableA
left join tableB
on tableA.column = tableB.column
where condition;

  • 예제1 snl_show 에 호스트로 출연한 celeb 을 기준으로 celeb 테이블과 snl_show 테이블을 LEFT JOIN
select celeb.id, celeb.name, snl_show.id, snl_show.host from celeb left join snl_show on celeb.name = snl_show.host;



RIGHT JOIN

  • right join : 두 개의 테이블에서 공통영역을 포함해 오른쪽 테이블의 다른 데이터를 포함하는 조인양식
select column1, column2, ...
from tableA
right join tableB
on tableA.column = tableB.column
where condition;

  • 예제1 snl_show 에 호스트로 출연한 celeb 을 기준으로 celeb 테이블과 snl_show 테이블을 RIGHT JOIN
select celeb.id, celeb.name, snl_show.id, snl_show.host from celeb right join snl_show on celeb.name = snl_show.host;



💡 FULL OUTER JOIN

  • full outer join : 두 개의 테이블에서 공통영역을 포함하여 양쪽 테이블의 다른 영역을 모두 포함하는 조인방식

  • MySQL에서는 FULL JOIN을 지원하지 않는다

  • 아래 코드 안됨 

select column1, column2, ...
from tableA
full outer join tableB
on tableA.column = tableB.column
where conditon
  • 이렇게 변경해서 해야됨
select column1, column2, ...
from tableA
left join tableB on tableA.column = tableB.column
union
select column1, column2, ...
from tableA
right join tableB on tableA.column = tableB.column
where condition;

  • 예제1 snl_show 에 호스트로 출연한 celeb 을 기준으로 celeb 테이블과 snl_show 테이블을 FULL OUTER JOIN
select celeb.id, celeb.name, snl_show.id, snl_show.host from celeb left join snl_show on celeb.name = snl_show.host
union
select celeb.id, celeb.name, snl_show.id, snl_show.host from celeb right join snl_show on celeb.name = snl_show.host;



💡 SELF JOIN

  • self join : 동일한 컬럼이 있을 경우, 컬럼명 앞에 테이블명을 명시해야 한다, inner join과 동일한 효과
select column1, column2, ...
from tableA, tableB, ...
where conditon;

- 예제1 snl_show 에 호스트로 출연한 celeb 을 기준으로 celeb 테이블과 snl_show 테이블을 SELF JOIN 
select celeb.id, celeb.name, snl_show.id, snl_show.host from 
celeb, snl_show where celeb.name = snl_show.host;
  • 💡 예제2 celeb 테이블의 연예인 중, snl_show 에 host 로 출연했고 소속사가 안테나인 사람의 이름과 직업을 검색
select name, job_title from celeb, snl_show 
where celeb.name = snl_show.host and agency = '안테나';
  • 💡 예제3 celeb 테이블의 연예인 중, snl_show 에 host 로 출연했고, 영화배우는 아니면서 YG 엔터테이먼트 소속이거나 40세 이상이면서 YG 엔터테이먼트 소속이 아닌 연예인의 이름과 나이, 직업, 소속사, 시즌, 에피소드 정보를 검색
select name, age, job_title, agency, season, episode from celeb, snl_show 
where celeb.name = snl_show.host
and ( 
(not job_title like '%영화배우%' and agency = 'YG엔터테이먼트') 
or (age >= 40 and agency != 'YG엔터테이먼트')
);
  • 예제4 snl_show 에 출연한 연예인의 snl_show 아이디, 시즌, 에피소드, 이름, 직업 정보를 검색
select snl_show.ID, season, episode, name, job_title 
from celeb, snl_show where celeb.name = snl_show.host;
  • 💡 예제5 snl_show 에 출연한 celeb 중, 에피소드 7, 8, 10 중에 출연했거나 소속사가 YG로 시작하고 뒤에 6글자로 끝나는 사람 중 작년(2020년) 9월15일 이후에 출연했던 사람을 검색
select name, season, episode, broadcast_date, agency from celeb, snl_show 
where celeb.name = snl_show.host
and (episode in (7, 8, 10) or agency like 'YG______') 
and broadcast_date > '2020-09-15';

💡 문제 1. snl_show 에 출연한 celeb 테이블의 연예인 중, 영화배우나 텔런트가 아닌 연예인의아이디, 이름, 직업, 시즌, 에피소드 정보를 검색

select celeb.id, name, job_title, season, episode from celeb, snl_show 
where celeb.name = snl_show.host
and not (job_title like '%영화배우%' or job_title like '텔런트');

💡 문제 2. snl_show 에 출연한 celeb 중, 작년 9월 15일 이후에 출연했거나 소속사 이름이 ‘엔터테이먼트’ 로 끝나지 않으면서 영화배우나 개그맨이 아닌 연예인의 celeb 아이디, 이름, 직업, 소속사를 검색

select celeb.id, name, job_title, agency from celeb, snl_show 
where celeb.name = snl_show.host
and (broadcast_date > '2020-09-15' or agency not like '%엔터테이먼트')
and not (job_title like '%개그맨%' or job_title like '%영화배우%');



실습환경 만들기

  • zerobase 사용(이동)
use zerobase;
  • 테이블 정보 확인
desc celeb;
select * from celeb;
select * from snl_show;



💡 CONCAT

  • concat : 여러 문자열을 하나로 합치거나 연결
select concat('sting1', 'string2', ...);

- 예제1 
select concat('concat', ' ', 'test');
  • 예제2
select concat('이름: ', name) from celeb;



ALIAS

  • alias : 컬럼이나 테이블 이름에 별칭 생성, as는 생략도 가능
select column as alias
from tablename;

  • 예제1 name을 이름으로 별칭을 만들어서 검색
select name as '이름' from celeb;
  • 예제2 name 은 이름으로, agency 는 소속사로 별칭을 만들어서 검색
select name as '이름', agency as '소속사' from celeb;
  • 💡 예제3 name 과 job_title 을 합쳐서 profile 이라는 별칭을 만들어서 검색
select concat(name, ' : ', job_title) as profile from celeb;
  • 💡 예제4 snl_korea 에 출연한 celeb 을 기준으로 두 테이블을 조인하여, celeb 테이블은 c, snl_show 테이블은 s 라는 별칭을 만들어서 출연한 시즌과 에피소드, 이름, 직업을 검색
 select s.season, s.episode, c.name, c.job_title from celeb as c , snl_show as s where c.name = s.host;
  • 💡 예제5 snl_korea 에 출연한 celeb 을 기준으로 두 테이블을 조인하여 다음과 같이 각 데이터의 별칭을 사용하여 검색
    • 시즌, 에피소드, 방송일을 합쳐서 ‘방송정보’
    • 이름, 직업을 합쳐서 ‘출연자정보’
 select 
 concat(s.season, '-', s.episode, ' ( ', s.broadcast_date, ' )') as '방송정보',
 concat( name, ' ( ', job_title, ' )') as '출연자정보' 
 from celeb as c, snl_show as s where c.name = s.host;
 select concat(s.season, '-', s.episode, ' ( ', s.broadcast_date, ' )') '방송정보', concat( name, ' ( ', job_title, ' )') '출연자정보'  from celeb as c, snl_show as s where c.name = s.host;

문제 1. 이름이 3글자인 연예인 정보를 검색하여 다음과 같이 출력

select concat('이름 : ', name, ' , 소속사 : ', agency) as '연예인정보' 
from celeb;

💡 문제 2. 앞글자가 2글자이고, ‘엔터테이먼트’ 로 끝나는 소속사 연예인 중SNL 에 출연한 연예인의 신상정보(나이, 성별)와 출연정보(시즌-에피소드, 방송날짜), 소속사 정보를 방송날짜 최신순으로 정렬하여 다음과 같이 검색

 select 
 c.agency as '소속사정보',
 concat('나이 : ', c.age, '(', c.sex, ')') as '신상정보',
 concat(s.season, '-', s.episode, ' , 방송날짜 : ', s.broadcast_date) as '출연정보'
 from celeb as c, snl_show as s 
 where c.name = s.host 
 and c.agency like '__엔터테이먼트' 
 order by broadcast_date desc;



DISTINCT

  • distinct : 검색한 결과의 중복 제거
select distinct column1, column2, ...
from tablename;

  • 예제1 연예인 소속사 종류를 검색 - 중복 포함
select agency from celeb;
  • 예제1 연예인 소속사 종류를 검색 - 중복 제외 (DISTINCT)
select distinct agency from celeb;
  • 예제2 가수 중에서, 성별과 직업별 종류를 검색 - 중복 포함
select sex, job_title from celeb 
where job_title like '%가수%';
  • 예제2가수 중에서, 성별과 직업별 종류를 검색 - 중복 제외 (DISTINCT)
select distinct sex, job_title from celeb
where job_title like '%가수%';

문제 1. celeb 테이블에서 성별과 소속사 별 종류를 검색하여 성별, 소속사 순으로 정렬

select distinct sex, agency from celeb order by sex, agency;



LIMIT

  • limit : 검색결과를 정렬된 순으로 주어진 숫자만큼만 조회
select column1, column2, ...
from tablename
where codition
limit number;

  • 예제1 celeb 데이터 3개만 가져오기
select * from celeb limit 3;
  • 예제2 나이가 가장 적은 연예인 4명을 검색
select * from celeb order by age asc limit 4;

💡 문제 1. celeb 테이블에서 남자 연예인 중 나이가 가장 많은 2명을 조회

select * from celeb where sex = 'M' order by age desc limit 2;

💡 문제 2. SNL에 출연한 연예인의 정보를 최신 나이 순으로 2개만 검색하여 다음과 같이 출력

select 
concat('SNL 시즌 ', s.season, ' 에피소드 ', s.episode, ' 호스트 ', s.host) as 'SNL 방송정보', age 
from celeb as c 
inner join snl_show as s on c.name = s.host 
order by age desc 
limit 2;

💻 출처 : 제로베이스 데이터 취업 스쿨

profile
zoe
#데이터분석 #퍼포먼스마케팅 #데이터 #디지털마케팅
이전 포스트

스터디노트 (SQL 6~7)

다음 포스트

스터디노트(SQL 11 aws)

0개의 댓글