create database APUBookStore

create table Publishers
(
	Publisher_ID varchar (255) PRIMARY KEY NOT NULL,
	Publisher_Name varchar (255),
	Publisher_Address varchar (255),
	City varchar (255),
	State varchar (255),
	Country varchar (255)
)

insert into Publishers values ('P001', 'Sunshine', 'Falcon9001','Sunshine City', 'Washington', 'Korea')
insert into Publishers values ('P002', 'Moonight', 'Sesame9002', 'Moonight City', 'Busan', 'America')
insert into Publishers values ('P003', 'Arise', 'Pine9003', 'Arise City', 'Yokohama', 'Japan')
insert into Publishers values ('P004', 'Luna', 'Maple9004', 'Luna City', 'Guangzhou', 'China')
insert into Publishers values ('P005', 'Eclipse', 'Cedar9005', 'Eclipse City', 'Nagaland', 'India')


create table Publisher_Stock
(
	Book_ID varchar (255) PRIMARY KEY NOT NULL,
	Publisher_ID varchar (255) references Publishers (Publisher_ID),
	Book_Title varchar (255),
	Book_Quantity varchar (255),
	Book_Price varchar (255),
	Book_Category varchar (255),
	Book_Language varchar (255)
)

insert into Publisher_Stock values ('B001', 'P001', 'Cinderella', '20', '5', 'Romance', 'English')
insert into Publisher_Stock values ('B002', 'P001', 'Mermaid', '20', '10', 'Romance', 'English')
insert into Publisher_Stock values ('B003', 'P002', 'Alladdin', '20', '15', 'Adventure', 'English')
insert into Publisher_Stock values ('B004', 'P002', 'Frozen', '20', '15', 'Adventure', 'English')
insert into Publisher_Stock values ('B005', 'P003', 'Cars', '20', '10', 'Adventure', 'Malay')
insert into Publisher_Stock values ('B006', 'P003', 'Toy Story', '20', '5', 'Adventure', 'Malay')
insert into Publisher_Stock values ('B007', 'P004', 'Mulan', '20', '5', 'Adventure', 'Chinese')
insert into Publisher_Stock values ('B008', 'P004', 'Snowwhite', '20', '10', 'Romance', 'Chinese')
insert into Publisher_Stock values ('B009', 'P005', 'Rapunzel', '20', '15', 'Romance', 'Chinese')
insert into Publisher_Stock values ('B010', 'P005', 'Mickey', '20', '20', 'Child', 'English')

create table Invoice
(
	Invoice_ID varchar (255) PRIMARY KEY NOT NULL,
	Invoice_Date varchar (255),
	Publisher_ID varchar (255) references Publishers (Publisher_ID),
	Book_Quantity int,
	Total_Price varchar (255)
)

insert into Invoice values ('I001', '02/01/17', 'P001', '5', '25')
insert into Invoice values ('I002', '02/01/17', 'P001', '10', '100')
insert into Invoice values ('I003', '03/02/17', 'P002', '15', '225')
insert into Invoice values ('I004', '03/02/17', 'P002', '15', '225')
insert into Invoice values ('I005', '04/03/17', 'P003', '10', '100')
insert into Invoice values ('I006', '04/03/17', 'P003', '5', '25')
insert into Invoice values ('I007', '05/04/17', 'P004', '5', '25')
insert into Invoice values ('I008', '05/04/17', 'P004', '10', '100')
insert into Invoice values ('I009', '06/05/17', 'P005', '15', '225')
insert into Invoice values ('I010', '06/05/17', 'P005', '20', '400')

create table Store_Order
(
	Store_Order_ID varchar (255) PRIMARY KEY NOT NULL,
	Book_ID varchar (255) references Publisher_Stock (Book_ID),
	Invoice_ID varchar (255) references Invoice (Invoice_ID),
	Publisher_ID varchar (255) references Publishers (Publisher_ID),
	Book_Quantity int,
	Order_Date int
)

insert into Store_Order values ('S001', 'B001', 'I001', 'P001', '5', '010117')
insert into Store_Order values ('S002', 'B002', 'I002', 'P001', '10', '010117')
insert into Store_Order values ('S003', 'B003', 'I003', 'P002', '15', '020217')
insert into Store_Order values ('S004', 'B004', 'I004', 'P002', '15', '020217')
insert into Store_Order values ('S005', 'B005', 'I005', 'P003', '10', '030317')
insert into Store_Order values ('S006', 'B006', 'I006', 'P003', '5', '030317')
insert into Store_Order values ('S007', 'B007', 'I007', 'P004', '5', '040417')
insert into Store_Order values ('S008', 'B008', 'I008', 'P004', '10', '040417')
insert into Store_Order values ('S009', 'B009', 'I009', 'P005', '15', '050517')
insert into Store_Order values ('S010', 'B010', 'I010', 'P005', '20', '050517')

