数据库0411小练习

  数据库0411小练习：

新建一张orderitem表，字段及数据如下图：

 

CREATE TABLE orderitem(
id INT NOT NULL,
product VARCHAR(10) NOT NULL,
price VARCHAR(10) NOT NULL
)
SELECT * FROM orderitem
INSERT INTO orderitem(id,product,price) VALUES (1,'电视机',2999);
INSERT INTO orderitem(id,product,price) VALUES (2,'电视机',2999);
INSERT INTO orderitem(id,product,price) VALUES (3,'洗衣机',1000);
INSERT INTO orderitem(id,product,price) VALUES (4,'洗衣机',1000);
INSERT INTO orderitem(id,product,price) VALUES (5,'洗衣机',1000);
INSERT INTO orderitem(id,product,price) VALUES (6,'冰箱',3999);
INSERT INTO orderitem(id,product,price) VALUES (7,'冰箱',3999);
INSERT INTO orderitem(id,product,price) VALUES (8,'空调',1999);

 

 

完成以下sql语句：

1.按商品名称统计，每类商品所购买的个数：

 SELECT product,COUNT(*) FROM orderitem GROUP BY product

2.按商品名称统计，每类商品所花费的总金额：

 SELECT product,SUM(price) FROM orderitem GROUP BY product

3.按商品名称统计，统计每类商品花费的总金额在5000元以上的商品

 SELECT product,SUM(price) FROM orderitem GROUP BY product HAVING SUM(price)>5000 

4.按商品名称统计，统计每类商品花费的总金额在5000元以上的商品，并且按照总金额升序排序

 SELECT product,SUM(price) FROM orderitem GROUP BY product HAVING SUM(price)>5000 ORDER BY SUM(price) ASC

5.按商品名称统计，统计每类商品购买数量大于1的商品，并且按照购买数量降序排序,只展示商品和购买数量

SELECT product,COUNT(*) FROM orderitem GROUP BY product HAVING COUNT(*)>1 ORDER BY COUNT(*) DESC;