day 0617 데이터베이스(3)

고객이름별로 주문한 건수를 출력합니다

(단, 주문이 없는 고객이름도 출력합니다)

select name, count(*)
from customer c, orders o
where c.custid = o.custid
group by name;

select name, count(orderid)
// 왼쪽을 조회하고싶다면 left , 오른쪽을 조회 right
from customer c left outer join orders o
on c.custid = o.custid
group by name;

 

<< select문의 형식 >>

select 칼럼명

from 테이블명

[where 조건식]

[group by 칼럼명]

[having 조건식]

[order by 칼럼명]

 

<< outer join의 형식 >>

select 칼럼명

from 테이블1 left/right outer join 테이블2

on 조건식

[group by 칼럼명]

[having 조건식]

[order by 칼럼명]

 

관리자 이름별로 부하직원의 수를 출력하시오

(단, 부하직원이 없는 직원의 이름도 출력)

select m.ename, count(*)
from emp m, emp e
where e.mgr = m.eno
group by m.ename;

select m.ename, count(e.ename)
from emp m left outer join emp e
on e.mgr = m.eno
group by m.ename
order by count(e.ename) desc;

 

부서명별로 총 직원의 수를 출력하시오

(단, 소속된 직원이 없는 부서명도 출력)

select dname, count(*)
from dept d, emp e
where d.dno = e.dno
group by dname;

select dname, count(ename)
from dept d left outer join emp e
on d.dno = e.dno
group by dname;

 

출판사별로 총 주문건수, 총 주문금액을 출력

(단, 주문내역이 없는 출판사도 출력)

select publisher, count(*), sum(saleprice)
from book b, orders o
where b.bookid = o.bookid
group by publisher;

select publisher, count(orderid), sum(saleprice)
from book b left outer join orders o
on b.bookid = o.bookid
group by publisher;

 

count 함수는 조건을 만족하는 레코드가 하나도 없다면 결과가 0이다

그러나 집계함수(sum, avg, min, max ..)들은 조건을 만족하는 레코드가 없으면 결과가 null이다

 

null일 때 다른 값으로 조회

nvl(칼럼, 값)

                               // nvl(sum(saleprice),가null 이면 0)을 출력
select publisher, count(orderid), nvl(sum(saleprice), 0)
from book b left outer join orders o
on b.bookid = o.bookid
group by publisher;

 

도서명별로 총 주문건수, 총 주문금액을 출력

(주문내역이 없는 도서명도 출력)

select bookname, count(saleprice), nvl(sum(saleprice), 0)
from book b left outer join orders o
on b.bookid = o.bookid
group by bookname;

 

고객명별로 총 주문건수, 총 주문금액, 평균 주문금액, 최고 주문금액을 출력

(주문내역이 없는 고객명도 출력)

select name, count(saleprice) cnt, nvl(sum(saleprice), 0) sum, 
       nvl(avg(saleprice), 0) avg, nvl(max(saleprice), 0) max
from customer c left outer join orders o
on c.custid = o.custid
group by name;

 

<< 3개의 테이블 outer join 하기 >>

select 칼럼명
from 테이블1 left/right outer join 테이블2
on 조건식
left/right outer join 테이블3
on 조건식;

 

 

고객의 이름과 고객이 주문한 도서이름을 출력하시오

(단, 주문이 없는 고객이름도 출력/고객이름 순으로 정렬)

select name, nvl(bookname, '없음') bookname
from customer c left outer join orders o
on c.custid = o.custid
left outer join book b
on o.bookid = b.bookid
order by name;

 

<< 3개 테이블을 outer join하고 where 조건식 사용 >>

select 칼럼명
from 테이블1 left/right outer join 테이블2
on 조건식
left/right outer join 테이블3
on 조건식
where 조건식;

 

'대한미디어'나 '이상미디어', '굿스포츠'의 도서를 구매한 고객의 이름과 출판사명, 주문한 도서명 출력

(주문이 없는 고객의 이름도 출력하고 고객이름 순으로 정렬)

