01.27(7일차)

select (select 1);

use world;

# 단일행 서브쿼리 -> 서브쿼리의 결과가 1개
select name, headofstate from country
where code = (select code from country where name = 'Sweden');

# 다중행 서브쿼리 -> 서브쿼리의 결과가 여러개
select name, headofstate from country
where name in (select name from country where name like('S%'));


select * from city where countrycode = (
select countrycode from city where name = 'seoul');

# 첫 번째 실습
# 1
select * from city order by population desc;
select countrycode from city where id = 1024; # 서브쿼리
select * from city where countrycode = (
select countrycode from city where id = 1024);
# 2
select * from country order by population desc;
select code from country where name = 'China'; # 서브 쿼리
select * from city where countrycode = (
select code from country where name = 'China');

# ANY -> 전체 중 하나라도 만족 -> 만족
select * from city where population > any(
select population from city where district = 'New York');

# ALL -> 전체를 모두 만족 -> 만족
select * from city where population > all(
select population from city where district = 'New York');

# 두 번째 실습
# 1
select population from country where continent = 'Asia'; # 서브 쿼리
select * from country where population > any(
select population from country where continent = 'Asia');

# 2
select code from country where continent = 'Europe'; # 서브 쿼리
select * from city where countrycode in (
select code from country where continent = 'Europe');

# 다중 조건문 -> if 내부에 또다른 if 작성
select if(population > 10000000, 
if(population > 15000000, 'A1', 'A2'), 
if(population > 5000000, 'B1', 'B2')) as pop from country;

# 세 번째 실습
# 1
select if(percentage > 50,
 if(percentage > 75, 'A1', 'A2'),
 if(percentage > 25, 'B1', 'B2')) as per from countrylanguage;
 
 # 2
 select 
 case
 when (percentage > 75) then 'A1'
 when (percentage > 50) then 'A2'
 when (percentage > 25) then 'B1'
 else 'B2'
 end as per
 from countrylanguage;
 
 
 use join_sample;
 select * from dept, emp;
 
# inner join -> 조건에 일치가 되는 결과만 가져온다
select * from emp inner join dept on emp.deptid = dept.id; # join 명시

select emp.id, emp.regDate, emp.name, dept.deptName from emp
inner join dept on emp.deptid = dept.id;

select * from emp, dept where emp.deptid = dept.id; # where 명시 -> join 

select emp.id, emp.regDate, emp.name, dept.deptName from emp, dept
where emp.deptid = dept.id;

select emp.id as '사원번호', emp.name as '이름', emp.regdate as '입사날짜', 
dept.deptname as '부서명' from emp
inner join dept on emp.deptid = dept.id;

select emp.id as '사원번호', emp.name as '이름', emp.regdate as '입사날짜', 
dept.deptname as '부서명' from emp, dept
where emp.deptid = dept.id;

use inner_sample2;

# 네 번째 실습
# 1
select * from girl_group, song;

# 2
select * from girl_group as gg
inner join song as s on gg.hit_song_id = s.id;

# 3
select * from girl_group as gg, song as s
where gg.hit_song_id = s.id;

# 4
select gg.name, s.title from girl_group as gg
inner join song as s on gg.hit_song_id = s.id;

# 5
select gg.name, s.title from girl_group as gg, song as s
where gg.hit_song_id = s.id;

use sale;

select * from emp, dept
where emp.dept_id = dept.dept_id;

select emp.emp_id as '사원번호',
emp.name as '이름',
emp.tel as '전화번호',
emp.email as '이메일',
emp.date as '입사날짜',
emp.dept_id as '부서 번호',
dept.name as '부서명',
dept.tel as '부서 전화번호'
from emp, dept
where emp.dept_id = dept.dept_id;

# 다섯 번째 실습
# 1
select * from emp, pro
where emp.emp_id = pro.emp_id;

# 2
select * from emp, cus
where emp.emp_id = cus.emp_id;

use join_sample;

# left join -> 왼쪽 테이블을 기준으로 조인 연산을 적용
# 왼쪽 테이블에 있는 데이터는 전부 가져온다.
insert into emp(name, deptid) value('김근형', 3);
select * from emp;
select * from emp, dept where emp.deptid = dept.id;

