7주차 배운점 느낀점 - join, Outer join, MINUS, INTERSECT, EXISTS

7주차

 

join에 대해서 배웠다. 이정도는할줄알아야해 라고하셨다. 덤앤더머 소리듣는다.
난 덤앤더머 소리를 듣기 싫다.
join을 활용한 예제들을 많이 풀었다. 이너조인, 아우터조인, 서브쿼리, EXISTS을 배웠다.
간단한 insert, update, delete, drop 의 문법을 익혔다.

 

SQL - 조인

 

두 개 이상 테이블에서 SQL
Customer 테이블을 Orders 테이블과 조건 없이 연결해보자.
Customer와 Orders 테이블의 합체 결과 투플의 개수는 고객이 다섯 명이고 주문이 열 개이므로 5×10 해서 50이 된다.

조인: 한테이블의 행을 다른 테이블의 행에 연결하여 두 개 이상의 테이블을 결합하는 연산

 

cartesian product

Customer 5개 , Orders 10개 = 50개

select * from customer,orders; -- join

 

동등 조인(equi join)
: 동등조건을 이용하여 테이블을 조인한다.
조건을 주어 어떤 테이블이든 참조할 수 있다.

select *
from customer, orders, book
where customer.custid = orders.custid and orders.bookid = book.Bookid;

 

고객과 고객의 주문에 관한 데이터를 고객번호 순으로 정렬하여 보이시오.

select * --4 순서
from customer, oders --1 
where customer.custid = orders.custid --2
order by customer.custid; --3

 

별명 사용가능

select *
from customer c, oders o 
where c.custid = o.custid
order by c.custid;

 

고객별로 주문한 모든 도서의 총 판매액을 구하고, 고객별로 정렬하시오

select name , sum(saleprice) '총 판매액'
from customer c , orders o
where c.custid = o.custid
group by c.custid
order by c.name;

 

고객의 이름과 고객이 주문한 도서의 이름을 구하시오
3개 테이블 이용, 외래키로 참조가능한 칼럼

select name, bookName
from customer c, orders o, book b
where c.custid = o.custid and b.bookid = o.bookid;

 

가격이 20,000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오
where 에 and로 조건 더 추가함

select name, bookName, price
from customer c, orders o, book b
where c.custid = o.custid and b.bookid = o.bookid and b.price = 20000;

 

외부조인(Outer join)

 

도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매가격을 구하시오
박세리는 책을구매하지 않아서 null 이지만 left outer join이기때문에 박세리도 나옴
= 왼쪽값을 다 보겠다.

select c.name, o.saleprice
from customer c Left outer join orders o on c.custid = o.custid;

 

조인 문법

 

가장 비싼 도서의 이름을 보이시오

select bookname from book where price = (select max(price) from book);

 

도서를 구매한 적이 있는 고객의 이름을 검색하시오

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 = '대한미디어'));

 

부속

상관 부속(correlated subquery)는 상위 부속의 투플을 이용하여 하위 부속를 계산함. 즉 상위 부속와 하위 부속가 독립적이지 않고 서로 관련을 맺고 있음.
부속질의 = 서브쿼리

 

출판사별로 출판사의 평균도서 가격보다 비싼 도서를 조회하시오

하위 부속질의
select 에서 나오는 값을 from절에 있는 테이블에서만 얻을 수 있다.
join은 모든 테이블에 대한 값을 핸들링할 수 있다.

처음생각:

select bookname from book where price > any(select avg(price) from book group by publisher);

 

이렇게 하게 되면 group by 결과가 publisher 별로 평균이 나오지만 상위 질의에서 어떤 publisher인지모르고 publisher별로 뽑아내지 못한다.
그렇기 때문에 b2.publisher = b1.publisher 로 연결을 해주고 평균들을 구하고 그거에 맞는 출판사별 price를 뽑는다.
while 처럼 돌면서 일치할때를 출력한다.

 

select b1.bookname, b1.price , b1.publisher
from book b1
where b1.price > 
(select avg(b2.price) from book b2 where b2.publisher = b1.publisher) ;

 

집합 연산
합집합 UNION, 차집합 MINUS, 교집합 INTERSECT

 

MINUS, INTERSECT 연산자
MySQL에는 MINUS, INTERSECT 연산자가 없으므로 다음과 같이 표현한다
MINUS 연산을 수행한 “대한민국에서 거주하는 고객의 이름에서 도서를 주문한 고객의 이
름 빼고 보이시오.” 를 NOT IN 연산자를 사용
INTERSECT 연산을 수행한 “대한민국에서 거주하는 고객 중 도서를 주문한 고객의 이름 보
이시오.” 를 IN 연산자를 사용

 

대한민국에서 거주하는 고객의 이름과 도서를 주문한 고객의 이름을 보이시오.
{고객 이름} = {대한민국에 거주하는 고객 이름} ∪ {도서를 주문한 고객 이름}
중복배제함

