当前位置:   article > 正文

天池龙珠SQL训练营日常 task3 打卡_10-1 创建视图,包含拥有属性值个数大于1的商品信息 分数 10 作者 冰冰 单位 广东

10-1 创建视图,包含拥有属性值个数大于1的商品信息 分数 10 作者 冰冰 单位 广东

TASK3

练习题1

创建出满足下述三个条件的视图(视图名称为 ViewPractice5_1)。使用 product(商品)表作为参照表,假设表中包含初始状态的 8 行数据。

条件 1:销售单价大于等于 1000 日元。
条件 2:登记日期是 2009 年 9 月 20 日。
条件 3:包含商品名称、销售单价和登记日期三列。
对该视图执行 SELECT 语句的结果如下所示。
在这里插入图片描述

解答:

create view viewpractice5_1 (product_name, sale_price, regist_date) as 
select product_name, sale_price, regist_date from product
	where sale_price >= 1000 and regist_date = '2009-09-20';
  • 1
  • 2
  • 3

运行结果:
在这里插入图片描述

练习题2

向习题一中创建的视图 ViewPractice5_1 中插入如下数据,会得到什么样的结果呢?

INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');
  • 1

解答:
由于插入的数据不符合视图的筛选条件,预测视图不会改变。
然而实际执行该语句时,会直接报错。

运行结果:
在这里插入图片描述

练习题3

请根据如下结果编写 SELECT 语句,其中 sale_price_all 列为全部商品的平均销售单价。

product_id | product_name | product_type | sale_price | sale_price_all
------------+-------------+--------------+------------+---------------------
0001       | T恤衫         | 衣服         | 1000       | 2097.5000000000000000
0002       | 打孔器        | 办公用品      | 500        | 2097.5000000000000000
0003       | 运动T| 衣服          | 4000      | 2097.5000000000000000
0004       | 菜刀          | 厨房用具      | 3000       | 2097.5000000000000000
0005       | 高压锅        | 厨房用具      | 6800       | 2097.5000000000000000
0006       | 叉子          | 厨房用具      | 500        | 2097.5000000000000000
0007       | 擦菜板        | 厨房用具       | 880       | 2097.5000000000000000
0008       | 圆珠笔        | 办公用品       | 100       | 2097.5000000000000000
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

解答:

select product_id, product_name, product_type, sale_price, 
	(select avg(sale_price) from product) as sale_price_all from product;
  • 1
  • 2

运行结果:
在这里插入图片描述

练习题4

请根据习题一中的条件编写一条 SQL 语句,创建一幅包含如下数据的视图(名称为AvgPriceByType)。

product_id | product_name | product_type | sale_price | avg_sale_price
------------+-------------+--------------+------------+---------------------
0001       | T恤衫         | 衣服         | 1000       |2500.0000000000000000
0002       | 打孔器         | 办公用品     | 500        | 300.0000000000000000
0003       | 运动T| 衣服        | 4000        |2500.0000000000000000
0004       | 菜刀          | 厨房用具      | 3000        |2795.0000000000000000
0005       | 高压锅         | 厨房用具     | 6800        |2795.0000000000000000
0006       | 叉子          | 厨房用具      | 500         |2795.0000000000000000
0007       | 擦菜板         | 厨房用具     | 880         |2795.0000000000000000
0008       | 圆珠笔         | 办公用品     | 100         | 300.0000000000000000
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

解答:
尝试在关联子查询中定义连接条件为 having 而不是 where ,发现结果可行。

create view avgpricebytype (product_id, product_name, product_type, sale_price,
 avg_sale_price) as
	select product_id, product_name, product_type, sale_price,
    (select avg(sale_price) from product as p2
		group by product_type
			having p2.product_type=p1.product_type) 
	as avg_sale_price from product as p1;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

运行结果:
在这里插入图片描述
同时原本用 where 连接的方式也可以:

create view avgpricebytype (product_id, product_name, product_type, sale_price,
 avg_sale_price) as
	select product_id, product_name, product_type, sale_price,
    (select avg(sale_price) from product as p2
		where p2.product_type=p1.product_type
		group by product_type) 
	as avg_sale_price from product as p1;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

练习题5

运算或者函数中含有 NULL 时,结果全都会变为NULL ?(判断题)

解答:
不一定,如 SUM(), COALESCE() 等函数中含有 NULL 时结果不会全变为 NULL。

练习题6

对本章中使用的 product(商品)表执行如下 2 条 SELECT 语句,能够得到什么样的结果呢?

SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000);
  • 1
  • 2
  • 3

SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
  • 1
  • 2
  • 3

解答:
①:可以得到 product 表中购入价不是500、2800、5000的这几行数据对应的产品名称和购入价。

运行结果:
在这里插入图片描述
②:返回了 0 条记录,查阅资料得知,当 not in 参数中包含 null 时,查询结果会为空。

运行结果:
在这里插入图片描述

思考:

当查询语句为 purchase_price IN (500, 2800, 5000, NULL) 时,相当于
purchase_price = 500 or
purchase_price = 2800 or
purchase_price = 5000 or
purchase_price = NULL

其中 purchase_price 为 null 的值和 null 比较结果始终为 null,即 (null = null) = null,返回结果不为 TRUE 或 FALSE 因此不会返回 purchase_price 为 null 的行。

练习题7

按照销售单价( sale_price)对练习 6.1 中的 product(商品)表中的商品进行如下分类。

低档商品:销售单价在1000日元以下(T恤衫、办公用品、叉子、擦菜板、 圆珠笔)
中档商品:销售单价在1001日元以上3000日元以下(菜刀)
高档商品:销售单价在3001日元以上(运动T恤、高压锅)
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句,结果如下所示。

执行结果

low_price | mid_price | high_price
----------+-----------+------------
        5 |         1 |         2
  • 1
  • 2
  • 3

解答:

select 
	count(case when sale_price <= 1000 then sale_price end) as low_price,
    count(case when sale_price between 1001 and 3000 then sale_price end) as mid_price,
    count(case when sale_price >= 3001 then sale_price end) as high_price
    from product;
  • 1
  • 2
  • 3
  • 4
  • 5

运行结果:
在这里插入图片描述

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/繁依Fanyi0/article/detail/612351
推荐阅读
相关标签
  

闽ICP备14008679号