create table Store_Inventory
(
	Store_Order_ID varchar (255) references Store_Order (Store_Order_ID),
	Publisher_ID varchar (255) references Publishers (Publisher_ID),
	Book_Quantity varchar (255),
	Order_Received_Date int
)

insert into Store_Inventory values ('S001', 'P001', '5', '030117')
insert into Store_Inventory values ('S002', 'P001', '10', '030117')
insert into Store_Inventory values ('S003', 'P002', '15', '040217')
insert into Store_Inventory values ('S004', 'P002', '15', '040217')
insert into Store_Inventory values ('S005', 'P003', '10', '050317')
insert into Store_Inventory values ('S006', 'P003', '5', '050317')
insert into Store_Inventory values ('S007', 'P004', '5', '060417')
insert into Store_Inventory values ('S008', 'P004', '10', '060417')
insert into Store_Inventory values ('S009', 'P005', '15', '070517')
insert into Store_Inventory values ('S010', 'P005', '20', '070517')

create table Member_Detail
(
	Member_ID varchar (255) PRIMARY KEY NOT NULL,
	Member_Name varchar (255),
	Member_Contact_Number int,
	Member_Email_Address varchar (255),
	Member_Address varchar (255),
	Gender varchar (255)
)

insert into Member_Detail values ('M001', 'Lee', '1001', 'lee@gmail.com', 'Falcon001M', 'Male')
insert into Member_Detail values ('M002', 'Chai', '1002', 'chai@gmail.com', 'Sesame002M', 'Male')
insert into Member_Detail values ('M003', 'Chua', '1003', 'chua@gmail.com', 'Pine003M', 'Male')
insert into Member_Detail values ('M004', 'Shi Xuan', '1004', 'shixuan@gmail.com', 'Maple004M', 'Female')
insert into Member_Detail values ('M005', 'Marcus', '1005', 'marcus@gmail.com', 'Cedar005M', 'Male')


create table Member_Cart
(
	Member_Order_ID varchar (255) PRIMARY KEY NOT NULL,
	Member_ID varchar (255) references Member_Detail (Member_ID),
	Book_ID varchar (255) references Publisher_Stock (Book_ID),
	Book_Quantity int,
	Book_Price int,
	Book_Order_Date int,
	Total_Price int,
	Status varchar (255)
)

insert into Member_Cart values ('MM001', 'M001', 'B001', '1', '5', '050117', '5', 'Paid')
insert into Member_Cart values ('MM002', 'M001', 'B002', '2', '10', '050117', '20', 'Unpaid')
insert into Member_Cart values ('MM003', 'M002', 'B003', '2', '15', '060217', '30', 'Paid')
insert into Member_Cart values ('MM004', 'M002', 'B004', '1', '15', '060217', '15', 'Unpaid')
insert into Member_Cart values ('MM005', 'M003', 'B005', '1', '10', '070317', '10', 'Paid')
insert into Member_Cart values ('MM006', 'M003', 'B006', '2', '5', '070317', '10', 'Unpaid')
insert into Member_Cart values ('MM007', 'M004', 'B007', '2', '5', '080417', '10', 'Paid')
insert into Member_Cart values ('MM008', 'M004', 'B008', '1', '10', '080417', '10', 'Unpaid')
insert into Member_Cart values ('MM009', 'M005', 'B009', '1', '15', '090517', '15', 'Paid')
insert into Member_Cart values ('MM010', 'M005', 'B010', '2', '20', '0901517', '40', 'Unpaid')

create table Member_Payment
(
	Member_Order_ID varchar (255) references Member_Cart (Member_Order_ID),
	Member_Payment_ID varchar (255) PRIMARY KEY NOT NULL,
	Member_ID varchar (255) references Member_Detail (Member_ID),
	Book_ID varchar (255) references Publisher_Stock (Book_ID),
	Book_Quantity int,
	Total_Price int,
	Payment_Date int
)

