오라클 실습 (외부조인/중첩질의문)

--외부조인(outer join)
--내부조인(inner join) : (지난 시간에 배운)일반적인 조인

--학생 테이블
create table student(
    s_id number, 
    s_name varchar2(20),
    d_code varchar2(20), 
    primary key(s_id)
);
--학과 테이블

create table department(
    d_name varchar2(20),
    d_code varchar2(20),
    primary key(d_code)
    );

insert into student values(1, '철수', 'A');
insert into student values(2, '영희', 'B');
insert into student values(3, '철수', 'NULL');

insert into department values('A', 'SW과');
insert into department values('B', '임베과');
insert into department values('C', '정보과');

commit;

drop table student;
drop table department;

select * from student;
select * from department;

select *
from student s, department d
where s.d_code = d.d_code;

--left outer join: 왼쪽에 있는 테이블의 정보를 억지로 null을 넣어서까지 출력하는 것
select *
from student s left outer join department d on s.d_code=d.d_code;

--right outer join: 오른쪽에 있는 테이블의 정보를 억지로 출력하는 것
select *
from student s right outer join department d on s.d_code=d.d_code;

--full outer join: 양쪽 테이블에 있는 정보를 억지로 다 출력하는것
select *
from student s full outer join department d on s.d_code=d.d_code;

--on 은 where의 역활을 함(조건 명시)

--inner join의 문법 2(on을 적어 주어야 함)
select *
from student s inner join department d on s.d_code = d.d_code;

--Q1. 도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매 가격을 구하시오
select name, saleprice
from customer c left outer join orders o on c.custid  = o.custid;

--부속질의문 (중첩질의문)
--마치 c언어의 이중 for문 느낌
--select안에 select가 있는 것

--Q2. 가격이 가장 비싼 도서의 이름은 무엇인가>
--두 단계로 나눠서 생각해볼 수 있음
--1단계. 가격이 가장 비싼 도서 
--2단계. where절에 where price=?(물음표안에 가격넣기)

--1단계

select max(price)
from book;

--2단계

select bookname
from book
where price=35000;

--위 두 단계를 하나로 합친다면?
select bookname
from book
where price=(select max(price)
             from book);
             
--Q3. 도서를 구매한 적 있는 고객의 이름을 출력하시오

--1단계, 도서를 구매한 적 있는 고객id구하기
select distinct custid
from orders;


--2단계, 중첩질의문을 사용해 답 완성
select name
from customer
where CUSTID IN (SELECT DISTINCT CUSTID
            FROM orders);
            
--c언어에서의 3중 for 문
--sql 3중첩 질의문 (3단계로 접근)

--1단계.book테이블에서 출판사가 대한미디어인 도서번호를 구하기
select bookid
from book
where publisher like '대한미디어';

--2단계. 이 도서번호를 이용하여 orders테이블에서 도서를 주문한 고객의 고객번호 구하기
select custid
from orders
where bookid in(select bookid
                from book);
--3단계. 이 고객번호를 이용하요 customer테이블에서 고객의 이름을 구하기
select name
from customer
where custid in(select custid
                from orders
                   where bookid in(select bookid
                        from book));
select * from customer;
select * from book;
select * from orders;

commit ;

--Q1(중첩 질의문으로 해결) 대한미디어에서 출판한 도서를 구매한 고객의 이름을 구하시오
--1단계.book테이블에서 출판사가 대한미디어인 도서번호를 구하기
select bookid
from book
where publisher like '대한미디어';
--2단계. 이 도서번호를 이용하여 orders테이블에서 도서를 주문한 고객의 고객번호 구하기
select custid
from orders
where bookid in(select bookid
                from book);
--3단계. 이 고객번호를 이용하요 customer테이블에서 고객의 이름을 구하기
select name
from customer
where custid in(select custid
                    from orders 
                        where bookid in(select bookid  
                                        from book 
                            where publisher like '대한미디어'));


--중첩질의문과 조인의 공통점!
--Q2. (조인으로 해결) 대한미디어에서 출판한 도서를 구매한 고객의 이름을 구하시오
select name
from customer c, book b, orders o
where c.custid=o.custid and o.bookid=b.bookid and publisher like '대한미디어';

--Q3. 박지성의 총 구매액 (조인으로 푸시오)
select sum(saleprice)
from customer c, orders o
where c.custid=o.custid and name like '박지성';

--Q3. 박지성의 총 구매액 (중첩 질의문으로 푸시오)
select sum(saleprice)
from orders
where custid in(select custid
                from customer
                where name like('박지성'));

select * from customer;
select * from book;
select * from orders;

--가장 비싼 책을 구매한 사람의 전화번호를 구하시오
-- 1단계 
select max(saleprice)
from orders;
--2단계
select phone
from customer
where order;
--이 문제 폐기

--주소에 대한민국이 들어간 사람들이 산 책들의 총 금액
select sum(saleprice)
from orders o, customer c
where o.custid=c.custid and address like '%대한민국%';

--Q5.select 뒤에 오는 자료형이 숫자라면 두개의 속성으로 사칙연산을 할 수 있다.
select bookname, price-saleprice
from book b, orders o
where o.bookid = b.bookid;

--Q6. 박지성이 구매한 도서의 이름, 가격 정가와 판매가격의 차이를 출력하시오
select bookname, price-saleprice
from book b, orders o, customer c
where o.bookid = b.bookid and o.custid=c.custid and name like'박지성';



--Q7. 주소에 대한민국이 들어간 사람들이 산 책들의 총 금액(중첩질의문으로 푸시오)
select sum(saleprice)
from orders
where custid in(select custid
                from customer
                where address like '%대한민국%');
                
--Q8.7월 1일부터 6일까지 주문한 책들의 원가 합계를 구하시오
--날짜에도 between 문법 사용가능
              
select sum(price)
from orders o, book b
where o.bookid = b.bookid and orderdate between '20200701' and '20200706'
order by orderdate; 
                

select * from customer;
select * from book;
select * from orders;
 
 
출판사가 굿스포츠인 책들의 이름과 할인가를 구하시오
select bookname, saleprice
from book b, orders o
where o.bookid = b.bookid and publisher like '굿스포츠';​