Mongo DB & SQL example

Certainly, providing code examples for each term would be extensive, so I'll provide a code example for a few representative terms in both SQL and MongoDB. This will give you a sense of how operations are performed in each type of database.

SQL Code Examples:

1. Create Table:

   CREATE TABLE employees (
       employee_id INT PRIMARY KEY,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       department_id INT
   );

1. Insert Data:

   INSERT INTO employees (employee_id, first_name, last_name, department_id)
   VALUES (1, 'John', 'Doe', 101);

1. Select Data:

   SELECT first_name, last_name FROM employees WHERE department_id = 101;

1. Update Data:

   UPDATE employees SET department_id = 102 WHERE employee_id = 1;

1. Delete Data:

   DELETE FROM employees WHERE employee_id = 1;

1. Join Tables:

   SELECT employees.first_name, employees.last_name, departments.department_name
   FROM employees
   INNER JOIN departments ON employees.department_id = departments.department_id;

MongoDB Code Examples:

1. Insert Document:

   db.employees.insertOne({
       employee_id: 1,
       first_name: 'John',
       last_name: 'Doe',
       department_id: 101
   });

1. Query Document:

   db.employees.find({ department_id: 101 }, { first_name: 1, last_name: 1 });

1. Update Document:

   db.employees.updateOne(
       { employee_id: 1 },
       { $set: { department_id: 102 } }
   );

1. Delete Document:

   db.employees.deleteOne({ employee_id: 1 });

1. Aggregation Pipeline:

   db.employees.aggregate([
       {
           $lookup: {
               from: 'departments',
               localField: 'department_id',
               foreignField: 'department_id',
               as: 'employee_department'
           }
       },
       { $unwind: '$employee_department' },
       {
           $project: {
               first_name: 1,
               last_name: 1,
               department_name: '$employee_department.department_name'
           }
       }
   ]);

These examples showcase basic operations like creating a table/collection, inserting, updating, deleting data, and performing a join/aggregation in both SQL and MongoDB. It's important to note that the actual implementation might vary depending on the specific SQL database (e.g., MySQL, PostgreSQL) and MongoDB version.

Certainly, let's explore more examples for both SQL and MongoDB:

SQL Code Examples:

1. Group By with Aggregate:

   SELECT department_id, AVG(salary) as avg_salary
   FROM employees
   GROUP BY department_id;

1. Create Index:

   CREATE INDEX idx_last_name ON employees(last_name);

1. Stored Procedure:

   DELIMITER //
   CREATE PROCEDURE GetEmployeeCount(IN dep_id INT, OUT emp_count INT)
   BEGIN
       SELECT COUNT(*) INTO emp_count FROM employees WHERE department_id = dep_id;
   END //
   DELIMITER ;

1. Transaction:
   

   START TRANSACTION;
   UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
   UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
   COMMIT;
   

MongoDB Code Examples:

1. Text Search:
   

   db.articles.createIndex({ content: 'text' });
   db.articles.find({ $text: { $search: "important" } });
   

2. Geospatial Query:
   

   db.places.createIndex({ location: "2dsphere" });
   db.places.find({
       location: {
           $near: {
               $geometry: {
                   type: "Point",
                   coordinates: [-73.9667, 40.78]
               },
               $maxDistance: 1000
           }
       }
   });
   

3. Create User:
   

   db.createUser({
       user: "admin",
       pwd: "adminPassword",
       roles: [{ role: "readWrite", db: "myDatabase" }]
   });
   

4. Sharding Configuration:
   

   sh.enableSharding("myDatabase");
   sh.shardCollection("myDatabase.myCollection", { shardKey: "shardField" });
   

5. Change Stream:
   

   const changeStream = db.collection('documents').watch();
   changeStream.on('change', (change) => {
       printjson(change);
   });
   

These examples cover more advanced operations such as grouping with aggregate functions, creating indexes, defining stored procedures, and working with transactions in SQL. For MongoDB, additional examples include text search, geospatial queries, user creation, sharding configuration, and change streams. Keep in mind that the specific syntax may vary based on the database engine and version.