all 붙이면 중복 배제 안함

select name
from customer
where address like '대한민국%'
union all -- 중복 배제 x
select name from customer where custid in(select custid from orders);
-- 중복배제한다.

 

대한민국에서 거주하는 고객의 이름에서 도서를 주문한 고객의 이름을 빼고 조회하시오

select name 
from customer
where address like '대한민국%' and name not in (select name from customer where custid in(select custid from orders));

 

대한민국에 거주하는 고객 중 도서를 주문한 고객을 조회하시오

select name 
from customer
where address like '대한민국%' and name in (select name from customer where custid in(select custid from orders));

 

EXISTS

EXISTS는 원래 단어에서 의미하는 것과 같이 조건에 맞는 튜플이 존재하면 결과에
포함시킴. 즉 부속문의 어떤 행이 조건에 만족하면 참임.
NOT EXISTS는 부속문의 모든 행이 조건에 만족하지 않을 때만 참임.

 

주문이 있는 고객의 이름과 주소를 보이시오.

 

첫행에 대한 값을 반환해줌 , while처럼 돌다가 c.custid = o.custid를 찾아서 거기 안에 있다고 판단하면 name, address 출력해줌 참이면 select 문에 대한 반환

 

select name, address from customer c
where exists (select * from orders o where c.custid = o.custid);

1. 구매 테이블에서 아이디가 'JYP'를 가진 사람이 구매한 물건을 발송하기 위해 이름/주소/연락처를 조회하시오.

select distinct name, addr, mobile1, mobile2
from usertbl u , buytbl b
where u.userid = b.userid and u.userid = 'JYP';

select b.userid, addr, concat(mobile1, mobile2) as '연락처'
from buytbl b join usertbl u on b.userID = u.userID
where b.userID = 'JYP';

 

2. 전체 회원들이 구매한 목록 모두를 아이디 순으로 출력하세요

select *
from usertbl u left outer join buytbl b on b.userid = u.userid
order by u.userid;

select *
from buytbl
order by userID;

 

3. 전체 회원의 구매기록을 조회하시오. 단, 구매 기록이 없는 회원도 출력하세요

select *
from usertbl u left outer join buytbl b on u.userid = b.userid;

 

4. 한번 도 구매한 적이 없는 회원의 아이디, 이름, 주소 , 연락처를 조회하세요

select userid, name, addr, mobile1
from usertbl
where userid NOT IN (select userid from buytbl);

select u.userID, name, addr, concat(mobile1, mobile2) as 연락처
from usertbl u left join buytbl b on b.userID = u.userID
where b.userID is null;

 

5.우대리 상관의 연락처를 조회하세요

자기자신 이너 조인

select empTel from emptbl e1 where emp = (select manager from emptbl e2 where '우대리' = e2.emp);

select A.emp as '부하직원', B.emp as '매니저', B.empTel as '매니저 연락처'
from empTbl A join empTbl B on A.manager = B.emp 
where A.emp = '우대리';

select empTel from emptbl where emp = (select manager from emptbl where '우대리' = emp);

 

6. usertbl에서 사용자를 조회하되 전화번호가 없는 사람을 제외하여 조회하세요

select *
from usertbl
where mobile1 Is NOT null;

 

7. usertbl에서 전화가 없는 사람만 조회하세요

select *
from usertbl
where mobile1 is null;

 

8. 학생 테이블, 동아리 테이블, 학생동아리 테이블을 이용해서 학생을 기준으로 학생 이름/ 지역/가입한 동아리/동아리 방 을 조회하세요
세 개 테이블 join

select st.stdName, st.addr, sc.clubName, cl.roomNo
from stdTbl st 
join stdclubTbl sc on st.stdName = sc.stdName
join clubtbl cl on sc.clubName = cl.clubName;

 

9. 동아리를 기준으로 가입한 학생의 목록을 조회하세요

select *
from stdclubTbl as sc
where sc.stdName in (select stdName from stdTbl);

 

10. 동아리에 가입되지 않은 학생의 이름도 포함하여 학생의 이름/주소/동아리명/동아리 방 조회하세요
'동아리에 가입되지 않은 학생의 이름'이라고 했으므로 stdtbl 기준 Left해줬다.

select s.stdname, s.addr, sc.clubname, c.roomNo 
from stdtbl s
left outer join stdclubtbl sc on s.stdname = sc.stdname
left outer join clubtbl c on c.clubname = sc.clubname;

 

11. 동아리를 기준으로 가입된 학생을 출력하되, 가입학생이 하나도 없는 동아리도 (이름/주소/동아리명/동아리 방) 조회하세요 .
'가입학생이 하나도 없는 동아리' 이므로 clubTbl 방향으로 right

select s.stdName, s.addr, c.clubName, c.roomNo
from stdclubTbl as sc
left join stdTbl as s
on s.stdName = sc.stdName
right join clubTbl as c
on sc.clubName = c.clubName
order by s.stdName;

 

