//show all databases
show dbs

//switch to a database
//the database will be created if it's not existing
use it4733

//check current database
db

//create collection - somewhat similar to sql table
//this step is not necessary
db.createCollection("people")

//insert into collection name "people"
//people will be created if it's not existing
db.people.insertOne({"PID":"000101","first_name":"Alice","last_name":"Smith"})
db.people.insertOne({"PID":"000131","first_name":"Bob","last_name":"Jobs"})

db.people.insertMany([
                     {"PID":"000234","first_name":"Carol","last_name":"Brown"},
                     {"PID":"000236","first_name":"Dean","last_name":"Davis"},
                     ])

//show all documents in collection people
db.people.find()

//find with filter
db.people.find({"PID":"000234"})
db.people.find({"first_name":"Alice"})

//documents in the same collections may have different fields
db.people.insertMany([
                     {"PID":"0230234","first_name":"Emma","last_name":"Greene","age":25},
                     {"PID":"0123334","first_name":"Emmet","last_name":"Jones","age":21},
                     {"PID":"0134436","first_name":"George","last_name":"Miller","age":36},
                     {"PID":"0021343","first_name":"Hannah","last_name":"Lawson","age":30},
                     {"PID":"0745323","first_name":"John","last_name":"Wilson","age":36,"email":"john@email.com"},
                     ])

db.people.find()
                     
/////////////
//more find statements
//SELECT * FROM people WHERE age = 36;
db.people.find({"age":36})

//SELECT * FROM people WHERE age > 30;
db.people.find({"age":{$gte:30}}) //other comparison operators: $eq (=), $gte (>=), $lt (<), $lte (<=)

//SELECT * FROM people WHERE first_name LIKE '%Emm%';
db.people.find({"first_name":/Emm/})

//literal strings are case-sensitive
db.people.find({"first_name":/emm/})

//SELECT * FROM people WHERE first_name LIKE '%Emm%' AND age > 23;
db.people.find({"first_name":/Emm/,"age":{$gt:23}})

//SELECT * FROM people WHERE first_name LIKE '%Emm%' AND age < 23;
db.people.find({"first_name":/Emm/,"age":{$lt:23}})

//SELECT first_name, last_name FROM people;
db.people.find({},{"first_name":1,"last_name":1,_id:0})

//SELECT * FROM people ORDER BY last_name;
db.people.find().sort({"last_name":1})

//SELECT first_name, last_name, age FROM people ORDER BY age DESC;
db.people.find({},{"first_name":1,"last_name":1,"age":1,_id:0}).sort({"age":-1})

//SELECT COUNT(*) FROM people;
db.people.count()

//SELECT COUNT(*) FROM people WHERE first_name LIKE '%Emm';
db.people.count({"first_name":/Emm/})

//SELECT first_name, last_name FROM people WHERE age = 36;
db.people.find({"age":36},{"first_name":1,"last_name":1,_id:0})

//note: the quotes "" are not needed in field names, for example
db.people.find({age:36},{first_name:1,last_name:1,_id:0}) //works
//however, you will need it to query an embedded document's field, as shown later

//insert a new document
db.people.insertOne({PID:"1234534",first_name:"Ken",last_name:"Rockwell",age:45,"phone_number":"0123456789"})

//this find all documents that have the field "age". 
//this kind of query is not possible in SQL, since tables have fixed schema, 
//and all rows in the same table have the same attributes
db.people.find({age:{$exists:true}})

//update documents
//UPDATE people SET age = 27 WHERE PID = "0230234";
db.people.updateMany({"PID":"0230234"},{$set:{"age":27}},{multi:true})

//we can add new field when updating
//not possible in SQL without altering the table first
//then all rows will have the new attribute added
db.people.updateMany({"PID":"000101"},{$set:{"age":30}})
db.people.find()

//update multiple fields
db.people.updateMany({"PID":"000101"},{$set:{"last_name":"Jones","age":35,"email":"alice@mail.com"}})
db.people.find({"PID":"000101"})

//delete fields from documents
db.people.updateMany({"PID":"1234534"}, {$unset: {"phone.number":1}} , {multi: true});
db.people.find({"PID":"1234534"})

//delete document
//DELETE FROM people WHERE PID = "1234534";
db.people.deleteOne({"PID":"1234534"})
db.people.find({"PID":"1234534"})

//drop collection people
db.people.drop()

//query with aggregation
db.items.insertMany([
                    {"itemID":"00010123","name":"Pen","category":"office","price":2.00,"quantity":10},
                    {"itemID":"00124325","name":"Pencil","category":"office","price":2.50,"quantity":12},
                    {"itemID":"00076883","name":"Marker","category":"office","price":3.00,"quantity":5},
                    {"itemID":"00045733","name":"Notepad","category":"office","price":1.50,"quantity":13},
                    {"itemID":"00012435","name":"Staple","category":"office","price":5.00,"quantity":8},
                    {"itemID":"01244799","name":"Notebook","category":"office","price":3.50,"quantity":7},
                    {"itemID":"12313423","name":"Computer","category":"electronic","price":999.99,"quantity":20},
                    {"itemID":"12543563","name":"Cell Phone","category":"electronic","price":899.99,"quantity":17},
                    {"itemID":"16452354","name":"Camera","category":"electronic","price":1999.99,"quantity":7}
                    ])

