Foundation of SQL

What is SQL, but more particularly, how we can understand SQL as a non-procedural language? The beauty of SQL lies in this, we only have to specify what we have to do without getting into the complexity of how it is done.

By the end of this, I want you to understand how various people use SQL and how it may differ for those working in different domains?

SQL stands for, Structured Query Language it is one of the widely used standards for relational database management systems. In layman terms, SQL helps us to connect with databases and communicate with them.SQL is quite easy to understand because, the plain description of its statements, the interpretation of the commands is also easy.

Because of this, it is the preferred language to read, write and, manipulate data.

Database Administrators(DBA) use SQL to manage the database, other roles like data scientist and data analyst are just the users of the database, which means DBA authorizes their access and permissions for using data. This means that the DBA, database architect and other similar roles administer the database whereas the analysts and data scientists just use the data from the database as an end-user. The similarity between all of these roles is that they need to have an in-depth understanding of SQL for doing their work, it may be an easy query or a complex one but good knowledge of SQL is a must.

Inside SQL, we have different forms of languages,

1) Data Definition Language(DDL): All the statements which are used to define or, create the database structure are, included in this. Commands in DDL are,

CREATE: It is used, for creating a database, tables and columns of a table. While using this statement, we also specify a data type for each column it could be an integer, string or date.

DROP: We use this for destroying a table or a database.

ALTER: We use this to make changes in an already existing data structure, like adding columns, changing a data type etc.

RENAME: As the name suggests, it helps in renaming the tables.

TRUNCATE: We use this to remove spaces and records of a table. Rollback from truncate is not allowed in all databases.

2) Data Manipulation Language(DML): Commands which involve the manipulation of data in the database.

SELECT: We use this for extracting data from the database, we specify from which table we are taking our data and which columns are we extracting.

Note: SELECT is not exactly a DML statement or a DDL statement, there has been confusion about this for a long time but, I have taken it under the DML category because I feel that grouping, conversion and filtering is a kind of manipulation. Many sources have mentioned a new Language Data Query Language(DQL): The SELECT command, is exclusively put in this category. Concluding, there are a lot of various associations of the SELECT command, but you can keep it in any category as long as you are familiar with the working of it.

INSERT INTO: Insertion of values into a table, is done with the help of this command.

UPDATE: To change data values in tables.

DELETE: We use this to delete the values from the table.

Note: After the use of DELETE command, the table structure remains intact only the values, are deleted.

3) Data Control Language(DCL): Control commands which handle the permissions and controls of some systems in the database.

GRANT: Allows a user to access a database.

REVOKE: It withdraws the access rights given to the user using the Grant command.

4) Transaction Control Language(TCL): The commands which involve transactions inside a database.

COMMIT: To commit a transaction.

ROLLBACK: To rollback, the transaction in case any error occurs in the database.

SET TRANSACTION: To specify the type of transaction that would be followed after this command.

SAVEPOINT: To create a checkpoint in the transactions to which a user can ROLLBACK.

So far we have covered the different types of languages within SQL, the associated commands with those languages, and what are they used for in the database management process, this was a basic idea about, the foundation of SQL and its uses. For a more interactive, or query-driven look at SQL, you can refer to the first article in the Interactive Guide to SQL Series.