Table of Contents
SQL (Structured Query Language) is used to perform operations on the records stored in the database such as updating records, deleting records, creating and modifying tables, views, etc.
History of the SQL
The SQL language was originally developed at the IBM research laboratory in San José, in connection with a project developing a prototype for a relational database management system called System R in the early 70s. The first database management systems based on SQL became available commercially by the end of the 70s.
What is SQL
- SQL stands for Structured Query Language.
- It is designed for managing data in a relational database management system (RDBMS).
- It is pronounced as S-Q-L or sometime See-Qwell.
- SQL is a database language, it is used for database creation, deletion, fetching rows, and modifying rows, etc.
- SQL is based on relational algebra and tuple relational calculus.
All DBMS like MySQL, Informix, PostgreSQL, Oracle, MS Access, Sybase, and SQL Server use SQL as standard database language.
Why SQL is required
SQL is required:
- To create new databases, tables and views
- To insert records in a database
- To update records in a database
- To delete records from a database
- To retrieve data from a database
SQL Commands
The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into the following groups based on their nature −
DDL Commands
- DDL stands for Data Definition Language
- DDL commands are used to change the structure of the table like creating a table, deleting a table, altering a table, etc.
- DDL commands are auto-committed, which means changes are permanent, rollback is not possible.
| Sr.No. | Command & Description |
|---|---|
| 1 | CREATE: Creates a new table, a view of a table, or other objects in the database. |
| 2 | ALTER: Modifies an existing database object, such as a table. |
| 3 | DROP: Deletes an entire table, a view of a table, or other objects in the database. |
DML Commands
- DDL stands for Data Manipulation Language
- DML commands are used to modify the database. It is responsible for all forms of changes in the database.
- DML commands are not auto-committed as DDL, they can be rollback.
| Sr.No. | Command & Description |
|---|---|
| 1 | SELECT: Retrieves certain records from one or more tables. |
| 2 | INSERT: Creates a record. |
| 3 | UPDATE: Modifies records. |
| 4 | DELETE: Deletes records. |
DCL Commands
- DCL stands for Data Control Language
- DCL commands are used to grant and take back authority from any database user.
| Sr.No. | Command & Description |
|---|---|
| 1 | GRANT: Gives a privilege to the user. |
| 2 | REVOKE: Takes back privileges granted from the user. |
TCL -Transaction Control Language
- TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.
- They are auto-committed to the database that’s why they cannot be used while creating tables or dropping them.
| Sr.No. | Command & Description |
|---|---|
| 1 | COMMIT: Commit command is used to save all the transactions to the database. |
| 2 | ROLLBACK: Rollback command is used to undo transactions that have not already been saved to the database. |
| 3 | SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire transaction. |
Once the commands are executed, the structure created is stored or saved automatically.
