SQL
oracle19c - 연습 (마당서점 04.29)
jomericano
2022. 5. 11. 20:11
CREATE TABLE Books (
bookid INTEGER PRIMARY KEY,
bookname VARCHAR(40),
publisher VARCHAR(40),
price INTEGER
);
CREATE TABLE Customer (
custid INTEGER PRIMARY KEY,
name VARCHAR(40),
address VARCHAR(50),
phone VARCHAR(20)
);
CREATE TABLE OrderBooks (
orderid INTEGER PRIMARY KEY,
custid INTEGER ,
bookid INTEGER ,
saleprice INTEGER ,
orderdate DATE,
FOREIGN KEY (custid) REFERENCES Customer(custid),
FOREIGN KEY (bookid) REFERENCES Books(bookid)
);
INSERT INTO Books VALUES(1, '축구의 역사', '굿스포츠', 7000);
INSERT INTO Books VALUES(2, '축구아는 여자', '나무수', 13000);
INSERT INTO Books VALUES(3, '축구의 이해', '대한미디어', 22000);
INSERT INTO Books VALUES(4, '골프 바이블', '대한미디어', 35000);
INSERT INTO Books VALUES(5, '피겨 교본', '굿스포츠', 8000);
INSERT INTO Books VALUES(6, '역도 단계별기술', '굿스포츠', 6000);
INSERT INTO Books VALUES(7, '야구의 추억', '이상미디어', 20000);
INSERT INTO Books VALUES(8, '야구를 부탁해', '이상미디어', 13000);
INSERT INTO Books VALUES(9, '올림픽 이야기', '삼성당', 7500);
INSERT INTO Books VALUES(10, 'Olympic Champions', 'Pearson', 13000);
INSERT INTO Books VALUES(21, 'Zen Golf', 'Pearson', 12000);
INSERT INTO Books VALUES(22, 'Soccer Skills', 'Human Kinetics', 15000);
INSERT INTO Customer VALUES (1, '박지성', '영국 맨체스타', '000-5000-0001');
INSERT INTO Customer VALUES (2, '김연아', '대한민국 서울', '000-6000-0001');
INSERT INTO Customer VALUES (3, '장미란', '대한민국 강원도', '000-7000-0001');
INSERT INTO Customer VALUES (4, '추신수', '미국 클리블랜드', '000-8000-0001');
INSERT INTO Customer VALUES (5, '박세리', '대한민국 대전', NULL);
INSERT INTO OrderBooks VALUES (1, 1, 1, 6000, '2014-07-01');
INSERT INTO OrderBooks VALUES (2, 1, 3, 21000, '2014-07-03');
INSERT INTO OrderBooks VALUES (3, 2, 5, 8000, '2014-07-03');
INSERT INTO OrderBooks VALUES (4, 3, 6, 6000, '2014-07-04');
INSERT INTO OrderBooks VALUES (5, 4, 7, 20000, '2014-07-05');
INSERT INTO OrderBooks VALUES (6, 1, 2, 12000, '2014-07-07');
INSERT INTO OrderBooks VALUES (7, 4, 8, 13000, '2014-07-07');
INSERT INTO OrderBooks VALUES (8, 3, 10, 12000, '2014-07-08');
INSERT INTO OrderBooks VALUES (9, 2, 10, 7000, '2014-07-09');
INSERT INTO OrderBooks VALUES (10, 3, 8, 13000, '2014-07-10');
commit;
-- ----------------------------------------------------------------------------
-- 김연아 고객의 전화번호 조회
select phone from CUSTOMER
where name ='김연아';
-- 모든 도서명과 가격을 조회
select BOOKNAME, PRICE from BOOKS;
-- search every book, publisher and price
select bookid, bookname, publisher, price from Books;
-- 모든 도서의 출판사 조회
select distinct publisher from Books;
-- 20000원 미만인 도서 조회
select bookname, price from Books
where price <= 20000;
select * from Books
where price between 10000 and 20000;
-- 출판사가 '굿스포츠' 또는 대한미디어인 도서 조회
select * from Books
where publisher = '대한미디어' or publisher = '굿스포츠';
select * from Books
where publisher in ('대한미디어', '굿스포츠');
-- 출판사가 굿스포츠 또는 대한미디어가 아닌 도서 조회
select * from BOOKS
where publisher not in ('대한미디어', '굿스포츠');
select * from BOOKS
where publisher <> '대한미디어'
and publisher <> '굿스포츠';
-- 가격이 만원이상 이만원 이하가 아닌 도서 조회
select * from BOOKS
where price not between 10000 and 20000;
select publisher
from Books
where bookname = '축구의 역사';
-- 축구라는 단어가 포함된 도서를 출간한 출판사 조회
select publisher, bookname
from BOOKS
where bookname like '%축구%';
-- 앞에서 두번째 위치에 '구'라는 단어가 포함된 도서 조회
select *
from BOOKS
where bookname like '_구%';
-- 축구에 관련된 도서중 가격이 2만원 이상인 도서
select *
from BOOKS
where bookname like '%축구%' and price >= 20000;
-- 도서를 이름순으로 조회
select *
from BOOKS
order by bookname;
-- 도서를 가격순으로 조회하되 가격이 같으면 이름순으로 정렬
select *
from BOOKS
order by price, bookname;
-- 도서를 가격 내림차순으로 정렬, 가격이 같으면 출판사 순으로 정렬
select *
from BOOKS
order by price desc, publisher;
-- 연습문제 마당 --------------------------------------------------------------------------------------------
-- 마당서점의 고객이 요구하는 다음 질문에 대해 SQL문을 작성하시오.
-- 1. 도서번호가 1인 도서의 이름
SELECT BOOKNAME FROM BOOKS
WHERE BOOKID = 1;
-- 2. 가격이 20,000원 이상인 도서의 이름
SELECT BOOKNAME, PRICE FROM BOOKS
WHERE PRICE >= 20000;
-- 3. 박지성의 총 구매액(고객번호는 1번으로 작성)
SELECT SUM(SALEPRICE) 총구매액 FROM ORDERBOOKS
WHERE CUSTID = 1;
-- 4. 박지성이 구매한 도서의 수
SELECT count(BOOKID) from ORDERBOOKS
where CUSTID = 1;
-- 서점에서 판매하는 도서 수 조회
select count(BOOKID) 판매도서수 from BOOKS;
-- 서점에 출고하는 출판사 수 조회
select count(distinct PUBLISHER) 출판사 from BOOKS;
-- 마당서점의 운영자와 경영자가 요구하는 다음 질문에 대해 SQL문을 작성하시오
-- 1. 마당서점 도서의 총 개수
select count(BOOKID) 판매도서수 from BOOKS;
-- 2. 마당서점에 도서를 출고하는 출판사의 총 개수
select count(distinct publisher) from books;
-- 3. 모든 고객의 이름, 주소
select NAME, ADDRESS from CUSTOMER;
-- 4. 2014년 7월 4일~ 7월 7일 사이에 주문받은 도서의 주문번호
select ORDERID, ORDERDATE from ORDERBOOKS
where ORDERDATE between '2014-07-04' and '2014-07-07';
-- 5. 2014년 7월 4일~ 7월 7일 사이에 주문받은 도서를 제외한 주문번호
select ORDERID, ORDERDATE from ORDERBOOKS
where ORDERDATE not between '2014-07-04' and '2014-07-07';
-- 6. 성이 '김'씨인 고객의 이름과 주소
select name, ADDRESS from CUSTOMER
where NAME like '김%';
-- 7. 성이 '김'씨이고 이름이 '아'로 끝나는 고객의 이름과 주소
select name, ADDRESS from CUSTOMER
where NAME like '김%아';
-- ---------------------------------------------------------------------------
-- 주문과 고객정보 모두 조회
select *
from CUSTOMER c inner join ORDERBOOKS o
using (custid);
-- 주문과 고객정보 모두 조회 단, 고객번호 순 정렬
select *
from CUSTOMER c inner join ORDERBOOKS o
using (custid) order by custid;
-- 고객의 이름, 고객이 주문한 도서의 가격을 조회 단, 이름, 가격순으로 내림차순 정렬
select name, saleprice
from CUSTOMER c inner join ORDERBOOKS o
using (custid) order by custid, saleprice desc;
-- 고객별로 주문한 책의 총 판매액 단, 고객별 정렬
select name, sum(saleprice) 총판매액
from CUSTOMER c
inner join OrderBooks o
using (custid)
group by name order by name;
-- 고객이름과 고객이 주문한 도서명 조회
select name, bookname
from CUSTOMER c
inner join OrderBooks o
using (custid)
inner join BOOKS b
using (bookid);
-- 2만원 이상 주문한 고객의 이름과 주문한 도서명 조회
select name, bookname
from CUSTOMER
inner join OrderBooks
using (custid)
inner join BOOKS
using (bookid)
where saleprice >20000;
-- 도서를 구매하지 않은 고객 포함하여, 고객이름과 도서명 조회
select NAME, bookname, saleprice
from CUSTOMER c full outer join OrderBooks o
using (custid) full outer join BOOKS b
using (bookid)
where NAME is not null
order by custid, saleprice desc;
-- 김연아 고객의 전화번호 조회
select phone from CUSTOMER
where name ='김연아';
-- 모든 도서명과 가격을 조회
select BOOKNAME, PRICE from BOOKS;
-- 제품 단가의 평균 조회
SELECT AVG(PRICE) "단가 평균" from "BOOKS";
-- 한빛제과에서 제조한 제품들의 재고량 합계 조회
select sum(AGE) from CUSTOMERS
where age = '한빛제과';
-- 가입한 고객 수 조회
select count(to_char(USERNAME)) from CUSTOMERS ;
-- 모든 컬럼을 대상으로 카운트는 별로 좋지않음
select count (*) from CUSTOMERS;
-- 제조업체 수를 조회 (중복을 배제)
select count(distinct PUBLISHER) 제조업체_수
from BOOKS ;
-- 출판사 별 책의 가격 합계
select PUBLISHER 책이름, sum(PRICE) 가격
from BOOKS
group by PUBLISHER
order by 가격 desc;
-- 출판사 별 책의 가격 합계
select PUBLISHER 책이름, count(bookname) 책수, max(price) 가장비싼책의가격
from BOOKS
group by PUBLISHER;
-- 3권 이상 출판한 업체 : 집계함수는 where조건절 대신 having절, select절에서만 사용가능
select PUBLISHER 제조업체, count(BOOKNAME) 책수, max(price) 최고단가
from BOOKS
having count(BOOKNAME) > 2
group by PUBLISHER;
--
select BOOKID 책id, avg(PRICE) 평균가, count(BOOKNAME) from BOOKS
group by BOOKID
having avg(PRICE) >= 1000;
-- 주문제품별로 고객이 주문한 총 수량 조회
select ORDERPROD 주문제품, ORDERNAME 주문고객, sum(UNIT) 주문총수량
from Orders
group by ORDERPROD, ORDERNAME
order by 주문제품;
select avg(AGE) "가입자 평균나이" from CUSTOMERS;
-- 김연아 고객이 주문한 도서의 총 주문금액 조회
select sum(SALEPRICE) from ORDERBOOKS
where CUSTID = 2;
-- 줄 복사는 ctrl + d
-- 고객이 주문한 도서의 총 금액, 평균 주문금액, 최저주문, 최고주문 조회
select sum(SALEPRICE) 총금액, avg(SALEPRICE) 평균주문금액, min(SALEPRICE) 최저주문, max(SALEPRICE) 최고주문
from ORDERBOOKS;
-- 서점의 도서 판매건수 조회
select count(BOOKID) 판매건수 from ORDERBOOKS;
-- 고객별 주문한 도서의 총 건수/ 판매액 조회
select CUSTID, count(BOOKID) 총건수, sum(SALEPRICE) 총판매액 from ORDERBOOKS
group by CUSTID;
-- 가격이 8000이상인 도서를 구매한 고객에대해 고객별 주문수량을 조회 (2권이상인 고객을 대상)
select CUSTID, count(BOOKID) 주문수량 from ORDERBOOKS
where SALEPRICE >= 8000
group by CUSTID
having count(BOOKID)>=2 ;
--select 문법
--select
--from
--where
--group by
--having
--;
-- group by 사용시 주의사항 (* 불가능) 오라클은 기업기반이라 문법 적용이 타이트함
-- 출판사별 출간한 도서의 정보 조회
select PUBLISHER from BOOKS
group by PUBLISHER;
-- 가장 비싼 도서의 이름을 조회
select max(price) from BOOKS;
select BOOKNAME from BOOKS where PRICE = 35000; -- X
select BOOKNAME from BOOKS where PRICE = (select max(price) from BOOKS); -- O
-- 도서를 구매한 적이 있는 고객의 이름을 조회
select distinct CUSTID from ORDERBOOKS;
select name from CUSTOMER
where CUSTID in ( select distinct CUSTID from ORDERBOOKS);
-- 대한미디어에서 출판한 도서를 구매한 사람의 이름을 검색
select BOOKID from BOOKS
where PUBLISHER = '대한미디어';
select CUSTID from ORDERBOOKS
where BOOKID in (3,4);
select name from CUSTOMER
where CUSTID in (select CUSTID from ORDERBOOKS
where BOOKID in ( select BOOKID from BOOKS
where PUBLISHER = '대한미디어'));
-- join문으로 풀기
select * from CUSTOMER c inner join ORDERBOOKS o
on c.CUSTID = o.CUSTID inner join BOOKS b
on o.BOOKID = b.BOOKID where PUBLISHER = '대한미디어';
-- 출판사별로 출판사의 평균도서가격보다 비싼 도서를 조회
select * from BOOKS;
select PUBLISHER, round(avg(PRICE)) from BOOKS group by PUBLISHER;
-- 출판사와는 상관없이 모든 도서를 기준으로 평균가격들보다 높은 도서조회
select BOOKNAME, PRICE from BOOKS where price > all (select round(avg(PRICE)) from BOOKS group by PUBLISHER);
-- 출판사에서 출간한 책들의 평균가격을 조사하고
-- 출판사에서 출간한 책들이 이것보다 비싼 도서 조회
select PUBLISHER, PRICE from BOOKS b1
where b1.PRICE > (select avg(b2.PRICE) from BOOKS b2 where b1.PUBLISHER=b2.PUBLISHER);
create or replace view bkabprice
as
select PUBLISHER, avg(PRICE) avgprice from BOOKS
group by PUBLISHER;
select b1.PUBLISHER, PRICE, avgprice from bkabprice b1 inner join BOOKS b2 on b1.PUBLISHER = b2.PUBLISHER
where PRICE > avgprice;
select bookname, PUBLISHER, price from BOOKS b1 where price > (select avg(price) from books b2 where b1.PUBLISHER = b2.PUBLISHER);
-- from절부터 시작, where조건을 거쳐 select문으로 검색
-- 도서를 주문하지 않은 고객의 이름 조회
select * from CUSTOMER c full outer join ORDERBOOKS o
using (custid) where orderid is null;
select * from CUSTOMER c inner join ORDERBOOKS o
using (custid);
-- (모든 고객 정보 - 도서를 주문한 고객) 차집합 연산
(select * from CUSTOMER) minus
(select * from CUSTOMER where CUSTID in (select CUSTID from ORDERBOOKS));
select * from CUSTOMER where CUSTID not in (select CUSTID from ORDERBOOKS);
-- 주문이 있는 고객의 이름, 주소 조회
-- 단, 상관질의문 (exist) 이용
select name, ADDRESS from CUSTOMER c where exists (select custid from orderbooks o where c.CUSTID=o.CUSTID);
-- 이결과가 있는지 없는지
select name, ADDRESS from CUSTOMER c where not exists (select custid from orderbooks o where c.CUSTID=o.CUSTID);
-- 마당서점 연습문제
--박지성이 구매한 도서의 출판사 수
select CUSTID from CUSTOMER where name ='박지성';
select count(distinct PUBLISHER) 출판사수 from BOOKS
where BOOKID in (select BOOKID from ORDERBOOKS
where CUSTID = (select CUSTID from CUSTOMER where name ='박지성'));
select count(distinct PUBLISHER) 출판사수
from BOOKS b inner join ORDERBOOKS o
on b.BOOKID = o.CUSTID;
-- 박지성이 구매한 도서의 이름, 정가, 판매가격 차이
create or replace view madang3
as
select *
from BOOKS b inner join ORDERBOOKS o
using (bookid) inner join CUSTOMER c
using (custid);
select bookname, price, PRICE-SALEPRICE 가격차이
from madang3 where name='박지성';
-- 박지성이 구매하지 않은 도서의 이름
select BOOKID from ORDERBOOKS where CUSTID = 1;
select PUBLISHER from BOOKS where BOOKID in (1,2,3);
select CUSTID from CUSTOMER where name = '박지성';
select bookname from BOOKS where BOOKID not in ( 1,2,3);
select BOOKNAME from BOOKS
where BOOKID not in (select BOOKID from ORDERBOOKS
where CUSTID = (select CUSTID from CUSTOMER where NAME = '박지성'));
-- 차집합
select bookname from books where BOOKID in ( select BOOKID from books minus
select BOOKID from ORDERBOOKS where CUSTID = 1) ;
select bookname from BOOKS
minus
select BOOKNAME from madang3 where name = '박지성';
-- 상관질의문 (특정 도서정보가 박지성이 주문한 도서목록에 존재하는 지 여부)
select bookname from BOOKS b where not exists
(select BOOKname from CUSTOMER c, ORDERBOOKS o
where c.CUSTID = o.CUSTID
and b.BOOKID = o.BOOKID
and c.name = '박지성');
select *
from BOOKS b inner join ORDERBOOKS o
using (bookid) inner join CUSTOMER c
using (custid) where name = '박지성';
-- 주문하지 않은 고객의 이름 (부속절 사용)
select NAME from CUSTOMER where CUSTID not in (select CUSTID from ORDERBOOKS);
-- 주문금액의 총액과 평균금액
select sum(SALEPRICE) 주문총금액, avg(SALEPRICE) 주문평균금액 from ORDERBOOKS;
-- 고객의 이름과 고객별 구매액
select NAME, sum(SALEPRICE) from madang3 GROUP BY NAME;
-- 고객의 이름과 고객이 구매한 도서목록
select NAME, BOOKNAME from madang3; -- 뷰가 있으면 진짜 편함
-- 도서의가격(book테이블)과 판매가격(orders 테이블) 의 차이가 가장 많은 주문
select bookname, price, PRICE-SALEPRICE 가격차이
from madang3;
-- 조건절에 수식 사용해서 비교가능
select name, BOOKNAME from madang3
where (PRICE- SALEPRICE) = (select max(price-SALEPRICE) from madang3);
-- 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름
select avg(SALEPRICE) 평균판매액 from ORDERBOOKS;
select name, round(avg(SALEPRICE)) 평균구매액 from madang3
group by name
having round(avg(SALEPRICE)) > (select avg(SALEPRICE) 평균판매액 from ORDERBOOKS);