SQL CONCEPTS
Published: 2023-02-22 07:04:22

## SQL

**SQL** stands for **S**tructured **Q**uery **L**anguage.It is a database language that is used to create, maintain and retrieve data from relational databases like MySQL,PostgreSQL
etc.We use SQL when data is structured in the from of tables.

Some of the key concepts of SQL are :

1. ACID
2. CAP Theorem
3. Joins
4. Aggregations, Filters in queries
5. Normalization
6. Indexes
7. Transactions
8. Locking mechanism
9. Database Isolation Levels
10. Triggers

Let's see them one by one.

## ACID

A transaction is a single logical unit of work which assess and if also may modify the contents of a database. Data is accessed by transaction using read and write operations.
In order to maintain consistency in a database before and after transaction certain properties are followed.These properties are called **ACID**.

ACID is an acronym for :

A : Atomicity
C : Consistency
I : Isolation
D : Durability

**Atomicity** : This property states that either the entire transaction takes place or it does not happen at all.
No transaction occurs partially. Atomicity involves two operations :
* Abort : If a transaction aborts,then changes made to the database are not visible.
* Commit : If a transaction commits, changes made to the database are visible.

**Consistency** : Consistency means that the database must be consistent before and after the transaction. It refers to the correctness of the database.

**Isolation** : Isolation properties ensures multiple transaction can occur at the same time without disturbing the consistency of the database. All the transactions take place independently without affecting other transactions. Changes occurring in one transaction will not be visible to other transactions until it is comitted.

**Durability** : This property states that changes made to the database after a transaction are stored in disc and they should persist even if the system fails. Therefore the changes made by the transaction are never lost.

## CAP THEOREM 

CAP theorem states that it is not possible to guarentee all the three desired properties : **C**onsistency,**A**vailability and **P**artition tolerance at the same time in a distributed system with data replication.
It states that any network shared data system can support only two of the three properties.

**Consistency** : Consistency means all  the nodes will have the same copy of the replicated data item for different types of transactions. All the clients should have the same view of the data. Consistency in CAP refers to sequential consistency.

**Availability** : Availability means that all the read and write requests for a data item will either be processed successfully or the user must receive a message that the operation could not be completed. Every non failing node must return a response for every read and write request in a reasonable amount of time.

**Partition tolerance** : Partition tolerance means that the system continues operation even if the network connecting the nodes has a fault that results in two or more partition, where the nodes in each partition can only communicate with each other. This means system continues to function despite of network partitions.

##JOINS

Joins in SQL are used to combine two or more rows between two or more tables based on some common field between them.
Different type of joins are :

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. FULL JOIN

Consider the tables below :

**students**

| Roll_number | Name   | Address | Phone | Age |
|-------------|--------|---------|-------|-----|
| 1           | Tom    | Delhi   | 123   | 18  |
| 2           | Tim    | Kolkata | 432   | 18  |
| 3           | Rahul  | Patna   | 999   | 19  |
| 4           | Ranjan | Gaya    | 787   | 18  |


**studentcourse**

| Course_id | Roll_number |
|-----------|-------------|
| 12        | 1           |
| 12        | 2           |
| 16        | 9           |
| 17        | 8           |
**INNER JOIN** : Inner join or simply join selects all the rows from the left and the right table as long as condition is satisfied.

```sql
select
    studendcourse.courseid as courseid,
    studentcourse.Roll_number
     as Roll_number,
    students.name
    as name
from
    studentcourse
    join students on
 students.Roll_number = 
studentcourse.Roll_number;
 
```

**Output**

| courseid | Roll_number | name |
|----------|-------------|------|
| 12       | 1           | Tom  |
| 12       | 2           | Tim  |

**Left join** :Left join 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.

```sql
select
    studendcourse.courseid as courseid studentcourse.Roll_numberas Roll_number 
students.name as name
from
    studentcourse
    left join students on students.Roll_number = studentcourse.Roll_number;
``` 
**Output**

|  courseid | Roll_number | name |
|-----------|-------------|------|
| 12        | 1           | Tom  |
| 12        | 2           | Tim  |
| 16        | 9           |      |
| 17        | 8           |      |

**Right join** : Right join returns all the rows on the right and matching rows on the left.

```sql
    studendcourse.courseid as courseid,
    studentcourse.Roll_numberas Roll_number,
    students.name as name
from
    studentcourse
    right join students on 
    students.Roll_number =
 studentcourse.Roll_number;
```
**Output**

