SQL查询

最新推荐文章于 2022-05-31 14:08:09 发布

原创最新推荐文章于 2022-05-31 14:08:09 发布 · 162 阅读

CC 4.0 BY-SA版权

该作业涉及SQL查询，包括从shop数据库的产品表中选取不同条件的数据，如产品名称、注册日期、价格比较、利润计算等。还涵盖了产品类型统计、销售与采购总价对比，以及按日期和价格排序。作业2关注商品销售统计，按产品类型分组并计算销售额，筛选出销售超出采购成本1.5倍的类型。

作业1

USE shop;
SELECT * FROM product;
SELECT product_name, regist_date
	FROM shop.product
    WHERE regist_date > '2009-04-28';
SELECT * 
	FROM product
    WHERE purchase_price is NULL;
SELECT * 
	FROM product
    WHERE purchase_price = NULL;
SELECT * 
	FROM product
    WHERE purchase_price <> NULL;
SELECT * 
	FROM product
    WHERE purchase_price > NULL;
SELECT * 
	FROM product
    WHERE purchase_price is NOT NULL;
SELECT product_name, sale_price, purchase_price
	FROM product
    WHERE sale_price - purchase_price >= 500;
SELECT product_name, sale_price, purchase_price
	FROM product
    WHERE purchase_price - sale_price <= -500;
SELECT product_name, sale_price, purchase_price
	FROM product
    WHERE NOT sale_price - purchase_price < 500;
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 = '厨房用具');

作业2

USE shop;
SELECT product_id, COUNT(product_name)
		FROM product
        WHERE regist_date > '2009-09-01'
        GROUP BY product_type;
# correct answer of 2.6
SELECT  product_type, 
		SUM(sale_price) AS sum,
        SUM(purchase_price) AS sum
		FROM product
        GROUP BY product_type
        HAVING SUM(sale_price) > 1.5 * SUM(purchase_price);
# for understanding only
SELECT  product_type, 
		SUM(sale_price) AS sum,
        SUM(purchase_price) AS sum
		FROM product
        GROUP BY product_type, sale_price, purchase_price;
SELECT  product_type, 
		SUM(sale_price) AS sum,
        SUM(purchase_price) AS sum
		FROM product
        GROUP BY product_type, sale_price, purchase_price
        HAVING SUM(sale_price) > 1.5 * SUM(purchase_price);
# answer for 2.7 (0003的商品因为日期是NULL，随机放在了开头或结尾，书上是开头，此处运行结果是末尾，书的答案也是这么给的)
SELECT *  FROM product
        ORDER BY regist_date DESC, sale_price, product_id;
SELECT *  FROM product
        ORDER BY regist_date DESC, sale_price;