db.items.find()
                    
//SELECT SUM(quantity) FROM items;
db.items.aggregate([
   {$group: { _id: null, "total_quantity": {$sum:"$quantity"}}}
])
   
//SELECT AVERAGE(price) FROM items;
db.items.aggregate([
   {$group: { _id: null, "average_cost": {$avg:"$price"}}}
])
   
//common aggregation operators: $sum, $avg, $min, $max, $first, $last
//SELECT AVERAGE(price) FROM items WHERE quantity >= 10;
db.items.aggregate([
   {$match: {"quantity":{$gte:10}}},
   {$group: { _id: null, "average_cost": {$avg:"$price"}}}
])
   
//SELECT SUM(quantity) FROM items GROUP BY category;
db.items.aggregate([
   {$group: {_id:"$category", "total_quantity": {$sum:"$quantity"}}}
])
   
//SELECT SUM(quantity), AVERAGE(price) FROM items GROUP BY category;
db.items.aggregate([
   {$group: {_id:"$category", "total_quantity": {$sum:"$quantity"}, "average_cost":{$avg:"$price"}}}
])
   
//SELECT SUM(quantity), AVERAGE(price) FROM items WHERE quantity >= 10 GROUP BY category;
db.items.aggregate([
   {$match: {"quantity":{$gte:10}}},
   {$group: {_id:"$category", "total_quantity": {$sum:"$quantity"}, "average_cost":{$avg:"$price"}}}
])
   
//add more categorical fields so we can test more complex aggregations
db.items.update({"price":{$lt:10}},{$set:{"price_cat":"cheap"}},{multi:true})
db.items.update({"price":{$gt:500}},{$set:{"price_cat":"expensive"}},{multi:true})

db.items.insertMany([
                    {"itemID":"45645665","name":"TShirt","category":"clothing","price":30.00,"quantity":15,"price_cat":"medium"},
                    {"itemID":"33524325","name":"Jeans","category":"clothing","price":35.00,"quantity":12,"price_cat":"medium"},
                    {"itemID":"12353583","name":"Jacket","category":"clothing","price":50.00,"quantity":5,"price_cat":"medium"}
                    ])
                    
//SELECT SUM(quantity) FROM items GROUP BY category, price_cat;
db.items.aggregate([
   {$group: {_id:{"category":"$category", "price category":"$price_cat"}, "total_quantity": {$sum:"$quantity"}}}
])
   

//embedding documents
//fields in a document can be an embedded document
db.employees.insertMany([
                      {"EID":"01321",
                       "name":"Alice Smith",
                       "address":{
                           "street_address":"123 Chastain Road",
                           "city":"Kennesaw",
                           "state":"GA",
                           "zip":"30144"}
                       },
                      {"EID":"01326",
                       "name":"Bob Wilsons",
                       "address":{
                           "street_address":"123 Roswell Road",
                           "city":"Marietta",
                           "state":"GA",
                           "zip":"30062"}
                       },
                      ])

db.employees.find()

//accessing fields in embedded documents
//find exact
db.employees.find({"address":{
                           "street_address":"123 Roswell Road",
                           "city":"Marietta",
                           "state":"GA",
                           "zip":"30062"}
                           })                       

//find specific field
db.employees.find({"address.zip":"30144"})


//////////////
//referencing
//there are multiple ways to do this in MongoDB
//this way resembles join in SQL
//sample tables
db.addresses.insertMany([
                       {"address_id":"000101","street_address":"123 Chastain Road","city":"Kennesaw","state":"GA","zip":"30144"},
                       {"address_id":"000102","street_address":"123 Roswell Road","city":"Marietta","state":"GA","zip":"30062"}
                       ])
                       
db.users.insertMany([
                    {"user_id":"01231","name":"Alice Smith","address_id":"000101"},
                    {"user_id":"01243","name":"Bob Wilsons","address_id":"000102"}
                   ])

///////////////////
//then "join" using aggregate() and $lookup
db.users.aggregate([
    {$lookup:
        {
          from: "addresses",
          localField: "address_id",
          foreignField: "address_id",
          as: "address"
        }
   }
])


//join multiple collections
db.accounts.insertMany([
                       {"user_id":"01231","username":"ASmith5","join_date":"2010-01-01"},
                       {"user_id":"01243","username":"BWilso7","join_date":"2009-03-02"}
                      ])

//we can chain $lookup                       
db.users.aggregate([
    {$lookup:
        {
          from: "accounts",
          localField: "user_id",
          foreignField: "user_id",
          as: "account_info"
        }
   },
   {$lookup:
        {
          from: "addresses",
          localField: "address_id",
          foreignField: "address_id",
          as: "address"
        }
   }
])
   