SQL commands are used to instruct databases to perform tasks. There are used commonly 16 SQL commands in Oracle Database. These 16 commands are grouped in several categories. Those are described below:
- DML: The Data Manipulation Language, shortly DML, is used to process data in database. The DML commands are :
- INSERT: The INSERT command is used to add or insert single/multiple data into a database table.
- UPDATE: The UPDATE command is used to update or change a data in a row of database table.
- DELETE: The DELETE command is used to delete data from database table. Without condition, all rows will be deleted from the table. To delete partially or a specific row we have to use condition with DELETE command.
- SELECT: The SELECT command is used to retrieve data from a table. Without condition, it will retrieve all data. To retrieve a specific or partial data we have to use condition with SELECT command. The SELECT command is also known as QUERY statement to programmers and database administrators.
- MERGE: You can use MERGE command to select rows from one or more than one data sources to insert and update data into the table or view. You can also use conditions with MERGE command. Depending on condition with MERGE statement you can execute one of commands either Insert or Delete or Update.
- DDL: The Data Definition Language, shortly DDL, is used to work with the structure of database objects. For example, to create and change the structure of TABLE, we can use DDL commands. The DDL command are:
- CREATE: The CREATE command is used to create the structure of database objects. For example, to create a new TABLE in database, we have to use CREATE command.
- ALTER: The ALTER command is used to change or modify the structure of database objects. For example, to add a new column or a new constraint in existing database TABLE (already created by CREATE command), to modify or change column data type, length of a table, we have to use ALTER command.
- DROP: We use DROP command to remove a database object structure. For example, DROP command can be used to remove a TABLE from the database. When we drop a database TABLE, all data, constraints, triggers, indexes and permissions of that TABLE will also be removed. So, we have to highly conscious about data before the TABLE is dropped.
- TRUNCATE: The TRUNCATE command is used to remove all rows from a database table. The truncated table cannot be rolled back. The TRUNCATE command works much faster than DELETE command. Another crucial advantage of TRUNCATE command is that it will deallocate or recreate or rearrange all spaces occupied or used by removed rows of a table.
- RENAME: The RENAME command can be used to rename an existing table.
- COMMENT: We can use COMMENT statement to add a comment for Table, View, and Table Column. This comment will be stored into the data dictionary.
- DCL: The Data Control Language, shortly DCL, is used to control user data. Using DCL command we can provide and deny permissions to database users. For example, If we want to grant permission to a specific user so that that user can only query data from a table and deny so that that user can not insert, update and delete data in that TABLE, then we have to use DCL commands. The DCL commands are:
- GRANT: The GRANT command is used to provide permissions or privileges to a user or role. The permissions may be System privileges, Object privileges on a particular object. For example, you have to use GRANT command, if you want to give permission on a table to a user so that the user can only retrieve data from that table.
- REVOKE: The REVOKE command is used to remove permissions or privileges from a user or role. For example, you have to use REVOKE command, if you want to remove permission (already granted on a table) from a user so that the user can not delete data from that table.
- TCL: The Transaction Control Language, shortly TCL, is used to control transaction process of database. Generally transaction is a logical part of an entire task. The steps of this task can be written in a block by using a group of DML statements of SQL. Every step of this task or every part of these DML statements is known as Transaction.
For example, if we think with store management software, Here we first store products and then we can sell. Suppose we have stored 100 pieces shirt, now we need to sell 25 pieces shirt from 100 pieces shirt. For this store calculation, we have to first increase (+) sales by 25 pieces shirt and then minus (-) from store by 25 pieces shirt those already sold. Finally we can get current store.
Store : 100 pieces shirt
Sales : 0 pieces shirt
Total Sales after selling 25 pieces shirt= (0+25) =25 pieces shirt. —>This is a transaction
Current stock in store= (100-25) =75 pieces shirt. —>This is another transaction.
And these two transactions collectively will be known as Task.
We can see from this calculation, suppose first transaction (total sales calculation) has been successfully completed but second transaction (current stock calculation) has been failure for a reason (reasons may be system fault, electricity problem, invalid data and others). In result, calculation will not be correct. So, both transactions should be successful for that correct result of the calculation and otherwise both transactions should be cancelled. In this circumstance, to control these transactions we have to use TCL commands.
Three TCL command are available in SQL. They are:
- COMMIT: is used to permanently save data which are changes in database when a transaction (in case of one transaction) and all transactions (in case of multiple transactions) have been successful.
- ROLLBACK: is used to undo all uncommitted data that have been changes in database and restore data in last committed stage.
- SAVEPOINT: this command is used to break and save the entire task into multiple smaller units. Then, we can rollback data to a specific unit. It supports partial rollback.
Suppose, an entire task has been broken in four transaction units, Transaction-1, Transaction-2, Transaction-3 and Transaction-4.If we want to rollback up to Transaction-2 from Transaction4, and then we have to save every transaction by SAVEPOINT command to perform partial rollback. Otherwise all transactions (Transaction-1, Transaction-2, Transaction-3 and Transaction-4) will be rollback.