本笔记为阿里云天池龙珠计划SQL训练营的学习内容,链接为:https://tianchi.aliyun.com/specials/promotion/aicampsql;
-- 习题1答案:
SELECT product_name, regist_date
FROM porduct
WHERE regist_date >'2009-4-28';
/* 习题2答案:
返回purchase_price为空的所有列
返回purchase_price不为空的所有列
返回purchase_name不为空的所有列 */
-- 习题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 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
AND (prodcut_type = "办公用品"
OR product_type = "厨房用具");
/* 习题5答案:
where在group by之前;select中出现的列需要在group by中出现,product_name不能sum */
-- 习题6答案:
SELECT product_type, SUM(sale_price),SUM(purchase_price)
FROM product
GROUP BY product_type
HAVING SUM(sale_price)>1.5*SUM(purchase_price);
-- 习题7答案:
SELECT *
FROM product
ORDER BY regist_date DESC, sale_price;