select * from emp left join dept on emp.deptid = dept.id; # join 명시
select * from dept left join emp on dept.id = emp.deptid; # join 명시

select * from emp left join dept on emp.deptid = dept.id
where dept.id is not null; # inner join과 같은 결과 도출 -> 교집합

select * from emp left join dept on emp.deptid = dept.id
where dept.id is null; # 차집합

# 여섯 번째 실습
# 1
select * from girl_group as gg
left join song as s on gg.hit_song_id = s.id;

# 2
select * from song as s
left join girl_group as gg on s.id = gg.hit_song_id;

# 3
select * from girl_group as gg
left join song as s on gg.hit_song_id = s.id
where s.id is not null; # 교집합 결과

select * from song as s
left join girl_group as gg on s.id = gg.hit_song_id
where gg.hit_song_id is not null; # 교집합 결과

# 4
select * from girl_group as gg
left join song as s on gg.hit_song_id = s.id
where s.id is null; # 차집합 결과

select * from song as s
left join girl_group as gg on s.id = gg.hit_song_id
where gg.hit_song_id is null; # 차집합 결과

# 5
select s.title as '제목', gg.name as '가수' from song as s
left join girl_group as gg on s.id = gg.hit_song_id;

# right join -> 오른쪽 테이블을 기준으로 데이터를 도출한다.
# 오른쪽 테이블에 있는 데이터들은 모두 가져온다.
use join_sample;

select * from dept right join emp on dept.id = emp.deptid;
select * from emp right join dept on emp.deptid = dept.id;

select * from dept right join emp on dept.id = emp.deptid
where dept.id is not null; # 교집합 결과

select * from dept right join emp on dept.id = emp.deptid
where dept.id is null; # 차집합 결과

drop database if exists union_sample;

create database union_sample;

use union_sample;

create table A(str varchar(8) primary key);
create table B(str varchar(8) primary key);

insert into A values('A'), ('B'), ('C'), ('D');
insert into B values('C'), ('D'), ('E'), ('F');

select * from A
union # 중복 X
select * from B;

select * from A
union all # 중복 포함
select * from B;

create table tableA(
no int unsigned not null primary key,
    id varchar(16) not null,
    name varchar(16) not null);

create table tableB(
no int unsigned not null primary key,
    id varchar(16) not null,
    name varchar(16) not null);

insert into tableA values(1, '100', 'value 100');
insert into tableA values(2, '101', 'value 101');
insert into tableA values(3, '102', 'value 102');
insert into tableA values(4, '103', 'value 103');
insert into tableA values(5, '104', 'value 104');
insert into tableA values(6, '105', 'value 105');
insert into tableA values(7, '106', 'value 106');
insert into tableA values(8, '107', 'value 107');

insert into tableB values(1, '101', 'value 101');
insert into tableB values(2, '103', 'value 103');
insert into tableB values(3, '106', 'value 106');
insert into tableB values(4, '107', 'value 107');
insert into tableB values(5, '110', 'value 110');

select id from tableA
union
select id from tableB;

select id from tableA
union all
select id from tableB;

select id, name from tableA
union
select id, name from tableB;

select id, name from tableA
union all
select id, name from tableB;

select no, id, name from tableA
union
select no, id, name from tableB;

select no, id, name from tableA
union all
select no, id, name from tableB;

# 데이터(레코드)의 중복은 모든 것이 다 일치해야 중복이 된다.

# 차집합 -> 집합A에서 집합B를 뺀 결과
use union_sample;
select * from A where str not in( # 차집합
select distinct * from B);

select * from A left join B on a.str = b.str
where b.str is null; # 차집합

select a.str from A left join B on a.str = b.str
where b.str is not null; # 교집합

select a.str from A where str in( # 교집합
select distinct * from B);

# 대칭 차집합 -> 합집합 - 교집합
select str from(
select str from A
union all
select str from B) as tmp
group by str having count(*) = 1;

select str from A where str not in(select distinct str from B)  # A - B
union all
select str from B where str not in(select distinct str from A); # B - A