SQL 알아가기2 [실습(테이블 생성, 쿼리)]

나의 전 게시물에서 sql 기초명령어를 알아봤고, 그 명령어들을 사용해서 실습을 해보았다.
.
.
.

그럼 이제 테이블을 만들어보자.

🎄 CREATE TABLE 🎄

위에 나와있는 테이블 정보로 메모장에 만들어보았다.

create table tbl_product_01(
pcode varchar2(10) not null primary key ,
name varchar2(20) ,
cost number(10)
);

insert into tbl_product_01 values('AA01','아메리카노',3000);
insert into tbl_product_01 values('AA02','에스프레소',3500);
insert into tbl_product_01 values('AA03','카페라떼',4000);
insert into tbl_product_01 values('AA04','카라멜마끼',4500);
insert into tbl_product_01 values('AA05','카푸치노',5000);
insert into tbl_product_01 values('AA06','초코롤케익',6000);
insert into tbl_product_01 values('AA07','녹차롤케익',6500);
insert into tbl_product_01 values('AA08','망고쥬스',7000);
insert into tbl_product_01 values('AA09','핫초코',2500);

우선 create table 테이블명(
컬럼ID 형태 길이 null 키 값을 차례대로 입력해준다.
);

테이블을 만들었으면 이제 이 테이블 안에 정보를 넣어야 한다.
정보를 넣을 땐
insert into 테이블명 values( );
괄호 안엔 테이블을 만들 때 적은 컬럼 순서대로 넣어줘야 한다.
number 형태는 백틱( ' ' )으로 감싸지 않는다.
.
.

두 번째 테이블도 만들어보자.

create table tbl_shop_01(
scode varchar2(10) not null primary key ,
sname varchar2(20) 
);

insert into tbl_shop_01 values('S001','강남점');
insert into tbl_shop_01 values('S002','강서점');
insert into tbl_shop_01 values('S003','강동점');
insert into tbl_shop_01 values('S004','강북점');
insert into tbl_shop_01 values('S005','동대문점');
insert into tbl_shop_01 values('S006','인천점');

마지막 세 번째 테이블이다.

create table tbl_salelist_01(
saleno number(10) not null primary key ,
pcode varchar2(10) not null ,
saledate date ,
scode varchar2(10) not null ,
amount number(10)
);

insert into tbl_salelist_01 values('100001','AA01','20180902','S001',50);
insert into tbl_salelist_01 values('100002','AA03','20180902','S002',40);
insert into tbl_salelist_01 values('100003','AA04','20180902','S002',20);
insert into tbl_salelist_01 values('100004','AA04','20180902','S001',30);
insert into tbl_salelist_01 values('100005','AA05','20180902','S004',40);
insert into tbl_salelist_01 values('100006','AA03','20180902','S004',30);
insert into tbl_salelist_01 values('100007','AA01','20180902','S003',40);
insert into tbl_salelist_01 values('100008','AA04','20180902','S004',10);
insert into tbl_salelist_01 values('100009','AA01','20180902','S003',20);
insert into tbl_salelist_01 values('100010','AA05','20180902','S003',30);
insert into tbl_salelist_01 values('100011','AA01','20180902','S001',40);
insert into tbl_salelist_01 values('100012','AA03','20180902','S002',50);
insert into tbl_salelist_01 values('100013','AA04','20180902','S002',50);
insert into tbl_salelist_01 values('100014','AA05','20180902','S004',20);
insert into tbl_salelist_01 values('100015','AA01','20180902','S003',30);
 

전 게시물에서 설명했던 primary key(PK)와 foreign key(FK)가 나왔는데, 저렇게 만들어주면 된다.

테이블은 모두 만들었고 이 테이블을 예시로 쿼리를 짜보겠다.

🎃 QUERY 🎃

select s.saleno , s.pcode , s.saledate , s.scode , p.name , 
s.amount,p.cost* s.amount
from tbl_salelist_01 s
join tbl_product_01 p
on s.pcode = p.pcode
;

이건 (가)상품테이블에 있는 상품명과 나머지 (다)판매 테이블이 다 나오기 때문에 두 테이블을 조인해주고
맨 오른쪽에 있는 총판매액을 구하기 위해서 (다)의 판매수량과 (가)의 금액을 곱해줘야 한다.
또한, 특정 칼럼으로만 보여지는 게 아니기 때문에 group by를 하지 않는다.

.
.

select 
s.scode ,
sh.sname,
sum(p.cost* s.amount)
from tbl_salelist_01 s
join tbl_product_01 p
on s.pcode = p.pcode
join tbl_shop_01 sh
on s.scode = sh.scode
group by s.scode,sh.sname
order by 1
;

3개의 테이블 정보를 모두 사용해야 해서 join을 두번 사용했다.
매장별로 나오기 때문에 group by를 해주었다.
.
.

select 
s.pcode ,
p.name ,
sum(p.cost * s.amount)
from tbl_product_01 p
join tbl_salelist_01 s
on s.pcode = p.pcode
group by s.pcode ,p.name 
order by 1
;

이것도 두번째와 비슷하게 group by로 상품별로 나오게 했다.
.
.
.
.

👌👌 다음 시간엔 디비연동해서 이렇게 짠 쿼리를 바탕으로 프로그램을 제작해서 웹에 출력하는 것을 해보겠다. (mvc or jsp 사용)