SQL Concepts
Published: 2023-02-21 06:25:32

## ACID
* The ACID properties define SQL database key properties to ensure consistent, safe and robust database modification when saved. 

**1. Atomicity** – Transaction acting on several pieces of information complete only if all pieces successfully save. 

**2. Consistency** - The saved data cannot violate the integrity of the database.

**3. Isolation** - The separation of resource or data modifications made by different transactions.

**4. Durability** - Ensures that changes made to the database (transactions) that are successfully committed will survive permanently, even in the case of system failures.


## CAP Theorem
* It originally introduced as the CAP principle, can be used to explain some of the competing requirements in a distributed system with replication. 
* The three letters in CAP refer to three desirable properties: 
**consistency**(among replicated copies),
**availability**(of the system for read and write operations) and **partition tolerance** (in the face of the nodes in the system being partitioned by a network fault).


## Joins
* SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. 

Different types of Joins are as follows: 
**1.INNER JOIN** :
* It selects all rows from both tables as long as the condition is satisfied.

### Syntax
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
INNER JOIN table2
ON table1.matching_column = table2.matching_column;

table1: First table.
table2: Second table
matching_column: Column common to both the tables.

### Example
```
SELECT ord.OrderID, cust.CustomerName
FROM Orders ord
INNER JOIN Customers cust ON ord.CustomerID = cust.CustomerID; 
```

**2.LEFT JOIN** :
* It returns all the rows of the table on the left side of the join and matches rows for the table on the right side of the join. 

### Syntax
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

### Example
```
SELECT cust.CustomerName, ord.OrderID
FROM Customers cust
LEFT JOIN Orders ord ON cust.CustomerID = ord.CustomerID
ORDER BY cust.CustomerName;
```

**3.RIGHT JOIN** :
* It returns all the rows of the table on the right side of the join and matching rows for the table on the left side of the join.

### Syntax
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;

### Example
```
SELECT cust.CustomerName, ord.OrderID
FROM Customers cust
RIGHT JOIN Orders ord ON cust.CustomerID = ord.CustomerID
ORDER BY cust.CustomerName;
```

**4.FULL JOIN** :
* It creates the result-set by combining results of both LEFT JOIN and RIGHT JOIN.
* The result-set will contain all the rows from both tables.
* For the rows for which there is no matching, the result-set will contain NULL values.

### Syntax
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
FULL JOIN table2
ON table1.matching_column = table2.matching_column;

### Example
```
SELECT cust.CustomerName, ord.OrderID
FROM Customers cust
FULL JOIN Orders ord ON cust.CustomerID = ord.CustomerID
ORDER BY cust.CustomerName;
```


## Aggregations, Filters
* It performs a calculation on multiple values and returns a single value.
* SQL provides many aggregate functions that include avg, count, sum, min, max, etc.
* An aggregate function ignores NULL values when it performs the calculation, except for the count function.

### Functions:
**1. count()**
* It returns the total number of rows from a database table that matches the criteria in the SQL query.

### Syntax
count(*) or count(column_name)

### Example
``` 
select count(*) from employees;
```

**2. sum()**
* It takes the name of the column as an argument and returns the sum of all the non NULL values in that column. 

### Syntax
sum(column_name)

### Example
```
select sum(emp_id) from employees;
```

**3. avg()**
* It uses the name of the column as an argument and returns the average of all the non NULL values in that column.

### Syntax
avg(column_name)

### Example
```
select avg(salary) from employees;
```

**4. max()**
* It takes the name of the column as an argument and returns the maximum value present in the column. MAX() returns NULL when no row is selected.

### Syntax
max(column_name)

### Example
```
select max(salary) from employees;
```

**5. min()**
* It takes the name of the column as an argument and returns the minimum value present in the column. MIN() returns NULL when no row is selected.

### Syntax
min(column_name)

### Example
```
select min(salary) from employees;
```

**6. filter()**
* The filter clause is used to, as the name suggests, filter the input data to an aggregation function.

### Syntax
EXPRESSION
AGGREGATION FUNCTION 1
FILTER(WHERE CLAUSE)
AGGREGATION FUNCTION 2
FILTER(WHERE CLAUSE)
.
.
.
EXPRESSION

### Example
```
select count(emp_id)
FILTER(WHERE ID!=2),
avg(length(Name))
FILTER(WHERE length(Name)>4)
from employees;
```

**7. group by**
* These statement groups rows that have the same values into summary rows.
* The GROUP BY statement is usually used along with aggregate functions such as count(), min(), max(), avg(), sum(), etc.

### Syntax
select column_name(s)
from table_name
WHERE condition
group by column_name(s)
order by column_name(s);

### Example
```
select COUNT(CustomerID), Country
from Customers
group by Country;
```

**8. having**
* These clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

### Syntax
expression
group by CLAUSE
having CLAUSE
condition

### Example
```
select emp_id
from employees
group by Name, employed
having employed = 1;
```

## Normalization
* These is the process to eliminate data redundancy and enhance data integrity in the table.
The database normalization process can be divided into following types:

**1.First Normal Form (1NF)**
* Data is stored in tables with rows that can be uniquely identified by a Primary Key.
* Data within each table is stored in individual columns in its most reduced form.There are no repeating groups.

**2. Second Normal Form (2NF)**
* All the rules from 1NF must be satisfied.
* Only those data that relates to a table’s primary key is stored in each table.

**3. Third Normal Form (3NF)**
* All the rules from 2NF must be satisfied.
* There should be no intra-table dependencies between the columns in each table.

**4. Boyce-Codd Normal Form/Fourth Normal Form(BCNF of 4NF)**
* BCNF stands for Boyce-Codd Normal Form, which is stronger than 3NF.
* This form which doesn’t contain any value dependency. A relation that is in 4NF also comes in BCNF. 

**5. Fifth Normal Form (5NF)**
* NF stands for Fifth Normal Form, where the relationship should be in 4NF to apply the fifth normal form. This normal form doesn’t contain any dependency.
 
**6. Sixth Normal Form (6NF)**
* It stands for Sixth Normal Form, which is not a standardized form of normalization. Therefore it isn’t used nowadays and may give a clear and standardized normalization in the future. 


## Indexes
* An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. 
* Indexes can be created or dropped with no effect on the data.

To create index command
```
CREATE INDEX index_name ON table_name;
```

To drop index command
```
DROP INDEX index_name;
```

Unique Index
```
CREATE UNIQUE INDEX index_name
on table_name (column_name);
```

To alter the index , we use rebuilding the existed table. 
```
ALTER INDEX IndexName 
ON TableName REBUILD;
```

## Transactions
* The following commands used to control transaction.

**commit** 
* It is used to save the changes.

### Syntax
commit;

### Example
```
> delete from Customers
  WHERE Age = 25;
> commit;
```

**rollback**
* These command used to undo transactions that have not already been saved to the database.

### Syntax
rollback;

### Example
```
> delete from Customers
  WHERE Age = 25;
> rollback;
```

**savepoint**
* It creates points within the groups of transactions in which to ROLLBACK.

### Syntax
SAVEPOINT SAVEPOINT_NAME;

The syntax for rolling back to a SAVEPOINT is as shown below.
ROLLBACK TO SAVEPOINT_NAME;

**set transaction**
* Places a name on a transaction.       

### Syntax
SET TRANSACTION [ READ WRITE | READ ONLY ];


## Locking mechanism
* he lock is a mechanism associated with a table for restricting unauthorized access to the data.
* We can apply a lock on row level, database level, table level, and page level.

Modes of lock
* Exclusive Lock (X)
* Shared Lock (S)
* Update Lock (U)
* Intent Lock (I)
* Schema Lock (Sch)
* Bulk Update Lock (BU)


## Database Isolation Levels
* It defines how one transaction is isolated from other transactions. 
Different types of isolation levels:

1. Read committed
 * In select query it will take only committed values of table.
 * If any transaction is opened and in-completed on table in others sessions then select query will wait till no transactions are pending on same table.

2. Read uncommitted
 * It is used when we want even the non-committed values of the rows.
 * Any updates and inserts that are even not committed should be reflected in over transaction.

3. Repeatable read
 * In select query data of the table that is used under transaction of isolation level "Repeatable Read" can not be modified from any other sessions till transaction is completed.

4. Serializable
 * This is the strongest of all the Isolation Levels and guarantees pure isolation.
 * No other transaction will be able to read and write values till this type of transaction is running on the database.

5. Snapshot
 * it is similar to the serializable.
 * The difference is Snapshot does not hold lock on table during the transaction so table can be modified in other sessions. 

## Triggers
1. Data Definition Language(DDL) Trigger:
 * DDL triggers are fired in response to the DDL events, such as CREATE, ALTER, and DROP statements.
 * We can create these triggers at the database level or server level, depending on the type of DDL events. 

2. Data Manipulation Language(DML) Trigger:
 * DML triggers are fired in response to DML events like INSERT, UPDATE, and DELETE statements in the user's table or view.
 * It can also be executed in response to DML like operations performed by system-defined stored procedures.

3. Logon Triggers:
 * It fire stored procedures in response to a LOGON event.
 * This event is raised when the user session is established with an instance of SQL Server.


## References
* ACID (https://www.geeksforgeeks.org/acid-properties-in-dbms/)
* CAP Theorem (https://www.bmc.com/blogs/cap-theorem/)
* Joins (https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/)
* Aggregation, filters (https://www.scaler.com/topics/sql/aggregate-function-in-sql/)
* Normalization (https://www.sqlservercentral.com/articles/database-normalization-in-sql-with-examples)
* Indexes (https://www.tutorialspoint.com/sql/sql-indexes.htm)
* Transactions (https://www.tutorialspoint.com/sql/sql-transactions.html)
* Locking mechanism (https://www.javatpoint.com/locks-in-sql-server)
* Database Isolation levels (https://www.besttechtools.com/articles/article/sql-server-isolation-levels-by-example)