select name, publisher, bookname
from customer c left outer join orders o
on c.custid = o.custid
left outer join book b
on o.bookid = b.bookid
where publisher in ('대한미디어', '이상미디어', '굿스포츠')
order by name;

-- 주문 내역이 없는 고객 내역은 출력되지 않는다

-- where는 inner 조인으로 동작하기 때문에 양쪽 테이블에 모두 만족하는 레코드만 검색이 된다

select name, nvl(publisher, '없음') publisher, nvl(bookname, '없음') bookname
from customer c left outer join orders o
on c.custid = o.custid
left outer join book b
on o.bookid = b.bookid
where publisher in ('대한미디어', '이상미디어', '굿스포츠') or b.publisher is null
order by name;

-- 다른 방법 (서브 쿼리 사용 sql문 안에 + sql문)

-- 서브쿼리는 sql문 안에 포함되는 다른 sql문을 말한다

select name, nvl(publisher, '없음') publisher, nvl(bookname, '없음') bookname
from customer c left outer join orders o
on c.custid = o.custid
left outer join (select * from book
                 where publisher in ('대한미디어', '이상미디어', '굿스포츠')) b
on o.bookid = b.bookid
order by name;

-- 이름이 중복 출력된다

 

<< 서브쿼리(중첩질의)의 사용 >>

sql 문장 안에 포함되는 다른 sql문을 "서브쿼리"라고 한다

 

가장 비싼 도서의 이름

select max(price) from book;

select bookname from book
where price = 35000;

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

 

<< 서브쿼리의 위치 >>

select 절

from 절

where 절

 

-- 고객, 주문, 도서 출력

-- A 결과

고객, 주문으로부터 고객이름, 도서번호를 출력

select name, bookid
from customer c left outer join orders o
on c.custid = o.custid;

 

-- B결과

도서 테이블의 '대한미디어', '이상미디어', '굿스포츠'의 출판사명, 도서명, 도서번호 출력
select publisher, bookname, bookid
from book
where publisher in ('대한미디어', '이상미디어', '굿스포츠');

 

-- 조인 () 자리에 A, B결과가 들어갈 수 있다

select name, publisher, bookname

from () A left outer join () B

on A.bookid = B.bookid

select name, nvl(publisher, '없음') publisher, nvl(bookname, '없음') bookname
from (select name, bookid 
      from customer c left outer join orders o 
      on c.custid = o.custid) A 
left outer join (select publisher, bookname, bookid 
                 from book 
                 where publisher in ('대한미디어', '이상미디어', '굿스포츠')) B
on A.bookid = B.bookid;

-- 이름 중복 출력2

-- 중복 제거는 distinct 지만 적용되지 않음..

 

도서와 주문테이블로 부터 출판사가 '이상미디어', '대한미디어', '굿스포츠'인 고객번호, 도서명, 출판사명 출력

select custid, bookname, publisher
from book b, orders o
where b.bookid = o.bookid and
publisher in ('이상미디어', '대한미디어', '굿스포츠');

 

위의 결과를 from절의 서브쿼리로 하여 customer와 outer join을 한다

 

select name, publisher, bookname

from customer c left outer join () o

on c.custid = o.custid

order by name;

select name, publisher, bookname
from customer c left outer join (
select custid, bookname, publisher
from book b, orders o
where b.bookid = o.bookid and
publisher in ('이상미디어', '대한미디어', '굿스포츠')) o
on c.custid = o.custid
order by name;

 

 

구매한 내역이 있는 고객의 이름 출력

(건수가 많을 땐 조인보다 서브쿼리 성능이 좋다)

 

- 조인

select distint name
from customer c, orders o
where c.custid = o.custid;

 

- 주문테이블로부터 고객번호 조회

select distinct custid from orders;

 

- 고객 테이블로부터 고객번호가 (1,2,3,4)에 해당하는 고객이름 조회

select name

from customer

where custid in ();

select name
from customer
where custid in (select distinct custid from orders);

단일행 : where custid = ();
다중행 : where custid in ();

 

-- 추가하기

insert into orders values(11, 2, 3, 22000, sysdate);
insert into orders values(12, 1, 4, 35000, sysdate);
insert into orders values(13, 4, 4, 36000, sysdate);
insert into orders values(14, 5, 4, 36000, sysdate);

 

