MySQL 쿼리 작성해보기

데이터는 아래를 참조하였다.

-- book table
drop table if exists book;
create table book(
	id bigint auto_increment,
    b_bookname varchar(20) not null,
    b_publisher varchar(10) not null,
    b_price bigint not null,
	constraint book primary key(id)
    );

insert into book(b_bookname, b_publisher, b_price)
	values('축구의 역사','굿스포츠',7000);
insert into book(b_bookname, b_publisher, b_price)
	values('축구스카우팅 리포트','나무수',13000);
insert into book(b_bookname, b_publisher, b_price)
	values('축구의 이해','대한미디어',22000);
insert into book(b_bookname, b_publisher, b_price)
	values('배구 바이블','대한미디어',35000);
insert into book(b_bookname, b_publisher, b_price)
	values('피겨 교본','굿스포츠',8000);
insert into book(b_bookname, b_publisher, b_price)
	values('피칭 단계별기술','굿스포츠',6000);
insert into book(b_bookname, b_publisher, b_price)
	values('야구의 추억','이상미디어',20000);
insert into book(b_bookname, b_publisher, b_price)
	values('야구를 부탁해','이상미디어',13000);
insert into book(b_bookname, b_publisher, b_price)
	values('올림픽 이야기','삼성당',7500);
insert into book(b_bookname, b_publisher, b_price)
	values('olympic champions','pearson',13000);
select * from book;

drop table if exists customer;
create table customer(
	id bigint auto_increment,
    c_name varchar(3) not null,
    c_address varchar(10) not null,
    c_phone varchar(15) default null,
    constraint customer primary key(id)
    );
insert into customer(c_name, c_address, c_phone)
	values('손흥민','영국 런던','010-5000-0001');
insert into customer(c_name, c_address, c_phone)
	values('김연아','대한민국 서울','010-6000-0001');
insert into customer(c_name, c_address, c_phone)
	values('김연경','중국 상하이','010-7000-0001');
insert into customer(c_name, c_address, c_phone)
	values('류현진','캐나다 토론토','010-8000-0001');
insert into customer(c_name, c_address)
	values('이강인','스페인 마요르카');
select * from customer;

drop table if exists orders;
create table orders(
	id bigint auto_increment,
    customer_id bigint,
    book_id bigint,
    o_saleprice bigint,
    o_orderdate date,
    constraint orders primary key(id),
    constraint orders foreign key(customer_id) references customer(id),
    constraint fk_orders foreign key(book_id) references book(id)
    );
    
insert into orders(customer_id, book_id, o_saleprice, o_orderdate)
	values(1, 1, '6000','2021-07-01');
insert into orders(customer_id, book_id, o_saleprice, o_orderdate)
	values(1, 3, '21000','2021-07-03');
insert into orders(customer_id, book_id, o_saleprice, o_orderdate)
	values(2, 5, '8000','2021-07-03');
insert into orders(customer_id, book_id, o_saleprice, o_orderdate)
	values(3, 6, '6000','2021-07-04');
insert into orders(customer_id, book_id, o_saleprice, o_orderdate)
	values(4, 7, '20000','2021-07-05');
insert into orders(customer_id, book_id, o_saleprice, o_orderdate)
	values(1, 2, '12000','2021-07-07');
insert into orders(customer_id, book_id, o_saleprice, o_orderdate)
	values(4, 8, '13000','2021-07-07');
insert into orders(customer_id, book_id, o_saleprice, o_orderdate)
	values(3, 10, '12000','2021-07-08');
insert into orders(customer_id, book_id, o_saleprice, o_orderdate)
	values(2, 10, '7000','2021-07-09');
insert into orders(customer_id, book_id, o_saleprice, o_orderdate)
	values(3, 8, '13000','2021-07-10');

 

 

-- 1. 모든 도서의 가격과 도서명 조회
select b_bookname, b_price from book;

-- 2. 모든 출판사 이름 조회
select b_publisher from book;

-- 2.1 중복값 제외 출판사 이름조회
select distinct b_publisher from book;

-- 3. book 테이블의 모든 내용 조회
select * from book;

-- 4. 20000미만인 도서만 조회
select * from book where b_price < 20000;

-- 5. 10000원이상 20000원이하 도서만 조회
select * from book where b_price <= 20000 and b_price>=10000;
select * from book where b_price between 10000 and 20000;

-- 6. 출판사가 굿스포츠 또는 대한미디어인 도서 조회
select * from book where b_publisher = '굿스포츠' or b_publisher = '대한미디어';
select * from book where b_publisher in('굿스포츠','대한미디어');

-- 7. 도서명에 축구가 포함된 모든 도서를 조회
select * from book where b_bookname like '%축구%';

-- 8. 도서명의 두번째 글자가 구 인 도서 조회
select * from book where b_bookname like '_구%';

-- 9. 축구 관련 도서 중 가격이 20000원이상인 도서 조회
select * from book where b_bookname like '%축구%' and b_price >=20000;

-- 10. 책 이름순으로 전체 도서 조회
select * from book order by b_bookname asc;

-- 11.도서를 가격이 낮은 것부터 조회하고 같은 가격일경우 도서명을 가나다 순으로 조회
select * from book order by b_price, b_bookname asc;




