What is SQL (STRUCTURED QUERY LANGUAGE) ?

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database. SQL is the standard language for Relation Database System.

All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server use SQL as standard database language.

Why SQL?

  • Allows users to access data in relational database management systems.
  • Allows users to describe the data.
  • Allows users to define the data in database and manipulate that data.
  • Allows embedding within other languages using SQL modules, libraries & pre-compilers.
  • Allows users to create and drop databases and tables

SQL Process

When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your request and SQL engine figures out how to interpret the task.

There are various components included in the process. These components are Query Dispatcher, Optimization Engines, Classic Query Engine and SQL Query Engine, etc. Classic query engine handles all non-SQL queries, but SQL query engine won’t handle logical files

SQL Commands

The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their nature.

  • DDL                       Data Definition Language:
  • CREATE                Creates a new table, a view of a table, or another object in the database
  • ALTER                   Modifies an existing database object, such as a table.
  • DROP                   Deletes an entire table, a view of a table or other object in the database.
  • DML                      Data Manipulation Language:
  • INSERT                 Creates a record
  • UPDATE               Modifies records
  • DELETE                Deletes records
  • DCL                       Data Control Language:
  • GRANT                 Gives a privilege to the user
  • REVOKE               Takes back privileges granted from user
  • DQL                      Data Query Language:
  • SELECT                Retrieves certain records from one or more tables

Types of SQL Statements

SQL statements are categorized into four different type of statements, which are

  1. DML (DATA MANIPULATION LANGUAGE)
  2. DDL (DATA DEFINITION LANGUAGE)
  3. DCL (DATA CONTROL LANGUAGE)
  4. TCL (TRANSACTION CONTROL LANGUAGE)

Let's see one by one.

1. Data Manipulation Language(DML)

In Data Manipulation Language(DML), we have four different SQL statements, Select, Insert, Update, and Delete. 

SELECT Statement

Select statement is used to select records from the table, with or without a condition.

Example:

select * from student – Gets all records of student table.

Select * from student where rank>5 - Gets records with the condition where students' rank is greater than 5.

INSERT

Insert statement is used to insert a set of values into database table. Insert statement it used with Values. 

Example:

Insert Into Student (StudentName, StudentAge, Gender) Values(ram,’10’,male)

UPDATE

Update statement is used to update existing values in atable, which is based on some condition.

Example:

update student set StudentName=’Manoj’ where StudentName=’Kumar’

The query given above will update the studentName from Manoj to Kumar where student Name Kumar.

DELETE

Delete statement is used to delete the existing record in the table, which is based on some condition.

Example: Delete from Student where StudentName=’Manoj’

The query given above will delete records which has StudentName as Manoj.

2. Data Definition Language (DDL)

In Data Definition Language (DDL), we have three different SQL statements.

CREATE

Create statement is used to create a new table in an existing database. CREATE statement is also used to create other database object such as a stored procedure, function etc.

Example: Create Table Student (Rank Int,StudentName varchar(50),Mark Float)

ALTER

Alter statement can add a column, modify a column, drop a column, rename a column or rename a table.

Example: Alter Table Student Add (StudentAddress varchar (100))

DROP

SQL DROP TABLE statement is used to remove a table definition and all the data, indexes, triggers, constraints and permission specifications for the table.

Example: Drop Student

TRUNCATE

TRUNCATE SQL query removes all rows from a table, without logging the individual row deletions.

Example: Truncate Table Table_Name

3. Data Control Language(DCL)

In Data Control Language(DCL), it defines the control over the data in the database. We have two different commands, which are

GRANT

Grant is allowed to do the specified user to the specified tasks.

Syntax

GRANT privilege_name

ON object_name

TO {user_name |PUBLIC |role_name}

[WITH GRANT OPTION];

REVOKE

It is used to cancel previously granted or denied permissions.

Syntax

REVOKE privilege_name

ON object_name

FROM {user_name |PUBLIC |role_name}

4. Transaction Control Language (TCL)

In Transaction Control Language (TCL), the commands are used to manage the transactions in the database. These are used to manage the changes made by DML statements. It also allows the statements to be grouped together into logical transactions.

COMMIT

Commit command is used to permanently save any transaction into the database.

Syntax Commit;

ROLLBACK

Rollback command is used to restore the database for the last committed state. It’s also used with save point to jump to the save point.

Syntax

Rollback to save point name