Table:product
字段1:product_id,4位字符,非空,主键
字段2:product_name,可变字符100位,非空
字段3:product_type,可变长32位,非空
字段4:sale_price,整型
字段5:purchase_price,整型
字段6:regist_date,日期型
插入数据如下:
‘0001’,’T恤’,’衣服’,1000,500,’2009-09-02’
‘0002’,’打孔器’,’办公用品’,500,320,’2009-09-11’
‘0003’,’运动T恤’,’衣服’,4000,2800,NULL
‘0004’,’菜刀’,’厨房用具’,3000,2800,’2009-09-20’
‘0005’,’高压锅’,’厨房用具’,6800,5000,’2009-01-15’
‘0006’,’叉子’,’厨房用具’,500,NULL,’2009-09-20’
‘0007’,’擦菜板’,’厨房用具’,880,790,’2008-04-28’
‘0008,’圆珠笔’,’办公用品’,100,NULL,’2009-11-11’
要求:
1、编写一条SELECT语句,求出销售单价(sale_price列)总和大于进货单价(purchase_price列)总和1.5倍的商品种类和销售总价和进货总价
2、查找商品表中商品销售价格的平均价、最高价、最低价
3、查找商品表中每种商品的平均售价,并降序显示
4、查找商品表中按类型统计商品数大于2的商品类型及商品数。
代码:
CREATE TABLE Product (
product_id CHAR(4) PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INT,
purchase_price INT,
regist_date DATE
);
INSERT INTO Product values
('0001','T恤','衣服',1000,500,'2009-09-02'),
('0002','打孔器','办公用品',500,320,'2009-09-11'),
('0003','运动T恤','衣服',4000,2800,NULL),
('0004','菜刀','厨房用具',3000,2800,'2009-09-20'),
('0005','高压锅','厨房用具',6800,5000,'2009-01-15'),
('0006','叉子','厨房用具',500,NULL,'2009-09-20'),
('0007','擦菜板','厨房用具',880,790,'2008-04-28'),
('0008','圆珠笔','办公用品',100,NULL,'2009-11-11');
-- 题1:
SELECT product_type,SUM(sale_price),SUM(purchase_price)
FROM Product
GROUP BY product_type
HAVING SUM(sale_price)>1.5*SUM(purchase_price);
-- 题2:
SELECT AVG(sale_price),MAX(sale_price),MIN(sale_price)
FROM Product;
-- 题3:
SELECT AVG(sale_price)
FROM Product
GROUP BY product_type
ORDER BY AVG(sale_price) DESC;
-- 题4:
SELECT product_type,count(*)
FROM Product
GROUP BY product_type
HAVING count(*)>2