Database SQL command exam questions
資料庫SQL 指令考題形式
1. 給資料表與問題寫出SQL 指令
1. 給資料表與問題寫出SQL 指令
範例一、有一 relational database 包含下列兩 relations：
軟體(編號,廠商,安裝日期,類型,價格)
硬體(編號,廠商,購買日期,類別,價格)
請完整寫出 SQL 語法敘述，該敘述能查出廠商為 IT 且價格大於 15000 元的硬體購買日期。（10 分）
請完整寫出 SQL 語法敘述，該敘述能查出類型為 DBMS 的軟體數量。（10 分）
答案
（一）
Select 購買日期
from 硬體
where 廠商='IT' and 價格>15000
（二）
Select count (*) as DBMS軟體數量
from 軟體
where 類型='DBMS'
範例二、某一家公司的關聯式資料庫（Relational Database）包含了員工、專案及分派三個表格（Table），員工表格與專案表格分別記錄員工與專案的基本資料，分派表格則記錄員工被分派做專案的資料。這三個表格的關聯綱要（Relational Schema）如下所示：
員工（員工編號、員工姓名、性別、地址、月薪） 主鍵為員工編號
專案（專案編號、專案名稱、專案類別） 主鍵為專案編號
分派（員工編號、專案編號、每週工作時數） 主鍵為員工編號 與專案編號
有底線的屬性（Attribute）為該表格之主鍵（Primary Key）；例如，分派表格之主鍵為員工編號與專案編號兩個屬性的組合。分派表格有員工編號與專案編號兩個外來鍵（Foreign Key），分別參照員工表格與專案表格的主鍵。分派表格中的每週工作時數屬性記錄該員工每週在該專案的工作時數。
針對下列三個查詢問題，請各寫出一個 SQL 指令來進行查詢。
列出專案類別為「行銷專案」的每一個專案之專案編號與專案名稱。（10 分）
列出每週專案總工作時數大於 40 小時的每一位員工之員工編號及其每週專案總工作時數。（15 分）
列出被分派做所有專案的每一位員工之員工編號與員工姓名。（15 分
答案
from 專案
where 專案類別='行銷專案'
from 員工,分派
（三）列出被分派做所有專案的每一位員工之員工編號與員工姓名。
Select 員工.員工編號,員工.員工姓名
From 員工,分派
Where 員工.員工編號=分派.員工編號
Group by 員工.員工編號
Having (select count (*) from 專案)=(select count(分派.專案編號) from 分派)
Order by 員工.員工編號
範例三、若某公司業務資料庫中包含了客戶、訂單、訂單明細、品項等資料表（Tables）如下：
CUSTOMER(CustNum, Name)
ORDER(OrderNum, CustNum, OrderDate, Amt)
ORDER_ITEM(OrderNum, ItemNum, Qty)
ITEM(ItemNum, Name, UnitPrice)
各資料表中的屬性分別為：
CUSTOMER：CustNum:客戶代碼、Name:客戶名稱，主鍵（Primary Key, PK）:CustNum
ORDER： OrderNum:訂 單 號 碼 、 CustNum:客 戶 代 碼 、 OrderDate:訂 單 日 期 、
Amt:訂單金額，主鍵:OrderNum
ORDER_ITEM： OrderNum： 訂 單 號 碼 、 ItemNum:品 項 代 碼 、 Qty:數 量 ，
主鍵:(OrderNum, ItemNum)
ITEM：ItemNum:品項代碼、Name:品名、UnitPrice:單價，主鍵:ItemNum
且有以下外來鍵（Foreign Key, FK）Æ主鍵（PK）的關係：
ORDER.CustNum-> CUSTOMER.CustNum
ORDER_ITEM.OrderNum-> ORDER.OrderNum
ORDER_ITEM. ItemNum -> ITEM. ItemNum
假設各屬性（attribute）之資料型態都已有適當定義。請寫出 SQL-99（或 Qracle、SQL-Server、MySQL...，但須註明）命令完成以下查詢：
總購買金額超過 100 萬的客戶之客戶名稱及其總購買金額。（10 分）
2016 年 9 月份每種品項的總銷售量，列出各品項的品名及銷售數量（Qty）。（10
答案
（一）總購買金額超過 100 萬的客戶之客戶名稱及其總購買金額。（10 分）
Select c.Name, sum(o.Amt) as 總購買金額
From Customer as c, Order as o
Where c.CustNum = o.CustNum
Group by c.CustNum
Having sum(o.Amt)>1000000
（二）2016 年 9 月份每種品項的總銷售量，列出各品項的品名及銷售數量（Qty）。
Select i.Name as 品名,sum(o.Qty) as 總銷量
From order as o,order_item as oi, Item as i
Where o.OrderNum=oi.OrderNum and oi.ItemNum=i.ItemNum and TO_CHAR(o.OrderDate,'YYYYMM')='201609'
Group by i.ItemNum
範例四、一個跨國量販公司資料庫系統的部分相關關聯式表格名稱、欄位屬性名稱和資料如下：
（一）請指出每一個表格最合理的主鍵（primary key）欄位集合與外鍵（foreign key）。
（二）YU01 分公司在今天售出產品編號 A13011 的 4 件貨品，請以 SQL 語法寫出新增此筆售貨紀錄到 SALE 表格中的功能。
（三）以 SQL 語法寫出查詢（query）：亞洲'Asia'商店所賣出產品名稱為'k-phoneS'的總數量。
答案
（一）請指出每一個表格最合理的主鍵（primary key）欄位集合與外鍵（foreign key）。
（二）YU01 分公司在今天售出產品編號 A13011 的 4 件貨品，請以 SQL 語法寫出新增此筆售貨紀錄到 SALE 表格中的功能。
INSERT INTO SALE (P_ID, S_DATE, NUM, C_ID) VALUES ('A13011', '09/02/2016', 4, 'YU01');
（三）以 SQL 語法寫出查詢（query）：亞洲'Asia'商店所賣出產品名稱為'k-phoneS'的總數量。
SELECT SUM(NUM) AS TotalQuantity
FROM SALE s,PRODUCT p,STATE st
WHERE s.P_ID = p.P_ID and
s.C_ID = c.C_ID and
c.CITY = st.CITY and
p.P_NAME = 'k-phoneS' AND st.AREA = 'Asia';
Comments
Post a Comment