2022.04.21. 목요일 이공계 전문기술 연수사업 19일차

오늘 배운 것

ex)고객의 주문에 대한 데이터를 모두 출력
select * 
from customer, orders 
where customer.custid=orders.custid;

ex)고객과 고객의 주문에 관한 데이터를 고객번호 순으로 정렬
select * 
from orders o,customer c 
where o.custid=c.custid 
order by o.custid;

ex)고객의 이름과 고객이 주문한 도서의 판매가격을 검색
select name, saleprice 
from customer,orders 
where customer.custid=orders.custid;

ex)고객별로  주문한 모든 도서의 총 판매액을 구하고 고객별로 정렬하시오
select name,sum(saleprice) 
from customer c, orders o 
where c.custid=o.custid 
group by name;

join
ㄴ두 개의 테이블을 합치는 것!!

ex)가격이 2만원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오
select name,bookname from orders o,customer c,book b
where price=20000 and c.custid=o.custid and b.bookid=o.bookid;

ex)고객의 이름과 고객이 주문한 도서의 이름을 구하시오
select name, bookname from customer,orders,book
where customer.custid=orders.custid and orders.bookid=book.bookid;
//주문 내역이 없는 고객이름은 출력되지 않음
이런 것을 innerjoin이라고 함!
양쪽 테이블에 모두 조건을 만족하는 레코드만 출력됨
(고객 정보에는 등록되었지만 주문을 안 했을 경우)

만약에 주문내역이 없는 고객이름도 출력해야한다면?
ㄴouter join 사용
조인할 때, 두 개의 테이블 중에 조건을 만족하지 않더라도 포함시키고자할 때 사용

left outer join
ㄴfrom 절을 기준으로 왼쪽에 있는 테이블은 조건을 만족하지 않더라도 모두 포함
right outer join
ㄴfrom 절을 기준으로 오른쪽에 있는 테이블은 조건을 만족하지 않더라도 모두 포함
full outer join
ㄴ양쪽의 테이블을 모두 포함(거의 쓸 일이 없음)

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

left outer join의 형식
select 컬럼1, 컬럼2
from 테이블1 left outer join 테이블2
on 조건식 (where을 쓰지 않고 on을 씀)
ㄴfrom 절에 왼쪽에 있는 테이블1의 내용은 조건을 만족하지 않더라도 모두 출력하라는 뜻

right outer join의 형식
select 컬럼1, 컬럼2
from 테이블1 right outter join 테이블2
on 조건식;
ㄴfrom 절을 기준으로 오른쪽에 있는 테이블2의 내용은 조건을 만족하지 않더라도 모두 출력하라는 뜻

ex)고객의 이름과 고객이 주문한 도서의 판매가격을 출력
단, 주문 내역이 없는 고객의 이름도 출력
select name, saleprice 
from customer c left outer join orders o 
on c.custid=o.custid;

**실습을 위해 새로운 부서 하나 더 등록**
insert into dept values(50,'개발3팀','제주');

ex)모든 부서명과 그 부서에 근무하는 직원의 이름을 출력하시오
단, 근무자가 없는 부서명도 출력하시오
select dname,ename 
from dept d left outer join emp e 
on d.dno=e.dno;

ex)모든 도서명과 도서의 가격, 도서의 판매가격, 판매일을 출력
단, 판매되지 않는 도서의 정보도 출력
select bookname,price,saleprice,orderdate 
from book b left outer join orders o 
on b.bookid=o.bookid;

insert into book values(11,'토끼 잘 키우는 법','쌍용미디어',30000);
insert into book values(12,'잘 키운 아기토끼','쌍용미디어',35000);

ex)출판사 별로 총 판매한 도서의 수, 총 판매 금액을 출력
단, 판매되지 않는 출판사도 포함
select publisher, count(*), sum(saleprice) 
from book b left outer join orders o 
on b.bookid=o.bookid 
group by publisher;

주문한 건수를 출력해야 하니 orders 테이블에 있는 컬럼을 써야함!
select publisher, count(o.bookid), sum(saleprice) 
from book b left outer join orders o 
on b.bookid=o.bookid 
group by publisher;

