BDS456B Program 3

3a. Execute query selectors (comparison selectors, logical selectors ) and list out the results on any collection.

//To create the new database as well as switch the database if not existing
use companyDB
//To insert the multiple values or documents
db.Employees.insertMany([
  { name: "Alice", age: 30, department: "HR", salary: 50000, joinDate: new Date("2015-01-15") },
  { name: "Bob", age: 24, department: "Engineering", salary: 70000, joinDate: new Date("2019-03-10") },
  { name: "Charlie", age: 29, department: "Engineering", salary: 75000, joinDate: new Date("2017-06-23") },
  { name: "David", age: 35, department: "Marketing", salary: 60000, joinDate: new Date("2014-11-01") },
  { name: "Eve", age: 28, department: "Finance", salary: 80000, joinDate: new Date("2018-08-19") }
])

Queries Using Comparison Selectors:

1. $eq (Equal): Find employees in the “Engineering” department.

db.Employees.find({ department: { $eq: "Engineering" } }).pretty()

OUTPUT:

[
  {
    _id: ObjectId('666c18217d3bfa1feacdcdf7'),
    name: 'Bob',
    age: 24,
    department: 'Engineering',
    salary: 70000,
    joinDate: ISODate('2019-03-10T00:00:00.000Z')
  },
  {
    _id: ObjectId('666c18217d3bfa1feacdcdf8'),
    name: 'Charlie',
    age: 29,
    department: 'Engineering',
    salary: 75000,
    joinDate: ISODate('2017-06-23T00:00:00.000Z')
  }
]

2. $ne (Not Equal): Find employees who are not in the “HR” department.

db.Employees.find({ department: { $ne: "HR" } }).pretty()

OUTPUT:

[
  {
    _id: ObjectId('666c18217d3bfa1feacdcdf7'),
    name: 'Bob',
    age: 24,
    department: 'Engineering',
    salary: 70000,
    joinDate: ISODate('2019-03-10T00:00:00.000Z')
  },
  {
    _id: ObjectId('666c18217d3bfa1feacdcdf8'),
    name: 'Charlie',
    age: 29,
    department: 'Engineering',
    salary: 75000,
    joinDate: ISODate('2017-06-23T00:00:00.000Z')
  },
  {
    _id: ObjectId('666c18217d3bfa1feacdcdf9'),
    name: 'David',
    age: 35,
    department: 'Marketing',
    salary: 60000,
    joinDate: ISODate('2014-11-01T00:00:00.000Z')
  },
  {
    _id: ObjectId('666c18217d3bfa1feacdcdfa'),
    name: 'Eve',
    age: 28,
    department: 'Finance',
    salary: 80000,
    joinDate: ISODate('2018-08-19T00:00:00.000Z')
  }
]

3. $gt (Greater Than): Find employees who are older than 30.

db.Employees.find({ age: { $gt: 30 } }).pretty()

OUTPUT:

[
  {
    _id: ObjectId('666c18217d3bfa1feacdcdf9'),
    name: 'David',
    age: 35,
    department: 'Marketing',
    salary: 60000,
    joinDate: ISODate('2014-11-01T00:00:00.000Z')
  }
]

4. $lt (Less Than): Find employees with a salary less than 70000.

db.Employees.find({ salary: { $lt: 70000 } }).pretty()

OUTPUT:

[
  {
    _id: ObjectId('666c18217d3bfa1feacdcdf6'),
    name: 'Alice',
    age: 30,
    department: 'HR',
    salary: 50000,
    joinDate: ISODate('2015-01-15T00:00:00.000Z')
  },
  {
    _id: ObjectId('666c18217d3bfa1feacdcdf9'),
    name: 'David',
    age: 35,
    department: 'Marketing',
    salary: 60000,
    joinDate: ISODate('2014-11-01T00:00:00.000Z')
  }
]

5. $gte (Greater Than or Equal): Find employees who joined on or after January 1, 2018.

db.Employees.find({ joinDate: { $gte: new Date("2018-01-01") } }).pretty()

OUTPUT:

[
  {
    _id: ObjectId('666c18217d3bfa1feacdcdf7'),
    name: 'Bob',
    age: 24,
    department: 'Engineering',
    salary: 70000,
    joinDate: ISODate('2019-03-10T00:00:00.000Z')
  },
  {
    _id: ObjectId('666c18217d3bfa1feacdcdfa'),
    name: 'Eve',
    age: 28,
    department: 'Finance',
    salary: 80000,
    joinDate: ISODate('2018-08-19T00:00:00.000Z')
  }
]

