from product
having regist_date > '2009-04-28'
order by regist_date desc
2.2
null表示空值,不确定,所以返回的也是不确定,即空值
null前应该接 is,才能得到空值对应的数据
2.3
SELECT product_name,purchase_price,sale_price
FROM product
WHERE sale_price - 500 >= purchase_price
2.4
select product_name,sale_price,purchase_price,sale_price *0.9 as profit
from product
where sale_price*0.9 - purchase_price >=100
2.5
sum()的()里面的只能是数值,不能是字符串;
SUM/AVG函数只适用于数值类型的列。
where应写在group by 之前 ,from 之后
2.6
select product_type,sum(sale_price),sum(purchase_price)
from product
group by product_type
having sum(sale_price) > sum(purchase_price)*1.5
2.7
不会做啊,看答案输出的结果和题目要求的也不一样。
数据库源码
product表源码
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)
)
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');