Untitled
unknown
sql
3 years ago
12 kB
9
Indexable
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
Editor is loading...