PUBLISHER       COUNT(O.BOOKID) SUM(SALEPRICE)
--------------- --------------- --------------
굿스포츠                      8         135000
삼성당                        1           8000
kbg                           1           6000
내가엄홍길                    1          14000
런런런                        1          62000
한국snpe협회                  1          26000
홈트나라                      1          52000
쌍용미디어                    0

쌍용미디어는 판매내역이 없어서 총 판매금액에 아무런 값도 표시되지 않음
0으로 표시하고 싶다면?
NVL함수 사용
ㄴ특정 컬럼의 값이 null인 경우 다른 값으로 대체하는 함수 nvl(컬럼이름,대체값)
=>select publisher, count(o.bookid), nvl(sum(saleprice),0) 
from book b left outer join orders o 
on b.bookid=o.bookid 
group by publisher;으로 수정

ex)고객별로 총 구매건수와 총 구매액을 출력 단, 구매내역이 없는 고객 명도 출력
select name, count(saleprice), sum(saleprice) 
from customer c left outer join orders o 
on c.custid=o.custid 
group by name;

NAME       COUNT(SALEPRICE) SUM(SALEPRICE)
---------- ---------------- --------------
박지성                    3          40000
김연아                    5         102000
장미란                    2          50000
추신수                    2          68000
손흥민                    1          21000
김연어                    1          22000

ex)부서별로 근무자의 수, 평균급여 출력 단, 근무자가 없는 부서명도 출력
select dname, nvl(count(ename),0),nvl(avg(salary),0) 
from dept d left outer join emp e 
on d.dno=e.dno 
group by dname;

DNAME      NVL(COUNT(ENAME),0) NVL(AVG(SALARY),0)
---------- ------------------- ------------------
총무팀                       5                440
개발2팀                      5                380
개발1팀                      5                390
기획팀                       4                525
개발3팀                      0                  0

self join 어렵다.. 공부 더 필요!
ㄴ테이블 하나에서 어떤 컬럼이 자신의 또다른 컬럼의 값을 참조하는 경우
실제로 물리적으로는 테이블이 하나인데 마치 두개인 것처럼 애칭을 줘서 join하는 것

ex)모든 직원의 이름과 관리자 이름을 출력
select 사원.ename,관리자.ename 
from emp 사원, emp 관리자 
where 사원.mgr=관리자.eno;

ex)모든 직원의 이름과 관리자의 이름을 출력
select e.ename ename, m.ename mname 
from emp e left outer join emp m 
on e.mgr=m.eno;

ex)서동혁의 부하직원을 모두 출력
select e.ename 
from emp e, emp m 
where e.mgr=m.eno and m.ename='서동혁';

ex)황예은의 관리자 이름을 출력
select m.ename 
from emp e, emp m 
where e.mgr=m.eno and e.ename='황예은';

--------------------------------------------------------------------------
부속질의(서브쿼리 혹은 중첩쿼리 혹은 하위질의 혹은 중첩질의)
ㄴsql문 안에 포함되는 또 다른 sql문

ex)가장 비싼 도서의 이름을 출력
select bookname from book 
where price=(select max(price) from book);

ex)도서를 구매한 적이 있는 고객의 이름 출력
select name from customer where custid in(select custid from orders);
select distinct name from customer,orders where customer.custid=orders.custid;
똑같은 문제를 조인으로도,서브쿼리로도 표현 가능!

자료의 수가 적을 때는 별 차이가 없지만
레코드의 수가 많을 때는 성능에 차이가 있음.
서브쿼리가 조인보다는 성능적인 면에서 효율성이 높음!

ex)서교동에 근무하는 모든 직원의 이름 출력 join 으로도, 서브쿼리로도 해보기!
select ename from emp e, dept d where d.dno=e.dno and dloc='서교동';
select ename from emp where dno in (select dno from dept where dloc='서교동');

ex)평균 급여보다 더 높은 급여를 받는 직원들의 이름, 급여를 출력 - 서브쿼리 이용
select ename, salary from emp where salary>=(select avg(salary) from emp);

