Project 2

Ans 1 :

mongo    
use ecommerce    
db.products.insert({
  sku: "111445GB3",
  title: "Simsong One mobile phone",
  description: "The greatest Onedroid phone on the market .....",

  manufacture_details: {
    model_number: "A123X",
    release_date: new ISODate("2012-05-17T08:14:15.656Z")
  },

  shipping_details: {
    weight: 350,
    width: 10,
    height: 10,
    depth: 1
  },

  quantity: 99,

  pricing: {
    price: 1000
  }
})

1.

db.inventory.updateOne( 
    { _id: ObjectId("600e814359ba901629a14e13") }, 
    {
        $unset: {image: 1, size: 1, qty: 1, price: 1},
        $set : { 
            item: "journal", 
            features: ["Inner pocket", "Durable cover"],
            skus: [ { 
                sku: "154A",
                price: {
                    base: NumberDecimal(9.99),
                    currency: "USD"
                },
                quantity: 20,
                options: {
                    size: { h: 14, l: 21, w: 1 },
                    features: ["72 sheets of premium lined paper"],
                    colors: ["brown", "red"],
                    ruling: "wide",
                    image: "images/journal1.jpg"
                }
            }, {
                sku: "154B",
                price: {
                    base: NumberDecimal(14.99),
                    currency: "USD",
                    discount: NumberDecimal(4.00)
                },
                quantity: 15,
                options: { 
                    size: { h: 18, l: 22, w: 2 },
                    features: ["140 sheets of premium paper"],
                    colors: ["brown"],
                    ruling: "unlined",
                    image: "images/journals.jpg"
                }
            } ]
        }
    }

Simple Queries to perform on ecommerce db

Ans:1 

SELECT 
FROM orders a
WHERE purch_amt >
(SELECT AVG(purch_amt) FROM orders b 
WHERE b.customer_id = a.customer_id);

Ans:2

SELECT
c.customer_id,
SUM(CAST(p.amount as float)) AS total_amount,
c.email,
COUNT(p.amount) AS payments_count
FROM
customer c INNER JOIN payment p
ON c.customer_id=p.customer_id
GROUP BY c.customer_id
ORDER BY total_amount desc
LIMIT 10;