|  courseid | Roll_number | name   |
|-----------|-------------|--------|
| 12        | 1           | Tom    |
| 12        | 2           | Tim    |
|           | 3           | Rahul  |
|           | 4           | Ranjan |

**Full join** : Full join combines the results of both left join and right join. The resulting output will have all the values from both the tables.

```sql
select
    studendcourse.courseid as courseid,
    studentcourse.Roll_numberas Roll_number,
    students.name as name
from
    studentcourse
    full join students on 
    students.Roll_number =
 studentcourse.Roll_number;
```

**Output**

|  courseid | Roll_number | name   |
|-----------|-------------|--------|
| 12        | 1           | Tom    |
| 12        | 2           | Tim    |
|           | 3           | Rahul  |
|           | 4           | Ranjan |
| 16        | 9           |        |
| 17        | 8           |        |

## AGGREGATE FUNCTIONS AND FILTERS

Aggregate functions in SQL are functions where the values of multiple rows are grouped together as input on certain criteria to give some results.

Some of the Aggregate functions in SQL are :

1. Count()
2. Sum()
3. Avg()
4. Min()
5. Max()

Let's understand them one by one with the help of the table below :

**employee**

| Id | Name   | Salary |
|----|--------|--------|
| 1  | Ram    | 1200   |
| 2  | Rahul  | 1300   |
| 3  | Ranjan | 1500   |


**Count()** : Count returns the count of column elements that matches the given conditions.

```sql
select
    count(*)
from
    employees
```
**Output**
3

**Sum()** : Sum() returns the sum of all column elements that  satisfies the given conditions.

```sql
select
    sum(salary)
from
    employees
```
**Output**
4000

**Avg()** : Avg() returns the average value of all the column elements satisfying the given conditions.

```sql
select
    avg(salary)
from
    employees
```

**Output**
1333
**Max()** : Max() returns the maximum value from all the column elements satisfying the given conditions.

```sql
select
    max(salary)
from
    employees
```

**Output**
1500

**Min()** : Returns the minimum value from all the column elements satisfying the given conditions.

```sql
select
    min(salary)
from
    employees
```

**Output**
1200

**FILTERS**
SQL allows filtering of data using various queries.Filtering can be done using these clauses:

1. GROUP BY
2. HAVING

Let's understand them with examples

**IPL**

| Year | Matchid | Winner |
|------|---------|--------|
| 2017 | 1       | kkr    |
| 2017 | 2       | kkr    |
| 2017 | 3       | csk    |
| 2018 | 4       | rcb    |
| 2018 | 5       | rcb    |
| 2018 | 6       | csk    |
| 2019 | 7       | kkr    |
| 2019 | 8       | csk    |
| 2019 | 9       | csk    |
**GROUP BY** : GROUP BY statement groups together the values that satisfies the given condition or have the same values.
```sql
select
    winner as team,
count(*) as matches_won
from
    ipl
group by
    winner
```

**Output**

| team | matches_won |
|------|-------------|
| kkr  | 3           |
| csk  | 4           |
| rcb  | 2           |

**HAVING** : HAVING clause is used when we want to use functionality of WHERE when using GROUP BY.

```sql
select
    winner as team,
    count(*) as matches_won
from
    ipl
group by
    winner
having
    matches_won > 3
```

**Output**

| team | matches_won |
|------|-------------|
| csk  | 4           |



## NORMALIZATION

Normalization is a stepwise process which allows us to decompose database tables in such a way that both data dependency and update anomalies are minimized. It uses the functional dependency existing in the table and primary key or candidate key in analyzing the tables.

Different normal forms are :

**1NF** : Given relation is in 1NF if it contains an atomic 
          value.

**2NF** : Given relation will be in 2NF if it is in 1NF and 
          all non-key attributes are fully functional 
          dependent on the primary key.

**3NF**	:Given relation will be in 3NF if it is in 2NF and no 
         transition dependency exists.

**BCNF**: Stronger definition of 3NF is known as Boyce Codd's 
          normal form or BCNF.

**4NF** : Given relation will be in 4NF if it is BCNF and has 
          no multi-valued dependency.

**5NF** :Given relation is in 5NF,if it is in 4NF and does 
         not contain any join dependency, joining must always 
         be lossless.

## INDEXES

An index is a pointer to data in an SQL table.Indexes are special lookup tables which the database search engine  uses to speed up the data retrieval. Index in a database is kind of  like an index in the back of a book.