-- 12. 주문 도서의 총 판매액 조회
select sum(o_saleprice) from orders;

-- 13. 1번 고객이 주문한 도서 총 판매액 조회
select sum(o_saleprice) from orders where customer_id = 1;

-- 14. orders 테이블로부터 평균판매가 , 최고판매가 , 최저판매가 조회
select round(avg(o_saleprice),0), max(o_saleprice), min(o_saleprice) from orders;

-- 15. 고객별로 주문한 도서의 총 수량과 총 판매액 조회
select customer_id,count(id),sum(o_saleprice) from orders group by customer_id;

-- 16. 가격이 8천원 이상인 도서를 구매한 고객에 대해 고객별 주문 도서의 총 수량조회(group by 사용)
-- 단 , 8천원이상인 도서 두권이상 구매고객
select customer_id,count(id) from orders 
	where o_saleprice>=8000 
		group by customer_id
			having count(id) >= 2;

-- 17. 김연아고객(고객번호 2) 총 구매액
select sum(o_saleprice) from orders 
	where customer_id = 
		( select id from customer where c_name = '김연아');
        
-- 18. 김연아 고객이 구매한 도서의 수
select count(*) from orders
	where customer_id = 
		( select id from customer where c_name = '김연아');
        
-- 19. 서점의 있는 도서의 총 권수
select count(*) from book;

-- 20. 출판사의 총 수
select count(distinct b_publisher) from book;

-- 21. 7월4일~7일 사이에 주문한 도서의 주문번호 주회
select id from orders where o_orderdate > '2021-07-03' and o_orderdate < '2021-07-08';
select id from orders where o_orderdate > str_to_date('2021-07-03', '%Y-%m-%d') 
						and o_orderdate < str_to_date('2021-07-08', '%Y-%m-%d');
select id from orders where o_orderdate between str_to_date('2021-07-04','%Y-%m-%d')
						and str_to_date('2021-07-07', '%Y-%m-%d');
-- 22. 7월4일~7일 사이에 주문하지않은 도서의 주문번호 조회
select id from orders where o_orderdate > '2021-07-07' or o_orderdate < '2021-07-04';



-- 23. 고객, 주문 테이블 조인하여 고객번호 순으로 정렬
select * from customer c , orders o where c.id = o.customer_id 
	order by c.id asc;
    
select * from customer c inner join orders o on c.id=o.customer_id;

-- 24. 고객이름(CUSTOMER), 고객이 주문한 도서 가격(ORDERS) 조회 
select c_name , o_saleprice from customer c , orders o
	where c.id = o.customer_id
			order by c_name, o_saleprice asc;
-- 25. 고객별(GROUP)로 주문한 도서의 총 판매액(SUM)과 고객이름을 조회하고 조회 결과를 가나다 순으로 정렬
select c_name , sum(o_saleprice) from customer c, orders o
	where c.id = o.customer_id
		group by c.c_name
			order by c.c_name;
-- 26. 고객명과 고객이 주문한 도서명을 조회(3테이블 조인)
select * from customer c , orders o , book b 
	where c.id = o.customer_id and o.book_id = b.id
		order by c.id, c_name , o_saleprice;
-- 27. 2만원(SALEPRICE) 이상 도서를 주문한 고객의 이름과 도서명을 조회 
select c_name , b_bookname from customer c , orders o , book b
	where c.id = o.customer_id and o.book_id = b.id
		and o.o_saleprice >= 20000;
-- 28. 손흥민 고객의 총 구매액과 고객명을 함께 조회
select c_name , sum(o_saleprice) from customer c , orders o
	where c.id = o.customer_id
		and c.c_name = (select c_name from customer c where c_name = '손흥민')
		group by c.c_name;
-- 29. 손흥민 고객의 총 구매수량과 고객명을 함께 조회
select c_name , count(o.id) from customer c , orders o
	where c.id = o.customer_id
		and c.c_name = (select c_name from customer c where c_name = '손흥민')
		group by c.c_name;
-- 30. 가장 비싼 도서의 이름을 조회 
select b_bookname from book
	where b_price = ( select max(b_price) from book );
-- 31. 책을 구매한 이력이 있는 고객의 이름을 조회
select distinct c_name from customer c , orders o
	where c.id = o.customer_id;
-- 32. 도서의 가격(PRICE)과 판매가격(SALEPRICE)의 차이가 가장 많이 나는 주문 조회 
select * from orders o , book b 
	where o.book_id = b.id
    and o.o_saleprice = ( select max(b_price - o_saleprice) from orders);
-- 33. 고객별 평균 구매 금액이 도서의 판매 평균 금액 보다 높은 고객의 이름 조회 
select c_name from customer c , orders o where c.id = o.customer_id group by c.id
	having avg(o_saleprice) > (select avg(o_saleprice) from orders);
-- 34. 고객번호가 5인 고객의 주소를 대한민국 인천으로 변경
update customer set c_address='대한민국 인천' where id= 5;
-- 35. 김씨 성을 가진 고객이 주문한 총 판매액 조회
select c_name, sum(o_saleprice) from customer c , orders o
	where c.id = o.customer_id and c_name like '김__'
		group by c_name;