The Basics of SQL & NoSQL

Hi everyone! Welcome to my article on Basics of SQL & NoSQL. Let’s dive in!

SQL

Basic CRUD Operations

CRUD stands for Create, Read, Update, and Delete, which are the four basic operations you can perform on a database. These operations correspond to the SQL commands INSERT, SELECT, UPDATE, and DELETE.

Create (INSERT)

To insert data into a table:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Example:

INSERT INTO Employees (ID, Name, Department)
VALUES (1, 'John Doe', 'Sales');

Read (SELECT)

To retrieve data from a table:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

SELECT ID, Name, Department
FROM Employees
WHERE Department = 'Sales';

Update (UPDATE)

To modify existing data in a table:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

UPDATE Employees
SET Department = 'Marketing'
WHERE Name = 'John Doe';

Delete (DELETE)

To remove data from a table:

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM Employees
WHERE Name = 'John Doe';

Simple to moderately complex queries

Simple Query

SELECT Name, Department
FROM Employees;

Query with WHERE Clause

SELECT Name, Department
FROM Employees
WHERE Department = 'Sales';

Query with ORDER BY

SELECT Name, Department
FROM Employees
ORDER BY Name ASC;

Query with JOIN

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

Query with GROUP BY and HAVING

SELECT Department, COUNT(*)
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 1;

Subquery

SELECT Name
FROM Employees
WHERE DepartmentID = (SELECT ID FROM Departments WHERE DepartmentName = 'Sales');

Joins, Indexes, and Normalization

Joins

INNER JOIN: Selects records that have matching values in both tables.

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

LEFT JOIN (or LEFT OUTER JOIN): Selects all records from the left table, and the matched records from the right table. The result is NULL from the right side if there is no match.

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

RIGHT JOIN (or RIGHT OUTER JOIN): Selects all records from the right table, and the matched records from the left table. The result is NULL from the left side when there is no match.

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

FULL JOIN (or FULL OUTER JOIN): Selects all records when there is a match in either left or right table.

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

Indexes

Indexes improve the speed of data retrieval operations on a table at the cost of additional space and slower insert/update/delete operations.

If you want to know more about Indexes, check my article on Database Indexes.

Database Indexes

Normalization

Normalization is the process of organizing the columns (attributes) and tables (relations) of a database to minimize redundancy and dependency.

• First Normal Form (1NF): Ensure that the columns contain only atomic (indivisible) values, and each column contains values of a single type.
• Second Normal Form (2NF): Ensure that the table is in 1NF and all non-key attributes are fully functional dependent on the primary key.
• Third Normal Form (3NF): Ensure that the table is in 2NF and all the attributes are functionally dependent only on the primary key.

If you want to know more about Database Normalization, check my article on Database Normalization.

Database Normalization

NoSQL Databases

Basic Understanding of NoSQL

NoSQL databases are designed to handle large volumes of unstructured or semi-structured data. They do not use SQL as their primary query language and do not adhere strictly to relational database models.

When to Use NoSQL

1. Big Data Applications: Handling large volumes of data that are not easily manageable with traditional relational databases.
2. Real-Time Web Apps: Providing fast and flexible access to data for applications with high user traffic.
3. Flexible Schema Design: Allowing dynamic changes to the data model without major disruptions.
4. Distributed Data Storage: Spreading data across multiple servers or locations to ensure high availability and reliability.

Types of NoSQL Databases

1. Document Stores: Store data in documents (e.g., JSON, BSON). Example: MongoDB.
2. Key-Value Stores: Store data as key-value pairs. Example: Redis.
3. Column Stores: Store data in columns rather than rows. Example: Apache Cassandra.
4. Graph Databases: Store data as nodes, edges, and properties. Example: Neo4j.

Example of Basic Operations in MongoDB (Document Store)

// Insert a document into a collection
db.employees.insertOne({ name: "John Doe", department: "Sales" });

// Read documents from a collection
db.employees.find({ department: "Sales" });

// Update documents in a collection
db.employees.updateOne({ name: "John Doe" }, { $set: { department: "Marketing" } });

// Delete documents from a collection
db.employees.deleteOne({ name: "John Doe" });

Summary

Understanding both SQL and NoSQL databases is crucial for a well-rounded knowledge of database management. SQL databases are ideal for structured data and complex queries, while NoSQL databases offer flexibility and scalability for handling large volumes of unstructured data. Knowing when and how to use each type, as well as mastering basic operations and concepts, will greatly enhance your ability to design and manage databases effectively.

Thank you for reading! If you have any questions or feedback, feel free to leave a comment below. Happy coding!