SQL Concepts
Tags: sql, postgres, programming, beginners
Published: 2023-02-22 05:25:16

<!---
Write a technical paper in detail in markdown format

Some concepts to understand for databases:

    ACID
    CAP Theorem
    Joins
    Aggregations, Filters in queries
    Normalization
    Indexes
    Transactions
    Locking mechanism
    Database Isolation Levels
    Triggers 
-->

# SQL Concepts
## ACID
![ACID Image](https://static.javatpoint.com/sqlserver/images/sql-server-transaction2.png "ACID")
* **ACID** stands for:
 + **A**-Atomicity
 + **C**-Consistency
 + **I**-Isolation
 + **D** - Durability
 
### Atomicity
* It refers to All changes to data being performed as if they are a single operation.
* Either all the changes will take place or none.
* Example- If you try to do some transaction there is some error the transaction will be canceled no change will take place.

### Consistency
* Data remain the same at the start of the operation and the end of the operation.

### Isolation
* Multiple transactions can be operated without interfering with each other.

### Durability
* The changes to the database will occur after transaction completion even if there is a system failure.

## CAP theorem 
It states that in a distributed computing system, it is impossible to simultaneously provide more than two out of three guarantees: Consistency, Availability, and Partition Tolerance.


## Joins

SQL Join statement is used to combine two or more tables. 

Different types of Joins are as follows: 
```sql

/*
Table 1
+------------+------+-------+
| student_id | name | class |
+------------+------+-------+
| 1          | nik  | 4     |
+------------+------+-------+
| 2          | sik  | 7     |
+------------+------+-------+
| 3          | dk   | 9     |
+------------+------+-------+
*/

/*
Table 2
+------------+-------+
| student_id | score |
+------------+-------+
| 1          | 5     |
+------------+-------+
| 2          | 4     |
+------------+-------+
| 4          | 6     |
+------------+-------+
*/



```
**1.INNER JOIN** :
It joins all the relevant data in tables.

### Syntax
```sql
/*
Table 1
+------------+------+-------+
| student_id | name | class |
+------------+------+-------+
| 1          | nik  | 4     |
+------------+------+-------+
| 2          | sik  | 7     |
+------------+------+-------+
| 3          | dk   | 9     |
+------------+------+-------+
*/

/*
Table 2
+------------+-------+
| student_id | score |
+------------+-------+
| 1          | 5     |
+------------+-------+
| 2          | 4     |
+------------+-------+
| 4          | 6     |
+------------+-------+
*/
SELECT table1.student_id,table1.name,table2.score
FROM table1 
INNER JOIN table2
ON table1.student_id = table2.student_id;

/*
Output-
+------------+------+-------+
| student_id | name | score |
+------------+------+-------+
| 1          | nik  | 5     |
+------------+------+-------+
| 2          | sik  | 4     |
+------------+------+-------+
*/

``` 
**2.LEFT JOIN** :
* It returns all the rows of the table on the left side of the join and Returns all the matching values on that row if not present then null. 

### Syntax
```sql
/*
Table 1
+------------+------+-------+
| student_id | name | class |
+------------+------+-------+
| 1          | nik  | 4     |
+------------+------+-------+
| 2          | sik  | 7     |
+------------+------+-------+
| 3          | dk   | 9     |
+------------+------+-------+
*/

/*
Table 2
+------------+-------+
| student_id | score |
+------------+-------+
| 1          | 5     |
+------------+-------+
| 2          | 4     |
+------------+-------+
| 4          | 6     |
+------------+-------+
*/
SELECT table1.student_id,table1.name,table2.score
FROM table1 
LEFT JOIN table2
ON table1.student_id = table2.student_id;

/*
Output-
+------------+------+-------+
| student_id | name | score |
+------------+------+-------+
| 1          | nik  | 5     |
+------------+------+-------+
| 2          | sik  | 4     |
+------------+------+-------+
| 3          | dk   | Null  |
+------------+------+-------+
/*
```

**3.RIGHT JOIN** :
* It returns all the rows of the table on the right side of the join and Returns all the matching values on that left if not present then null.

### Syntax
```sql
/*
Table 1
+------------+------+-------+
| student_id | name | class |
+------------+------+-------+
| 1          | nik  | 4     |
+------------+------+-------+
| 2          | sik  | 7     |
+------------+------+-------+
| 3          | dk   | 9     |
+------------+------+-------+
*/

/*
Table 2
+------------+-------+
| student_id | score |
+------------+-------+
| 1          | 5     |
+------------+-------+
| 2          | 4     |
+------------+-------+
| 4          | 6     |
+------------+-------+
*/
SELECT table1.student_id,table1.name,table2.score
FROM table1 
RIGHT JOIN table2
ON table1.student_id = table2.student_id;

/*
Output-
+------------+------+-------+
| student_id | name | score |
+------------+------+-------+
| 1          | nik  | 5     |
+------------+------+-------+
| 2          | sik  | 4     |
+------------+------+-------+
| 4          | Null | 6     |
+------------+------+-------+
/*
```

**4.FULL JOIN** :
* It joins the table and will contain all the rows which also include null values of right join and left join.

### Syntax
```sql
/*
Table 1
+------------+------+-------+
| student_id | name | class |
+------------+------+-------+
| 1          | nik  | 4     |
+------------+------+-------+
| 2          | sik  | 7     |
+------------+------+-------+
| 3          | dk   | 9     |
+------------+------+-------+
*/

/*
Table 2
+------------+-------+
| student_id | score |
+------------+-------+
| 1          | 5     |
+------------+-------+
| 2          | 4     |
+------------+-------+
| 4          | 6     |
+------------+-------+
*/
SELECT table1.student_id,table1.name,table2.score
FROM table1 
FULL JOIN table2
ON table1.student_id = table2.student_id;

/*
Output-
+------------+------+-------+
| student_id | name | score |
+------------+------+-------+
| 1          | nik  | 5     |
+------------+------+-------+
| 2          | sik  | 4     |
+------------+------+-------+
| 3          | dk   | Null  |
+------------+------+-------+
| 4          | Null | 6     |
+------------+------+-------+
/*
```

## Aggregations, Filters in queries
* It performs a calculation on multiple columns and returns a single value.
* SQL provides many aggregate functions such as avg, count, sum, min, max, etc.

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

### Syntax
```sql
count(column_name)
```
### Example
``` sql
select count(*) from student;

/* 
It will return count of rows present in student table
*/
```

**2. sum()**
* It adds up all the values of a particular column. 

### Syntax
```sql
sum(column_name)
```
### Example
```sql
select sum(score) from students;
/*
This will add up all the scores of students in that particular column
*/
```

**3. avg()**
* It will return the average value of a particular column.

### Syntax
```sql
avg(column_name)
```
### Example
```sql
select avg(score) from students;
/* 
This function will return the average of that particular table by doing sum of score then divide it by count.
*/
```

**4. max()**
* It simply returns the maximum value in that particular column.

### Syntax
```sql
max(column_name)
```
### Example
```sql
select max(score) from students;
/* 
This return the max score scored by student
*/
```

**5. min()**
* It simply returns the minimum value in the particular column.
### Syntax
```sql
min(column_name)
```
### Example
```sql
select min(score) from students;
/* 
This return minimum score scored by student.
*/
```

**6. filter()**
* The filter clause extends aggregate functions (sum, avg, count, …) by an additional where clause.

### Syntax
```sql
SUM(column_name)
FILTER(WHERE CONDITION)
```

### Example
```sql
select sum(score)
filter(where name like 'A%')
from students;
/* 
This will sum the score of all student whose name start with A
*/
```

**7. group by**
* These statements group 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
```sql
select column_name(s)
from table_name
WHERE condition
group by column_name(s);
```
### Example
```sql
select sum(score)
from students
group by name
/*
This will add up the score of all student with same name.
*/
```

**8. having**
* These clauses add some conditions to the groupby.

### Syntax
```sql
expression
group by CLAUSE
having CLAUSE
condition
```
### Example
```sql
select sum(score)
from students
group by name
having name like 'A%'
/*
This will add up the score of all student with same name starting with A.
*/
```

## Normalization
* Normalization is organizing a database's table and its columns.
* It simply means just breaking down a single table into multiple tables.
* Generally used normalization types are 1NF, 2NF, and 3NF.

**1. 1NF**
* Each column should contain only one values.
For example-
If a table contains two columns person and number according to **1NF** it should only contain one mobile number for every person.

**2. 2NF**
* It should follow **1NF**.
* The Primary key of the table should be always unique.
For example-
We got a table for students containing their ID and name according to **2NF** the ID must be unique to each student.

**3. 3NF**
* It should follow **2NF**.
* All the non-key columns must be mutually independent.
For example-
consider a table, the person it contains ID, name, and address to follow **3NF** The table needs to break down in two tables one which contains ID and name and another table which contains ID and address.

## Indexes
* It improves query performance.
* It reduces the time of accessing the table by creating a cache memory.
    
## Transactions
* It refers to all the properties of ACID.
![ACID Image](https://static.javatpoint.com/sqlserver/images/sql-server-transaction2.png "ACID")
## Locking mechanism
* The lock mechanism is used to restrict unauthorized access.
* It is used to ensure data integrity.
* It is also used to ensure data consistency. Locations, where the lock can be placed, are:
   + Table
   + Key
   + Page
   + Database
   + File
   + Object
* Different types of lock modes.
   + Exclusive Lock (X)- It can hold one transaction on a resource at a particular time.
   + Shared Lock (S)- It is used on pages or rows.
   + Update Lock (U)- It same as an exclusive lock and it is more adaptable compared to an exclusive lock.
   + Schema Lock (Sch)- It is used when an operation depends on a table.
   + Bulk Update Lock (BU)- This lock is used when we need to insert a large amount of data in the database.


## Database Isolation Levels
* It defines the degrees to which we need to isolate the data.
* The SQL standard defines four isolation levels- Read Uncommitted, Read Committed, Repeatable Read, and Serializable.


## Triggers
* It is a statement that executes itself when there is a particular modification to the database.
* Types of triggers
  + **AFTER INSER**T- It activates after data is inserted into the table. 
 
  + **AFTER UPDATE**: It activates after data in the table is modified. 
 
  + **AFTER DELETE**: It activates after data is deleted/removed from the table. 
  
  + **BEFORE INSERT**: It activates before data is inserted into the table. 
 
  + **BEFORE UPDATE**: It activates before data is modified in the table. 
 
  + **BEFORE DELETE**: It activates before data is deleted from the table. 

## References
* Geek For Geeks. "ACID Properties in DBMS". Retrieved from https://www.geeksforgeeks.org/acid-properties-in-dbms/
* Geek For Geeks. "The CAP Theorem in DBMS". Retrieved from https://www.geeksforgeeks.org/the-cap-theorem-in-dbms/
* Geek For Geeks. "Aggregate functions in SQL". Retrieved from https://www.geeksforgeeks.org/aggregate-functions-in-sql/
* JAVATPOINT. "Normalization". Retrieved from https://www.javatpoint.com/dbms-normalization.
* JAVATPOINT. "PostgreSQL Indexes". Retrieved from https://www.javatpoint.com/postgresql-indexes
* JAVATPOINT. "Transaction". Retrieved from https://www.javatpoint.com/sql-server-transaction
* JAVATPOINT. "Locks in SQL Server". Retrieved from https://www.javatpoint.com/locks-in-sql-server
* Geek For Geeks. "Transaction Isolation Levels in DBMS". Retrieved from https://www.geeksforgeeks.org/transaction-isolation-levels-dbms/
* Geek For Geeks. "SQL | Triggers". Retrieved from https://www.geeksforgeeks.org/sql-triggers/?ref=gcse