112年高考二級高等資料庫設計
|
三、考慮以下的商品資料庫綱目 (Database Schema)： User(uId, name, date) // 使用者編號，姓名，加入日期 Product(pId, seller, name, category, star, sale) // 商品編號，賣家名稱，商品名稱，類別，星等，售出數量 Opinion(uId, pId, seller, time, comment, star) // 使用者編號，商品編號，賣家名稱，評論日期，意見，星等 各資料表的主鍵 (Primary key) 以底線表示。此外，並有以下的外鍵 (Foreign key)： Opinion(uId) 參考到 User 的主鍵 Opinion(pId, seller) 參考到 Product 的主鍵 請寫出下列問題的結構化查詢語言 (SQL)： (一)列出2023年加入的使用者編號和姓名（5分） (二)列出商品名稱為「掃地機器人」在2023年的總評論數（8分） (三)找出在2023年對於「掃地機器人」做出最多評論的使用者之編號和姓 名。（10分） |
答：
User資料表：
|
uId |
name |
date |
|
U001 |
王一 |
2022-01-05 |
|
U002 |
張二 |
2022-01-22 |
|
U003 |
陳三 |
2023-03-01 |
|
U004 |
吳四 |
2023-05-05 |
Product資料表：
|
pId |
seller |
name |
category |
star |
sale |
|
P001 |
賣家一 |
掃地機器人 |
類別一 |
2 |
100 |
|
P001 |
賣家二 |
掃地機器人 |
類別一 |
2 |
300 |
|
P002 |
賣家一 |
商品二 |
類別二 |
3 |
200 |
|
P002 |
賣家二 |
商品二 |
類別二 |
4 |
400 |
|
P003 |
賣家二 |
商品三 |
類別二 |
5 |
600 |
|
P004 |
賣家三 |
商品四 |
類別一 |
2 |
100 |
Opinion資料表：
|
uId |
pId |
seller |
time |
comment |
star |
|
U001 |
P001 |
賣家一 |
2022-10-01 12:00:00.000 |
評論一 |
2 |
|
U001 |
P001 |
賣家二 |
2022-10-03 00:00:00.000 |
評論二 |
3 |
|
U002 |
P001 |
賣家一 |
2023-10-05 00:00:00.000 |
評論三 |
2 |
|
U002 |
P001 |
賣家二 |
2023-10-07 16:20:00.000 |
評論四 |
3 |
|
U003 |
P001 |
賣家二 |
2023-03-22 00:00:00.000 |
評論五 |
3 |
|
U003 |
P001 |
賣家二 |
2023-11-01 00:00:00.000 |
評論六 |
2 |
|
U004 |
P003 |
賣家二 |
2024-02-01 20:00:00.000 |
評論七 |
3 |
(一)列出2023年加入的使用者編號和姓名
Select uId As 使用者編號, name As 姓名
From [User]
Where Year(date) = '2023'
執行結果：
|
使用者編號 |
姓名 |
|
U003 |
陳三 |
|
U004 |
吳四 |
(二)列出商品名稱為「掃地機器人」在2023年的總評論數
Select Count(*) As 總評論數
From Product As P, Opinion As O
Where P.pid = O.pid And P.seller = O.seller And Year(O.time) = '2023' And
P.name = '掃地機器人'
Group By P.pid
執行結果：
|
總評論數 |
|
4 |
(三)找出在2023年對於「掃地機器人」做出最多評論的使用者之編號和姓名
Select U.uid As 使用者編號, U.name As 姓名
From [User] As U, Product As P, Opinion As O
Where U.uid = O.uid And P.pId = O.pId And P.seller = O.seller And
P.name = '掃地機器人' And Year(O.time) = '2023'
Group By U.uid, U.name
Having Count(*) = (Select Max(ReviewCount)
From (Select Count(*) As ReviewCount
From Product As P, Opinion As O
Where P.pId = O.pId And P.seller = O.seller And
P.name = '掃地機器人' And Year(O.time) = '2023'
Group By O.uid) As Tb)
執行結果：
|
使用者編號 |
姓名 |
|
U002 |
張二 |
|
U003 |
陳三 |
留言列表
國考資訊處理解答 (7)
