Easy Way To Understand SQL Statements and Its Uses

In this tutorial, we will learn about the various statements available for use in SQL. With this tutorial, we will dive deep into the world of SQL Statements with examples wherever required.

What are the SQL Statements?

SQL statements update, manipulate and maintain the data in a database, or retrieve data from a database. Some of the common statements that we use are DDL and DML statements.

The basic SELECT, UPDATE and DELETE statements are used extensively by developers in integration and deployment of even industry-level applications.

The CRUD operations which we perform extensively are also done using the SQL statements.

Demo Database

Let us know have a look at our demo database – DataFlair.
Query: 

SELECT * FROM DataFlair;

Output:

Uses of SQL Statements

Some of the uses of SQL statements are as follows:

1. Allow us to perform CRUD operations.
2. Provides us with the flexibility to maintain and create the database.
3. DML statements help us to manipulate the data.
4. DDL statements help us to declare the data.

Types of SQL Statements

SQL works by running the commands provided by the user. We can divide the SQL commands into five distinctions based on the functionality they provide:

1. DDL (Data Definition Language)

DDL is used to define the structure or schema of the database and to specify additional properties of the data.

These statements define the implementation of the schema and are mostly hidden from the user, also the data stored in the database is made to follow certain constraints.

Some of the DDL commands are as follows :

a. Create

To create objects in the database

CREATE TABLE tableName(
col1 datatype,
col2  datatype,
col3  datatype…..);

Example: Let us now see the query to create our DataFlair database.

CREATE TABLE DataFlair(
emp_id varchar(5),
name varchar(50),
location varchar(50),
experience int);

Output:

2. Alter in SQL

Alters the structure of the database

Syntax

ALTER TABLE tableName
ADD columnName datatype;

Example:

ALTER TABLE DataFlair
ADD emp_id varchar(5);

Output:

3. Drop in SQL

Delete objects from the database
Syntax:

DROP TABLE tableName;

Example: Let us now see how to drop the table DataFlair.
Query:

DROP TABLE DataFlair;

Output:

4. Truncate in SQL

Deletes all the data stored in a table
Syntax:

TRUNCATE TABLE  tableName;

Example: Let us now see how to truncate a table.
Query:

TRUNCATE TABLE  DataFlair;

Output:

B. DML(Data Manipulation Language)

DML is used for managing data contained in the schema objects. DML’s can be divided into two subtypes :

• Procedural DML: In these types of DML’s we need to specify the type of data we need and how to get that data.
• Declarative or Non-Procedural DML: Here, we only need to know what data is required, but the specifications on the process to get the data is not required. The database, on its own, figures out how to get the data efficiently.

Some of DML statements are as follows :

1. Insert in SQL

Used to insert data in the database
Syntax:

INSERT INTO TABLE_NAME (col1, col2, col3,...)  
VALUES (val1, val2, val3,...);

Example: Let us now try to insert a record in our database.

Query:

INSERT INTO dataflair
VALUE ('Z87','Rabina','Pune',3);
SELECT * FROM DataFlair;

Output:

2. Update in SQL

Used to modify the existing data in the database
Syntax:

UPDATE tableName
SET col1 = val1, col2 = val2...., 
WHERE condition;

Example: Let us run an update query on our table DataFlair.
Query:

UPDATE dataflair
SET emp_id = 'A99' 
WHERE name='Rabina';

Output:

3. Delete data in SQL

Used to delete all the records contained
Syntax:

DELETE FROM tableName
WHERE condition;

Example: Let us now try to delete the row corresponding to emp_id ‘A99’.
Query:

DELETE FROM Dataflair
WHERE emp_id = ‘A99’;

Output:

C. DQL (Data Query Language)

DQL is also known as the Data Query language. It is used to retrieve the data stored in the database created by us and the data we store in the database.

Some of the DQL statements are as follows:

1. Select in SQL

Used to view the database.
Syntax:

SELECT * FROM tableName;

Example: Let us view our database – DataFlair.
Query:

SELECT * FROM DataFlair;

Output:

D. TCL (Transaction Control Language)

TCL commands are used to handle transactions in the database. These statements come in handy to manage and track the changes being made by the DML queries on our database.

Some of TCL statements are as follows :

1. Commit in SQL

Used to permanently save any transaction into the database.
Syntax:

Commit;

Example: Let us now save the transaction using commit.
Query:

Commit;

Output:

2. Rollback in SQL

Used to restore the database to the last committed state and is also used with the Savepoint command to move over to any saved state in a transaction.
Syntax:

ROLLBACK TO savedTransaction;

Example: Let us now move back to the saved state.
Query:

ROLLBACK TO initialState;

Output:

3. Savepoint in SQL

It is used to temporarily store a transaction so that we can move to the point we need whenever desired.
Syntax:

SAVEPOINT pointName;

Example: Let us now save the present state of database transaction.
Query:

SAVEPOINT initialState;

Output:

E. DCL (Data Control Language)

DCL commands are similar to the computer programming language in syntax.

They are a component of the Structured Query Language while using DCL commands we need to be cautious as some databases don’t allow rollback command on the DCL command.

One such example is the Oracle database – when we execute DCL on an oracle database it results in an implicit commit and thus rollback is not allowed.

Some of the DCL statements are as follows:

1. Grant in SQL

Used to allow a specific set of users to perform some specified tasks.
Syntax:

GRANT privilege 
ON privilege_level
TO account_name;

Example: Let us now provide the update access to the user.
Query:

GRANT UPDATE
ON DataFlair
TO user;

Output:

2. Revoke in SQL

Used to cancel all the previously granted or denied permissions.
Syntax:

REVOKE privilegeName
ON tableName
FROM userName;

Example: Let us now revoke the update privilege from the user.
Query:

REVOKE Update
ON dataflair
FROM user;

Output:

Summary

In this tutorial, we have seen all about the SQL statements right from the definitions to the syntax and then examples of each.

In SQL, we have various types of statements like DDL, DML, DQL, etc. With this tutorial we have covered each one in detail discussing the further bifurcations in each and then the properties.

In this article, we have covered in detail all the statements. Statements are used for CRUD operations most of the time. Statements help us to create and maintain databases.