SQL Concepts

ACID Properties

• ACID stands for Atomicity, Consistency, Isolation and Durability.
• These are some of the most basic properties of any scalable project

Atomicity

• This ensures that all the database operations should be completed successfuly, Otherwise in any case database should role back to it's previus state
• This ensures no leak of data and helps maintain data consistency

Consistency

• Database will remain consistent at any given point of time
• e.g Database will follow all the constraints, rules at any given time this helps to maintain consistent data

Isolation

• Every transaction in a database is isolated
• Multiple transactions will not interfare with each other, This will be achieved by using locking mechanism

Durability

• This property refers to maintaining consistent data for long time
• Even when the system crashes data could be restored

Implimentation

• Locking mechanism ensures that transactions do not interfare with each other, hence helps to maintain Isolation
• Data follows the given constriants at any point of time, e.g It will not allow duplicates in a primary key at any given time
• Logging techniques are used to log transactions, so that even in case of system crash, we can role back to the transactions that we want,hence databases are durable
• database will role back the entire transaction if it could not complete it successfuly, hence there is no concept of partial exicution

---

CAP Theorem

• CAP stands for consistency, Availability, Partition Tolerance
• In this article, I am going to explain cap theorem with an example
• In case of distributed systems, every system should access the same data, this property is called Consistency
• Even in case of failure of one system, others should be able to perform, this property is called availability
• Even when the network breaks down application sholud perform it's operation, this property is called Partition Tolerance

Explanation

• Let's take an example of todo-list app
• since the traffic is high, we are using two netwroks(systems) to meet the demand
• let's say for example, oneday client asks for a data from system1, but system1 don't have that data because system2 is handling that client and storing in system2
• This problem is called Consistency problem, because system1 and system2 data are not same
• To over come tha above problem let's say we have created a code where both systems exchange their data, so that consistency will be maintained
• Now, After sometime system2 gets down due to some bug, then requests will have to be handled by system1 and then pass them to system2 to maintain consistency but system2 is down.
• So, here we have two options either compromise on consistency or make entire application stop working.
• This property is called Availability
• In case of network break down between two systems, Then we need to chose consistency or availability
• This is called Partition Tolerance

Conclusion

• CAP theorem states that it's imposible to attain three properties in case of system break down, hence we have to choose
• Most of Systems now a days choose AP, because consistency can be attained after somtime

---

Joins

• As the name suggests Joins are used to used to join two tables based on the column that is common in both the tables

• Some of the most commonly used joins are

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • SELF JOIN

INNER JOIN

• INNER JOIN key word is used to join two tables which have same values

    SELECT  column_name(s),  
    FROM  table1 t1
    INNER  JOIN  table2 t2
    ON  t1.column_name = t2.column_name;

• This will return combined table of having all the columns from two tables

LEFT JOIN

• LEFT JOIN keyword is used to join two tables based on common columns
• This will return all the rows from first table and records that match from the second table

    SELECT  column_name(s) 
    FROM  table1 t1  
    LEFT  JOIN  table2 t2
    ON  t1.column_name = t2.column_name;

RIGHT JOIN

• RIGHT JOIN keyword is used to join two tables based on common columns
• This will return all the rows from second table and records that match from the first table

    SELECT  column_name(s) 
    FROM  table1 t1  
    RIGHT  JOIN  table2 t2
    ON  t1.column_name = t2.column_name;

SELF JOIN

• SELF JOIN keyword is used to join a table with itself by using a column which have common values of other column in same table

    SELECT
        e.Id,e.FullName,e.ManagerId,m.FullName as ManagerName
    FROM Employees e
    JOIN Employees m
    ON e.ManagerId = m.Id

• This can be achieved by using alieses

---

Aggregations, Filters in queries

• Aggrigators are functions that are perfomed on a column name depending on reqirement
• Filters are the conditions that are used to filter required data

Aggregations

• Some of the common aggregators are
  • COUNT(colomn_name) :
    • returns the number of records in that column
    • It will not count NULL values
  • SUM(column_name):
    • returns the sum of all the values in tha column
    • It ignores NULL values
  • MIN(column_name):
    • returns the minimum value in the column
  • MAX(column_name):
    • returns the maximum value in the column
  • AVG(column_name):
    • returns the average value of column values
  • STDDEV(column_name):
    • returns standard deviation of values of the given column
  • CONCAT(value1,value2):
    • returns the concatinated string of given values #### example:

SELECT COUNT(student) FROM students;

• The above statement returns the count of records in students table
• similarly we can use different aggregator functions

Filters

