What is SQL?
SQL stands for Structured Query Language. The SQL programming language was developed by IBM researchers Raymond Boyce and Donald Chamberlin in the 1970s.
Structured query
language (SQL) is a standard language for accessing and manipulating databases
in a relational database.
We can perform
various operations by using different SQL statements such as CREATE, INSERT,
UPDATE, MODIFY, DELETE, SEARCH, etc. in the database and can also perform
Arithmetic and Logical operations.
SQL is
understandable as it uses common English Keywords in its Statement.
Types of SQL Commands
Structured query language (SQL) commands are mainly categorized into 05 categories:
1. DDL – Data Definition Language
2. DML – Data Manipulation Language
3. DQL – Data Query Language
4. DCL – Data Control Language
5. TCL – Transaction Control Language
• DDL stands for Data Definition Language: DDL commands deal with the schema, i.e., the physical structure of the table in the database. DDL commands are used to create, modify, and delete database structures but not the data. DDL commands are auto-commit in nature when executed and every progression in the table is reflected and saved right away.
Following are the five DDL commands in SQL
CREATE: The CREATE
command is used to create a new database or its object, such as a table, index,
view, function, store procedure, etc.
DROP: The DROP
command is used to delete objects from the database such as tables, views,
indexes, etc.
ALTER: The ALTER
command is used to alter/modify the structure of an existing object from the
database.
TRUNCATE: The
TRUNCATE command is used to remove all the records from a table, including
spaces, but it keeps the structure intact.
RENAME: The RENAME command is used to rename the existing object in the database.
• DML stands for
Data Manipulation Language: After the creation of the tables and the database
with DDL commands, we INSERT, UPDATE, and DELETE the data inside, using DML
commands. In other words, DML commands can make changes in the data, unlike DDL
commands which deal with the schema and not the data. DML Commands can be
changed and rolled back and it is useful after you have made mistakes or want
to change values.
Following are the
04 main DML commands in SQL
INSERT: The INSERT
command is used to add new records/data to a table.
SELECT: The SELECT
command is used to retrieve the data for display purposes.
UPDATE: The UPDATE
command is used to update/modify the data which already exists in a table.
DELETE: The DELETE
command is used to remove records from a database table.
• DQL stands for
Data Query Language: Data query language consists of only one command i.e.
SELECT. The SELECT command is used to retrieve data from the database and can
be used with a combination of other SQL clauses. We can apply certain
conditions when retrieving the data.
The data that is
retrieved using the SELECT command is temporary and for display purposes.
• DCL stands for
Data Control Language: As the name suggests, It is used to GRANT and REVOKE the
controls over the Tables in the database. Only the persons/users who have been
granted the controls can access the data. We can allow different users with
different rights, permissions, and other controls of the database system.
Following are the
two main DCL commands in SQL
GRANT: The GRANT Command is used to give the privileges to the user to access the database.
REVOKE: The REVOKE Command is used to revoke/withdraw the access privileges given to the user by GRANT Command.
• TCL stands for
Transaction Control Language: We can save our transactions using TCL Commands
to the database, can create multiple savepoints to save particular portions of
transactions, and can roll back to a specific saved point
If any of the
tasks fails from a transaction, the transaction fails. Transaction is a set of
tasks in a single execution unit. Therefore, a transaction has two results
only: failure or success.
Following are the
three main TCL commands in SQL
COMMIT: The COMMIT Command is used to commit a Transaction.
ROLLBACK: The ROLLBACK Command is used to roll back a transaction or roll back a transaction to a specific saved point.
SAVEPOINT: The
SAVEPOINT is used to set savepoints within a transaction.