#1.
Get all collections
show collections
#2. Fetch all categories
db.categories.find()
db.categories.find().pretty()
#3. Get all categories with id = 2001 //SELECT * FROM categories WHERE id = 2001
db.categories.find({_id: 2001})
#4. Get id, category_name //SELECT id, category_name FROM categories;
db.categories.find({},{_id: 1, category_name: 1})
db.categories.find({},{_id: 0, category_name: 1})
db.categories.find({},{_id: 0})
db.categories.find({},{category_name: 0})
#5. Get all products whose category_id = 2001
db.products.find({category_id: 2001})
#6. Get all products whose price is more than 30000 // WHERE price>30000
db.products.find({price:{$gt: 30000}})
#7. Price is greater or equal to 43500
db.products.find({price:{$gte: 43500}})
#8. Price is less than 43500
db.products.find({price:{$lt: 43500}})
#9. Price is less than or equal to 43500
db.products.find({price:{$lte: 43500}})
#10. Get all products whose category id is not equal to 2001 //WHERE NOT
category_id = 2001
db.products.find({category_id: {$ne: 2001}})
#11. Get all products whose category id is 2001 or 2004
// WHERE category_id = 2001 OR cateogry_id = 2004;
// WHERE category_id IN(2001, 2004)
db.products.find({category_id: {$in: [2001, 2004]}})
#12. Get all products whose price is more that 45000 and less than 50000 //WHERE
price>45000 and price<50000
db.products.find({price: {$gt: 45000, $lt: 50000}})
db.products.find(
{
$and: [
{price: {$gt: 45000}},
{price: {$lt: 50000}}
]
})
#13. Get all products whose price is greater than 10000 and category id is 2003
db.products.find(
{
$and: [
{price: {$gt: 10000}},
{category_id: 2003}
]
})
#14. Get all products whose price is less than 10000 or greater than 50000
db.products.find(
{
$or:
[
{price: {$lt: 10000}}, {price: {$gt: 50000}}
]
})
#15. Get all products whose price is less than 10000 or greater than 50000 and
category_id is 2003 or company id is 1001
--1.
db.products.find(
{
$and:[
{$or: [
{price: {$lt: 10000}}, {price: {$gt: 50000}}
] }
}
)
-- 2.
db.products.find(
{$or:[
{category_id: 2001}, {company_id: 1001}
]}
)
db.products.find(
{
$and:[
{$or: [
{price: {$lt: 10000}}, {price: {$gt: 50000}}
] },
{$or:[
{category_id: 2001}, {company_id: 1001}
]}
]})
#16. Get all orders of the year 2013
//SELECT * FROM orders WHERE order_date >= '2013-01-01' AND order_date<='2013-12-
31';
//WHERE order_date BETWEEN '2013-01-01' AND '2013-12-31'
//WHERE YEAR(order_date) = 2013;
db.orders.find(
{
order_date: {'$gte':ISODate("2013-01-01"), '$lte': ISODate("2013-12-31")}
})
db.orders.find(
{
$expr:{
$eq:[{"$year":"$order_date"}, 2013]
}
})
#17. Get all products whose product name has Apple in it
// SELECT * FROM products WHERE product_name LIKE '%Apple%'
db.products.find(
{
product_name: /Apple/
})
#18. Get all customers whose name starts with M/m
db.customers.find({customer_name: /^M/})
db.customers.find({
customer_name: {
$regex: /^m/i
}
})
#19. Get all customers whose name ends with h
//SELECT * FROM customer WHERE name LIKE '%h'
db.customers.find(
{
customer_name: /h$/
}
)
#20. Get all customers whose mobile numbers starts with 9
db.customers.find(
{
mobileno: /^9/
}
)
#21. GEt the customer with mobile number '945936245', '8963521478'
db.customers.find(
{
mobileno: ['945936245', '8963521478']
}
)
db.customers.find(
{
mobileno: {$all: ['8963521478', '945936245']}
}
)
#22. Get all customers whose mobile number is 8963521478
db.customers.find(
{
mobileno: {$eq: '8963521478'}
}
)
#23. Get all customers whose Pri mobile number is 8963521478
db.customers.find(
{
"mobileno.0": {$eq: '8963521478'}
}
)
db.customers.find( { "mobileno.0": '8963521478' })
#24. Get all customer from
streetName1: 'Sultanpet',
city: 'Durg',
state: 'Chattisghad',
isDeleted: false
db.customers.find(
{
address: {
streetName1: 'Sultanpet',
city: 'Durg',
state: 'Chattisghad',
isDeleted: false
}
}
)
#25. Get all customers from Bangalore
db.customers.find(
{
'address.city':'Bangalore'
}
)
#26. Get only address information of customers whose id is 2
db.customers.find(
{_id: 2}, {address:1, _id:0}
)
#27. Get all orders of the year 2013 which is having product_id as 9
db.orders.find(
{
"orderItems.product_id": 9,
"order_date" :{
$gte: ISODate('2013-01-01'),
$lte: ISODate('2013-12-31')
}
}, {"orderItems.$": 1, _id: 0})
