Project 2
1)Assuming that you are building a catalog for an ecommerce website, the following is a sample schema for the same:
Customers: - _id - name - email - password - shipping_address - billing_address
Products: - _id - name - price - description - image - category - brand - segment - tags - in_stock
Orders: - _id - customer_id - product_id - quantity - shipping_details - billing_details - order_status
Admins: - _id - name - email - password
Sellers: - _id - name - email - password
2.There are a few different collections that could be created for an ecommerce database in MongoDB. Some potential collections could include:
-Products
-Customers
-Orders
-Shopping Cart
Assuming we are building a basic ecommerce database, a few sample documents for each collection could look like the following:
Products:
{
"_id": 1,
"name": "shoes",
"price": 999,
"image": "shoes.jpg",
"description": "A small rubber object that is used to perform a task."
}
Customers:
{
"_id": 1,
"name": "John Doe",
"email": "[email protected]",
"address": "123 Main St."
}
Orders:
{
"_id": 1,
"customer_id": 1,
"items": [
{
"product_id": 1,
"quantity": 2
},
],
"total": 999,
"status": "pending"
}
Shopping Cart:
{
"_id": 1,
"customer_id": 1,
"items": [
{
"product_id": 1,
"quantity": 1
},
{
"total": 999
}
1)SELECT AVG(OrderValue)
FROM Customer
INNER JOIN [Order]
ON Customer.CustomerID = [Order].CustomerID
2)SELECT top 10 *
FROM customers
WHERE order_value > (
SELECT AVG(order_value)
FROM customers
)
AND date_joined < DATEADD(month, -2, GETDATE())
3)SELECT SUM(quantity*cost) AS "Total"
FROM orders
JOIN products ON products.id = orders.product_id
WHERE cost BETWEEN 100 AND 300
AND orders.created_at >= '2020-07-01 00:00:00'
AND orders.created_at < '2020-08-01 00:00:00'
4)SELECT COUNT(*) as "Number of Customers", City FROM Customers GROUP BY City
5)SELECT seller.name, count(o.seller_id) AS products_sold
FROM seller
JOIN orders ON seller.id=orders.seller_id
JOIN customer ON customer.id=orders.customer_id
WHERE customer.city=seller.city
GROUP BY seller.name
ORDER BY products_sold DESC