6. $lte (Less Than or Equal): Find employees who are 28 years old or younger.

db.Employees.find({ age: { $lte: 28 } }).pretty()

OUTPUT:

[
  {
    _id: ObjectId('666c18217d3bfa1feacdcdf7'),
    name: 'Bob',
    age: 24,
    department: 'Engineering',
    salary: 70000,
    joinDate: ISODate('2019-03-10T00:00:00.000Z')
  },
  {
    _id: ObjectId('666c18217d3bfa1feacdcdfa'),
    name: 'Eve',
    age: 28,
    department: 'Finance',
    salary: 80000,
    joinDate: ISODate('2018-08-19T00:00:00.000Z')
  }
]

Queries Using Logical Selectors:

1. $and (Logical AND): Find employees who are in the “Engineering” department and have a salary greater than 70000.

db.Employees.find({ 
  $and: [
    { department: "Engineering" },
    { salary: { $gt: 70000 } }
  ] 
}).pretty()

OUTPUT:

[
  {
    _id: ObjectId('666c18217d3bfa1feacdcdf8'),
    name: 'Charlie',
    age: 29,
    department: 'Engineering',
    salary: 75000,
    joinDate: ISODate('2017-06-23T00:00:00.000Z')
  }
]

2. $or (Logical OR): Find employees who are either in the “HR” department or have a salary less than 60000.

db.Employees.find({ 
  $or: [
    { department: "HR" },
    { salary: { $lt: 60000 } }
  ] 
}).pretty()

OUTPUT:

[
  {
    _id: ObjectId('666c18217d3bfa1feacdcdf6'),
    name: 'Alice',
    age: 30,
    department: 'HR',
    salary: 50000,
    joinDate: ISODate('2015-01-15T00:00:00.000Z')
  }
]

3. $not (Logical NOT): Find employees who are not in the “Engineering” department.

db.Employees.find({ 
  department: { 
    $not: { $eq: "Engineering" } 
  } 
}).pretty()

OUTPUT:

[
  {
    _id: ObjectId('666c18217d3bfa1feacdcdf6'),
    name: 'Alice',
    age: 30,
    department: 'HR',
    salary: 50000,
    joinDate: ISODate('2015-01-15T00:00:00.000Z')
  },
  {
    _id: ObjectId('666c18217d3bfa1feacdcdf9'),
    name: 'David',
    age: 35,
    department: 'Marketing',
    salary: 60000,
    joinDate: ISODate('2014-11-01T00:00:00.000Z')
  },
  {
    _id: ObjectId('666c18217d3bfa1feacdcdfa'),
    name: 'Eve',
    age: 28,
    department: 'Finance',
    salary: 80000,
    joinDate: ISODate('2018-08-19T00:00:00.000Z')
  }
]

4. $nor (Logical NOR): Find employees who are neither in the “HR” department nor have a salary greater than 75000.

db.Employees.find({ 
  $nor: [
    { department: "HR" },
    { salary: { $gt: 75000 } }
  ] 
}).pretty()

OUTPUT:

[
  {
    _id: ObjectId('666c18217d3bfa1feacdcdf7'),
    name: 'Bob',
    age: 24,
    department: 'Engineering',
    salary: 70000,
    joinDate: ISODate('2019-03-10T00:00:00.000Z')
  },
  {
    _id: ObjectId('666c18217d3bfa1feacdcdf8'),
    name: 'Charlie',
    age: 29,
    department: 'Engineering',
    salary: 75000,
    joinDate: ISODate('2017-06-23T00:00:00.000Z')
  },
  {
    _id: ObjectId('666c18217d3bfa1feacdcdf9'),
    name: 'David',
    age: 35,
    department: 'Marketing',
    salary: 60000,
    joinDate: ISODate('2014-11-01T00:00:00.000Z')
  }
]

3b. Execute query selectors (Geospatial selectors, Bitwise selectors ) and list out the results on any collection.

Geospatial Selectors:

use geoDatabase
db.Places.insertMany([
  { name: "Central Park", location: { type: "Point", coordinates: [-73.9654, 40.7829] } },
  { name: "Times Square", location: { type: "Point", coordinates: [-73.9851, 40.7580] } },
  { name: "Brooklyn Bridge", location: { type: "Point", coordinates: [-73.9969, 40.7061] } },
  { name: "Empire State Building", location: { type: "Point", coordinates: [-73.9857, 40.7488] } },
  { name: "Statue of Liberty", location: { type: "Point", coordinates: [-74.0445, 40.6892] } }
])
// Create a geospatial index
db.Places.createIndex({ location: "2dsphere" })