• Here are some of the filters
  • = (equals) :
    • this operator used check if two values are equal or not
  • != (not equals):
    • retruns true if two values are not equals else returns false
  • > (greater than):
    • returns true if value1 is greater than value2, else returns false
  • < (less than)
    • returns true if value1 is less than value2, else returns false
  • NOT:
    • It will negates the boolean obtained
  • LIKE :
    • It is key word can be used to search a pattern
  • IN:
    • returns true if value is present inside set of values
  • NOT IN :
    • returns true if value is not present in set of values
  • IS NULL:
    • checkes if that value is null or not
  • IS NOT NULL:
    • checks if that value is not null or not #### Example

SELECT * FROM students
WHERE marks > 90 AND 
name LIKE '%raj%' AND 
age NOT IN BETWEEN 10 AND 13

• The above snippet gives the student recors whose marks are greater than 90 and name should contain 'raj' and age is not in between 10 and 13.
• similarly we can use different filters to get required result

---

Normalization

• Normalization refers to organising redundent data into tables such that no data redundency is present
• There are majorly five stages of normalization
  • First Normal Form (1NF):
    • single row should not have multiple values
    • tables can't be furture divided into smaller parts
  • Second Normal Form(2NF):
    • This requires that all non-key attributes in a table are functionally dependent on the entire primary key, rather than just a part of it.
  • Third Normal Form(3NF):
    • Non key attribute of any table should depend only on primary key not on any other key

• Most of the present databases are in 3NF

• There are other normalisation forms but not majorly used

---

Indexing

• To improve the performence of a select query we use indexing
• Indexing will create a pointer to the data where database is stored
• Indexing should not be used if data manipulation is intense

Creating a Index

• Basic syntax to create indexing

CREATE INDEX index_name ON table_name;

• we can create unique indexes as well ### Drop a Index
• If we want to remove indexing

DROP INDEX index_name;

---

Transactions

• Transaction in sql is some set of instructions that we give to database to change data
• Transactions follow ACID principles ## Transaction Control Commands
• These commands can only be applied to the DML statements
• They are INSERT, DELETE, and UPDATE only
• here are transaction control commands
  • COMMIT :
    • after DML statement, if we use commit statement changes will be saved permanantly to database.
    • It will update the database since last COMMIT or ROLLBACK
  • ROLLBACK :
    • rollback command will undo the changes in the database that were made since last commit or last rollback
  • SET SAVEPOINT:
    • we can create a savepoint upto where we want to save the statement
  • RELEASE SAVEPOINT:
    • It will remove the savepoint from database
• we can't rollback changes once they are commited

---

Locking Mechanism

• Locking mechanism in sql helps to maintain isolation property which is one of the ACID principles
• we use keyword USE LOCK to enable a lock to any data base resource i.e Tables,columns , rows, keys etc

Types of Locks:

1. Exclusive Lock (X):

• The exclusive lock happens during a Data Manipulation Language (DML) operation such as INSERT, UPDATE, and DELETE query.
• Once the lock is used we can't access the table till the process completes

2. Shared Lock(S):

• This happpens during select statement
• Updating a table during shared lock is not possible

3. Schema Lock (Sch):

We use schema locks when a transaction to create or alter a table (Data Definition Language statement) is used in the SQL Server database.

example:

    BEGIN TRAN

    USE LOCKS

    UPDATE employees
    SET title = 'Software Engineer II'
    WHERE emp_id = 101
    ROLLBACK;

• Since we are updating a table Exclusive lock will be active till the transaction completes

---

Isolation Levels

• Isolation levels define the degree to which a transaction must be isolated from the data modifications made by any other transaction in the database system.

• The SQL standard defines four isolation levels:

  • Read Uncommitted – Read Uncommitted is the lowest isolation level. In this level, one transaction may read not yet committed changes made by other transactions, thereby allowing dirty reads. At this level, transactions are not isolated from each other.
  • Read Committed – This isolation level guarantees that any data read is committed at the moment it is read. Thus it does not allow dirty read. The transaction holds a read or write lock on the current row, and thus prevents other transactions from reading, updating, or deleting it.
  • 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. Since other transactions cannot read, update or delete these rows, consequently it avoids non-repeatable read.
  • Serializable – This is the highest isolation level. A serializable execution is guaranteed to be serializable. Serializable execution is defined to be an execution of operations in which concurrently executing transactions appears to be serially executing.

---

Triggers

• Trigger is a set of instructions that we want to perform when ever a data manupulaiton happens on a table
• Triggers can't be called manually, as they are run only after every DML transaction

Examlple

• Creating a trigger

    CREATE  TRIGGER  schema.trigger_name
    ON table_name
    AFTER {INSERT, UPDATE, DELETE}
    [NOT  FOR REPLICATION]
    AS
    {SQL_Statements}

• {SQL_Statements} will be exicuted when ever you to DML operations in above query

References

• GFG
• scaler
• w3schools
• learnsql.com
• locking by scaler