12. 동아리에 가입하지 않은 학생도 출력하고, 학생이 한 명도 없는 동아리도 조회 하세요.
'가입되지 않은학생도' 에서 stdTbl 방향으로 right , '학생이 한 명도없는 동아리' 에서 clobTbl 방향으로 right

select *
from stdclubTbl as sc
right join stdTbl as s
on s.stdName = sc.stdName
union
select *
from stdclubTbl as sc
right join clubTbl as c
on sc.clubName = c.clubName;    

 

ALTER 문
ALTER 문은 생성된 테이블의 속성과 속성에 관한 제약을 변경하며,
기본키 및 외래키를 변경함
ADD, DROP은 속성을 추가하거나 제거할 때 사용함
MODIFY는 속성의 기본값을 설정하거나 삭제할 때 사용함
ADD <제약이름>, DROP <제약이름>은 제약사항을 추가하거나 삭제할 때 사용함

 

NewBook 테이블에 VARCHAR(13)의 자료형을 가진 isbn 속성을 추가하시오.

ALTER TABLE Newbook ADD isbn varchar(13);

 

NewBook 테이블의 isbn 속성의 데이터 타입을 INTEGER형으로 변경하시오.

ALTER TABLE NewBook MODIFY isbn integer;

 

NewBook 테이블의 isbn 속성을 삭제하시오.

ALTER TABLE NewBook DROP COLUMN isbn;

 

NewBook 테이블의 bookid 속성에 NOT NULL 제약조건을 적용하시오.

ALTER TABLE NewBook MODIFY bookid integer not null;

 

NewBook 테이블의 bookid 속성을 기본키로 변경하시오.

ALTER TABLE NewBook ADD PRIMARY KEY(Bookid);

 

DROP 문
DROP 문은 테이블을 삭제하는 명령
DROP 문은 테이블의 구조와 데이터를 모두 삭제하므로 사용에 주의해야 함
(데이터만 삭제하려면 DELETE 문을 사용함).

NewBook 테이블을 삭제하시오.

DROP TABLE NewBook;

 

NewCustomer 테이블을 삭제하시오. 만약 삭제가 거절된다면 원인을 파악하고 관련된 테이
블을 같이 삭제하시오(NewOrders 테이블이 NewCustomer를 참조하고 있음).
error 남 자식인 NewOrders 있기 때문에

DROP TABLE NewOrders; DROP TABLE NewCustomer;

 

INSERT 문
INSERT 문은 테이블에 새로운 투플을 삽입하는 명령임.

 

Book 테이블에 새로운 도서 ‘스포츠 의학’을 삽입하시오. 스포츠 의학은 한솔의학서적에서 출간했
으며 가격은 90,000원이다.

INSERT INTO Book(bookid, bookname, publisher, price)
values (11,'스포츠 의학','한솔의서적',90000);

 

Book 테이블에 새로운 도서 ‘스포츠 의학’을 삽입하시오. 스포츠 의학은 한솔의학서적에서 출
간했으며 가격은 미정이다.
not null이 아니므로 비워둘 수 있음

INSERT INTO Book(booid,bookname,publisher) values (11,'스포츠의학','한솔의학서적');

 

수입도서 목록(Imported_book)을 Book 테이블에 모두 삽입하시오.
(Imported_book 테이블은 스크립트 Book 테이블과 같이 이미 만들어져 있음)

INSERT INTO Book(bookid,bookname,price, publisher)
SELECT bookid, bookname, price, publisher from imported_book;

 

UPDATE 문
UPDATE 문은 특정 속성 값을 수정하는 명령이다.

Customer 테이블에서 고객번호가 5인 고객의 주소를 ‘대한민국 부산’으로 변경하시오.

UPDATE customer
SET address = '대한민국 부산'
WHERE custid = 5;

 

SET SQL_SAFE_UPDATES=0;
해제안시켜놓으면 업데이트 딜리트 할때 에러뜰수있음

 

DELETE 문

Book 테이블에서 도서번호가 11인 도서를 삭제하시오.

DELETE FROM book WHERE bookid = 11;

 

모든 고객을 삭제하시오.

DELETE FROM customer; 

 

 

회고

 

학교에서 공부할때, 정처기를 공부할때 조인을 외우기만 했다. 대충 돌아가는 과정만 알고있었는데 오늘 기능에대해 더 자세하게 알게되었다.
간단한 join문만 알고있었는데, 세 테이블을 사용하는 조인은 처음봤다 (내가 기억못하는건지 모르겠다), 많은 예제들을 풀면서
문제에서 마지막으로 출력하라는 테이블 기준으로 아우터 조인방향을 정해주고, 세 테이블을 엮어주니 내가 원하는 값이 나왔다.
간단하지만 새로알게 되는게 많았다. 수업때 문제를 많이 풀어볼 시간을 주셔서 좋다.