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.
Stay updated with latest technology trends
Join DataFlair on Telegram!!
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.
Let us know have a look at our demo database – DataFlair.
SELECT * FROM DataFlair;
Uses of SQL Statements
Some of the uses of SQL statements are as follows:
- Allow us to perform CRUD operations.
- Provides us with the flexibility to maintain and create the database.
- DML statements help us to manipulate the data.
- 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 :
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);
2. Alter in SQL
Alters the structure of the database
ALTER TABLE tableName ADD columnName datatype;
ALTER TABLE DataFlair ADD emp_id varchar(5);
3. Drop in SQL
Delete objects from the database
DROP TABLE tableName;
Example: Let us now see how to drop the table DataFlair.
DROP TABLE DataFlair;
4. Truncate in SQL
Deletes all the data stored in a table
TRUNCATE TABLE tableName;
Example: Let us now see how to truncate a table.
TRUNCATE TABLE DataFlair;
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
INSERT INTO TABLE_NAME (col1, col2, col3,...) VALUES (val1, val2, val3,...);
Example: Let us now try to insert a record in our database.
INSERT INTO dataflair VALUE ('Z87','Rabina','Pune',3); SELECT * FROM DataFlair;
2. Update in SQL
Used to modify the existing data in the database
UPDATE tableName SET col1 = val1, col2 = val2...., WHERE condition;
Example: Let us run an update query on our table DataFlair.
UPDATE dataflair SET emp_id = 'A99' WHERE name='Rabina';
3. Delete data in SQL
Used to delete all the records contained
DELETE FROM tableName WHERE condition;
Example: Let us now try to delete the row corresponding to emp_id ‘A99’.
DELETE FROM Dataflair WHERE emp_id = ‘A99’;
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.
SELECT * FROM tableName;
Example: Let us view our database – DataFlair.
SELECT * FROM DataFlair;
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.
Example: Let us now save the transaction using commit.
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.
ROLLBACK TO savedTransaction;
Example: Let us now move back to the saved state.
ROLLBACK TO initialState;
3. Savepoint in SQL
It is used to temporarily store a transaction so that we can move to the point we need whenever desired.
Example: Let us now save the present state of database transaction.
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.
GRANT privilege ON privilege_level TO account_name;
Example: Let us now provide the update access to the user.
GRANT UPDATE ON DataFlair TO user;
2. Revoke in SQL
Used to cancel all the previously granted or denied permissions.
REVOKE privilegeName ON tableName FROM userName;
Example: Let us now revoke the update privilege from the user.
REVOKE Update ON dataflair FROM user;
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.