CNavya21

Posted on Feb 21, 2023

SQL Concepts

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:

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

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