Saturday, 4 March 2017

Overview of 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 sublanguages 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 id 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 for reading this post. if you find this post helpful please share and subscribe.
Smartyy Sunny

No comments:

Post a Comment

Make Top Rank Blog