# SQL考題

資料表

產品資料表(Products)
| ProductId | ProductName | UnitPrice |
| --------- | ---------------------------- | --------- |
| 1 | Chai | 18.00 |
| 2 | Chang | 19.00 |
| 3 | Aniseed Syrup | 10.00 |
| 4 | Chef Anton's Cajun Seasoning | 22.00 |
| 5 | Chef Anton's Gumbo Mix | 21.35 |

訂單資料表(Orders)
| OrderId | CustomerName | OrderDate |
| ------- | ------------ | ------------------- |
| 10248 | VINET | 2019-07-05 00:00:00 |
| 10249 | TOMSP | 2019-07-07 00:00:00 |
| 10250 | HANAR | 2019-07-08 00:00:00 |
| 10251 | VITCE | 2019-07-10 00:00:00 |

訂單明細資料表(OrderDetails)

| OrderId | ProductId | Quantity |
| ------- | --------- | -------- |
| 10248 | 1 | 5 |
| 10248 | 3 | 8 |
| 10248 | 4 | 15 |
| 10248 | 5 | 24 |
| 10249 | 2 | 7 |
| 10249 | 5 | 2 |
| 10250 | 3 | 45 |
| 10250 | 4 | 6 |
| 10251 | 1 | 9 |
| 10251 | 4 | 1 |
| 10251 | 5 | 3 |

建立Table的SQL Script

CREATE TABLE `Products`
    (`ProductId` int, `ProductName` varchar(29), `UnitPrice` decimal)
;
    
INSERT INTO `Products`
    (`ProductId`, `ProductName`, `UnitPrice`)
VALUES
    (1, 'Chai', 18.00),
    (2, 'Chang', 19.00),
    (3, 'Aniseed Syrup', 10.00),
    (4, 'Chef Anton''s Cajun Seasoning', 25.00),
    (5, 'Chef Anton''s Gumbo Mix', 21.35)
;

CREATE TABLE `Orders`
    (`OrderId` varchar(7), `CustomerName` varchar(12), `OrderDate` date)
;
    
INSERT INTO `Orders`
    (`OrderId`, `CustomerName`, `OrderDate`)
VALUES
    ('10248', 'VINET', '2019-07-05 13:00:00'),
    ('10249', 'TOMSP', '2019-07-07 14:00:00'),
    ('10250', 'HANAR', '2019-07-08 15:00:00'),
    ('10251', 'VITCE', '2019-07-10 16:00:00')
;



CREATE TABLE `OrderDetails`
    (`OrderId` varchar(7), `ProductId` int, `Quantity` int)
;
    
INSERT INTO `OrderDetails`
    (`OrderId`, `ProductId`, `Quantity`)
VALUES
    ('10248', 1, 5),
    ('10248', 3, 8),
    ('10248', 4, 15),
    ('10248', 5, 24),
    ('10249', 2, 7),
    ('10249', 5, 2),
    ('10250', 3, 45),
    ('10250', 4, 6),
    ('10251', 1, 9),
    ('10251', 4, 1),
    ('10251', 5, 3)
;

1. 找出Customer為`VITCE`的客戶 所購買的最高單價的產品名稱

SELECT Products.ProductName, MAX(Products.UnitPrice) as UnitPrice
FROM `Products`
INNER JOIN `OrderDetails` ON OrderDetails.ProductId = Products.ProductId
INNER JOIN `Orders` ON Orders.OrderId = OrderDetails.OrderId
WHERE Orders.CustomerName = 'VITCE'

2. 找出Customer為`VINET`的客戶 沒有購買的產品編號與產品名稱

SELECT Products.ProductId, Products.ProductName FROM `Products`
LEFT JOIN 
(
  SELECT OrderDetails.ProductId,OrderDetails.OrderId FROM `OrderDetails` 
  INNER JOIN `Orders` ON Orders.OrderId = OrderDetails.OrderId
  WHERE Orders.CustomerName = 'VINET'
) AS Table1 
ON Table1.ProductId = Products.ProductId
WHERE Table1.OrderId IS NULL

3. 找出`2019-07-05`到`2019-07-08`期間 每筆訂單數量最多的產品與總價

SELECT Orders.OrderId, Products.ProductName, 
MAX(OrderDetails.Quantity) as Quantity, 
Products.UnitPrice * Quantity as Total
FROM `Orders`
INNER JOIN `OrderDetails`ON Orders.OrderId = OrderDetails.OrderId
INNER JOIN `Products` ON Products.ProductId = OrderDetails.ProductId
WHERE OrderDate  BETWEEN '2019-07-05 00:00:00' AND '2019-07-08:23:59:59'
GROUP BY Orders.OrderId 
ORDER BY Quantity DESC

### MongoDB

gamelist Collection中的資料如下

{
_id: ObjectId(7df78ad8902c)
title: 'Two-Eight Bar',
minplayer: 3,
maxplayer: 5,
allow: 100,
type: 'table'
},
{
_id: ObjectId(7df78ad8902d)
title: 'Golden Flower',
minplayer: 4,
maxplayer: 6,
allow: 200,
type: 'table'
},
{
_id: ObjectId(7df78ad8902d)
title: 'Holdem Poker',
minplayer: 4,
maxplayer: 6,
allow: 300,
type: 'table'
},
{
_id: ObjectId(7df78ad8902e)
title: 'Fish Joy',
minplayer: 4,
maxplayer: 4,
allow: 10,
type: 'fish'
},
{
_id: ObjectId(7df78ad8902e)
title: 'Yellow Man',
minplayer: 1,
maxplayer: 1,
allow: 10,
type: 'slot'
},

1. 找出gamelist maxplayer > 5 的項目

db.gamelist.find({maxplayer : {$gt : 5}})

2. 透過aggreate()計算不同type各有多少款遊戲

db.gamelist.aggregate([{$group : {_id : "$type", num: {$sum : 1}}}])