[오르미 백엔드 7기] (database 실습2)

show databases ;
drop database if exists `practice`;
create database `practice`;
use `practice`;
create table air_logistics (
    #unsigned 절대값
    id int unsigned not null primary key auto_increment,
    category varchar(10) not null,
    special_baggage Boolean not null comment 'true = 1 , false = 0',
    send varchar(5) not null,
    receive varchar(5) not null,
    airline varchar(10) not null,
    distance int not null,
    special_rate int unsigned not null,
    rate int unsigned not null,
    send_date datetime default CURRENT_TIMESTAMP
);

insert into air_logistics(category, special_baggage, send, receive, airline, distance, special_rate, rate, send_date) values ('식품',0,'중국','한국','대한항공',1,0,1500,"2024-11-30");
insert into air_logistics(category, special_baggage, send, receive, airline, distance, special_rate, rate, send_date) values ('기호품',0,'한국','호주','아시아나',2,0,2000,"2024-11-30");
insert into air_logistics(category, special_baggage, send, receive, airline, distance, special_rate, rate, send_date) values ('전자제품',1,'일본','한국','낫폰항공',1,1000,1500,"2024-11-30");
insert into air_logistics(category, special_baggage, send, receive, airline, distance, special_rate, rate, send_date) values ('의학품',1,'미국','한국','델타항공',4,1000,1500,"2024-12-01");
insert into air_logistics(category, special_baggage, send, receive, airline, distance, special_rate, rate, send_date) values ('식품',0,'인도','한국','고우에어',3,0,1500,"2024-12-01");
insert into air_logistics(category, special_baggage, send, receive, airline, distance, special_rate, rate, send_date) values ('일바누편',0,'한국','캐나다','대한항공',4,0,1500,"2024-12-01");
insert into air_logistics(category, special_baggage, send, receive, airline, distance, special_rate, rate, send_date) values ('의류',1,'한국','일본','아시아나',1,1000,1500,"2024-12-02");
insert into air_logistics(category, special_baggage, send, receive, airline, distance, special_rate, rate, send_date) values ('전자제품',1,'미국','한국','델타항공',4,1000,1500,"2024-12-02");
insert into air_logistics(category, special_baggage, send, receive, airline, distance, special_rate, rate, send_date) values ('주류',1,'칠레','한국','라탐항공',4,0,1000,"2024-12-03");
insert into air_logistics(category, special_baggage, send, receive, airline, distance, special_rate, rate, send_date) values ('주류',1,'독일','한국','대한항공',4,0,1000,"2024-12-03");
select * from air_logistics;
# 1.우리 공항에서 보내는 곳과 그 보낸 곳의 항공편 수가 어떻게 되는지 전체 데이터를 알고 싶습니다.
select
    send as '보내는 곳',
    count(`send`) as '항공편 수'
from
    air_logistics
-- 보내는 곳과 count를 묶어주기 위해 group by 사용
group by
    send;

# 1-1. 추가 한국의 항공편만 보고싶습니다.
# where을 사용한 경우
select send as '보내는곳', count(`send`) as '항공편수'
from air_logistics
where send = '한국'
group by send
;
# having을 사용한경우 where 로 할 수 있다면 성능상 where이 좋습니다.
select
    send as '보내는곳', count(`send`) as '항공편수'
from air_logistics
group by send
having send = '한국'
;
# 1-3 미국에서 우리 공항으로 들어오는 물류의 항공편이 몇건인지
select
    count(`send`) as '미국에서 들어오는 항공편 수'
from air_logistics
where send = '미국' and receive = '한국';

-- 품류 관리표
create table `category_tb`(
    id int unsigned not null primary key auto_increment,
    category varchar(10) not null,
    special_baggage Boolean not null comment 'true = 1 , false = 0'
);
insert into category_tb(category, special_baggage) values ('식품',false);
insert into category_tb(category, special_baggage) values ('기호품',false);
insert into category_tb(category, special_baggage) values ('전자제품',true);
insert into category_tb(category, special_baggage) values ('의학품',true);
insert into category_tb(category, special_baggage) values ('식품',false);
insert into category_tb(category, special_baggage) values ('일바누편',false);
insert into category_tb(category, special_baggage) values ('의류',true);
insert into category_tb(category, special_baggage) values ('전자제품',true);
insert into category_tb(category, special_baggage) values ('주류',true);
insert into category_tb(category, special_baggage) values ('주류',true);
select * from category_tb;
-- 원본 테이블에 품류 번호 컬럼 추가
alter table air_logistics add column category_id int unsigned not null after id;


desc air_logistics;
select * from air_logistics;
select * from category_tb;
-- 두 테이블을 join하여동일한 컬럼 값으로 변경
# update 값을바꿀테이블 join 엮을테이블 on 공통된컬럼 set 바꿀테이블에수정할값;
update air_logistics a
join
    category_tb b
on a.category = b.category
set a.category_id = b.id;
-- 외래키 추가
alter table air_logistics add foreign key (category_id) references category_tb(id);
-- 이제 엮였으므로 원본에서 불필요한 catgory와 special_beggage 삭제
alter table air_logistics
drop column category,
drop column special_baggage;

