Sql language and their sub languages

Hi friends, here we come with some new topics that is based on Sql language.

Oracle is s software and their functionality is disscussed in older post. To work with Database we need a client to send request and accept responce. And to communicate with oracle server we need a language and that language is called SQL. SQL is initially developed by IBM and their name was SEQUEL.

SQL is structured Query Language. User communicates with oracle server by sending command and instruction , that instruction and command is called Query. A query is a command and instruction submitted to oracle server to perform operation on database.

If English is a language then sentance is used to communicates with people, same as If SQL is a language then Query is used to communicate with database.

English=sentance

SQL    =Query

SQL language is common to all RDBMS, All the Database uses same Language that's why it is common.

Based on operation SQL is catagorized into following sublanguage.

DDL--data Defination Language

DML--Data Manipulation language

DQL--data Query language

TCL--Transaction Control Language

DCL--Data Control Language

Now we will disscuss all these sub languages here:

DDL- Data Defination Language--> this language is used to create data defination. without data defination database can not accept any data. let's see an example----

Emp_no Emp_name Emp_sal        ------->these are data defination that defines data.  

set of command is used to perform DDL operation:

create-- To create ttable defination

Alter-- To Modify table defination

drop -- To drop table defination

Truncate-- To delete all the data of table.

Rename-- To change the table name

DML-Data Manipulation Language----This language is used to perform operatrion on data. there is set of command and these are following:

Insert: To Insert a new row in a table

Update: To modify row data

Delete: To delete row or a particular record.

Insert All: To insert data in multiple table. *we will also discuss Insert all briefly with example in next post...

Merge: Combination of insert and update, it means it performs insert and update command on a same time.

DQL-Data Query Language--there is command to perform query operation. the command is SELECT.

SELECT is used to retrive data from database file.

TCL- Transaction Control language-- This languaged is used to control DML operation. It has set of commands like:

Commit: It saves transaction. It means that if you perform any operation that is initially happend on instance. so if you insert, update and delete any data that is removed from instance not from Database. To save operation on database you need to execute commit command. It is something like CTRL+S in windows documnet, this is only for understanding purpose. actually CTRL+S not works on Oracle.

Rollback: Rollback is used to cancle transaction. It is something like undo operation. If you done some changes in instance and want to cancle that transaction or changes Rollback command works.

Savepoint: It is one of the best TCL command that provide facility to cancle transaction. It will cancle part of transaction. let;s see an example of save point..

there is a 

table 

T1

F1

10

20

30

savepoint s1;

40

50

in this case if we execute rollback command then it will delete data form 50 to 40. if save point not exist the it will delete entire inserted data.

DCL-Data control Language--It is set of command that control the data between users of oracle. They specified two most importantt command that is GRANT and REVOKE.

GRANT- It is used to give permission of users. If a user wants to acess another user table then GRANT command provide acess privildge.

REVOKE- If any user wants to give acess permission back then REVOKE command works. Actually Revoke is designed to take back permission.

                                                                                           Thank you(Sunny Kumar)