SQL (Structured Query Language) is a programming language used to manage and manipulate data stored in relational databases. It is a standard language for interacting with relational databases and is used to create, read, update, and delete records in the database. It was the first commercial language introduced for E.F Codd’s Relational model. Today almost all RDBMS(MySql, Oracle, Informix, Sybase, MS Access) use SQL as the standard database language. SQL is used to perform all types of data operations in RDBMS.
SQL is a declarative language, which means that it specifies what the result should be, rather than how to get there. This makes it easy for users to understand and write SQL statements, even if they do not have a lot of programming experience. It is used in a wide range of applications, including web development, data analysis, and business management. It is a powerful and flexible language that is essential for working with relational databases.
There’re various types of SQL. Each type of SQL serves a specific purpose and is used in different situations. For example, DDL is used to define the structure of the database, while DQL is used to retrieve data from the database. There are several types of SQL, including:
1. DDL: Data Definition Language
DDL (Data Definition Language) is a type of SQL (Structured Query Language) that is used to define the database structure or schema. It is used to create, modify, and delete database objects such as tables, indices, and users. All DDL commands are auto committed which means DDL Commands save all the changes permanently in the database and rollback is difficult. Some examples of DDL statements include:
|CREATE TABLE||Used to create a new table in the database or database itself|
|ALTER TABLE||Used to modify an existing table in the database|
|TRUNCATE TABLE||Used to clear all the values from the table|
|DROP TABLE||Used to delete a table from the database or the database itself|
|RENAME||Used to rename a table|
2. DML: Data Manipulation Language
DML (Data Manipulation Language) is a type of SQL that is used to manage data within the database. It is used to insert, update, delete, and select data from the database. Unlike DDL, DML commands are not auto-committed. It means changes as a result of executing DML queries are not permanent to the database, they can be rolled back instantly. Following are some DML commands with their meanings:
|INSERT INTO||Used to insert new data into the database|
|UPDATE||used to modify existing data in the database|
|DELETE FROM||Used to delete data from the database|
|MERGE||Used to merge two rows or two relations|
3. TCL: Transaction Control Language
TCL (Transaction Control Language) is a type of SQL that is used to manage the transactions in the database. It is used to manage the changes made by DML statements. These commands are to keep a check on other DML commands and their effect on the database and to roll back to the original state. It can also make changes permanent in the database system.
|COMMIT||Used to save the changes made by a transaction to the database i.e. to permanently save|
|ROLLBACK||Used to undo the changes made by a transaction|
|SAVEPOINT||Used to identify a point in a transaction to which you can later roll back i.e. to save temporarily|
4. DCL: Data Control Language
DCL (Data Control Language) is a type of SQL that is used to control access to the database. It is used to grant and revoke permissions on the database objects to users. Data control language provides commands to grant and take the authority(access control rights) back. Some examples of DCL statements include:
|GRANT||Used to give a user permission to perform a specific task i.e. to grant permission for rights|
|REVOKE||used to take back permission that was previously granted to a user|
5. DQL: Data Query Language
DQL (Data Query Language) is a type of SQL that is used to retrieve data from the database. It is used to select, retrieve, and view data from the database. The SELECT statement is the most commonly used DQL statement.
|SELECT||retrieve records from one or more table|