Today I Learned

Cross Join in PostgreSQL is like a full outer join, but without any matching condition. It simply combines every row from the first table with every row from the second table, creating a result set that is the product of the number of rows in both tables.

Example:

Let's say you have two tables:

Customers with columns customer_id and name
Products with columns product_id and name

A cross join between these two tables would produce a result set where every customer is paired with every product. This could be useful if you want to generate a list of all possible combinations of customers and products, perhaps for a recommendation system or a marketing campaign.

SELECT customers.customer_id, customers.name, products.product_id, products.name
FROM customers
CROSS JOIN products;

This query would create a table with a row for every combination of customer and product, showing each customer's ID and name along with each product's ID and name.

Stored Procedures

Databases

Stored Procedures are precompiled code blocks that reside within a database. They provide a way to encapsulate and reuse frequently executed SQL statements, improving performance, maintainability, and security.

Benefits of Stored Procedures:

Performance: Precompiled code executes faster than executing statements directly.
Modularity: Encapsulate complex logic, making code more organized and reusable.
Security: Centralize security rules and permissions.
Data validation: Enforce data integrity and consistency.

Example:

CREATE PROCEDURE GetCustomers
AS
BEGIN
    SELECT CustomerID, CustomerName, City
    FROM Customers;
END;

// How to use?

EXEC GetCustomers;

Example using params:

CREATE PROCEDURE GetCustomersByCity
    @City nvarchar(50)
AS
BEGIN
    SELECT CustomerID, CustomerName
    FROM Customers
    WHERE City = @City;
END;

// How to use?

EXEC GetCustomersByCity @City = 'London';

Database normalization

Databases

It's the process of organizing data in a relational database to reduce redundancy and improve data integrity.

Importance:

Minimize Redundancy: Eliminate duplicate data, reducing storage space.
Avoid Update Anomalies: Prevent inconsistencies when updating data.
Simplify Queries: Simplify data retrieval and improve query performance.
Enhance Data Integrity: Ensure accurate and consistent data.

Optimistic Concurrency Control (OCC)

Databases

Optimistic Concurrency Control (OCC) is a technique used in computer systems, particularly in databases, to manage concurrent access to shared resources without resorting to locking mechanisms that can potentially introduce performance bottlenecks and contention.

Imagine you have a group of friends trying to update a shared document online at the same time. Optimistic Concurrency Control is like letting everyone make changes freely and then checking for conflicts later.

Here's how it works:

Checking Out the Document: Each friend checks out the document and sees a version number (like "1.0"). This tells them the state of the document when they started.
Making Changes: Friends start making changes to the document without telling each other. They trust that conflicts are rare.
Checking Back In: Before saving their changes, each friend checks the version number again to make sure nobody else has made changes in the meantime.
Conflict Check: If someone else made changes (maybe the version is now "1.1"), it means there's a conflict. Friends are notified, and they need to figure out how to resolve the conflicting changes.
Saving Changes: If no conflicts are found, the changes are saved, and everyone's updates are combined.

A.C.I.D.

Databases

ACID properties are principles of transaction-oriented database recovery.
ACID is an acronym for a set of properties for database transactions used to make sure data is valid.

The ACID principles are:

Atomicity: A transaction is made up of multiple statements. Atomicity ensures that either all statements are executed or none are executed.
Consistency: Consistency ensures that integrity restraints are followed.
Isolation: Transaction often occurs concurrently, at the same time, and isolation ensures that this does not lead to inconsistent data.
Durability: Durability ensures that once a transaction is committed, it will remain committed even in the case of a system failure.

See more examples on: https://www.freecodecamp.org/news/acid-databases-explained/?ref=dailydev