Monday, October 14News That Matters

Understanding SQL and How It Works

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:

  1. System – includes privilege to create session, table, etc.
  2. Object – includes privilege to any command or query to work on tables.

DCL Commands:

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

Syntax: COMMIT;

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

× one = two