insert into Member_Payment values ('MM001', 'MP001', 'M001', 'B001', '1', '5', '050117')
insert into Member_Payment values ('MM003', 'MP003', 'M002', 'B003', '2', '30', '060117')
insert into Member_Payment values ('MM005', 'MP005', 'M003', 'B005', '1', '10', '070117')
insert into Member_Payment values ('MM007', 'MP007', 'M004', 'B007', '2', '10', '080117')
insert into Member_Payment values ('MM009', 'MP009', 'M005', 'B009', '1', '15', '090117')

create table Book_Review
(
	Review_ID varchar (255) PRIMARY KEY NOT NULL,
	Review_Date int,
	Book_ID varchar (255) references Publisher_Stock (Book_ID),
	Member_ID varchar (255) references Member_Detail (Member_ID),
	Feedback varchar (255),
	Rating int
)

insert into Book_Review values ('R001', '060117', 'B001', 'M001', 'Good', 8)
insert into Book_Review values ('R002', '070217', 'B003', 'M002', 'Bad', 3)
insert into Book_Review values ('R003', '080317', 'B005', 'M003', 'Average', 6)
insert into Book_Review values ('R004', '090417', 'B007', 'M004', 'Good', 9)
insert into Book_Review values ('R005', '100517', 'B009', 'M005', 'Average', 5)

create table Book_Category
(
	Book_Category_Type varchar (255),
	Publisher_ID varchar (255) references Publishers (Publisher_ID),
	Book_ID varchar (255) references Publisher_Stock (Book_ID),
	Book_Quantity int
)

insert into Book_Category values ('Romance', 'P001', 'B001', '20')
insert into Book_Category values ('Romance', 'P001', 'B002', '20')
insert into Book_Category values ('Adventure', 'P002', 'B003', '20')
insert into Book_Category values ('Adventure', 'P002', 'B004', '20')
insert into Book_Category values ('Adventure', 'P003', 'B005', '20')
insert into Book_Category values ('Adventure', 'P003', 'B006', '20')
insert into Book_Category values ('Adventure', 'P004', 'B007', '20')
insert into Book_Category values ('Romance', 'P004', 'B008', '20')
insert into Book_Category values ('Romance', 'P005', 'B009', '20')
insert into Book_Category values ('Child', 'P005', 'B010', '20')


--Question 1--

select P_S.Book_ID, P_S.Book_Title, P_S.Book_Quantity, P_S.Book_Price, P.Publisher_ID
from Publishers P
inner join Publisher_Stock P_S
on P.Publisher_ID = P_S.Publisher_ID

--Question 2--

select S_O.Store_Order_ID, S_O.Book_ID, S_O.Publisher_ID, S_O.Book_Quantity, B_C.Book_Category_Type
from Store_Order S_O, Book_Category B_C
where S_O.Book_ID = B_C.Book_ID
AND S_O.Publisher_ID = B_C.Publisher_ID

--Question 3--

select P.Publisher_Name, P.Publisher_Address, I.Invoice_ID, I.Invoice_Date, I.Book_Quantity, I.Total_Price
from Publishers P
inner join Invoice I
on P.Publisher_ID = I.Publisher_ID

--Question 4--

select count(Member_ID) as No_of_Members
from Member_Detail

select count(Gender) as No_of_Male
from Member_Detail
where Gender not like '%Fe%'

select count(Gender) as No_of_Female
from Member_Detail
where Gender like '%Fe%'

--Question 5--

select M_D.Member_ID, M_D.Member_Address, M_D.Member_Contact_Number, M_P.Member_Payment_ID, M_P.Book_ID, M_P.Book_Quantity, M_P.Payment_Date
from Member_Detail M_D
inner join Member_Payment M_P
on M_D.Member_ID = M_P.Member_ID

--Question 6--

select Book_Category_Type, Book_ID, Book_Quantity
from Book_Category

--Question 7--

select Book_Category_Type, sum (Book_Quantity) as No_of_Romance
from Book_Category
group by Book_Category_Type

--Question 8--

select Member_ID, sum (Book_Quantity) as No_of_Books, sum (Total_Price) as Total_Price
from Member_Cart
group by Member_ID

--Question 9--

select Member_ID, Review_ID, Book_ID, Rating,
case
when Rating = 0 then 'Terrible'
when Rating > 6 then 'Good'
when Rating >= 5 and Rating <= 6 then 'Average'
when Rating < 5 then 'Bad'
else NULL
end as [Feedback]
from Book_Review