Geospatial Queries:

1. $near (Find places near a certain point): Find places near a specific coordinate, for example, near Times Square.

db.Places.find({
  location: {
    $near: {
      $geometry: {
        type: "Point",
        coordinates: [-73.9851, 40.7580]
      },
      $maxDistance: 5000 // distance in meters
    }
  }
}).pretty()

OUTPUT:

[
  {
    _id: ObjectId('666c25eb7d3bfa1feacdcdfc'),
    name: 'Times Square',
    location: { type: 'Point', coordinates: [ -73.9851, 40.758 ] }
  },
  {
    _id: ObjectId('666c25eb7d3bfa1feacdcdfe'),
    name: 'Empire State Building',
    location: { type: 'Point', coordinates: [ -73.9857, 40.7488 ] }
  },
  {
    _id: ObjectId('666c25eb7d3bfa1feacdcdfb'),
    name: 'Central Park',
    location: { type: 'Point', coordinates: [ -73.9654, 40.7829 ] }
  }
]

2. $geoWithin (Find places within a specific area): Find places within a specific polygon, for example, an area covering part of Manhattan.

db.Places.find({
  location: {
    $geoWithin: {
      $geometry: {
        type: "Polygon",
        coordinates: [
          [
            [-70.016, 35.715],
            [-74.014, 40.717],
            [-73.990, 40.730],
            [-73.990, 40.715],
            [-70.016, 35.715]
          ]
        ]
      }
    }
  }
}).pretty()

OUTPUT:

[
  {
    _id: ObjectId('666c25eb7d3bfa1feacdcdfd'),
    name: 'Brooklyn Bridge',
    location: { type: 'Point', coordinates: [ -73.9969, 40.7061 ] }
  }
]

Bitwise Selectors:

use techDB
db.Devices.insertMany([
  { name: "Device A", status: 5 }, // Binary: 0101
  { name: "Device B", status: 3 }, // Binary: 0011
  { name: "Device C", status: 12 }, // Binary: 1100
  { name: "Device D", status: 10 }, // Binary: 1010
  { name: "Device E", status: 7 }  // Binary: 0111
])

Execute Bitwise Queries:

1. $bitsAllSet (Find documents where all bits are set): Find devices where the binary status has both the 1st and 3rd bits set (binary mask 0101, or decimal 5).

db.Devices.find({
  status: { $bitsAllSet: [0, 2] }
}).pretty()

OUTPUT:

[
  {
    _id: ObjectId('666c28847d3bfa1feacdce00'),
    name: 'Device A',
    status: 5
  },
  {
    _id: ObjectId('666c28847d3bfa1feacdce04'),
    name: 'Device E',
    status: 7
  }
]

2. $bitsAnySet (Find documents where any of the bits are set): Find devices where the binary status has at least the 2nd bit set (binary mask 0010, or decimal 2).

db.Devices.find({
  status: { $bitsAnySet: [1] }
}).pretty()

OUTPUT:

[
  {
    _id: ObjectId('666c28847d3bfa1feacdce01'),
    name: 'Device B',
    status: 3
  },
  {
    _id: ObjectId('666c28847d3bfa1feacdce03'),
    name: 'Device D',
    status: 10
  },
  {
    _id: ObjectId('666c28847d3bfa1feacdce04'),
    name: 'Device E',
    status: 7
  }
]

3. $bitsAllClear (Find documents where all bits are clear): Find devices where the binary status has both the 2nd and 4th bits clear (binary mask 1010, or decimal 10).

db.Devices.find({
  status: { $bitsAllClear: [1, 3] }
}).pretty()

OUTPUT:

[
  {
    _id: ObjectId('666c28847d3bfa1feacdce00'),
    name: 'Device A',
    status: 5
  }
]

4. $bitsAnyClear (Find documents where any of the bits are clear): Find devices where the binary status has at least the 1st bit clear (binary mask 0001, or decimal 1).

db.Devices.find({
  status: { $bitsAnyClear: [0] }
}).pretty()

OUTPUT:

[
  {
    _id: ObjectId('666c28847d3bfa1feacdce02'),
    name: 'Device C',
    status: 12
  },
  {
    _id: ObjectId('666c28847d3bfa1feacdce03'),
    name: 'Device D',
    status: 10
  }
]

Leave a Reply

Your email address will not be published. Required fields are marked *