Project 2

db.customer.insertMany([
{
_id: 1,
customer_id: 1,
customer_fname:'Raja Babu',
customer_lname:'Das',
customer_dob:2002,
customer_address:'Ganjam',
customer_city:'Bhanjanagar',
is_premium: 1,
customer_contactno:1234657890,
customer_email:'[email protected]'
},
{
_id: 2,
customer_id: 2,
customer_fname:'Liza',
customer_lname:'Dalei',
customer_dob:2000,
customer_address:'Tanarada',
customer_city:'Bhanjanagar',
is_premium: 0,
customer_contactno:2345678901,
customer_email:'[email protected]'
},
{
_id: 3,
customer_id: 3,
customer_fname:'Anchal',
customer_lname:'Dshoza',
customer_dob:2000,
customer_address:'Nagpur',
customer_city:'Maharastra',
is_premium: 0,
customer_contactno:5678901234,
customer_email:'[email protected]'
}
]);

db.cart.insertMany([
{
_id: 1,
cart_item_qty: 2,
product_id: 102,
customer_id: 1
},
{
_id: 2,
cart_item_qty: 1,
product_id: 201,
customer_id: 2
},
{
_id: 3,
cart_item_qty: 1,
product_id: 202,
customer_id: 3
}
,
{
_id: 4,
cart_item_qty: 1,
product_id: 501,
customer_id: 2
}
,
{
_id: 5,
cart_item_qty: 1,
product_id: 401,
customer_id: 3
}
]);

db.categories.insertMany([
{
_id:1,
categroy_name: 'Home Applicances',
},
{
_id:2,
categroy_name: 'Computers, Laptop and Mobiles',
sub_categories: ['Computers', 'Laptops', 'Mobiles']
},
{
_id:3,
categroy_name: 'Books',
sub_categories: ['Fiction', 'Biography', 'Fantacy Fiction','Non Fiction']
},
{
_id:4,
categroy_name: 'Fasion',
sub_categories: ['mens', 'womens']
},
{
_id:5,
categroy_name: 'Games and Consoles',
sub_categories: ['titles', 'console']
}
]);

db.products.insertMany([
{
_id:1,
product_name: 'Laptop',
product_model: 'C201',
product_brand: 'HP',
product_segments: ['Computers', 'Laptops'],
product_cost: 100000,
categroy_name: 'Computers, Laptop and Mobiles'
},
{
_id:2,
product_name: 'Smart Phone',
product_model: 'C202',
product_brand: 'Google',
product_segments: ['Mobile', 'Android'],
product_cost: 60000,
categroy_name: 'Computers, Laptop and Mobiles'
},
{
_id:3,
product_name: 'Lord of the Rings',
product_model: 'C501',
product_segments: ['Book', 'Novels'],
product_cost: 1500,
categroy_name: 'Books'
},
{
_id:4,
product_name: 'Pokemon Scarlet',
product_model: 'C701',
product_segments: ['pokemon', 'gen9'],
product_cost: 4500,
categroy_name: 'Games and Consoles'
},
{
_id:5,
product_name: 'Nintendo Switch',
product_model: 'C702',
product_segments: ['console'],
product_cost: 30000,
categroy_name: 'Games and Consoles'
},
]);

db.orders.insertMany([
{
_id: 1,
product_name: 'Nintendo Switch',
customer_id: 1,
order_qty: 1,
order_status: 'Delivered',
order_date: new Date('2022-11-15')
},
{
_id: 2,
product_name: 'Pokemon Scarlet',
customer_id: 2,
order_qty: 2,
order_status: 'Dispatched',
order_date: new Date('2022-11-27')
},
{
_id: 3,
product_name: 'Lord of the Rings',
customer_id: 3,
order_qty: 1,
order_status: 'Delivered',
order_date: new Date('2022-11-10')
},
{
_id: 4,
product_name: 'Smart Phone',
customer_id: 1,
order_qty: 1,
order_status: 'cancelled',
order_date: new Date('2022-11-18')
},
{
_id: 5,
product_name: 'Laptop',
customer_id: 1,
order_qty: 1,
order_status: 'Dispatched',
order_date: new Date('2022-11-01')
}
]);

db.seller.insertMany([
{
_id: 1,
seller_name: 'Ankit Chaowda',
seller_business: 'Gada Electronics',
seller_mobileno: 4567891230,
seller_address: 'Andheri',
seller_city: 'Mumbai'
},
{
_id: 2,
seller_name: 'Bakely Harvard',
seller_business: 'ABC Sports and Fasion',
seller_mobileno: 7890123456,
seller_address: 'Connaught Place',
seller_city: 'Delhi'
}
]);

db.catlog.insertMany([
{
seller_name: 'Ankit Chaowda',
product_name: 'Smart Phone',
product_qty: 10,
},
{
seller_name: 'Ankit Chaowda',
product_name: 'Laptop',
product_qty: 5,
},
{
seller_name: 'Bakely Harvard',
product_name: 'Pokemon Scarlet',
product_qty: 20,
},
{
seller_name: 'Bakely Harvard',
product_name: 'Nintendo Switch',
product_qty: 15,
}
]);

(1)Solution:-

select * from customers where avg(ord_value):

(2)Solution:-

select customer_id, count(distinct ord_no), max(high_value) from orders where cus_month<2 group by customer_id order by desc limit off 10;

(3)Solution:-

Select month(OrderDate) as month_name sum(OrderTotal) as sum_of_month, avg(sum(OrderTotal)) over () as avg_sum from [db].[dbo].[orders] where YEAR(OrderDate) = YEAR(GetDate());

(4)Solution:-

select count(various_city) as "names of cities" from customers;

(5)Solution:-

select seller.name as "Seller", customer.cust_name , customer.city from seller, customer where seller.city=customer.city group by product_id order by min(city) desc;