An Introduction to SQL Sub-Languages and Commands for Beginners

Introduction

SQL stands for Structure Query Language. It is the most popular RDBMS (Relational Data Base Management System) language for creating, accessing, and managing databases. Developed in 1970 at IBM, SQL became a standard of the American National Standards Institute (ANSI) in 1986 and the ISO (International Organisation for Standardisation) in 1987. Initially called SEQUEL, which stands for Structured English Query Language, the name was later changed to SQL, although the pronunciation remained the same. Many RDBMS software like Oracle, MySQL, MS Access, and SQL Server use SQL as their standard query language. In this article, we will learn about the different sub-languages in SQL.

Learning Objectives:

• Understand where and why SQL is used.
• Learn about the different sub-languages in SQL, such as DDL, DML, DQL, DCL, and TCL.
• Learn to use the various SQL commands under these sub-languages.

This article was published as a part of the Data Science Blogathon.

Why SQL?

It is easier to view, add, delete, and change data in a database by using simple queries. Apart from this, there are several other reasons why we use queries. Such as, It is helpful to find specific data by using filtering conditions in a short time. It is also helpful to summarise data by reviewing the current data. All the query operations are done using a standard query language, SQL. SQL provides sub-languages, including several commands, to maintain and manage data in a database. These help in handling query operations in a database.

SQL is a case-insensitive language, so we can use both upper and lower case letters when writing code.

Sub Languages of SQL

SQL consists of 5 types of sub-languages: DDL, DML, DQL/DRL, DCL, and TCL. Let’s have a detailed look at each of them.

DDL: Data Defining Language

DDL commands are used to change and modify the structure of the table. By default, DDL commands are auto-committed, meaning when one changes and modifies a table, the values get automatically saved in the database.

Here are the commands of DDL – CREATE, ALTER, DROP, TRUNCATE, RENAME

CREATE: This command creates a new table and index in a database.

Syntax: CREATE table table_name (col_1 datatype size , col_ 2 datatype size ,...........,col_n datatype  size);
Example: CREATE table Student (Roll_number INTEGER(5), Name VARCHAR(30), Age INTEGER(3),  City VARCHAR(30));

ALTER: This command changes the structure of a table in a database. We can add a new column in a table and change the data type and size of a column.

Syntax: ALTER table table_name ADD column_name COLUMN definition;
               ALTER table table_name MODIFY(column_definition);
Example: ALTER TABLE Student ADD ( Ph_number INTEGER(12));

[In the above example, we added a new column in the existing table student.]

ALTER TABLE Student MODIFY City VARCHAR(50);

[In the above example, we changed the size of the City from 30 to 50.]

RENAME: This command changes the name of an existing table in a database. For this purpose, we use ALTER command.

Syntax: ALTER TABLE table_name RENAME TO new_table_name;
Example: ALTER TABLE Student RENAME TO Student_details;

In the above example, we changed the name of the table Student to Student_details. After that, when we use the old table name, it displays the message “Table Student doesn’t exist in databases “. In this example, S denotes the name of the database. And when we use the new table name, i.e., Student_details, it displays all details of the table.

DROP: The DROP command permanently removes a table from a database.

Syntax: DROP table table_name;
Example: DROP TABLE Student;

TRUNCATE: This command helps remove all rows from a table while retaining the table structure.

Syntax: TRUNCATE table table_name;
Example: TRUNCATE TABLE Student;

In the above example, first, we use the TRUNCATE command, which deletes all rows from the Student_details table. When we try to retrieve the table details, it shows a message ‘EMPTY set’. After that, we use the DROP command, which completely deletes the table Student_details from the database. So when we try to retrieve the table details again, it shows the message ‘Student_details’ doesn’t exist in the database.

DML: Data Manipulation Language

DML commands are those commands that help manage and modify the database. These commands are not auto-committed, meaning they can’t permanently save the data in a database.

Here are the DML commands- INSERT, UPDATE, DELETE

INSERT: This statement adds table row values.

