【SQL】Task02:基础查询与排序

练习题3.1作答:
SELECT product name, regist_date
FROM product
WHERE regist_date > ‘2009-4-28’

练习题3.2作答:
Line1:找出product表中purchase_price为NULL的所有数据
Line2:找出product表中purchase_price不等于NULL的所有数据
Line3:找出product表中purchase_price大于NULL的所有数据

练习题3.3作答:
第一种:
SELECT product_name, sale_price, purchase_price
FROM product
WHERE sale_price - purchase_price > 500
ORDER BY sale_price;
第二种:
SELECT product_name, sale_price, purchase_price
FROM product
WHERE NOT sale_price - purchase_price <= 500
ORDER BY sale_price;

练习题3.4作答:
SELECT product_name,
product_type,
sale_price * 0.9 AS profit
FROM product
WHERE product_name = ‘办公用品’
OR product_name = ‘厨房用具’
AND ((sale_price* 0.9 - purchase_price ) > 100);

练习题3.5作答:
1、WHERE语句使用在GROUP BY之前,分号放在GROUP BY结尾
2、SUM函数只适用数值类型的列,product_name不是数值类型
3、SUM使用的括号为中文,改成英文括号

练习题3.6作答:
SELECT product_type,
SUM(sale_price) AS sum,
SUM(purchase_price) AS sum
FROM product
GROUP BY product_type
HAVING product_type = ‘办公用品’
OR product_type = ‘衣服’
AND SUM(sale_price) > SUM(purchase_price * 1.5);

练习题3.7作答:
SELECT *
FROM product
ORDER BY regist_date DESC;