create table tbl_01(
A varchar2(10),
B varchar2(10),
C varchar2(10)
);
create table tbl_02(
B varchar2(10),
C varchar2(10),
D varchar2(10)
);
insert into tbl_01 values('a1', 'b1', 'c1');
insert into tbl_01 values('a2', 'b1', 'c1');
insert into tbl_01 values('a3', 'b1', 'c2');
insert into tbl_01 values('a4', 'b2', 'c3');
--select * from tbl_01;
insert into tbl_02 values('b1', 'c1', 'd1');
insert into tbl_02 values('b1', 'c1', 'd2');
insert into tbl_02 values('b2', 'c3', 'd3');
insert into tbl_02 values('b3', 'c3', 'd3');
--select * from tbl_02;
--1. 자연조인 실습
select *
--from tbl_01 natural join tbl_02;
from tbl_01 full outer join tbl_02 using(B,C); --using은 생으로 값이 다 나옴, USING이 기니 괄호 () 안에 짧게 들어간다~ 라고 외우기
--from tbl_01 full outer join tbl_02 on(tbl_01.b=tbl_02.b and tbl_01.c=tbl_02.c); --on은 중복제거가 되어서 나옴, on이 짧으니 괄호 () 안에 길게 들어간다~ 라고 외우기
--using 과 on의 차이점!! -> 시험
--using = 생으로
--on = 중복제거
________________________
--select count(*)
--from tbl_01 full outer join tbl_02 using(B,C);
--> 7
-- 모든 튜플의 개수 출력
-----------------------------
--select count(d)
--from tbl_01 full outer join tbl_02 using(B,C);
--> 6
--선택한 열의 튜플의 개수만 출력
--()괄호 안 알고싶은 열의 속성을 넣음
-------------------
--select count(distinct d)
--from tbl_01 full outer join tbl_02 using(B,C);
-->3
--선택한 열의 튜플의 개수를 출력, 그런데 중복되는 속성 제거
--()괄호 안 알고싶은 열의 제목 속성을 넣음
마당서점 문제
-- 마당서점의 고객이 요구하는 다음 질문에 대해 sql문을 작성하시오
--(1) 도서번호가 1인 도서의 이름
--select bookname
--from book
--where bookid=1;
--(2)가격이 20000원 이상인 도서의 이름
--select bookname
--from book
--where price>=20000;
--(3)박지성의 총 구매액(박지성의 고객번호는 1번으로 놓고 작성)
--select sum(saleprice)
--from orders
--where custid = 1;
--조인으로 푸는 방법
--select sum(saleprice)
--from customer c, orders o --자연 조인과 같음
--where c.custid=o.custid and name like '박지성';
--(4)박지성이 구매한 도서의 수(박지성의 고객번호는 1번으로 놓고 작성)
--select count(custid)
--from orders
--where custid = 1;
--조인으로 푸는 법
--select name, count(*)
--from customer c, orders o
--where c.custid=o.custid and name like '%박지성%'
--group by c.name; --여러 개로 흩어져 있는 것을 모아서 보려고
--group by를 안하면 박지성이라는 name은 3개가 나오고, count는 행 1개가 나오니 name을 1개로 묶어주기
--2. 마당서점의 운영자와 경영자가 요구하는 다음 질문에 대해 sql 문을 작성하시오
--(1) 마당서점 도서의 총 개수
--select count(*) from book;
--(2) 마당서점에 도서를 출고하는 출판사의 총 개수
--select count(distinct publisher)
--from book;
--(3)모든 고객의 이름, 주소
--select name, address
--from customer;
--(4)2020년 7월 4일에서 7월 7일 사이에 주문받은 도서의 주문번호
--select orderid
--from orders
--where orderdate between '2020-07-04' and '2020-07-07';
--(5)2020년 7월 4일에서 7월 7일 사이에 주문받은 도서를 제외한 도서의 주문번호
--select orderid
--from orders
--where orderdate not between '2020-07-04' and '2020-07-07';
--(6)성이 '김'씨인 고객의 이름과 주소
--select name, address
--from customer
--where name like '김%';
--(7)성이 '김'씨이고 이름이 '아'로 끝나는 고객의 이름과 주소
--select name, address
--from customer
--where name like '김%아';--시험
--orders(테이블) X customer(테이블) (카디션 프로덕트)
select * from orders, customer;
--차수(열,애트리뷰트) = 9, 카디널리티(행,투플) : 50
--조건에 맞게 조인시키는 건 내추럴조인
--내추럴 조인하면 10개의 행이 나옴
___
--시험2
--고객별로 주문한 모든 도서의 총 판매액을 구하고 고객별로 정렬하시오
select custid, name, sum(saleprice)
from customer c, orders o
where c.custid = o.custid
group by c.name --select문에 name이 있으니
order by c.name;
--select 절에 custid를 넣고싶다면 group by에도 custid가 들어가야함
--group by로 묶어준 이후로 select 절에는 group by에서 사용한 속성과 집계함수만 나올 수 있음
__
--left,right,full outer join출제함
select *
1. from R left outer join s on (R.B = S.B and R.C=S.C)
2. using(b,c)
on은 중복제거가 됨
using은 안됨
__
--부속 질의 2문제(너무 깊게는 안나감)
--질의문 안에 질의문이 중복되어 있는 것
--어떤 결과값이 나오는지가 질문
--가장 비싼 도서의 이름을 보이시오
select bookname
from book
where price=(select max(price) from book);
--도서를 구매한 적 있는 고객의 이름
select name
from customer
where in (select custid from orders);
--세개는 너무 많아....
EXISTS
--조건에 맞는 튜플이 존재하면 결과에 포함시킴
--즉 부속질의문의 어떤 행이 조건에 만족하면 참
--not exists는 부속질의문의 모든 행이 조건에 만족하지 않을 때만 참
--not exists뒤의 행이 잘못되지 않은지 잘 보기!!!
--NOT EXISTS는 부속 질의에서 일치하는 행이 하나도 없을 때 TRUE를 반환하고,
--일치하는 행이 하나라도 있으면 FALSE를 반환
--Exist뒤에는 무조건 서브쿼리(부속질의)가 옴
--IN은 숫자같은 게 와도 됨
-- 예 ) where custid in(1,2,3,4)
--도서를 구매한 적이 있는 고객의 이름과 주소를 검색하시오
select name,address
from customer cs
where exists(select * from orders od where cs.custid = od.custid);
-- 조인했을 때 해당 튜플이 orders 테이블이 존재하니?
-- > exixts는 박세리빼고 모든 튜플
-- > not exist는 박세리 튜플만 나옴
--in을 사용한 쿼리문
--도서를 구매한 적이 있는 고객의 이름을 검색하시오
select name
from customer
where custid in (select custid from orders)
--in은 데이터가 있느냐 물어보는 것