【SQL】Task03:复杂一些的数据库

练习题3.1作答:
CREATE TABLE product_1
(product_name VARCHAR(100) NOT NULL,
sale_price INTEGER NOT NULL,
regist_date VARCHAR(100) NOT NULL
);

INSERT INTO product_1 (product_name, sale_price, regist_date) VALUES (‘T恤衫’, 1500, ‘2009-09-20’);
INSERT INTO product_1 (product_name, sale_price, regist_date) VALUES (‘菜刀’, 3000, ‘2009-09-20’);
INSERT INTO product_1 (product_name, sale_price, regist_date) VALUES (‘老人与海’, 150, ‘2008-09-20’);
INSERT INTO product_1 (product_name, sale_price, regist_date) VALUES (‘风扇’, 100, ‘2007-09-20’);
INSERT INTO product (product_name, sale_price, regist_date) VALUES (‘卫生纸’, 30, ‘2006-09-20’);
INSERT INTO product (product_name, sale_price, regist_date) VALUES (‘笔记本’, 40, ‘2005-09-20’);
INSERT INTO product (product_name, sale_price, regist_date) VALUES (‘电饭煲’, 500, ‘2004-09-20’);
INSERT INTO product (product_name, sale_price, regist_date) VALUES (‘行李箱’, 600, ‘2003-09-20’);

CREATE VIEW ViewPractice5_1
AS
SELECT product_name,
sale_price,
regist_date
FROM product
WHERE sale_price > 1000
AND regist_date = ‘2009-09-20’;

练习题3.2作答:
INSERT INTO ViewPractice5_1 VALUES (’ 刀子 ', 300, ‘2009-11-02’);
的结果是:product中添加数据成功,ViewPractice5_1视图中没有数据添加,因为不满足条件

练习题3.3作答:
CREATE TABLE product_3
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER NOT NULL
);

INSERT INTO product_3 (product_id, product_name, product_type, sale_price) VALUES (‘0001’, ‘T恤衫’, ‘衣服’, 1000);
INSERT INTO product_3 (product_id, product_name, product_type, sale_price) VALUES (‘0002’, ‘打孔器’, ‘办公用品’, 500);
INSERT INTO product_3 (product_id, product_name, product_type, sale_price) VALUES (‘0003’,‘运行T恤’, ‘衣服’, 4000);
INSERT INTO product_3 (product_id, product_name, product_type, sale_price) VALUES (‘0004’,‘菜刀’, ‘厨房用具’, 3000);
INSERT INTO product_3 (product_id, product_name, product_type, sale_price) VALUES (‘0005’,‘高压锅’, ‘厨房用具’, 6800);
INSERT INTO product_3 (product_id, product_name, product_type, sale_price) VALUES (‘0006’,‘叉子’, ‘厨房用具’, 500);
INSERT INTO product_3 (product_id, product_name, product_type, sale_price) VALUES (‘0007’,‘擦菜板’, ‘厨房用具’, 880);
INSERT INTO product_3 (product_id, product_name, product_type, sale_price) VALUES (‘0008’,‘圆珠笔’, ‘办公用品’, 100);

SELECT *,
(SELECT AVG(sale_price) FROM ViewPractice5_3) AS sale_price_all
FROM ViewPractice5_3;

练习题3.4作答:
– 创建视图的语句
CREATE VIEW AvgPriceByType
AS
SELECT product_id,
product_name,
product_type,
sale_price,
(SELECT AVG(sale_price)
FROM product p2
WHERE p1.product_type = p2.product_type
GROUP BY p1.product_type) AS avg_sale_price
FROM product p1;

练习题3.5作答:
正确

练习题3.6作答:
1:结果为取出的purchase_price不是500,2800,5000数据,谓词与null无法比较,不包含purchase_price为null的值
2:NOT IN 中不能包含null,通常返回空;

练习题3.7作答:
SELECT SUM(CASE WHEN sale_price <= 1000 THEN 1 ELSE 0 END) AS low_price,
SUM(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN 1 ELSE 0 END) AS mid_price,
SUM(CASE WHEN sale_price >= 3001 THEN 1 ELSE 0 END) AS high_price
FROM product;