---
# System prepended metadata

title: 資料庫處理面試題目

---

#### 表一：Users

| id  | name     | email                | age | country    | city       | gender | phone_number  | registration_date | last_login_date |
|-----|----------|----------------------|-----|------------|------------|--------|---------------|-------------------|-----------------|
| 1   | Alice    | alice@example.com    | 28  | USA        | New York   | Female | 123-456-7890  | 2023-01-01        | 2024-01-20      |
| 2   | Bob      | bob@example.com      | 35  | Canada     | Toronto    | Male   | 234-567-8901  | 2023-02-15        | 2024-01-18      |
| 3   | Charlie  | charlie@example.com  | 22  | UK         | London     | Male   | 345-678-9012  | 2023-03-20        | 2024-01-19      |
| 4   | David    | david@example.com    | 30  | USA        | Chicago    | Male   | 456-789-0123  | 2023-04-10        | 2024-01-15      |
| 5   | Emma     | emma@example.com     | 26  | Canada     | Vancouver  | Female | 567-890-1234  | 2023-05-18        | 2024-01-12      |
| 6   | Frank    | frank@example.com    | 40  | UK         | Manchester | Male   | 678-901-2345  | 2023-06-22        | 2024-01-10      |
| 7   | Grace    | grace@example.com    | 31  | USA        | Miami      | Female | 789-012-3456  | 2023-07-30        | 2024-01-08      |
| 8   | Henry    | henry@example.com    | 29  | Canada     | Montreal   | Male   | 890-123-4567  | 2023-08-15        | 2024-01-05      |
| 9   | Isla     | isla@example.com     | 25  | UK         | Edinburgh  | Female | 901-234-5678  | 2023-09-10        | 2024-01-03      |
| 10  | Jack     | jack@example.com     | 36  | USA        | San Francisco | Male | 012-345-6789  | 2023-10-05        | 2024-01-01      |
| 11  | Kiera    | kiera@example.com    | 32  | Canada     | Ottawa     | Female | 123-456-7899  | 2023-11-20        | 2023-12-30      |
| 12  | Liam     | liam@example.com     | 38  | UK         | Liverpool  | Male   | 234-567-8910  | 2023-12-15        | 2023-12-28      |
| 13  | Mia      | mia@example.com      | 27  | USA        | Boston     | Female | 345-678-9011  | 2023-12-01        | 2023-12-25      |
| 14  | Noah     | noah@example.com     | 33  | Canada     | Calgary    | Male   | 456-789-0122  | 2023-11-05        | 2023-12-20      |
| 15  | Olivia   | olivia@example.com   | 24  | UK         | Bristol    | Female | 567-890-1233  | 2023-10-25        | 2023-12-15      |

#### 表二：Products

| product_id | name      | category    | price | stock | supplier  | weight | color | description       | launch_date | discontinued |
|------------|-----------|-------------|-------|-------|-----------|--------|-------|-------------------|-------------|--------------|
| 1001       | Product A | Electronics | 50    | 100   | Supplier1 | 1.5kg  | Black | High-quality...   | 2023-02-01  | No           |
| 1002       | Product B | Home Goods  | 75    | 50    | Supplier2 | 2kg    | White | Eco-friendly...   | 2023-04-15  | No           |
| 1003       | Product C | Books       | 20    | 200   | Supplier2 | 0.5kg  | Blue  | Best-selling...   | 2023-06-20  | Yes          |
| 1004       | Product D | Fashion     | 100   | 30    | Supplier2 | 1kg    | Red   | Stylish...        | 2023-05-10  | No           |
| 1005       | Product E | Toys        | 25    | 150   | Supplier3 | 0.3kg  | Green | Fun for all ages..| 2023-03-12  | No           |


#### 表三：Orders