'대한미디어'의 도서를 구매한 고객의 이름 출력

(조인, 서브쿼리)

- 조인

select distinct name
from customer c, orders o, book b
c.custid = o.custid and
b.bookid = o.bookid and
publisher = '대한미디어';

 

- 서브쿼리

A : 도서테이블에서 대한미디어에서 출간하는 도서번호

select bookid from book where publisher = '대한미디어';

 

B : 주문테이블에서 A를 구매한 고객번호

select custid from orders

where bookid in ();

 

select distinct custid from orders
where bookid in (select bookid from book where publisher = '대한미디어');

 

고객테이블에서 고객번호가 B에 해당하는 고객이름

select name
from customer
where custid in (
select distinct custid from orders
where bookid in (select bookid from book where publisher = '대한미디어'));

 

부하직원이 있는 직원의 이름을 출력

(조인, 서브쿼리)

select distinct m.ename
from emp e, emp m
where e.mgr = m.eno;

select ename
from emp
where eno in (select mgr from emp);

 

---

 

14:00 ~

 

-- 데이터베이스 명령어의 종류와 각각에 대하여 설명하시오

DDL 데이터 정의어 
  데이터베이스 테이블이나 개체를 생성하거나 수정, 삭제하는 명령어
DCL 데이터 제어어
  사용자 계정을 생성하고, 권한을 부여하거나 제거하는 명령어
DML 데이터 조작어
  레코드를 추가, 수정, 삭제, 조회하는 명령어

 

-- 데이터조회를 위한 select문의 형식을 쓰시오

select 칼럼명
from 테이블명
[where 조건식]
[group by 칼럼명]
[having 조건식]
[order by 칼럼명]

 

-- where절에 대하여 설명하시오

조회하고자 하는 조건식을 써준다

 

-- group by절에 대하여 설명하시오

특정 칼럼의 값을 종류별로 집계함수 할 때 써준다

 

-- having에 대하여 설명하시오 

group by절의 결과에 대한 조건식

 

-- inner join, outer join, self join에 대하여 각각 설명하시오

inner join (내부에서만 조인) 두 테이블의 조건식을 모두 만족하는 레코드를 검색
outer join (외부에서 조인) 왼쪽/오른쪽 테이블의 조건식이 만족하지 않더라도 조회할 때 사용
self join (자기 자신과 조인) 하나의 동일한 테이블에 다른 속성을 참조 할 때 사용

 

-- 서브쿼리에 대하여 설명하시오

sql문에 포함하는 다른 sql문을 말하며 ()로 묶어서 써준다
select절, from절, where절에 올 수 있다

 

'장미란' 고객의 주문내역을 출력

select * from orders;

고객번호가 '장미란'에 해당하는

select * from orders
where custid = (select custid from customer where name= '장미란');

select o.*
from customer c, orders o
where c.custid = o.custid and
name = '장미란';

 

-- 서브쿼리와 조인

동일한 문제 해결을 위하여 서브쿼리를 사용할 수도 있고 조인을 사용할 수도 있어요

데이터 수가 많을 때는 조인보다는 서브쿼리가 성능이 더 좋다 

 

'장미란' 고객의 총 주문건수, 총 주문금액, 평균 주문금액을 출력

(서브쿼리 이용)

select count(*) cnt, sum(saleprice) sum, avg(saleprice) avg
from orders
where custid = (select custid from customer where name = '장미란');

 

고객별 총 주문건수, 총 주문금액, 평균 주문금액을 출력

(단,주문 내역이 없는 고객도 출력하고 총 주문건수가 높은순으로 정렬 동일하면 총 주문금액이 높은순)

 

-- 조인 (주문 내역 없는 고객 x)

select name, count(*) cnt, sum(saleprice) sum, avg(saleprice) avg
from customer c, orders o
where c.custid = o.custid
group by name
order by count(*) desc, sum(saleprice) desc;

 

