多表查询练习answer.txt

来自「orale培训教材包括了所有的sql说明和实例」· 文本 代码 · 共 40 行

--查询采购信息和对应经手人的各人信息：图形分析：补充3_product.ppt
--不仅要知道采购信息，还要知道相应采购人的信息。

SELECT plsql101_purchase.product_name,
       plsql101_person.last_name,
       plsql101_person.first_name,
       plsql101_purchase.quantity
FROM   plsql101_purchase,
       plsql101_person
WHERE  plsql101_person.person_code = plsql101_purchase.salesperson
; 

--采购的商品，此商品的单价，采购总量，对应经手人是谁：
--图形分析：补充3_product.ppt中的三表图

SELECT plsql101_purchase.product_name,
       plsql101_product.product_price,
       plsql101_purchase.quantity,
       plsql101_person.last_name
FROM   plsql101_product,
       plsql101_person,
       plsql101_purchase
WHERE  plsql101_product.product_name = plsql101_purchase.product_name
       and
       plsql101_person.person_code = plsql101_purchase.salesperson
;

-- 采购日期，数量，商品最后（上次）采购日期，对应经手人是谁：
SELECT plsql101_purchase.purchase_date,
       plsql101_purchase.quantity,
       plsql101_product.last_stock_date,
       plsql101_person.last_name
FROM   plsql101_product,
       plsql101_person,
       plsql101_purchase
WHERE  plsql101_product.product_name = plsql101_purchase.product_name
       and
       plsql101_person.person_code = plsql101_purchase.salesperson
;