| order_id | user_id | order_date | amount | product_id | quantity | shipping_address      | order_status | payment_method | delivery_date |
|----------|---------|------------|--------|------------|----------|-----------------------|--------------|----------------|---------------|
| 101      | 1       | 2024-01-01 | 150    | 1001       | 2        | 123 New St, NY        | Processed    | Credit Card    | 2024-01-05    |
| 101      | 1       | 2024-01-01 | 100    | 1002       | 1        | 123 New St, NY        | Processed    | Credit Card    | 2024-01-05    |
| 102      | 2       | 2024-01-03 | 200    | 1003       | 1        | 456 Old St, TO        | Shipped      | PayPal         | 2024-01-08    |
| 103      | 1       | 2024-01-05 | 100    | 1004       | 3        | 123 New St, NY        | Delivered    | Debit Card     | 2024-01-07    |
| 104      | 3       | 2024-01-07 | 250    | 1005       | 5        | 789 Park Ave, LD      | Processing   | Credit Card    | 2024-01-12    |
| 105      | 4       | 2024-01-10 | 300    | 1001       | 2        | 321 West St, CH       | Shipped      | Debit Card     | 2024-01-15    |
| 105      | 4       | 2024-01-10 | 150    | 1002       | 1        | 321 West St, CH       | Shipped      | Debit Card     | 2024-01-15    |
| 106      | 5       | 2024-01-12 | 75     | 1003       | 1        | 654 East St, VC       | Delivered    | PayPal         | 2024-01-17    |
| 107      | 1       | 2024-01-14 | 150    | 1004       | 2        | 123 New St, NY        | Cancelled    | Credit Card    | -             |
| 108      | 2       | 2024-01-16 | 100    | 1005       | 2        | 456 Old St, TO        | Processing   | Debit Card     | 2024-01-20    |
| 109      | 3       | 2024-01-18 | 200    | 1001       | 4        | 789 Park Ave, LD      | Shipped      | Credit Card    | 2024-01-23    |
| 110      | 6       | 2024-01-20 | 300    | 1002       | 2        | 963 Hill Rd, MN       | Delivered    | PayPal         | 2024-01-25    |
| 111      | 7       | 2024-01-22 | 50     | 1003       | 1        | 147 River St, MI      | Processing   | Debit Card     | 2024-01-27    |
| 112      | 8       | 2024-01-24 | 125    | 1004       | 1        | 258 Lake Rd, MTL      | Shipped      | Credit Card    | 2024-01-29    |
| 113      | 9       | 2024-01-26 | 175    | 1005       | 3        | 369 Ocean Ave, ED     | Delivered    | PayPal         | 2024-01-31    |
| 114      | 10      | 2024-01-28 | 200    | 1001       | 1        | 741 Mountain St, SF   | Processing   | Debit Card     | 2024-02-02    |
| 115      | 11      | 2024-01-30 | 150    | 1002       | 1        | 852 Valley Rd, OT     | Shipped      | Credit Card    | 2024-02-04    |
| 116      | 12      | 2024-02-01 | 100    | 1003       | 2        | 963 Hill Rd, LV       | Cancelled    | PayPal         | -             |
| 117      | 13      | 2024-02-03 | 250    | 1004       | 2        | 147 River St, BS      | Delivered    | Debit Card     | 2024-02-07    |
| 118      | 14      | 2024-02-05 | 300    | 1005       | 4        | 258 Lake Rd, CG       | Processing   | Credit Card    | 2024-02-10    |
| 119      | 15      | 2024-02-07 | 75     | 1001       | 1        | 369 Ocean Ave, BR     | Shipped      | PayPal         | 2024-02-12    |
| 120      | 16      | 2024-02-09 | 50     | 1002       | 1        | 741 Mountain St, SF   | Delivered    | Debit Card     | 2024-02-14    |
| 121      | 17      | 2024-02-11 | 125    | 1003       | 1        | 852 Valley Rd, OT     | Processing   | Credit Card    | 2024-02-16    |
| 122      | 18      | 2024-02-13 | 175    | 1004       | 3        | 963 Hill Rd, LV       | Shipped      | PayPal         | 2024-02-18    |
| 123      | 19      | 2024-02-15 | 200    | 1005       | 2        | 147 River St, BS      | Cancelled    | Debit Card     | -             |

請回答下面的問題，可以給出SQL，或是一段程式碼來完成問題的需求：

1.  觀察上面三個資料表，請問這三個資料表的資料應該是在什麼介面(前台/後台)中被建立的?如果你覺得有不同的可能性，可以都寫出。

2.  這三個資料表中有存在一個必然的錯誤，請指出，並說明可能導致這個錯誤的原因。
    
3.  透過Orders計算出原本Products中的庫存，沒有delivery_date的訂單為退貨單。
    
4.  查詢最受歡迎的產品
    
5.  查詢2024年一月總共銷售多少金額的Supplier2供應商的產品。
    
6.  列出客戶消費總金額排行名單。
    
7.  查詢2024年一月總共有多少筆訂單，並計算總金額。
    
8.  查詢從來沒有消費過的用戶
    
9.  查詢計算 'Orders' 表中每個用戶的總訂單數量。

10. 針對你回答題2提出的原因，給出修正資料表的方式。