SQL Concepts

ACID Properties

DBMS is the management of data that should remain integrated when any changes are done in it.

It is because if the integrity of the data is affected, whole data will get disturbed and corrupted.

Therefore, to maintain the integrity of the data, there are four properties described in the database management system, which are known as the ACID properties.

The ACID properties are meant for the transaction that goes through a different group of tasks, and there we come to see the role of the ACID properties.

1. Atomicity

The term atomicity defines that the data remains atomic.

It means if any operation is performed on the data, either it should be performed or executed completely or should not be executed at all.

• Commit: In case a transaction commits, the changes made are visible to us. Thus, atomicity is also called the ‘All or nothing rule’.

• Abort: In case a transaction aborts, the changes made to the database are not visible to us.

2. Consistency

Consistency means that we have to maintain the integrity constraints so that any given database stays consistent both before and after a transaction.

3. Isolation

Isolation ensures the occurrence of multiple transactions concurrently without a database state leading to a state of inconsistency.

A transaction occurs independently, i.e. without any interference.

Any changes that occur in any particular transaction would NOT be ever visible to the other transactions unless and until this particular change in this transaction has been committed or written to the memory.

4. Durability

The durability property states that once the execution of a transaction is completed, the modifications and updates on the database gets written on and stored in the disk.

These persist even after the occurrence of a system failure. Such updates become permanent and get stored in non-volatile memory. Thus, the effects of this transaction are never lost.

CAP Theorem

The CAP theorem, originally introduced as the CAP principle, can be used to explain some of the competing requirements in a distributed system with replication. It is a tool used to make system designers aware of the trade-offs while designing networked shared-data systems.

• Consistency

Consistency means that all clients see the same data at the same time, no matter which node they connect to.

For this to happen, whenever data is written to one node, it must be instantly forwarded or replicated to all the other nodes in the system before the write is deemed successful.

• Availability

Availability means that any client making a request for data gets a response, even if one or more nodes are down. Another way to state this all working nodes in the distributed system return a valid response for any request, without exception.

• Partition tolerance

A partition is a communications break within a distributed system a lost or temporarily delayed connection between two nodes.

Partition tolerance means that the cluster must continue to work despite any number of communication breakdowns between nodes in the system.

Joins

Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

Join Types in SQL are:

• The CROSS JOIN
• The INNER JOIN
• The LEFT OUTER JOIN
• The RIGHT OUTER JOIN
• The FULL OUTER JOIN

The CROSS JOIN

A CROSS JOIN matches every row of the first table with every row of the second table. If the input tables have m and n columns, respectively, the resulting table will have m + n columns. Because CROSS JOIN have the potential to generate extremely large tables, care must be taken to use them only when appropriate.

Syntax:

SELECT ... FROM TABLE1 CROSS JOIN TABLE2 ...

SELECT EMP_ID, NAME, DEPT 
FROM COMPANY 
CROSS JOIN DEPARTMENT;

The INNER JOIN

A INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-condition.

The query compares each row of table1 with each row of table2 to find all pairs of rows, which satisfy the join-condition.

When the join-condition is satisfied, column values for each matched pair of rows of table1 and table2 are combined into a result row.

Syntax:

SELECT TABLE1.COLUMN1,TABLE2.COLUMN1 
FROM TABLE1 
[INNER] JOIN TABLE2 
ON TABLE1.COMMON_COLUMN = TABLE2.COMMON_COLUMN ...

SELECT EMP_ID, NAME, DEPT 
FROM COMPANY 
INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;

The OUTER JOIN

The OUTER JOIN is an extension of the INNER JOIN. SQL standard defines three types of OUTER JOINs: LEFT, RIGHT, and FULL and PostgreSQL supports all of these.

The LEFT OUTER JOIN

In case of LEFT OUTER JOIN, an inner join is performed first.

Then, for each row in table T1 that does not satisfy the join condition with any row in table T2, a joined row is added with null values in columns of T2.

Thus, the joined table always has at least one row for each row in T1.

Syntax:

SELECT T1.COLUMN_1,T2.COLUMN1
FROM TABLE1 AS T1
LEFT [OUTER] JOIN TABLE2 AS T2
ON T1.COMMON_COLUMN = T2.COMMON_COLUMN ...

SELECT EMP_ID, NAME, DEPT 
FROM COMPANY 
LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;