**Creating index command**
```sql
CREATE INDEX index_1 ON employee;
```
**Creating index column command**
```sql
CREATE INDEX index_1
ON employee (emp_id);
```
**Creating Unique indexes**

Unique index does not allow duplicate values to be inserted into the table.
```sql
CREATE UNIQUE INDEX index_1
on  employee(id);
```

**Creating Composite index**

A composite index is an index made from two or more columns.
```sql
CREATE INDEX index_1
ON employee (emp_id,name);
```

**Dropping the index command**

```sql
DROP index index_1
```

## TRANSACTION 

Transactions are basically group a set of tasks into a single execution unit. Every transaction starts with one task and ends when all the tasks in the group successfully complete. If any one of the tasks fail, the transaction will fail. Hence, a transaction has only two results: success or failure.
Implemeting transaction using SQL:

**Begin transaction**

It indicates the starting point of a local transaction.
```sql
BEGIN TRANSACTION transaction_1 ;
```

**Set transaction**

```sql
SET TRANSACTION [ READ WRITE | READ ONLY ];
```

**Commit**

Commit command saves all the transactions to the database since the last COMMIT.
```sql
COMMIT;
```

**Rollback**

Rollback is the process of reversing changes. This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
```sql
ROLLBACK;
```
## LOCKING MECHANISM

Locking is a mechanism which is  associated with a table for restricting unauthorized access to the data. It is mainly used to solve the concurrency problem in transactions.Locking can applied on to row level, database level, table level, and page level. 
Data consistency is really important in SQL , and it can be done by means of SQL Locks. A lock gets established in SQL Server when a transaction gets started, and is released when the transaction ends.

- **Shared (S) Locks**: This type of lock occurs when we want to read something.
- **Exclusive (X) Locks**:This lock prevents any type of modification.It prevents transactions like inserting,updating or deleting.


## DATABASE ISOLATION LEVELS


 Database isolation determines how transaction integrity is visible to other users.
Transaction must take place in a system such that it's the only transaction accessing the resources in a  given database system. 
Database isolation levels define the degree to which a transaction should be isolated from the data modifications made by any other transaction in the database system.

SQL standards has defined 4 isolation levels:

**Read Uncommitted** : It's the lowest isolation level. At 
this transaction level, transactions are not isolated from one other.
**Read Committed** : This level ensures that any data read is 
committed at the moment it is being read.
**Repeatable Read**: This is the most restrictive isolation 
level. The transaction holds read locks on all rows it references and writes locks on referenced rows for update and 
delete actions. 
**Serializable** :   It's the highest isolation level. Serializable execution is basically an execution of operations in which concurrently executing transactions 
appears to be serially executing.

## TRIGGERS

A trigger is a stored procedure in database which gets automatically invoked whenever a special event in the database occurs.A trigger may be invoked when a row is inserted into a specified table or when certain table columns are being updated. 

```sql
create trigger [name] 
[before | after]  
{insert | update | delete}  
on [table_name]  
[for each row]  
[trigger_body] 
```
**create trigger [ name ]** : This will create a trigger of the given name.

**[ before | after ]** : This specifies when the trigger that we made gets executed.

**{ insert | update | delete }** : This specifies the operation.

**on [ table_name ]** : This specifies the name of the table on which the trigger is made.

**[ for each row ]** : This specifies a row level trigger.

**[ trigger_body ]** : This specifies the operations that will be performed when the trigger gets fired.


##REFERENCES

- [geeksforgeeks-sql](https://www.geeksforgeeks.org/sql-tutorial/)
- [geeksforgeeks-acid](https://www.geeksforgeeks.org/acid-properties-in-dbms/)
- [geeksforgeeks-cap theorem](https://www.geeksforgeeks.org/the-cap-theorem-in-geek/)
- [geeksforgeeks-joins](https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/)
- [javapoint-normalization](https://www.javatpoint.com/dbms-normalization)
- [geeksforgeeks-transaction](https://www.geeksforgeeks.org/sql-transactions/)
- [javapoint-locking mechanism](https://www.javatpoint.com/locks-in-sql-server)
- [geeksforgeeks-isolation levels](https://www.geeksforgeeks.org/transaction-isolation-levels-dbms/)
- [geeksforgeeks-sql triggers](https://www.geeksforgeeks.org/sql-trigger-student-database/)