ENAME   SALARY
------- ------
박정수     700
김도연     500
김민혁     500
김소윤     600
이희승     500
조태헌     500
황예은     500
고주희     500

ex)박지성이 구매한 적이 있는 도서를 구매한 고객의 이름을 출력
select name from orders, customer 
where orders.custid=customer.custid 
and bookid in (select bookid from orders where custid=1) and name <> '박지성';

ex)런런런에서 출간한 도서를 구매한 적이 있는 고객의 이름
select name from customer
where custid in (select custid from orders where bookid in 
(select bookid from book where publisher='런런런'));

--------------------------------------------------------------
상관 부속질의(상관 서브쿼리)
ㄴ메인쿼리와 서브쿼리에 사용되는 테이블의 칼럼을 조건식에 서로 관련을 맺고 있을 때 사용 

ex)출판사별로 출판사의 평균도서보다 비싼 도서 이름을 출력
select publisher, b1.bookname 
from book b1 
where b1.price > 
(select avg(b2.price) from book b2 where b1.publisher=b2.publisher);

PUBLISHER       BOOKNAME
--------------- --------------------
굿스포츠        골프 바이블
굿스포츠        피겨 교본
쌍용미디어      잘 키운 아기토끼

-----------------------------------------------------------------------
집합연산

합집합 union
차집합 minus
교집합 intersect

ex)주문하지 않은 고객의 이름을 출력
select name from customer 
minus select name from customer 
where custid in (select distinct custid from orders);

NAME
----------
김아아

------------------------------------------------------------------------
exist 연산자
ㄴ서브쿼리에 레코드가 존재한다면 메인쿼리가 동작
exist쓸 때는 반드시 상관 서브쿼리가 되어야 함!!!(=>이거 무슨 소리인지 알기!!!)

ex)주문이 있는 고객 이름과 주소를 출력
select name,address from customer c 
where exist (select * from orders o where c.custid=o.custid);

-------------------------------------------------------------------------
상관서브쿼리
ㄴ서브쿼리의 조건식이 메인쿼리의 테이블과 연관이 있는 경우를 말함
(서브쿼리와 메인쿼리가 상관이 있다)
select 컬럼1, 컬럼2, ... from 테이블1
(select 컬럼3, from 테이블2 where 테이블1.컬럼1=테이블2.컬럼5);

-----------------------------------------------------------------------
exist
ㄴ서브쿼리의 결과의 레코드가 존재한다면 메인쿼리를 실행
select 컬럼1, 컬럼2 from 테이블1 where exist (select 컬럼3 from 테이블2 where 테이블1.컬럼1 = 테이블2.컬럼4);

--------------------------------------------------------------------------------------------------
sql 연습~~~

ex)박지성이 구매한 도서의 출판사 수 
select count(distinct publisher)
from book where publisher in (select publisher from book ,orders
where book.bookid=orders.bookid and orders.custid=1);

ex)박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이
select bookname, price, price-saleprice from book,orders 
where orders.bookid=book.bookid 
and orders.bookid in (select distinct bookid from orders where custid=1);

ex)박지성이 구매하지 않은 도서의 이름
select bookname from book 
minus select bookname from book, orders 
where book.bookid=orders.bookid and orders.custid=1;

팔린책에서 / 모든 책에서..?

ex)고객명을 입력받아 그 고객이 주문하지 않은 도서목록을 출력하는 웹 문서 작성
ㄴ이름이 김연아인 사람이 주문하지 않은 도서목록을 출력
select bookname from book where book.bookid not in (select orders.bookid from orders where orders.custid = (select customer.custid from customer where name ='김연아'));

 

오늘 헷갈린 것

• 상관 서브쿼리
• exist 연산자의 서브쿼리는 꼭 상관 서브쿼리여야하는 이유..?

오늘 한 생각

남들과 비교는 옳지 않다!

강의+복습+운동을 루틴화해서 만드는 작업중..

밀가루랑 당을 좀 줄이자.. 밀가루랑 당을 하도 먹었더니 더 피로한 기분.