2-3章习题

SQL课后习题 第2、3章习题

2.1

SELECT product_name, regist_date
 FROM Product
 WHERE regist_date >’ 2009-04-28’;

2.2

(1)(2)(3)输出均为0行记录，不能对NULL使用比较运算符。希望选取是或不是 NULL 的记录时，需要使用 IS NULL 或者IS NOT NULL 运算符。

2.3

题目原解：

SELECT product_name, sale_price, purchase_price
 FROM Product
 WHERE sale_price - purchase_price >= 500;

解(1)

SELECT product_name, sale_price, purchase_price
FROM Product
WHERE NOT sale_price - purchase_price < 500;

解(2)

SELECT product_name, sale_price, purchase_price
 FROM Product
 WHERE sale_price>4000
OR product_type =’衣服’;

2.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 (product_type='办公用品' 
OR product_type='厨房用具');

3.1

①GROUP BY应该在WHERE后面;
②使用GROUP BY子句时，SELECT子句中不能出现聚合键之外的列名。

3.2

SELECT product_type,sum(sale_price) AS "售价总和",sum(purchase_price)AS "进价总和"
FROM Product
WHERE (product_type='办公用品' OR product_type='衣服')
GROUP BY product_type
Having sum(sale_price)>1.5*sum(purchase_price)
ORDER BY sum(sale_price) DESC;

3.3

SELECT *
FROM Product
ORDER BY regist_date DESC,sale_price;