CREATE DATABASE file;
USE file;

CREATE TABLE customer (
	user_id varchar(50),
    user_name varchar(50),
    membership int);
    
CREATE TABLE library (
	book_id int,
    book_name varchar(50),
    price int);
    
CREATE TABLE orderinfo (
	order_no int,
    buyer_id varchar(50),
    book_id int);
    
insert into customer values ('랄로123', '랄로', 2);
insert into customer values ('도파123', '도파', 1);
insert into customer values ('파카123', '파카', 3);
insert into customer values ('미야123', '미야', 4);
insert into customer values ('말구123', '말구', 1);

insert into library values (10, '메이플 1권', 30000);
insert into library values (11, '메이플 2권', 31000);
insert into library values (12, '메이플 3권', 32000);
insert into library values (13, '메이플 4권', 33000);

insert into orderinfo values (1, '랄로123', '12');
insert into orderinfo values (2, '도파123', '10');
insert into orderinfo values (3, '도파123', '11');
insert into orderinfo values (4, '파카123', '13');
insert into orderinfo values (5, '미야123', '12');
insert into orderinfo values (6, '말구123', '10');
insert into orderinfo values (7, '말구123', '13');

select*from customer;
delete from customer;
select*from library;
delete from library;
select*from orderinfo;
delete from orderinfo;

#문제: 멤버십등급이 1등급인 고객을 찾아 해당 고객의 아이디와 전체 기간에 대한 누적 구매액을 구하시오.
#ex) 
#고객 아이디   누적 구매액
# 도파123      100000

SELECT sum(price)
FROM library
WHERE book_id = '10';

SELECT user_id as '고객 아이디'
FROM customer
WHERE membership = 1;

#join으로 풀면 될듯...
#정답 쿼리문
SELECT c.user_name, sum(lb.price)
FROM customer c, orderinfo oi, library lb
WHERE c.user_id = oi.buyer_id 
	AND lb.book_id = oi.book_id
    AND c.membership = 1
GROUP BY c.user_id;