Daily used commands for a Developer

Summary of Top 10 SQL Commands

Command	Description

SELECT

Retrieve data from a table

INSERT

Add new records

UPDATE

Modify existing records

DELETE

Remove records

CREATE TABLE

Define a new table

ALTER TABLE

Modify table structure

DROP TABLE

Delete an entire table

JOIN	Combine data from multiple tables

GROUP BY & HAVING

Aggregate and filter data

ORDER BY

Sort query results

SELECT – Retrieve Data from a Table

SELECT * FROM Employees;
SELECT Name, Age FROM Employees WHERE Age > 30;

✅ Fetches data from a database table.

---

2️⃣ INSERT – Add New Records

INSERT INTO Employees (Name, Age, City) 
VALUES ('John Doe', 28, 'New York');

✅ Inserts new data into a table.

---

3️⃣ UPDATE – Modify Existing Records

UPDATE Employees 
SET Age = 29 
WHERE Name = 'John Doe';

✅ Updates existing data in a table.

---

4️⃣ DELETE – Remove Records

DELETE FROM Employees WHERE Age < 25;

✅ Removes specific records from a table.

---

5️⃣ CREATE TABLE – Define a New Table

CREATE TABLE Employees (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100),
    Age INT,
    City VARCHAR(50)
);

✅ Creates a new table in the database.

---

6️⃣ ALTER TABLE – Modify an Existing Table

ALTER TABLE Employees ADD COLUMN Salary DECIMAL(10,2);

✅ Adds, removes, or modifies columns in an existing table.

---

7️⃣ DROP TABLE – Delete an Entire Table

DROP TABLE Employees;

✅ Completely removes a table from the database.

---

8️⃣ JOIN – Combine Data from Multiple Tables

SELECT Employees.Name, Departments.DepartmentName 
FROM Employees 
INNER JOIN Departments ON Employees.DepartmentID = Departments.ID;

✅ Retrieves data from multiple related tables.

---

9️⃣ GROUP BY & HAVING – Aggregate Data

SELECT City, COUNT(*) AS EmployeeCount 
FROM Employees 
GROUP BY City 
HAVING COUNT(*) > 5;

✅ Groups records and filters aggregates.

---

🔟 ORDER BY – Sort Query Results

SELECT * FROM Employees ORDER BY Age DESC;

✅ Sorts data in ascending or descending order.

1️⃣ Show All Databases

show dbs

✅ Lists all databases in the MongoDB server.

---

2️⃣ Use a Specific Database

use myDatabase

✅ Switches to a specific database (creates it if it doesn’t exist).

---

3️⃣ Show All Collections

show collections

✅ Lists all collections (tables) inside the current database.

---

4️⃣ Insert a Document

db.employees.insertOne({ name: "John Doe", age: 30, city: "New York" })

✅ Adds a new record into the employees collection.

---

5️⃣ Find (Retrieve) Documents

db.employees.find()
db.employees.find({ age: { $gt: 25 } })

✅ Fetches all documents or filters by conditions.

---

6️⃣ Update a Document

db.employees.updateOne({ name: "John Doe" }, { $set: { age: 31 } })

✅ Modifies specific fields in a document.

---

7️⃣ Delete a Document

db.employees.deleteOne({ name: "John Doe" })

✅ Removes a single document from the collection.

---

8️⃣ Create an Index (Improve Query Performance)

db.employees.createIndex({ name: 1 })

✅ Adds an index to speed up queries.

---

9️⃣ Aggregate (Group & Process Data)

db.employees.aggregate([
    { $group: { _id: "$city", total: { $sum: 1 } } }
])

✅ Groups documents and performs operations like sum, count, etc.

---

🔟 Drop a Collection (Delete a Table)

db.employees.drop()

✅ Removes the entire collection from the database.

1️⃣ Find Documents Greater Than a Specific Date ($gt)

👉 Get orders placed after 2024-02-05

db.orders.find({ orderDate: { $gt: ISODate("2024-02-05T00:00:00Z") } })

✅ Returns orders after 2024-02-05

---

2️⃣ Find Documents Less Than a Specific Date ($lt)

👉 Get orders placed before 2024-02-05

db.orders.find({ orderDate: { $lt: ISODate("2024-02-05T00:00:00Z") } })

✅ Returns orders before 2024-02-05

---

3️⃣ Find Documents Between Two Dates ($gte and $lte)

👉 Get orders placed between 2024-02-01 and 2024-02-10

db.orders.find({ 
    orderDate: { 
        $gte: ISODate("2024-02-01T00:00:00Z"), 
        $lte: ISODate("2024-02-10T23:59:59Z") 
    } 
})

✅ Returns orders within the specified date range

---

4️⃣ Find Documents on an Exact Date ($eq)

👉 Get orders placed exactly on 2024-02-05

db.orders.find({ orderDate: { $eq: ISODate("2024-02-05T00:00:00Z") } })

✅ Returns orders with the exact date

---

5️⃣ Find Orders Within the Last 7 Days ($gte and new Date())

db.orders.find({ 
    orderDate: { 
        $gte: new Date(new Date().setDate(new Date().getDate() - 7))
    } 
})