NK1FC

Posted on Feb 22, 2023

SQL Concepts

#sql #postgres #programming #beginners

SQL Concepts

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:


/*
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

/*
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

/*
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

/*
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

/*
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

count(column_name)

Example

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

sum(column_name)

Example

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

avg(column_name)

Example

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

max(column_name)

Example

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

min(column_name)

Example

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

SUM(column_name)
FILTER(WHERE CONDITION)

Example

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

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

Example

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

expression
group by CLAUSE
having CLAUSE
condition

Example

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. ## 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 INSERT- 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

SQL Concepts

ACID

Atomicity

Consistency

Isolation

Durability

CAP theorem

Joins

Syntax

Syntax

Syntax

Syntax

Aggregations, Filters in queries

Functions:

Syntax

Example

Syntax

Example

Syntax

Example

Syntax

Example

Example

Syntax

Example

Syntax

Example

Syntax

Example

Normalization

Indexes

Transactions

Database Isolation Levels

Triggers

References

Read next

JavaScript vs. Deno, Vite 6, Astro 5, and more

Day 20: Docker Security

Systems Engineering: Free Learning Resources for Tech Enthusiasts

Autofac Dependency Injection in ASP .NET Core 8