select name, count(saleprice) cnt, nvl(sum(saleprice), 0) sum, nvl(avg(saleprice), 0) avg
from customer c left outer join orders o
on c.custid = o.custid
group by name
order by count(saleprice) desc, sum(saleprice) desc;

 

'굿스포츠', '대한미디어', '이상미디어' 출판사들의 도서를 구매한 고객들의

고객별 총 주문건수, 총 주문금액, 평균 주문금액을 출력

(단, 총 주문건수가 높은순으로 출력하고 동일하면 총 주문금액이 높은순으로 출력)

 

-- join

select name, count(*) cnt, nvl(sum(saleprice), 0) sum, nvl(avg(saleprice), 0) avg
from customer c, orders o, book b
where c.custid = o.custid and
b.bookid = o.bookid and
publisher in ('굿스포츠', '대한미디어', '이상미디어')
group by name
order by cnt desc, sum desc;

- 고객 아이디별 총 주문건수, 총 주문금액, 평균 주문금액

- 고객 아이디별 총 주문건수, 총 주문금액, 평균 주문금액
select custid, count(saleprice), sum(saleprice), avg(saleprice)
from orders
group by custid;

- ('굿스포츠', '대한미디어', '이상미디어') 도서를 구매한

select custid, (select name from customer c where c.custid = o.custid) name, 
 count(saleprice) cnt, sum(saleprice) sum, avg(saleprice) avg
from orders o where bookid in (select bookid from book where publisher in ('굿스포츠', '대한미디어', '이상미디어'))
group by custid;

'굿스포츠', '대한미디어', '이상미디어' 도서를 구매한

고객들의 고객별 총 주문건수, 총 주문금액, 평균 주문금액을 출력

(단, 총 주문건수가 3건이상인 고객만 출력하고 총 주문건수가 높은순으로 출력 동일하면 총 주문금액이 높은순)

having cnt >= 3
order cnt desc, sum desc;

 

** 문제 **

 

1) 마당서점의 도서의 총 개수

select count(*) from book;

 

2) 마당서점에 도서를 출고하는 출판사의 총 개수

select count(publisher) from book;

 

3) 모든 고객의 이름, 주소

select name, address from customer;

 

4) 2024/06/01 ~ 06/17 사이에 주문받은 도서의 주문번호

select orderid from orders where orderdate between '24/06/01' and '24/06/17';

 

5) 2024/06/01 ~ 06/17 사이에 주문받은 도서를 제외한 주문번호

select orderid from orders where orderdate not between '24/06/01' and '24/06/17';

 

6) 성이 '김'씨인 고객의 이름과 주소

select name, address from customer where name like '김%';

 

7) 성이 '김'씨 이고 이름이 '아'로 끝나는 고객의 이름과 주소

select name, address from customer where name like '김%아';

 

8) 주문하지 않은 고객의 이름

select name from customer where custid not in(select custid from orders);

 

-- minus 뒤의 select문에서 앞의 select문을 뺀다

select name from customer
minus
select name from customer c, orders o
where c.custid = o.custid;

 

9) 주문금액의 총 액과 주문의 평균금액

select sum(saleprice) sum, avg(saleprice) avg from orders;

 

10) 고객의 이름과 고객별 구매액

select name, sum(saleprice) from customer c, orders o where c.custid = o.custid group by name;

 

11) 고객의 이름과 고객이 구매한 도서목록 (고객별로 구매한 도서목록)

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

 

12) 도서의 가격과 판매가격의 차이가 가장 많은 주문

select price - saleprice from book b, orders o where b.bookid = o.bookid;

select o.*
from orders o, book b
where o.bookid = b.bookid and
price - saleprice = (select max(price - saleprice)
from orders o, book b
where o.bookid = b.bookid);

 

13) 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름

select name from (select name, avg(saleprice) avg
from customer c, orders o
where c.custid = o.custid
group by name)
where avg > (select avg(saleprice) from orders);

 

- 오늘 학습한 내용에 대하여 요약 정리하고 궁금한 점 질문합니다

- 팀 별로 프로젝트 관련 작업을 진행합니다

- 프로그램 연습) 고객의 이름을 입력받아 고객의 총 주문건수, 총 주문금액, 평균 주문금액을 출력