Introduction
SQL stands for "Structured Query Language." It is a standard computer language used to manipulate, store, and retrieve data from relational databases(RDBMS).
Structured Query Language is the standard database language used by all Relational Database Management Systems (RDMS), including MySQL, MS Access, Oracle, Sybase, Informix, Postgres, and SQL Server. SQL performs the required tasks using commands such as Create, Drop, Insert, etc.
These SQL commands are divided into four categories:
- Data Definition Language (DDL)
- Data Query Language (DQL)
- Data Manipulation Language (DML)
- Data Control Language (DCL)
- Transaction Control Language (TCL)
Let's learn these commands one by one in brief.
Note:- RDBMS stands for Relational DataBase Management Systems. It's a program that allows us to build, delete, and update relational databases. A relational database is a database system that retrieves and stores data in the form of rows and columns in a tabular format. It is a subset of the DBMS designed by E.F. Codd in the 1970s.
Data Definition Language (DDL)
Data Definition Language or DDL commands in SQL are used to build and alter the database schema and its objects. In simple words, Data Definition Language(DDL) is a set of Structured Query Language commands used to create, update, and delete database structures but not data.
The DDL commands deal with how data should be stored in the database. Generally, a general user shouldn't use these commands; instead, they should use an application to access the database.
In SQL, there are five primarily used DDL commands. These commands are listed below.
| Command | Description |
|---|---|
| CREATE | It is a DDL command that is used to create databases, tables, triggers, and other database objects. For Example:- To create a database, we use the following command. CREATE DATABASE database_name; |
| DROP | It is a DDL command that is used to destroy or remove database objects from a SQL database. This DDL command lets us quickly delete the entire table, view, or index from the database. For Example:- To delete a table from the SQL database, we use the following command. DROP TABLE table_name; |
| ALTER | It is a DDL command this is used for Modifying and renaming aspects of an existing database table. This command can also be used to add and drop constraints from the table. For Example:- We use the following command to add a new field to the existing table. ALTER TABLE table_name ADD column_name column_definition; We use the following command to modify the column of the table: ALTER TABLE table_name MODIFY ( column_name column_datatype(size)); |
| TRUNCATE | TRUNCATE is another DDL command that deletes or eliminates all records from a table. This command also deletes the space set aside for storing table records. The TRUNCATE command does not have a WHERE clause, similar to the DROP command. TRUNCATE is faster than both DROP and DELETE. After using this command, we cannot roll back the data, just like with the DROP command. For Example:- We use the following command to delete the table. TRUNCATE TABLE table_name; |
| RENAME | The DDL command RENAME is used to modify the name of a database table. For Example:- We use the following command to rename the table. RENAME TABLE old_table_name TO new_table_name; |
Data Query Language (DQL)
Data Query Language or DQL command retrieves data from a database. The objective of the DQL Command is to obtain a schema relation based on the query given to it. It contains the SELECT command.
With the help of this command, you can retrieve data from the database and manipulate it. When a SELECT command is executed on a table or tables, the result is compiled into a new temporary table, which is then displayed or received by the program, i.e., a front-end.
Data Query Language contains only one command.
| Command | Description |
|---|---|
| SELECT | In SQL, the SELECT command or statement is used to retrieve data records from a database table and present them in the form of a result set. It is typically seen as a DQL command, although it can also be regarded as DML. The basic syntax of the SELECT command is: SELECT column_name1,… FROM table_name WHERE condition_ expression; |
Data Manipulation Language (DML)
The Data Manipulation Language or DML commands in Structured Query Language modify the data in the SQL database. We can simply access, store, alter, update, and delete existing records in the database using DML commands.
The three primarily used Data Manipulation Language (DML) commands in SQL are as follows.
| Command | Description |
|---|---|
| INSERT | It is an important data manipulation (DML) command in Structured Query Language that allows users to insert data into database tables. Syntax: INSERT INTO table_name ( column-1,column-2, …. column-N) VALUES (value-1, value-2, value-3, .... value-N); |
| DELETE | SQL users can delete one or more existing records from the database tables using the DELETE DML command. This Data Manipulation Language command does not delete the stored data permanently from the database. We combine the DELETE command with the WHERE clause to choose particular rows from the table. Syntax: DELETE FROM table_Name WHERE condition; |
| UPDATE | This DML command is used to change the value of a column in a Structured Query Language Database table. Syntax: UPDATE table_name SET [column_name_1= value_1, column_name_2 = value_2 ...column_nameN = valueN] WHERE [CONDITION]; |
Data Control Language (DCL)
Data Control Language or DCL is associated with the commands used in Structured Query Language that permit users to access, alter or work on the different privileges to control the database.
This command also allows the database owner to give access, revoke access, and change the given permissions as and when required. DCL is basically used for enforcing data security.
There are two DCL commands present in SQL:
| Command | Description |
|---|---|
| Grant | GRANT is a DCL command that grants (give access to) security privileges to specific database users. It is mostly used to restrict user access to INSERT, DELETE, UPDATE, SELECT, EXECUTE, ALTER, or to provide user data privileges. The basic syntax of the GRANT command is: GRANT ON TO |
| Revoke | REVOKE is a DCL command used to revoke given access granted via the GRANT command. It is mainly used to revert back to the time when no access was specified, i.e., withdrawing the permission that was authorized to carry out specific tasks. The basic syntax of the REVOKE command is: REVOKE ON FROM |
Transaction Control Language (TCL)
Transaction control language (TCL) commands are used to handle the transaction within the database. Transactions combine a collection of tasks into a single execution unit.
Each transaction starts with some specific task and finishes when all of the tasks in the group are completed successfully.
The transaction fails if any of the tasks fails. As a result, a transaction has only two outcomes, i.e., either success or failure.
We use the following TCL commands to control the execution of a transaction:
| Command | Description |
|---|---|
| COMMIT | It is a TCL command that is used to save the data permanently. Any DML command, such as -INSERT, DELETE, or UPDATE, can be rolled back if the data is not permanently stored. To be on the safe side, we use the COMMIT command. Basic Syntax: commit; |
| ROLLBACK | The ROLLBACK command retrieves or restores data to the most recent savepoint or committed state. Suppose the data inserted, removed, or altered is incorrect for any reason. In that case, you can roll back the data to a specific savepoint, or if no savepoint is available, then the rollback is done from the last committed state. Basic Syntax: rollback |
| SAVEPOINT | This command temporarily saves data at a specific point so it can be rolled back to that point if required. Basic Syntax: savepoint A; |
| SET TRANSACTION | This command specifies the transaction's characteristics. Basic Syntax: SET TRANSACTION [Read Write / Read Only]; |
Conclusion
- SQL stands for "Structured Query Language." It is a standard computer language used to manipulate, store, and retrieve data from relational databases(RDBMS).
- The SQL commands are divided into four categories:
- Data Definition Language (DDL)
- Data Query Language (DQL)
- Data Manipulation Language (DML)
- Data Control Language (DCL)
- Transaction Control Language (TCL)
- Data Definition Language(DDL) is a set of Structured Query Language commands used to create, update, and delete database structures but not data. Some of the common DDL commands are: CREATE, ALTER, DROP and TRUNCATE.
- Data Query Language or DQL command retrieves data from a database. It only includes the SELECT command.
- The Data Manipulation Language or DML commands in Structured Query Language modify the data in the SQL database. Some of the common DDL commands are: INSERT, UPDATE, and DELETE.
- Data Control Language or DCL is associated with the commands used in Structured Query Language that permit users to access, alter or work on the different privileges to control the database. The two common DCL commands are GRANT and REVOKE.
- Transaction control language (TCL) commands are used to handle the transaction within the database. Some of the common DDL commands are COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION.
Top comments (0)