阿里天池龙珠计划SQL训练营课后答案(task02)

CREATE TABLE product(
     product_id CHAR(4) NOT NULL, 
     product_name VARCHAR(100) NOT NULL, 
     product_type VARCHAR(32) NOT NULL, 
     sale_price INTEGER, 
     purchase_price INTEGER, 
     regist_date DATE, 
     PRIMARY KEY(product_id)
 );

-- DML ：插入数据
INSERT INTO product VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO product VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO product VALUES('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO product VALUES('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO product VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO product VALUES('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO product VALUES('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO product VALUES('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');

select * from `product`;

-- 1
select `product_name`, `regist_date`
from `product`
where `regist_date` > '2009-04-28';

-- 2
# 1.从product表中返回购买价格是NULL的数据
# 2.从product表中返回购买价格不是NULL的数据
# 3.从product表中返回购买价格大于NULL的数据

-- 3
select `product_name`, `sale_price`, `purchase_price`
from `product`
where`sale_price` - `purchase_price` >= 500;

select `product_name`, `sale_price`, `purchase_price`
from `product`
where not `sale_price` - `purchase_price` < 500;

-- 4
select `product_name`, `product_type`, `sale_price`*0.9 - `purchase_price` as profit
from `product`
where `sale_price`*0.9 - `purchase_price` > 100;

SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type;

SELECT product_type, COUNT(*)
FROM product;

SELECT purchase_price, COUNT(*)
  FROM product
 GROUP BY purchase_price;
 
 SELECT purchase_price, COUNT(*)
  FROM product
 WHERE product_type = '衣服'
 GROUP BY purchase_price;
 
 SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING COUNT(*) = 2;

-- 5
#书写顺序为 1**.**SELECT → 2. FROM → 3. WHERE → 4. GROUP BY
#执行顺序为 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
SELECT product_type, sum(sale_price)
FROM product 
WHERE regist_date > '2009-09-01'
GROUP BY product_type;

-- 6
select product_type, sum(sale_price) as sum, sum(purchase_price) as sum
from product
group by product_type
having sum(sale_price) > sum(purchase_price)*1.5; -- where只能指定记录行 ，不能指定组的条件 

-- 7
select *
from product
order by regist_date is null desc, regist_date desc, sale_price;