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


1.

SELECT product_name, regist_date
FROM product
WHERE regist_date > ‘2009-4-28’;

2.

Line1:找出product表中purchase_price为NULL的所有数据
Line2:找出product表中purchase_price不等于NULL的所有数据
Line3:找出product表中purchase_price大于NULL的所有数据

3.

第一种
SELECT product_name, sale_price, purchase_price
FROM product
WHERE sale_price - purchase_price > 500
OR 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 AS profit
FROM product
WHERE product_type = ‘办公用品’
OR product_type = ‘厨房用具’
AND ((sale_price * 0.9 - purchase_price) > 100);

5.

SUM函数只可以用来计算数值类型的数据,而product_name是字符类型的
SUM函数的括号应该用半角而不是全角
WHERE 应该在 GROUP BY语句之前

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);

7.

SELECT *
FROM product
ORDER BY - regist_date, sale_price;

浙ICP备19012682号