SQL學習(三） 複雜查詢

我們本節考察的資料庫如下所示：

 

 

3.1

建立出滿足下述三個條件的檢視（檢視名稱為 ViewPractice5_1）。使用 product（商品）表作為參照表，假設表中包含初始狀態的 8 行資料。

• 條件 1：銷售單價大於等於 1000 日元。
• 條件 2：登記日期是 2009 年 9 月 20 日。
• 條件 3：包含商品名稱、銷售單價和登記日期三列。

對該檢視執行 SELECT 語句的結果如下所示。

SELECT * FROM ViewPractice5_1;

執行結果

product_name | sale_price | regist_date
--------------+------------+------------
T恤衫         |   1000    | 2009-09-20
菜刀          |    3000    | 2009-09-20

我的程式碼

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"

 

3.2

向習題一中建立的檢視 ViewPractice5_1 中插入如下資料，會得到什麼樣的結果呢？

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

//顯示插入失敗

 

3.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

SELECT product_id,product_name,product_type,sale_price,
       (SELECT AVG(sale_price)
          FROM product) AS sale_price_all
  FROM product;

3.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

提示：其中的關鍵是 avg_sale_price 列。與習題三不同，這裡需要計算出的 是各商品種類的平均銷售單價。這與使用關聯子查詢所得到的結果相同。 也就是說，該列可以使用關聯子查詢進行建立。問題就是應該在什麼地方使用這個關聯子查詢。

SELECT product_id,product_name,product_type,sale_price,
       (SELECT AVG(sale_price)
                       FROM product AS p2
                      WHERE p1.product_type = p2.product_type
                      GROUP BY product_type) AS sale_price_all
  FROM product as p1;

 

3.5

運算或者函式中含有 NULL 時，結果全都會變為NULL ？（判斷題）

--不對，比如IS NULL這種函式

 

3.6

對本章中使用的 product（商品）表執行如下 2 條 SELECT 語句，能夠得到什麼樣的結果呢？

①

SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000);

②

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

--1選出了purchase_price不為500,2800,5000的產品名稱和purchase_price

--結果為空

產生上述結果的原因是null不能參與比較運算子，即與任何資料比較結果都為null，第二部分where程式碼實際上等同於下面的程式碼

 WHERE purchase_price <>500 and purchase_price <> 2800 and purchase_price <> 5000 and purchase_price <>NULL;

3.7

按照銷售單價（ sale_price）對練習 3.6 中的 product（商品）表中的商品進行如下分類。

• 低檔商品：銷售單價在1000日元以下（T恤衫、辦公用品、叉子、擦菜板、 圓珠筆）
• 中檔商品：銷售單價在1001日元以上3000日元以下（菜刀）
• 高檔商品：銷售單價在3001日元以上（運動T恤、高壓鍋）

請編寫出統計上述商品種類中所包含的商品數量的 SELECT 語句，結果如下所示。

執行結果

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

 select (select count(*) from product where sale_price<=1000) as low_price,
 (select count(*) from product where (sale_price>1000 and sale_price<=3000)) as mid_price,
 (select count(*) from product where sale_price>3000) as high_price;