Syntax: INSERT INTO table_name(col 1, col 2, col 3,......,col n) VALUES(value 1, value 2, value 3.........., value n);  OR
INSERT INTO table_name VALUES(value 1, value 2, value 3.........., value n);
EXAMPLE: INSERT INTO Student VALUES (01, 'Rohini',20,' Delhi');

UPDATE: This statement updates the values of a column in a table. To use this, the WHERE condition is necessary.

Syntax: UPDATE table_name SET [column 1= value 1 , column n=value n] WHERE condition;
Example: UPDATE Student SET Name='Mahima' WHERE Roll_number=03;

DELETE: This command removes a particular row from a table. Note that the WHERE condition is necessary to perform the delete operation.

Syntax: DELETE FROM table_name WHERE condition;
Example: DELETE FROM Student WHERE Roll_number =2;

DQL: Data Query Language

DQL is used to fetch data from the database. It only uses the SELECT command.

SELECT: This command helps retrieve table data from the database based on the condition described by the WHERE condition.

Syntax: SELECT * FROM table_name WHERE condition;

             SELECT * FROM table_name;

Example: SELECT * FROM Student WHERE Name= “Rohini”;
SELECT * FROM Student;

DCL: Data Control Language

DCL commands mainly deal with rights, permissions, and other controls of the database. It uses only 2 commands, i.e., GRANT and revokes.

GRANT: This command GRANTs access privileges to a user in the database.

Syntax: GRANT privileges_names ON object_name TO user ;
Example: GRANT ALL ON Salary_detail TO 'Ashish' '@' localhost ;

REVOKE: This command removes access privileges from the user in a database.

Syntax: REVOKE privileges_names ON object_name FROM user_name;
Example: REVOKE All ON Salary_detail FROM Ashish ;

[By using this command, user Ashish’s permissions on the Salary_detail table have been removed. ]

TCL: Transaction Control Language

TCL commands mainly deal with the transactions in a database. They only work along with DML commands like INSERT, UPDATE, and DELETE, as they cannot automatically commit to a database.

TCL commands are COMMIT, ROLLBACK, and SAVEPOINT.

COMMIT: This command saves database transactions permanently.

Syntax: COMMIT;

Example: INSERT INTO Student_details VALUES(02,’Rajat’,21,’Delhi’,9874008);

         COMMIT;

ROLLBACK: This command removes the recently updated transaction from the database.

Syntax: ROLLBACK;
Example: DELETE FROM Student_details WHERE Roll_number=4;
                ROLLBACK;

In the above example, before using the ROLLBACK command, we must disable auto-commit by using the command ‘SET AUTOCOMMIT=0’. The ROLLBACK command doesn’t affect a transaction that has already been committed.

SAVEPOINT: This command creates a point within the group of transactions. So it helps to roll back the transaction at a certain point without rollback the entire transaction.

Syntax: SAVEPOINT savepoint_name;
              ROLLBACK TO savepoint_name;

Example: SAVEPOINT SP1;
INSERT INTO Student_details VALUES(05,'Suraj',21,'Goa',9974458); ;
    //deleted
    SAVEPOINT SP2;
    //Savepoint created.
    //Rollback completed.

    //Savepoint created.

    ROLLBACK TO SP1;

** Before using SAVEPOINT and ROLLBACK in a database, you must remember to disable auto-commit. Because by default, auto-commit is enabled in the database**

Conclusion

We have now covered some of the basic concepts of SQL, including sub-languages in SQL and the commands under each of them. To summarize, DDL commands help change and modify the table’s structure, while DML commands modify the content in a database. TCL commands mainly deal with the transactions in a database and only work along with DML commands. DCL commands deal with rights, permissions, and other controls of the database, while DQL helps to fetch data from the database.

Key Takeaways:

• Structure Query Language (SQL) is the most popular RDBMS (Relational Data Base Management System) language.
• The 5 sub languages of SQL are DDL, DML, DQL/DRL, DCL, and TCL.
• Each sub-language has specific commands that help carry out various functions related to creating and editing databases.

Frequently Asked Questions

The media shown in this article is not owned by Analytics Vidhya and is used at the author’s discretion.