Untitled

insert into customers values ('Nguyễn Trãi','Việt Nam','nva@gmail.com','0934300560','Hà Nội','Nguyễn Văn','Tèo','2000-12-05')
insert into customers values ('Hai bà trưng','Việt Nam','nvb@gmail.com','0355300378','Hà Nội','Nguyễn Văn','Toàn','2001-11-30')
insert into customers values ('Cầu Giấy','Việt Nam','nvc@gmail.com','0873898800','Hà Nội','Nguyễn Văn','Tùng','1990-01-31')
insert into customers values ('Bãi Cháy','Việt Nam','abc@gmail.com','0905663644','Hạ Long','Trần Thị ','Hà','1999-03-05')
insert into customers values ('Hòa Bình','Việt Nam','zxc@gmail.com','0357930045','Hạ Long','Dương Minh','Viễn','1998-12-23')
insert into customers values ('Cầu Giấy','Việt Nam','mtp@gmail.com','0987654321','Hà Nội','Sơn','Tùng','1997-02-28')
insert into customers values ('Tuần Châu','Việt Nam','jqk@gmail.com','034356987','Hạ Long','Thần','Bài','1997-06-25')
insert into customers values ('Quận 1','Việt Nam','qka@gmail.com','0123456789','TP.Hồ Chí Minh','Mã Văn','Tài','1995-06-03')
insert into customers values ('Quận 1','Việt Nam','ubjq@gmail.com','0654123789','TP.Hồ Chí Minh','Vũ Thị','Lan','1993-04-25')
insert into customers values ('Quận 2','Việt Nam','pop@gmail.com','0258963147','TP.Hồ Chí Minh','Nguyện Thị','Quỳnh','2000-10-24')
insert into customers values ('Nguyễn Trãi','Việt Nam','ntt@gmail.com','0245698731','Hà Nội','Nguyễn Thu','Thảo','2003-12-08')
insert into customers values ('Cẩm Phả','Việt Nam','ttt@gmail.com','0963258741','Hạ Long','Nguyễn Thị','Nhung','1994-01-05')
insert into customers values ('Hà Tu','Việt Nam','asd@gmail.com','0236514789','Hạ Long','Lò Văn','Tôn','1999-10-15')
insert into customers values ('Quận 10','Việt Nam','zxc@gmail.com','0365984550','TP.Hồ Chí Minh','Phan Thị','Châu','2002-09-05')
insert into customers values ('Trần Hưng Đạo','Việt Nam','nvn@gmail.com','098765332','Hà Nội','Trần Văn','Tí','1997-11-19')
insert into customers values ('Lê Hồng Phong','Việt Nam','jop@gmail.com','0689742536','Hà Nội','Lê Văn','Vở','1998-07-15')
insert into customers values ('Trương Định','Việt Nam','vua@gmail.com','0398746521','Hà Nội','Đinh','Thiện','2001-04-30')
insert into customers values ('Móng Cái','Việt Nam','qwe@gmail.com','0963753159','Hạ Long','Văn','Tùng','1996-03-18')
insert into customers values ('Hoành Bồ','Việt Nam','ghu@gmail.com','0659732147','Hạ Long','Đinh Văn','Quang','1993-03-08')
insert into customers values ('Hòn Gai','Việt Nam','uio@gmail.com','0765314895','Hạ Long','Vương Văn','Hà','1992-04-15')
insert into category values ('Áo')
insert into category values ('Quần')
insert into category values ('Mỹ Phẩm')
insert into category values ('Bánh')
insert into category values ('Kẹo')
insert into category values ('Đồ Chơi')
insert into category values ('Mô Hình')
insert into category values ('Đồ Điện Tử')
insert into category values ('Đồ Gia Dụng')
insert into category values ('Đồ Văn Phòng')
insert into category values ('Đồ Bếp')
insert into category values ('Đồ Dùng Học Tập')
insert into category values ('Sách')
insert into category values ('Phụ Kiện')
insert into category values ('Giày Dép')
insert into products values('150000','0','Áo Phông','Hàng Việt Nam chất lượng cao','001')
insert into products values('450000','0.1','Áo Khoác','Áo khoác mùa đông dành cho nam giới ','001')
insert into products values('150000','0.05','Quần Jean','Quần Châu Âu trẻ trung thời trang','002')
insert into products values('35000','0','Bánh Chocopie','15 cái/hộp','004')
insert into products values('70000','0','Sữa rửa mặt','20g trị mụn và tàn nhan','003')
insert into products values('175000','0.25','Quần Âu','Thời trang phong cách và trẻ trung','002')
insert into products values('15000','0','Kẹo Sữa','được làm từ sữa','005')
insert into products values('100000','0','Máy Bay Đồ Chơi','An toàn và dễ sử dụng','006')
insert into products values('300000','0.1','Mô Hình Lego','An toàn ,chất lượng cao','007')
insert into products values('700000','0','Đồng hồ điện tử','bền ,phong cách','008')
insert into products values('1000000','0.25','Tủ gỗ ','làm từ gỗ 100%','009')
insert into products values('15000','0','bút bi','thân thiện và an toàn','010')
insert into products values('80000','0.15','Sách là bạn','Tác giả Yasuo','013')
insert into products values('125000','0.05','Chảo chống dính','Hàng Việt Nam chất lượng cao','011')
insert into products values('55000','0','dao làm bếp','An toàn và dễ sử dụng ','011')
insert into products values('15000','0','Thước kẻ','Nhựa an toàn','012')
insert into products values('175000','0.15','Tai nghe','Hãng Acer','014')
insert into products values('250000','0.05','Giày Vải','Adidas','015')
insert into products values('15000','0','Compa','','012')
insert into products values('150000','0','Dép tổ ong','Hàng Việt Nam chất lượng cao','015')
insert into orders values('1','2021-01-15')
insert into orders values('2','2021-01-23')
insert into orders values('1','2021-02-15')
insert into orders values('2','2021-02-28')
insert into orders values('2','2021-03-18')
insert into orders values('3','2021-04-15')
insert into orders values('4','2021-04-30')
insert into orders values('5','2021-05-15')
insert into orders values('5','2021-06-16')
insert into orders values('6','2021-07-17')
insert into orders values('7','2021-08-18')
insert into orders values('8','2021-09-19')
insert into orders values('9','2021-05-20')
insert into orders values('10','2021-10-21')
insert into orders values('11','2021-12-25')
insert into orders values('11','2021-11-26')
insert into orders values('11','2021-12-15')
insert into orders values('12','2021-10-30')
insert into orders values('13','2021-08-27')
insert into orders values('14','2021-07-16')
insert into orders values('15','2021-02-08')
insert into orders values('15','2021-03-23')
insert into orders values('14','2021-04-19')
insert into orders values('1','2021-05-25')
insert into orders values('4','2021-01-31')
insert into orders values('7','2021-02-28')
insert into orders values('8','2021-08-15')
insert into orders values('9','2021-10-17')
insert into orders values('10','2021-09-05')
insert into orders values('3','2021-11-09')
insert into orders_products values('1','1','1')
insert into orders_products values('1','2','1')
insert into orders_products values('2','3','2')
insert into orders_products values('1','4','1')
insert into orders_products values('1','5','1')
insert into orders_products values('1','6','1')
insert into orders_products values('2','15','1')
insert into orders_products values('2','14','2')
insert into orders_products values('2','13','1')
insert into orders_products values('3','10','4')
insert into orders_products values('4','7','1')
insert into orders_products values('5','8','3')
insert into orders_products values('6','9','2')
insert into orders_products values('7','10','1')
insert into orders_products values('8','1','1')
insert into orders_products values('9','2','2')
insert into orders_products values('10','3','3')
insert into orders_products values('11','4','2')
insert into orders_products values('11','5','1')
insert into orders_products values('12','6','5')
insert into orders_products values('12','7','1')
insert into orders_products values('12','8','1')
insert into orders_products values('13','1','2')
insert into orders_products values('13','3','1')
insert into orders_products values('14','12','3')
insert into orders_products values('15','13','1')
insert into orders_products values('16','15','1')
insert into orders_products values('16','14','1')
insert into orders_products values('17','14','1')
insert into orders_products values('18','1','1')
insert into orders_products values('19','2','1')
insert into orders_products values('20','1','1')
insert into orders_products values('20','3','1')
insert into orders_products values('21','10','2')
insert into orders_products values('22','11','1')
insert into orders_products values('23','15','10')
insert into orders_products values('24','14','1')
insert into orders_products values('25','1','2')
insert into orders_products values('15','2','2')
insert into orders_products values('16','7','1')
insert into orders_products values('27','8','1')
insert into orders_products values('28','10','1')
insert into orders_products values('29','14','5')
insert into orders_products values('29','9','1')
insert into orders_products values('30','8','1')
insert into bill_details values('880000.000','1')
insert into bill_details values('685000.000','2')
insert into bill_details values('2800000.000','3')
insert into bill_details values('15000.000','4')
insert into bill_details values('300000.000','5')
insert into bill_details values('600000.000','6')
insert into bill_details values('700000.000','7')
insert into bill_details values('150000.000','8')
insert into bill_details values('900000.000','9')
insert into bill_details values('450000.000','10')
insert into bill_details values('140000.000','11')
insert into bill_details values('990000.000','12')
insert into bill_details values('450000.000','13')
insert into bill_details values('45000.000','14')
insert into bill_details values('80000.000','15')
insert into bill_details values('180000.000','16')
insert into bill_details values('125000.000','17')
insert into bill_details values('150000.000','18')
insert into bill_details values('450000.000','19')
insert into bill_details values('300000.000','20')
insert into bill_details values('1400000.000','21')
insert into bill_details values('1000000.000','22')
insert into bill_details values('550000.000','23')
insert into bill_details values('125000.000','24')
insert into bill_details values('1200000.000','25')
insert into bill_details values('15000.000','26')
insert into bill_details values('100000.000','27')
insert into bill_details values('700000.000','28')
insert into bill_details values('925000.000','29')
insert into bill_details values('100000.000','30')
-------------------------------
-- Cháu nào mua nhiều  
select customers.*,A.numbers_purcharses
from customers,(select customerID,COUNT(customerID) as numbers_purcharses
from orders
group by customerID ) as A
where customers.customerID=A.customerID
order by numbers_purcharses DESC
-------------------------------
-- hàng nào bán chạy 
select product_name,A.soluong
from products,(select productID ,SUM(quantity) AS soluong
from orders_products
group by productID) as A
where products.productID=A.productID and A.soluong>=5
-------------------------------
-- thu nhập hàng tháng  
select B.month,B.year,SUM(cost_total) as income
from(
select A.month,A.year,cost_total
from bill_details,( select orders.orderID, MONTH(order_date) as month,YEAR(order_date) as year
from orders) as A
where bill_details.orderID=A.orderID) as B
group by B.month,B.year
-------------------------------
-- tiền theo từng đơn hàng (cái này để ghi vào bill detail cho dễ )
select C.orderID,SUM(tien) as totalcost
from(select A.orderID,A.cost*A.quantity as tien
from (select orderID,quantity,products.cost
from orders_products ,products
where orders_products.productID=products.productID) as A) as C
group by C.orderID
-------------------------------
-- khách hàng vàng (cháu nào dùng nhiều tiền )
select customers.last_name,customers.first_name,A.total
from customers,(select orders.customerID,SUM(cost_total) as total
from bill_details,orders
where orders.orderID=bill_details.orderID
group by customerID) as A
where customers.customerID=a.customerID
order by total DESC