데이터베이스 수업 파일 백업(4월 2일, 4월 9일)

 

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은 데이터가 있느냐 물어보는 것