SQL stands for Structured Query Language. According to the American National Standards Institute (ANSI), it is the standard language for accessing and manipulating databases.
But there is more to know than how it is defined. To help you gain further understanding, INOC, a global leader in the NOC services industry, is providing a comprehensive explanation about SQL and how it works.
What is SQL?
SQL became the standard programming language for relational databases since its creation in the late 1970s. Whenever you interact with a database, SQL translates your commands into an SQL statement. These statements are used to modify database table and index structures, and update and retrieve data from a database. Although most database systems use SQL, they still have different versions of the software. However, to be compliant with the ANSI standard, they all support the standard SQL statements including Select, Insert, Update, Delete, Retrieve and Create.
This software has four main components including Data Manipulation Language (DML), Data Definition Language (DDL), Data Control Language (DCL) and Transaction Control Language (TCL).
4 Main Components of Data Manipulation Language
1. Data Manipulation Language
DML is a set of computer languages used to work with data in SQL server. It contains three most common DML commands which are the following:
Insert – to add new information
Syntax: INSERT INTO <table> [<columns,..>] VALUES (value […]);
Update – to modify information
Syntax: UPDATE <table name> SET columname = value [, column name = value …] WHERE <condition>;
Delete – to remove information
Syntax: DELETE FROM <table name> WHERE <condition>;
2. Data Definition Language
DDL is the set of SQL commands used to create and manipulate structures in a database including views, schemas, tables and indexes. DDL is also referred to as data description language as it describes the fields and records in a database table. Among the most common DDL statements are:
Create – to generate new table
Syntax: CREATE <table name>
Alter – to modify existing database
Syntax: ALTER TABLE <table_name> ADD (<NewColumnName> <Data_Type>(<size>),……n)
Drop – to delete table
Syntax: DROP TABLE <table_name>
3. Data Control Language
DCL is a component of the SQL language used to control user privilege to database objects. This privilege enables a user to perform any operation in the database such as creating tables, sequences or views.
There are two types of privileges:
- System – includes privilege to create session, table, etc.
- Object – includes privilege to any command or query to work on tables.
Grant – to grant user privilege
Syntax: Syntax: GRANT <object privileges> ON <object_name> TO <User_Name> [WITH GRANT OPTION]
Revoke – to remove user privilege
Syntax: REVOKE <Object_Privileges> ON <Object_Name> FROM <User_Name>
Note: Any combination of these commands are allowed.
4. Transaction Control Language
The TCL command is used to manage transactions in database and manipulate the changes made by DML statements. It allows that statements to be grouped together into logical transactions. There are at least three TCL commands which are as follows:
Commit – to permanently save transaction into database
Rollback – to restore database to previous state
Syntax: ROLLBACK to SAVEPOINT-NAME;
Savepoint – to temporarily save a transaction
Syntax: SAVEPOINT to SAVEPOINT-NAME;
SQL commands are designed to have a syntax similar to the English language. These commands normally begin with a statement describing the action to take, followed by a clause that describes the target of the command. A series of clauses can also be added to provide additional instructions.
Did you find this article informative? Share your thoughts in the comment section below.