# 1-4특별수하물이 0일때는 O 아닐때는 x 조회
-- case를 사용할 경우 반드시 end를 쓴다 -> 이 sql은 switch case문처럼 작동
select
    a.category as '품류',
    case
        -- case의 조건
        when a.special_baggage = true
        -- 참이라면
        then 'O'
        -- 거짓이라면 (생략가능)
        else 'X'
        -- 종료했을 때 결과 (생략가능)
    end as '특별_수하물_여부'
from category_tb a;
# 쪼개놓은 테이블 2개를 다시 원본테이블로 표시하는 방법
-- select from 왼쪽테이블 join 오른쪽테이블 on 공통된 컬럼
# join은 left join 혹은 right join 중 하나를 선택할 수 있으며
# 만약 left join을 한다면 on에 있는 공통된 컬럼에서 왼쪽값을 기준으로 일치하는 행을 넣고 없다면 null 값을 넣는다.
select
    a.id as '번호',
    b.category as '품류',
    b.special_baggage as '특별 수하물 여부',
    a.send as '보내는 곳',
    a.receive as '받는 곳',
    a.airline as '항공편',
    a.distance as '거리',
    a.special_rate as '특별운임비',
    a.rate as '운임비',
    a.send_date as '보내는 날짜'
from
    air_logistics a
right join
    category_tb b
on a.category_id = b.id ;
select * from air_logistics;
select * from category_tb;

-- 거리탈 운임 표
CREATE TABLE `distance_charge` (
    id int unsigned not null primary key auto_increment,
    `culture_code` VARCHAR(5) NOT NULL UNIQUE,
    distance int not null,
    rate int unsigned not null
);

-- 데이터 추가
INSERT INTO `distance_charge`
SET `culture_code` = '아시아',
    `distance` = 1,
    `rate` = 1500
;

INSERT INTO `distance_charge`
SET `culture_code` = '오세아니아',
    `distance` = 2,
    `rate` = 2000
;

INSERT INTO `distance_charge`
SET `culture_code` = '인도',
    `distance` = 3,
    `rate` = 2500
;

INSERT INTO `distance_charge`
SET `culture_code` = '유럽-미국',
    `distance` = 4,
    `rate` = 3000
;

-- 문화권 테이블
CREATE TABLE `culture` (
    `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `culture_code` VARCHAR(5) NOT NULL,
    `nation` VARCHAR(5) NOT NULL UNIQUE
);
-- 데이터추가

INSERT INTO `culture`
SET `culture_code` = '아시아',
    `nation` = '한국'
;

INSERT INTO `culture`
SET `culture_code` = '아시아',
    `nation` = '일본'
;

INSERT INTO `culture`
SET `culture_code` = '아시아',
    `nation` = '중국'
;

INSERT INTO `culture`
SET `culture_code` = '오세아니아',
    `nation` = '호주'
;

INSERT INTO `culture`
SET `culture_code` = '인도',
    `nation` = '인도'
;

INSERT INTO `culture`
SET `culture_code` = '유럽-미국',
    `nation` = '독일'
;

INSERT INTO `culture`
SET `culture_code` = '유럽-미국',
    `nation` = '칠레'
;

INSERT INTO `culture`
SET `culture_code` = '유럽-미국',
    `nation` = '미국'
;

INSERT INTO `culture`
SET `culture_code` = '유럽-미국',
    `nation` = '캐나다'
;

alter table air_logistics
drop column distance,
drop column rate;
select * from air_logistics;

-- 외래키 추가

# alter table
#     draft
#     add constraint
#         fk_export_nt foreign key(target_nation)
#             references
#                 culture(nation)
# ; 이코드는fk_export_nt 제약조건 이름
alter table air_logistics add foreign key(receive) references  culture(nation);
# constraint를 넣는 이유가 뭐지? 없어도 똑같지 않나?
alter table culture add constraint foreign key(culture_code) references distance_charge(culture_code);


-- 특별 수하물 운임표
CREATE TABLE `special_charge_table`(
    id int unsigned not null primary key auto_increment,
    special_baggage Boolean not null comment 'true = 1 , false = 0',
    special_rate int unsigned not null
);
-- 데이터 추가
INSERT INTO `special_charge_table`
SET special_baggage = true,
    `special_rate` = 1000
;

INSERT INTO `special_charge_table`
SET special_baggage = false,
    `special_rate` = 0
;
drop table special_charge_table;
-- 외래키 추가
alter table category_tb add constraint foreign key (special_baggage) references special_charge_table(special_baggage);

-- 칠레로 가는 전자제품의 운임(특별운임 포함)
select
    (
        select st.category
        from category_tb st
        where st.category = '전자제품'
    ) as '품류'
     , c.nation
     , ((
            select dc.rate
            from distance_charge dc
                     left join culture c
                               on dc.culture_code = c.culture_code
            where c.nation = '칠레'
              and
                c.culture_code = dc.culture_code
        )
    +
        (
            select sct.special_rate
            from special_charge_table sct
                     left join category_tb s
                               on sct.special_baggage = s.special_baggage
            where s.category = '전자제품'
        ))
      as '운임'
from
    culture c
where
    c.nation = '칠레'
;

후기

중간에 놓쳐서 뒤에가 조금 더 이해가 안갔던 것 같습니다. 혼자 차근차근 하니 막상 그렇게 많은 내용은 아니였던 것 같고 강사님이 뒤에 문제가 더 많았다고 들었던 것 같은데 강의 때 조금더 잘 따라갔다면 더 많이 풀 수 있지 않았을까 하는 아쉬움이 남습니다.