SQL Statement Or SQL Commands: As we know, as a developer, Data analysts, or QA, we are always dealing with databases. And If you want to interact with databases, you need to use a language that the database can understand. For this purpose, some SQL commands or statements are defined like Select, Create, Delete and Commit.
In simple words, we can say SQL statements are designed to be simple and easy to understand, with a syntax similar to plain English. This makes them easy to learn and use for people with little or no programming experience. The basic structure of an SQL statement typically includes a keyword (such as SELECT, INSERT, UPDATE, or DELETE), followed by one or more clauses (such as WHERE, JOIN, or GROUP BY) that specify the conditions and details of the operation and finally ended with a Semicolon.
SQL Commands Types
There are 5 Different categories of SQL commands / SQL statements are there based on their characteristics or behavior, and they are below:
- DDL – Data Definition Language
- DQL – Data Query Language
- DML – Data Manipulation Language
- DCL – Data Control Language
- TCL – Transaction Control Language
Data Definition Language (DDL Commands)
Data Definition Language (DDL) is a subset of SQL (Structured Query Language), which is used to define and manage the structure of a database. Data Definition Language (DDL) includes commands for creating, altering, and deleting database objects such as tables, indexes, and views.
Database administrators and developers typically use DDL SQL statements to create and maintain the structure of a database but not to insert, update or delete data. Examples of DDL commands include CREATE, ALTER, DROP and TRUNCATE.
There are few SQL commands, or Statements are there which are coming under the DDL commands, and the list of DDL statements are:
- CREATE: This command is used when creating a database or its objects like tables, views, indexes, store procedures, functions, and triggers.
- DROP: While deleting an object from the database.
- ALTER: Used while altering the structure of the database.
- TRUNCATE: Used when we are removing all the records of a table.
- COMMENT: Used while adding comments to the data dictionary.
- RENAME: This command is used while renaming an object existing in the database.
Data Query Language (DQL Commands)
Like Data Definition Language (DDL Statements), Data Query Language (DQL) is also a subset of SQL. This DQL statement is used to retrieve data from a database. The primary command in DQL is the SELECT statement, which is used to retrieve data from one or more tables in a database.
By executing the DQL command, we can retrieve data from the database and perform other operations like filtering, sorting, and grouping the data. While executing the DQL statements, we are not making any changes to the database schema or the data stored within it. The result of the DQL command is stored in a temporary table and displayed in the front-end application.
We have one DQL command, which is:
SELECT: By using this command, we can retrieve the data from the database.
Data Manipulation Language (DML Commands)
As the name suggests, we can manipulate the data inside a database using the DML statements. The DML commands include most statements like INSERT, UPDATE, DELETE, etc. The list of the commands of DML statements are:
- INSERT: This command is used to insert data into a table.
- UPDATE: This command we are using to update an existing data of a table.
- DELETE: By using this command, we can delete data from a table.
Data Control Language (DCL Command)
Data Control Language (DCL Command) is a query language used in database management systems to control access to the data stored in the database. It includes commands such as GRANT and REVOKE, which are used to manage user permissions and access to the data.
Here are the DCL Commands:
- GRANT: By using this command, we can give user access privileges to the database. So that we can allow specified users to perform specific tasks.
- REVOKE: This command removes the user’s access privileges approved by the GRANT command.
Transaction Control Language (TCL Command)
Transaction Control Language (TCL) is a set of commands used to manage and control the transactions that take place in a database. Transactions are groups of tasks, like inserting, updating, or deleting data, executed together as a single unit of work. TCL commands are used to handle these tasks and ensure that all the tasks are executed correctly and that the database remains consistent.
If any tasks fail, the TCL commands can undo any changes made and return the database to its original state. In simple words, TCL is like a traffic cop that ensures that all transaction tasks are done correctly and in the right order.
Here are the following TCL commands are:
- COMMIT: This command is used to save the transactions.
- ROLLBACK: This command is used to undo changes or roll back a transaction in case of any error occurs.
- SAVEPOINT: This command is used to roll back a transaction to a specific point rather than the complete transaction.
This blog post discusses all five categories of SQL Commands or SQL Statements, including DCL, DML, DDL, TCL, and DQL subtypes. Each command is explained in detail, like their use and requirements in Data Base Management System.
The SQL commands allow you to construct and manipulate various database objects and also help you to write SQL queries. After reading this blog, you can easily determine which command you should use to interact with your database.