Introduction to SQL Commands and Sub Languages

Introduction

SQL, or Structured Query Language, stands as the cornerstone of relational database management systems (RDBMS). It serves as the principal language for creating, accessing, and managing databases. Originating in 1970 at IBM, SQL gained recognition as a standard endorsed by the American National Standards Institute (ANSI) in 1986 and later by the ISO (International Organisation for Standardisation) in 1987. Originally christened SEQUEL, an abbreviation for Structured English Query Language, its name transitioned to SQL, retaining its phonetic pronunciation. Notably, SQL queries are fundamental to operations across various RDBMS software, including Oracle, MySQL, MS Access, and SQL Server, where SQL serves as the standard query language. In the subsequent discussion, we delve into the diverse sub-languages within the realm of SQL.

Learning Objectives:

• Understand where and why SQL Query 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.

Write SQL Query?

SELECT customer_name, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY total_amount DESC;

This SQL query selects the customer_name, order_date, and total_amount columns from the orders table. It filters the results to include only orders placed between January 1, 2023, and December 31, 2023, and orders them by total_amount in descending order.

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 Query 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 Sql 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 (30), Age INTEGER(3),  City(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( 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 the ALTER command, which allows us to modify the structure of tables and perform various operations such as adding, deleting, or modifying columns, as well as executing SQL statements to make necessary adjustments.

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”. This scenario highlights the importance of updating SQL statements or SQL queries when altering table names. In this example, ‘S’ denotes the name of the database. However, when we use the new table name, i.e., Student_details, the SQL statement retrieves and displays all details of the table without any issues.

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 utilize the TRUNCATE SQL statement, which effectively removes all rows from the Student_details table within the relational databases. Upon attempting to retrieve the table details, the system returns a message indicating an ‘EMPTY set’. Subsequently, we employ the DROP SQL Query to completely eliminate the Student_details table from the database. Consequently, any attempts to retrieve the table details thereafter result in a message indicating that ‘Student_details’ no longer exists within the database.

DML: Data Manipulation Language

DML commands, such as INSERT, UPDATE, DELETE, and SELECT, are instrumental in managing and modifying data within a relational database. These commands allow users to interact with the database by inserting new records, updating existing ones, deleting unwanted data, and retrieving information based on specific criteria. SQL statements, which encompass SQL queries, play a crucial role in executing these DML commands effectively. However, it’s essential to note that DML commands do not automatically commit changes to the database, requiring explicit transaction management to ensure data integrity and consistency.

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 ;

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

What is Data Definition Language

Data Definition Language (DDL) encompasses a suite of SQL commands tailored for defining, modifying, and deleting the structure or schema of various database objects, including tables, indexes, and views. With DDL commands, users can seamlessly create new databases, design or adjust existing structures, and discard unnecessary elements, effectively sculpting the organization and management of SQL databases. This pivotal role of DDL not only empowers users to establish the foundation of their database environment but also facilitates the retrieval of data, ensuring smooth navigation and utilization of the SQL database resources.

Database Management System

A Database Management System (DBMS) is essential software for efficiently managing databases, particularly in the realm of database systems and relational databases. It enables users to define data structures, manipulate data using commands like INSERT and UPDATE, and ensure data integrity and security. DBMS supports concurrent access, backups, and recovery, optimizing queries for improved performance, including sql Command. With features like scalability and query optimization, DBMS is crucial for various sectors, including business, healthcare, and finance, facilitating critical operations and decision-making processes.

SQL Commands

Here are some common SQL commands:

CREATE TABLE: This command is utilized to create a new table in the database, where data is stored.

INSERT INTO: Used for adding new records into a table within the database.

SELECT: Employed to retrieve data from one or more tables stored in the database.

UPDATE: Used for modifying existing records within a table stored in the database.

DELETE: Employed to remove records from a table located within the database.

ALTER TABLE: Utilized to modify the structure of an existing table where data is stored.

DROP TABLE: This command is used to delete an entire table from the database, including all its stored data.

CREATE INDEX: Utilized to create an index on a table to enhance query performance for database users.

GRANT: Used to provide privileges to database users, granting them access to certain functionalities and data.

REVOKE: This command is used to retract privileges from database users, limiting their access to certain functionalities and data.

Structured query language

Structured Query Language (SQL), often referred to as SQL, serves as a fundamental tool for managing databases. It plays a pivotal role in enabling users to interact seamlessly with databases, facilitating the storage, retrieval, and modification of information. Essentially, SQL acts as a bridge between humans and databases, enabling effective communication and manipulation of data. This versatile language is employed across various database systems to efficiently structure and store data, making it a cornerstone for anyone looking to master SQL.

Conclusion

In our exploration of SQL, we’ve delved into fundamental concepts like sub-languages within SQL and the corresponding commands within each. To recap, DDL commands are pivotal for altering and refining the structure of tables, whereas DML commands are essential for manipulating data within a database. TCL commands primarily manage transactions within a database and are closely intertwined with DML commands. DCL commands, on the other hand, govern rights, permissions, and other administrative controls over the database. Lastly, DQL enables the retrieval of data from the database. This knowledge forms the bedrock for querying structured data, ensuring that the required data is obtained from the database through the same query. As you continue to learn SQL, mastering these concepts will empower you to effectively manage and manipulate data with precision and efficiency.

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.

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