Mongo DB & SQL example
Tags: webdev, javascript, beginners, programming
Published: 2024-02-04 13:24:45

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:**
   ```sql
   CREATE TABLE employees (
       employee_id INT PRIMARY KEY,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       department_id INT
   );
   ```

2. **Insert Data:**
   ```sql
   INSERT INTO employees (employee_id, first_name, last_name, department_id)
   VALUES (1, 'John', 'Doe', 101);
   ```

3. **Select Data:**
   ```sql
   SELECT first_name, last_name FROM employees WHERE department_id = 101;
   ```

4. **Update Data:**
   ```sql
   UPDATE employees SET department_id = 102 WHERE employee_id = 1;
   ```

5. **Delete Data:**
   ```sql
   DELETE FROM employees WHERE employee_id = 1;
   ```

6. **Join Tables:**
   ```sql
   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:**
   ```javascript
   db.employees.insertOne({
       employee_id: 1,
       first_name: 'John',
       last_name: 'Doe',
       department_id: 101
   });
   ```

2. **Query Document:**
   ```javascript
   db.employees.find({ department_id: 101 }, { first_name: 1, last_name: 1 });
   ```

3. **Update Document:**
   ```javascript
   db.employees.updateOne(
       { employee_id: 1 },
       { $set: { department_id: 102 } }
   );
   ```

4. **Delete Document:**
   ```javascript
   db.employees.deleteOne({ employee_id: 1 });
   ```

5. **Aggregation Pipeline:**
   ```javascript
   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:

7. **Group By with Aggregate:**
   ```sql
   SELECT department_id, AVG(salary) as avg_salary
   FROM employees
   GROUP BY department_id;
   ```

8. **Create Index:**
   ```sql
   CREATE INDEX idx_last_name ON employees(last_name);
   ```

9. **Stored Procedure:**
   ```sql
   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 ;
   ```

10. **Transaction:**
    ```sql
    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:

6. **Text Search:**
    ```javascript
    db.articles.createIndex({ content: 'text' });
    db.articles.find({ $text: { $search: "important" } });
    ```

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

8. **Create User:**
    ```javascript
    db.createUser({
        user: "admin",
        pwd: "adminPassword",
        roles: [{ role: "readWrite", db: "myDatabase" }]
    });
    ```

9. **Sharding Configuration:**
    ```javascript
    sh.enableSharding("myDatabase");
    sh.shardCollection("myDatabase.myCollection", { shardKey: "shardField" });
    ```

10. **Change Stream:**
    ```javascript
    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.