[DataBase] SQL 실습

[DataBase] SQL 실습

서점 데이터 (Create Table)

---

• Book (bookid, bookname, publisher, price)
• Orders (orderid, custid, bookid, saleprice, orderdate)
• Customer (custid, name, address, phone)

create table Book (
    bookid int,
    bookname varchar(20),
    publisher varchar(15),
    price numeric(8,2),
    primary key (bookid));


create table Customer (
    custid int,
    name char(5),
    address varchar(20),
    phone varchar(15),
    primary key (custid));
    
    
create table Orders (
	orderid int,
    custid int,
    bookid int,
    saleprice numeric(8,2),
    orderprice varchar(20),
    primary key (orderid),
    foreign key (bookid) references Book(bookid),
	foreign key (custid) references Customer(custid));

서점 데이터 (Insert)

---

• Book Table

bookid	bookname	publisher	price
1	축구의 역사	굿스포츠	7000
2	축구 아는 여자	나무수	13000
3	축구의 이해	대한 미디어	22000
4	골프 바이블	대한 미디어	35000
5	피겨 교본	굿스포츠	8000
6	역도 단계별기술	굿스포츠	6000
7	야구의 추억	이상미디어	20000
8	야구를 부탁해	이상미디어	13000
9	올림픽 이야기	삼성당	7500
10	Olympic Champions	Pearson	13000

• Customer Table

custid	name	address	phone
1	박지성	영국 맨체스타	000-5000-0001
2	김연아	대한민국 서울	000-6000-0001
3	장미란	대한민국 강원도	000-7000-0001
4	추신수	미국 클리블랜드	000-8000-0001
5	박세리	대한민국 대전	NULL

• Order Table

orderid	custid	bookid	saleprice	orderdate
1	1	1	6000	2013-07-01
2	1	3	21000	2013-07-03
3	2	5	8000	2013-07-03
4	3	6	6000	2013-07-04
5	4	7	20000	2013-07-05
6	1	2	12000	2013-07-07
7	4	8	13000	2013-07-07
8	3	10	12000	2013-07-08
9	2	10	7000	2013-07-09
10	3	8	13000	2013-07-10

insert into Book (bookid, bookname, publisher, price) values
	('1', '축구의 역사', '굿스포츠', 7000),
    ('2', '축구 아는 여자', '나무수', 13000),
    ('3', '축구의 이해', '대한미디어', 22000),
    ('4', '골프 바이블', '대한미디어', 35000),
 	('5', '피겨 교본', '굿스포츠', 8000),
    ('6', '역도 단계별기술', '굿스포츠', 6000),
  	('7', '야구의 추억', '이상미디어', 20000),
    ('8', '야구를 부탁해', '이상미디어', 13000),
    ('9', '올림픽 이야기', '삼성당', 7500),
 	('10', 'Olympic Champions', 'Pearson', 13000);
 	
 	
insert into Customer (custid, name, address, phone) values
    ('1', '박지성', '영국 맨체스타', '000-5000-0001'),
    ('2', '김연아', '대한민국 서울', '000-6000-0001'),
    ('3', '장미란', '대한민국 강원도', '000-7000-0001'),
    ('4', '추신수', '미국 클리블랜드', '000-8000-0001'),
    ('5', '박세리', '대한민국 대전', NULL);
    

insert into Orders (orderid, custid, bookid, saleprice, orderdate) values
	('1', '1', '1', 6000, '2013-07-01'),
    ('2', '1', '3', 21000, '2013-07-03'),
    ('3', '2', '5', 8000, '2013-07-03'),
    ('4', '3', '6', 6000, '2013-07-04'),
    ('5', '4', '7', 20000, '2013-07-05'),
    ('6', '1', '2', 12000, '2013-07-07'),
    ('7', '4', '8', 13000, '2013-07-07'),
    ('8', '3', '10', 12000, '2013-07-08'),
    ('9', '2', '10', 7000, '2013-07-09'),
    ('10', '3', '8', 13000, '2013-07-10');

서점 데이터 (Select)

---

1. 모든 도서의 이름과 가격을 검색하시오

select bookname, price from Book;

1. 모든 도서의 도서번호, 도서이름, 출판사, 가격을 검색하시오

select * from Book;

1. 도서 테이블에 있는 모든 출판사를 검색하시오

select publisher from Book;

1. 가격이 20,000원 미만인 도서를 검색하시오

select * from Book where price < 20000;

1. 가격이 10,000원 이상 20,000 이하인 도서를 검색하시오

select * from Book where price >= 10000 and price <= 20000;

서점 데이터 (Join)

---

1. 고객과 고객의 주문에 관한 데이터를 모두 보이시오.

select * from customer, orders where customer.custid = orders.custid;

1. 고객의 이름과 고객이 주문한 도서의 가격을 검색하시오.

   

2. 고객의 이름과 고객이 주문한 도서의 이름을 구하시오.

select customer.name, book.bookname 
from customer, book, orders
where customer.custid = orders.custid and orders.bookid = book.bookid;

1. 가격이 20,000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오.

select customer.name, book.bookname
from customer, book, orders
where customer.custid = orders.custid 
	and orders.bookid = book.bookid 
	and book.saleprice = 20000;

서점 데이터(Orderby)

---

1. 고객과 고객의 주문에 관한 데이터를 고객별로 정렬하여 보이시오.

select * from customer, orders
where customer.custid = orders.custid
order by customer.custid;

1. 도서이름에 ‘축구’가 포함된 출판사를 검색하시오.

select bookname, publisher from book
where bookname like '%축구%';

1. 도서이름 왼쪽 두 번째 위치에 ‘구’라는 문자열을 갖는 도서를 모두 검색하시오.

select * from book
where bookname like '_구%';

1. 출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’인 도서를 검색하시오.

select * from book
where publisher='굿스포츠' or publisher = '대한미디어';

1. 도서를 이름순으로 검색하시오.

select * from book
order by bookname;

1. 도서를 가격의 내림차순으로 검색하시오. 만약 가격이 같다면 출판사의 오름차순으로 검색한다.

select * from book
order by price desc, publisher asc;

서점 데이터(As)

---

1. 고객이 주문한 도서의 총 판매액을 구하시오.

select sum(saleprice) as '총 판매액'
from orders;

1. 2번 김연아 고객이 주문한 도서의 총 판매액을 구하시오

select sum(saleprice) as '총 매출'
from orders where custid = 2;

1. 고객이 주문한 도서의 총 판매액, 평균값, 최저가, 최고가를 구하시오.

select sum(saleprice) as Total,
avg(saleprice) as Average,
min(saleprice) as Minimum,
max(saleprice) as Maximum
from orders;

1. 마당서점의 도서 판매 건수를 구하시오.

select count(*) as '판매 건수' from orders;

1. 고객별로 주문한 도서의 총 슈량과 총 판매액을 구하시오.

select custid, count(*) as '도서 수량', sum(saleprice) as '총 액'
from orders
group by custid;

1. 가격이 8000원 이상인 도서를 구매한 고객에 대하여 고객별 주문 도서의 총 수량을 구하시오. 단, 2권 이상 구매한 고객만 구한다.

select custid, count(*) as '도서 수량'
from orders where saleprice >= 8000
group by custid having count(*) >= 2;

---