Introduction of SQL. Sub language of SQL.

As we seen in previous post, For communication to the oracle server there is a client is required. As we know that computer only understand binary number. For that we use programming language like c, c++, java etc. Same as here, after connecting to oracle server user communicate with oracle server by using a language called SQL. And as we know that every language have pr-define structures. In Oracle user communicate with server by sending commands/instructions called queries. In other words, a query is a commands or instructions submitted to oracle server to perform some operations over database.

SQL is introduced by IBM and initial name of this language was "SEQUEL" later it is renamed as "SQL". Now one question is arise that, if SQL is the product of IBM then how can it used by oracle. Then answer is here: SQL was introduced by IBM, but now it is in the control of IEEE. And if is open source now. That's why oracle or some other company using it. SQL is common to all RDBMS. Now we are going to see the sub language of SQL and some of those commands. Here we only know about commands. More details and example of that command we will see in upcoming post.

Based on operations over database SQL is categorized into following categories.

• DDL (Data Definition Language)
• DML (Data Manipulation Language)
• DQL (Data Query language)
• TCL (Transaction Control Language)
• DCL (Data Control Language)

DDL (Data Definition Language)

Set of commands to perform operations over data definitions. We already learn the different between data and data definitions. So, I think there is no confusion between data and data definitions.

DDL commands

• CREATE: - To create table definition. Using this command we can create a new table definition.
• ALTER: - To modify table definition. If there is a need to change field name or change datatype of field then we will use this command.
• DROP: - To drop table definition. It will delete all data as well as table.
• TRUNCATE: - Delete all data from table. But table definition is not deleted. If a table is truncated it look like a new created table. Or we can say that truncated table is a empty table.
• RENAME: - To change name of the table.

DML (Data Manipulation Language)

Set of commands to perform operation over data. This sub language have some commands using that we can perform operation over data of table.

DML commands

• INSERT: - To insert new row in table.
• UPDATE: - To modify data of table.
• DELETE: - To delete row(s) from table.

from oracle 9i there is two more commands is introduced. That is:

• INSERT ALL: - To insert data into multiple table.
• MARGE: - Combination of insert & update.

DQL (Data Query Language)

There is only one command in this sub language.

• SELECT: - Used to retrieve data from table(s).

TCL (Transaction Control Language)

Set of commands to control transactions. We can say this in other words like: Set of commands to control DML commands.

TCL commands

• COMMIT: - This command is used to save the transactions. Saving transaction means what we done like INSERT, UPDATE, DELETE will be saved.
• ROLLBACK: - Cancels the transactions. In other words we can say that what we work ( INSERT, UPDATE, DELETE) after execution of COMMIT or after starting of oracle client is canceled. 
• SAVEPOINT: - To cancel part of the transaction. It means we can create a save point any time between working on oracle client. And if any time we need to came back at that point or we can say that if we wants our database like that time then we can call savepoint with command ROLLBACK.

DCL (Data Control Language)

Set of commands to control data between oracle and user. In other word this commands are use for giving permission to user and take back permission from user.

DCL commands

• GRANT: - To give permission to user.
• REVOKE: - To take back permission from user.