[조인]
- 테이블과 테이블을 붙인다 -> 옆으로 붙임
ex) A테이블, B테이블, C테이블 -> A = B와 연결, B = C와 연결
- 행과 열의 개수가 다를 수 있음.
- 곱하기의 느낌
- 고객과 고객의 주문에 관한 데이터를 모두 보이시오
select * from customer, orders where customer.custid = orders.cusitd;
-> customer 테이블 + orders 테이블을 조인시켜서 연관있는 것들을 연결
-> 이 경우는 두 테이블에 공통적으로 들어있는 custid를 기준으로!
- 고객과 고객의 주문에 관한 데이터를 고객번호 순으로 정렬하여 보이시오
select *
from customer, orders
where customer.custid = orders.custid
order by customer.custid;
- 고객의 이름과 고객이 주문한 도서의 판매가격을 검색하시오.
select name, saleprice
from customer, orders
where customer.custid = orders.custid;
- 고객별로 주문한 모든 도서의 총 판매액을 구하고, 고객별로 정렬하시오.
select name, sum(saleprice)
from customer, orders
where customer.custid = orders.custid
group by customer.name
order by customer.name;
- 고객의 이름과 고객이 주문한 도서의 이름을 구하시오.
select customer.name, book.bookname
from customer, orders, book
where customer.custid = orders.custid
and orders.bookid = book.bookid;
- 가격이 20,000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오.
select customer.name, book.bookname
from customer, orders, book
where book.price = 20000
and customer.custid = orders.custid
and orders.bookid = book.bookid;
[외부조인]
- 도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매가격을 구하시오.
-> customer 테이블에는 이름이 있는데 주문한 적이 없어서 orders에는 고객 이름이 없어도 모두 합침
select name, saleprice
from customer LEFT OUTER JOIN
orders ON customer.custid = orders.custid;
select name, saleprice
from customer, orders
where customer.custid = orders.custid(+)
고객 테이블
- 박지성, 김연아, 장미란, 추신수, 박세리
주문 테이블 : 책 구매자와 정보 나옴
- 박지성, 김연아, 장미란
-> LEFT OUTER 조인 : 왼쪽 고객 테이블에는 있는데 오른쪽 주문 테이블에서 구매한 적이 없는 고객도 다 포함하여 조인(책을 구매한 적이 없는 추신수, 박세리를 포함해서 나옴 이 때 판매 가격은 NULL)
[부속질의] sub query : SQL 문 내에 또 다른 SQL 문
- 가장 비싼 도서의 이름을 보이시오
select bookname
from book
where price = (select max(price) -> where A = B 처럼 완전한 문장이 와야해서 B 부분은 ()로 감쌈
from book); ->부속질의
또는
select bookname, max(price)
from book
group by bookname;
- 도서를 구매한 적이 있는 고객의 이름을 검색하시오.
select name
from customer
where custid in (select custid from orders);
- 대한미디어에서 출판한 도서를 구매한 고객의 이름을 보이시오
select name
from customer
where custid in ( select custid
from orders
where bookid in ( select bookid
from book
where publisher like '대한미디어'));
또는
select name
from customer, orders, book
where customer.custid = orders.custid
and orders.bookid = book.bookid
and publisher like '대한미디어');
[상관 부속질의] (<-> 비상관 부속질의)
main query가 있고 sub query가 있음 -> 보통 sub부터 하고나서 main 처리함 : 비상관의 경우
but 상관 : main query와 sub query가 밀접한 연관이 있음 -> main부터 처리함
출판사별로 출판사의 평균 도서 가격보다 비싼 도서를 구하시오.
-> 평균보다 비싼 도서를 구해보면
select bookname
from book
where price > avg(price) -> 비교하는 앞의 price의 출판사와 뒤의 price출판사가 같아야함 -> 구분해줘야 함
->
select b1.bookname
from book b1
where b1.price > (select avg(b2.price)
from book b2
where b2.publisher=b1.publisher));
-> b1과 b2가 상관이 있다
-> (main) book에서 책 이름 뽑아냄 b1의 가격이 /// book에서 출판사가 같은 것의 책 price의 평균을 구함
-> 출판사별 평균 도서 가격보다 비싼 도서를 구하는 질의
[집합연산]
합집합 : Union -> 테이블을 위아래로 붙임
차집합 : minus // except -> A - B -> 오라클이 아니라 MS-SQL에서 씀
교집합 : intersect
- 도서를 주문하지 않은 고객의 이름을 보이시오
-> 차집합(모든 고객 이름 - 도서를 주문한 고객)
select name
from customer -> 모든 고객 이름(customer 테이블)
minus
select name -> 도서를 주문한 고객의 이름만(customer 테이블, 주문내역 orders -> 공통 : custid)
from customer
where custid in ( select custid
from orders);
[Exists] : 조건에 맞는 튜플이 존재하면 결과에 포함시킴
(부속질의문의 어떤 행이 조건에 만족하면 참인 반면 NOT EXISTS는 부속질의문의 모든 행이 조건에 만족하지 않을 때만 참임)
cf) price in(3000, 2000, 1000) : 3000, 2000, 1000 중 하나 -> 다항 연산자
exists는 단항 연산자 : exists( 문장 )
- 주문이 있는 고객의 이름과 주소를 보이시오
select name, address
from customer cs
where exists (select *
from orders od
where cs.custid = od.custid);
-> 주문이 있는 고객의 이름과 주소를 보이시오
Select distinct name, address
From customer, orders
Where customer.custid = orders.custid;
[연습문제 풀이]
1. 마당서점의 고객이 요구하는 다음 질문에 대해 SQL 문을 작성하시오.
(5) 박지성이 구매한 도서의 출판사 수
select count(distinct publisher) as 박지성출판사
from customer, orders, book
where customer.custid = orders.custid
and orders.bookid = book.bookid
and name like '박지성';
(6) 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이
select bookname, price, price-saleprice as 할인가격
from customer, orders, book
where customer.custid = orders.custid
and orders.bookid = book.bookid
name like '박지성';
(7) 박지성이 구매하지 않은 도서의 이름
- 방법1 : 집합연산 (-) minus -> 전체도서 - 박지성이 구매한 도서
select bookname
from book
minus
select bookname
from book, orders, customer
where book.bookid = orders.bookid
and order.custid = customer.custid
and name like '박지성';
- 방법2 : in 연산 (비상관) -> not in
select bookname
from book
where bookname not in (select bookname
from book, orders, customer
where book.bookid = orders.bookid
and order.custid = customer.custid
and name like '박지성';)
- 방법3-1 : exists 연산(상관) 박지성이 구매하지 않은 도서의 이름
select bookname
from book b1
where not exists (select *
from book b2, orders, customer
where b1.bookid = b2.bookid
and orders.bookid = b2.bookid
and orders.custid = customer.custid
and name like '박지성');
- 방법3-2 : exists 연산(상관) 단순화
select bookname
from book b1
where not exists (select bookname
from customer, orders
where customer.custid = orders.custid
and orders.bookid = b1.bookid
and name like '박지성');
2. 마당서점의 운영자와 경영자가 요구하는 다음 질문에 대해 SQL 문을 작성하시오.
(8) 주문하지 않은 고객의 이름(조인 사용)
- 방법1 : minus (-)
select name
from customer
minus
select name
from customer, orders
where customer.custid = orders.custid;
- 방법2 : in(비상관)
select name
from customer
where name not in (select name
from orders, customer
where customer.custid = orders.custid);
- 방법3-1 : exists (상관)
select name
from customer c1
where not exists (select name
from customer c2, orders
where c2.custid = orders.custid
and c1.custid = c2.custid);
- 방법 3-2 : exists (상관) 단순화
select name
from customer
where not exists (selcet name
from orders
where orders.custid = customer.custid);
(9) 주문 금액의 총액과 주문의 평균 금액
select sum(saleprice), avg(saleprice)
from orders;
(10) 고객의 이름과 고객별 구매액
select name, sum(saleprice)
from customer, orders
where customer.custid = orders.custid
group by customer.name;
(11) 고객의 이름과 고객이 구매한 도서 목록
select name, bookname
from customer, orders, book
where customer.custid = orders.custid
and orders.bookid = book.bookid;
[다음 시간에...]
(12) 도서의 가격(Book 테이블)과 판매가격(Orders 테이블)의 차이가 가장 많은 주문
(13) 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름-> 상관 또는 group by having
'프로그래밍 > 자바(java) 융합개발자 2차' 카테고리의 다른 글
| [취성패] 자바 배우기 - 5일차 일지(SQL 고급 - 내장함수) (0) | 2020.12.18 |
|---|---|
| [취성패] 자바프로그래밍 - 4일차 학습일지(SQL-데이터 정의어) (0) | 2020.12.17 |
| [취성패] 자바 프로그래밍 - 1, 2일차 학습일지(SQL) (0) | 2020.12.15 |
| [취성패] 자바 배우기 - 선수학습(문자열 클래스) (0) | 2020.12.13 |
| [취성패] 자바 배우기 - 선수학습(Object 클래스) (0) | 2020.12.13 |