SQL
SQL stands for Structured Query Language.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 :
- ACID
- CAP Theorem
- Joins
- Aggregations, Filters in queries
- Normalization
- Indexes
- Transactions
- Locking mechanism
- Database Isolation Levels
- 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 : Consistency,Availability and Partition 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 :
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- 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.
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.
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.
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.
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 :
- Count()
- Sum()
- Avg()
- Min()
- 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.
select
count(*)
from
employees
Output
3
Sum() : Sum() returns the sum of all column elements that satisfies the given conditions.
select
sum(salary)
from
employees
Output
4000
Avg() : Avg() returns the average value of all the column elements satisfying the given conditions.
select
avg(salary)
from
employees
Output
1333
Max() : Max() returns the maximum value from all the column elements satisfying the given conditions.
select
max(salary)
from
employees
Output
1500
Min() : Returns the minimum value from all the column elements satisfying the given conditions.
select
min(salary)
from
employees
Output
1200
FILTERS
SQL allows filtering of data using various queries.Filtering can be done using these clauses:
- GROUP BY
- 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.
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.
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
CREATE INDEX index_1 ON employee;
Creating index column command
CREATE INDEX index_1
ON employee (emp_id);
Creating Unique indexes
Unique index does not allow duplicate values to be inserted into the table.
CREATE UNIQUE INDEX index_1
on employee(id);
Creating Composite index
A composite index is an index made from two or more columns.
CREATE INDEX index_1
ON employee (emp_id,name);
Dropping the index command
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.
BEGIN TRANSACTION transaction_1 ;
Set transaction
SET TRANSACTION [ READ WRITE | READ ONLY ];
Commit
Commit command saves all the transactions to the database since the last COMMIT.
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.
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.
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.
Top comments (1)
You can also check Tutorialspoint- SQL Tutorial Reference: