學習內容

學習內容

練習題

練習題:
分別使用 UNION 或者 OR 謂詞,找出毛利率不足 30%或毛利率未知的商品.

or：

select product_name from product
where sale_price / purchase_price is null
or sale_price < 1.3 * purchase_price;

union：

select product_name from product
where sale_price / purchase_price is null
union
select product_name from product
where sale_price < 1.3 * purchase_price;

練習題:
找出 Product 和 Product2 中售價高於 500 的商品的基本資訊.

select * from product
where sale_price > 500
union
select * from product2
where sale_price > 500;

練習題:

商店決定對product表中利潤低於50%和售價低於1000的商品提價, 請使用UNION ALL 語句將分別滿足上述兩個條件的結果取並集. 查詢結果類似下表:

SELECT * 
  FROM Product 
 WHERE sale_price < 1000
 UNION ALL
SELECT * 
  FROM Product 
 WHERE sale_price > 1.5 * purchase_price;

練習題:

使用 SYSDATE()函式可以返回當前日期時間, 是一個日期時間型別的資料, 試測試該資料型別和數值,字串等型別的相容性.

例如, 以下程式碼可以正確執行, 說明時間日期型別和字串,數值以及缺失值均能相容.

練習題**?*

找出只存在於Product表但不存在於Product2表的商品.

select * from product
where product_id not in (select product_id from product2);

練習題:

使用NOT謂詞進行集合的減法運算, 求出Product表中, 售價高於2000,但利潤低於30%的商品, 結果應該如下表所示.

SELECT * 
  FROM Product
 WHERE sale_price > 2000 
   AND product_id NOT IN (SELECT product_id 
                            FROM Product 
                           WHERE sale_price<1.3*purchase_price)

練習題:

****使用AND謂詞查詢product表中利潤率高於50%,並且售價低於1500的商品,查詢結果如下所示.

SELECT * 
  FROM Product
 WHERE sale_price > 1.5 * purchase_price 
   AND sale_price < 1500

練習題:

使用Product表和Product2表的對稱差來查詢哪些商品只在其中一張表, 結果類似於:

SELECT * 
  FROM Product
 WHERE product_id NOT IN (SELECT product_id FROM Product2)
UNION
SELECT * 
  FROM Product2
 WHERE product_id NOT IN (SELECT product_id FROM Product)

練習題:

每個商店中, 售價最高的商品的售價分別是多少?

SELECT SP.shop_id
      ,SP.shop_name
      ,MAX(P.sale_price) AS max_price
  FROMshopproduct AS SP
 INNER JOINproduct AS P
    ON SP.product_id = P.product_id
 GROUP BY SP.shop_id,SP.shop_name

練習題:
每類商品中售價最高的商品都在哪些商店有售?

select sp.shop_name, p.product_type, p.sale_price
from shopproduct as sp
join (select * from product as p2
	 where sale_price = (select max(sale_price) as max_price 
							from product as p1
                            where p1.product_type = p2.product_type
                            group by p1.product_type)) as p
on sp.product_id = p.product_id;

練習題:

試寫出與上述自然連結等價的內連結.

SELECT  SP.product_id,SP.shop_id,SP.shop_name,SP.quantity
       ,P.product_name,P.product_type,P.sale_price
       ,P.purchase_price,P.regist_date  
  FROM shopproduct AS SP 
 INNER JOIN Product AS P 
    ON SP.product_id = P.product_id

練習題:
使用內連結求 Product 表和 Product2 表的交集.

SELECT P1.*
  FROM Product AS P1
 INNER JOIN Product2 AS P2
    ON P1.product_id = P2.product_id

練習題:

使用外連結從ShopProduct表和Product表中找出那些在某個商店庫存少於50的商品及對應的商店.希望得到如下結果.

SELECT P.product_id
      ,P.product_name
      ,P.sale_price
       ,SP.shop_id
      ,SP.shop_name
      ,SP.quantity 
  FROM Product AS P
  LEFT OUTER JOIN-- 先篩選quantity<50的商品
   (SELECT *
      FROM ShopProduct
     WHERE quantity < 50 ) AS SP
    ON SP.product_id = P.product_id

練習題:

希望對 Product 表中的商品按照售價賦予排名. 一個從集合論出發,使用自左連結的思路是, 對每一種商品,找出售價不低於它的所有商品, 然後對售價不低於它的商品使用 COUNT 函式計數. 例如, 對於價格最高的商品,

SELECT  product_id
       ,product_name
       ,sale_price
       ,COUNT(p2_id) AS rank
  FROM (--使用自左連結對每種商品找出價格不低於它的商品
        SELECT P1.product_id
               ,P1.product_name
               ,P1.sale_price
               ,P2.product_id AS P2_id
               ,P2.product_name AS P2_name
               ,P2.sale_price AS P2_price 
          FROM Product AS P1 
          LEFT OUTER JOIN Product AS P2 
            ON P1.sale_price <= P2.sale_price 
        ) AS X
 GROUP BY product_id, product_name, sale_price
 ORDER BY rank; 

注 1: COUNT 函式的引數是列名時, 會忽略該列中的缺失值, 引數為 * 時則不忽略缺失值. 注 2: 上述排名方案存在一些問題–如果兩個商品的價格相等, 則會導致兩個商品的排名錯誤, 例如, 叉子和打孔器的排名應該都是第六, 但上述查詢導致二者排名都是第七. 試修改上述查詢使得二者的排名均為第六.

select product_id, product_name, sale_price, count(distinct price) as rank_r
from (select p1.product_id, p1.product_name, p1.sale_price, p2.product_id as p2_id, p2.sale_price as price
	 from product as p1
     left join product as p2
     on p1.sale_price <= p2.sale_price) as p
group by product_id
order by rank_r;

或者

select p1.product_id,
	   p1.product_name,
       p1.sale_price,
       count(distinct p2.sale_price) as rank_r
from product as p1
left join product as p2
on p1.sale_price <= p2.sale_price
group by p1.product_id
order by rank_r;

或者

select p1.product_id, p1.product_name, p1.sale_price, 
(select count(distinct p2.sale_price) from product as p2 where p1.sale_price <= p2.sale_price) as rank_r
from product as p1
order by rank_r;

或者直接用開窗函式dense_rank()

練習題:

請按照商品的售價從低到高,對售價進行累計求和[注:這個案例缺少實際意義, 並且由於有兩種商品價格相同導致了不必要的複雜度, 但示例資料庫的表結構比較簡單, 暫時未想出有實際意義的例題]

SELECT	product_id, product_name, sale_price
       ,SUM(P2_price) AS cum_price 
  FROM
        (SELECT  P1.product_id, P1.product_name, P1.sale_price
                ,P2.product_id AS P2_id
                ,P2.product_name AS P2_name
                ,P2.sale_price AS P2_price 
           FROM Product AS P1 
           LEFT OUTER JOIN Product AS P2 
             ON ((P1.sale_price > P2.sale_price)
             OR (P1.sale_price = P2.sale_price 
            AND P1.product_name <= p2.product_name))
	      ORDER BY P1.sale_price,P1.product_id) AS X
 GROUP BY product_id, product_name, sale_price
 ORDER BY sale_price,cum_price;

或者直接用開窗函式sum(sale_price) over(…)
練習題:

試將上述查詢改用關聯子查詢實現.

select product_id, product_name, sale_price,
(select sum(sale_price) from product as p2 
	where p1.sale_price > p2.sale_price 
    or ((p1.sale_price = p2.sale_price) and (p1.product_name <= p2.product_name))) as com_sum
from product as p1
order by com_sum;