쇼핑몰과 카테고리 1단계, 2단계

 

1단계

 

Table 설계 및 Data 입력

use mydb3;

create table tb_categories(
    category_id int auto_increment primary key,
    category_name varchar(100) not null
);

insert into tb_categories(category_name) values('상의');
insert into tb_categories(category_name) values('하의');
insert into tb_categories(category_name) values('외투');

create table tb_products(
    product_id int auto_increment primary key,
    product_name varchar(255),
    category_id int, -- 카테고리 없는 경우
    price decimal(10, 2),
    size enum('S', 'M', 'L', 'XL'),
    color varchar(50),
    foreign key(category_id) references tb_categories(category_id)
);

insert into tb_products(product_name, category_id, price, size, color)
values('티셔츠', 1, 19900, 'M', '흰색');

insert into tb_products(product_name, category_id, price, size, color)
values('청바지', 2, 49000, 'L', '청색');

insert into tb_products(product_name, category_id, price, size, color)
values('자켓', 3, 99000, 'L', '검정색');

insert into tb_products(product_name, category_id, price, size, color)
values('셔츠', 1, 30000, 'S', '보라색');

-- 특정 카테고리에 속하는 모든 상품을 조회 쿼리
select p.product_name, p.price, p.size, p.color
from tb_products as p
join tb_categories as c 
on p.category_id = c.category_id
where c.category_name = '상의';

 

 

 

2단계

 

tb_categories Table 설계 및 Data 입력

create database demo3;
use demo3;

-- 카테고리 테이블(상위, 하위 개념 추가)
create table tb_categories(
    category_id int auto_increment primary key,
    parent_id int null,
    category_name varchar(100) not null,
    foreign key(parent_id) references tb_categories(category_id)
);

insert into tb_categories(category_name) values('남성복');
insert into tb_categories(category_name) values('여성복');

 

-- 남성복의 하위 카테고리
insert into tb_categories(category_name, parent_id) values('팬츠', 1);
insert into tb_categories(category_name, parent_id) values('셔츠', 1);

-- 여성복의 하위 카테고리
insert into tb_categories(category_name, parent_id) values('팬츠', 2);
insert into tb_categories(category_name, parent_id) values('셔츠', 2);

-- 남성복 팬츠의 하위 카테고리
insert into tb_categories(category_name, parent_id) values('면바지', 3);
insert into tb_categories(category_name, parent_id) values('슬랙스', 3);

-- 여성복 팬츠의 하위 카테고리
insert into tb_categories(category_name, parent_id) values('면바지', 5);
insert into tb_categories(category_name, parent_id) values('슬랙스', 5);

 

 

tb_products Table 설계 및 Data 입력

use demo3;
create table tb_products(
    product_id int auto_increment primary key,
    product_name varchar(255),
    category_id int,
    price decimal(10,2),
    size varchar(10),
    color varchar(50),
    foreign key(category_id) references tb_categories(category_id)
);

-- 남성복 상품 입력
insert into tb_products(product_name, category_id, price, size, color)
values('남성셔츠', 4, 49000, 'L', '퍼플'),
      ('남성면바지', 7, 59000, 'M', '핑크'),
      ('남성슬랙스', 8, 105000, 'XL', '샤인');
      
-- 여성복 상품 입력      
insert into tb_products(product_name, category_id, price, size, color)
values('여성셔츠', 6, 49000, 'L', '퍼플'),
      ('여성면바지', 9, 59000, 'M', '핑크'),
      ('여성슬랙스', 10, 105000, 'XL', '샤인');

 

1단계(남성, 여성 팬츠(특정 카테고리)에 속하는 상품 모두 조회)

select p.product_name, p.price, p.size, p.color
from tb_products as p
join tb_categories as c
on p.category_id = c.category_id
where c.category_name = '면바지';

 

2단계(여성 면바지만 조회)

select p.product_name, p.price, p.size, p.color, c.parent_id
from tb_products as p
join tb_categories as c
on p.category_id = c.category_id
where c.category_name = '면바지' and c.parent_id = 5;

 

2단계 -- Subquery 사용

subquery : 쿼리 안의 쿼리

-- c.parent_id = 5의 '5'를 select 구문(subquery)으로...
select p.product_name, p.price, p.size, p.color, c.parent_id
from tb_products as p
join tb_categories as c
on p.category_id = c.category_id
where c.category_name = '면바지' 
and c.parent_id = 
(select category_id from tb_categories where category_name = '팬츠' and parent_id = 2);

 

 

Subquery 작성 과정

-- 여성, 남성 팬츠 모두 출력됨.(다중행으로 출력되었으나 단일행으로 들어가야 함.)
select * from tb_categories where category_name = '팬츠';

-- 여성 팬츠만 출력됨.(단일행)(여성복 -> 팬츠)
select * from tb_categories where category_name = '팬츠' and parent_id = 2;

-- 여성 팬츠의 parent_id = 2만 출력됨.
select parent_id from tb_categories where category_name = '팬츠' and parent_id = 2;

-- 여성 팬츠의  category_id = 5만 출력됨.
select category_id from tb_categories where category_name = '팬츠' and parent_id = 2;

 

 

예제 문제

-- 문제 1. 남성복 셔츠만 출력하시오. 
-- 예시 1
select p.product_name, p.price, p.size, p.color, c.parent_id
from tb_products as p
join tb_categories as c
on p.category_id = c.category_id
where product_name = '남성셔츠';

-- 예시2
select p.product_name, p.price, p.size, p.color, c.parent_id
from tb_products as p
join tb_categories as c
on p.category_id = c.category_id
where c.category_name = '셔츠' and c.parent_id = 1;

-- 예시3(subquery 사용)
select p.product_name, p.price, p.size, p.color, c.parent_id
from tb_products as p
join tb_categories as c
on p.category_id = c.category_id
where c.category_name = '셔츠'
and c.parent_id
= (select parent_id from tb_categories where category_name = '셔츠' and parent_id = 1);

-- 남성, 여성 셔츠 전부 출력(다중행)
select * from tb_categories where category_name = '셔츠'; 

-- 남성 셔츠만 출력(단일행) (남성복 -> 셔츠)
select * from tb_categories where category_name = '셔츠' and parent_id = 1; 

-- 남성 셔츠의 parent_id만 출력
select parent_id from tb_categories where category_name = '셔츠' and parent_id = 1;

 

 

-- 문제 2. 여성복 슬랙스만 출력하시오.
-- 예시1
select p.product_name, p.price, p.size, p.color, c.parent_id
from tb_products as p
join tb_categories as c
on p.category_id = c.category_id
where product_name = '여성슬랙스';

-- 예시2
select p.product_name, p.price, p.size, p.color, c.parent_id
from tb_products as p
join tb_categories as c
on p.category_id = c.category_id
where c.category_name = '슬랙스' and c.parent_id = 5;

-- 예시3(subquery 사용)
select p.product_name, p.price, p.size, p.color, c.parent_id
from tb_products as p
join tb_categories as c
on p.category_id = c.category_id
where c.category_name = '슬랙스'
and c.parent_id
= (select parent_id from tb_categories where category_name = '슬랙스' and parent_id = 5);

-- 남성, 여성 슬랙스 전부 출력(다중행)
select * from tb_categories where category_name = '슬랙스';

-- 여성 슬랙스만 출력(단일행) (여성복 -> 팬츠 -> 슬랙스)
select * from tb_categories where category_name = '슬랙스' and parent_id = 5;

-- 여성 슬랙스의 parent_id = 5 만 출력됨.
select parent_id from tb_categories where category_name = '슬랙스' and parent_id = 5;