Oracle SQL Commands

Oracle SQL Basic Commands

·         Data Definition Language Commands:-

1.Create: By using this command we can create any data base objects like tables, procedures, functions, view, sequence , triggers etc….

Syntax:  create table Student(Student_id number(10),Student_name varchar2(10));

2.Alter: By using this command we can change the structure of the existing data base object. Here Structure means the layout(ex: removing columns, adding columns, renaming columns etc..) but not the data inside the data base object.

Syntax: alter table student add student_marks number(10);

             alter table student add (student_marks number(10), student_address                           varchar2(10));

Alter+modify:  By using this command we change the datatypes of columns and also we can increase or decrease the size of the data base table columns

Syntax: alter table student modify (student_id varchar2(10), student_name varchar2(20));

Alter+drop: By using this command we can remove the columns from the existing data base object  like table.

Syntax: alter table student drop (student_id,student_name);

Alter+rename: By using this command we can rename the existing columns ,but it is not possible to rename more than one column at a time.

Syntax: alter table student rename student_id to student_no;

We can drop maximum 999 columns from a table because every table should contain atleast one column

3.Drop: By using this command we drop the data base object from the database

Syntax: drop table student;

From Oracle 10g onwards if we drop any table, that table is going to be stored into the recycle bin memory area

Syntax: show recyclebin;

In order to get back those tables we can use a command called flashback which was introduced in oracle 10g

Syntax: flashback table student to before drop;

Purge: By using this command ,we can drop the database objects from the database without storing in to the recyclebin memory area of the database.

Syntax: drop table student purge;

We can also use purge Query to clear the recycle bin

Syntax: purge recyclebin;

4.Truncate: By using this command we can remove the entire records from the data base object but we cannot remove the structure.

 It is not possible to get back the truncated data.

Syntax: truncate table student;

5.Rename: By using this command we can change the names of the existing database objects.

Syntax: rename student to student_info;

  

Data Manipulation language commands:-

1.Insert: By using this command we can insert the data into the database objects

Syntax: insert into student values (101, ‘Kalyan’, ‘12-jun-13’, ‘hyderabad’);

Syntax: insert into student values (&student_no,&student_name,&student_dob,&student_city);

Syntax: insert into student (student_no,student_name) values (&student_number, ‘&student_name’);

2.Update: By using this command we can modify the data which is present in the database object.

Syntax: update student set student_name= ’badri’ where student_id=23;

Syntax: update employee set bonus=1000;

//it will applicable to all employees in the db object.

Syntax: update employee set esal=esal+5000 where eid=23;  

//it will applicable to only a particular employee whose eid is 23

3.Delete: By using this command we can delete the data from the existing database objects

Syntax: delete from student where student_name=’rajesh’;

//it will deletes the particular student details whose name is rajesh.

Note: The data which is present inside the database objects is case-sensitive.

Data Retrieval Language(or) Data Query Language Commands:

Select: By using these command  we can retrieve the data from the database objects in user required format.

Syntax: select * from student;                

             select * from student where student_id=22;

            select * from student where student_name=’praveen’;

Using select Query with ArithmeticOperations:

          select  emp_no,emp_name,emp_sal+5000 from emp;

          select emp_no, emp_name, emp_sal+commission  from emp;

if commission column has null values then we will get wrong output because null+anything=null

Inorder to solve the above problem we have null values handling functions

Null: Null is a unavailable / undefined / unexpected  value and it is not equals to zero. To handle null values we have a function called nvl() which is mostly used when compared to other null value handling functions.

nvl(): It accepts two parameters. In the first parameter we have to set the the column name which contains the null value and in the second parameter we have to set the value which we want to pass in place of the null value.

Syntax: select emp_no, emp_name, emp_sal+nvl(commission ,2000) from emp;

Using select Query with dual:

Dual: Dual is a predefined virtual table contains one column and one row. We can test the predefined functions functionalities by using dual table.

Syntax: select nvl(null,20) from dual; //20

               Select 5*5 from dual;//25

               Select 100+100 from dual;//200

Using Select Query with Comparison operators:

By using comparison operators we can retrieve the data from the database objects by comparing with the required values.

Syntax: select * from emp where esal>3000;

             select * from emp where job<>’manager’;

Using Select Query with Logical operators:

By using the select Query with logical operators we can check for multiple conditions.

Syntax: select * from emp where deptno=20 OR deptno=30;

Using Select Query with Special operators:

Syntax: select * from emp where deptno in(20,30)//same like  OR

             Select * from emp where salary between 1000 and 2000;

             Select * from emp where salary not between 1000 and 2000;

            Select * from emp where commission is null;

Using select Query with like:

Here we use two symbols (– and %)

Syntax: select * from emp where ename like ‘-c%’;   //it displays employees information whose name second letter is ‘c’

Select * from emp where ename like ‘%c’;  //it displays employees information whose name last letter is ‘c’

Concatenation(||):

It displays user defined text in between two columns

Syntax:Select ename ||’ salary is’||sal from emp;  //ravi salary is 200000

Select Query with variuos Functions:

Function is a sub-routine which performs a definite task. Let us see some function syntaxes which are used along with the select Query.

Select ascii(‘a’) from dual; //97

Select chr(97) from dual; //a

Select concat(‘chaar’ ‘minar’)from dual; //chaarminar

 Select initcap(‘hyderabad’)from dual; //Hyderabad—First letter in capital

Select initcap(‘pawan kalyan’)from dual; //Pawan Kalyan---p,k are capital

Select instr(‘pawankalyan’, ‘a’)from dual;//first occurrence of ‘a’ is 2

Select instr(‘pawankalyan’, ‘a’,3)from dual;//first occurrence of ‘a’ is 4. 
Here search starts from third letter ‘w’ so the first occurrence of ‘a’ is 4^th position.

Select length(‘pawan kalyan’)from dual; //12

Select lower(‘PAWAN KALYAN’)  from dual;//pawan kalyan

Select lpad (string, padded length, pad character)from dual;

If the padded length is greater than the string length then its difference is added by the pad character on to the left side. Left padding

Select lpad(‘pawan kalyan’,15, ‘*’)from dual ;//***pawan kalian

Here the length of ‘pawan kalyan’ is 12 and the padded length is 15, the difference is 15-12=3. So 3 places on left side of the string is added by ‘*’

Select lpad(‘pawan kalyan’,10, ‘*’)from dual; // pawan kaly

If the padded length is less than the string length ,then the string itself truncates.

Select ltrim(‘    pawan kalyan’)from dual; //pawan kalyan

Data Control Language Commands:

DCL commands used to control the data access and database transaction control.

Grant: Grant command is used to assign privileges to users to access a database object.

Syntax:

Grant insert, update on student_info to HR_teacher;

Grant all on employees to system;

Grant on Column level:

Syntax:

Grant update(salary,commission)on employees to system;

Revoke: Revoke command is used to retrieve the privileges assigned to the users on a database objects.

Syntax:

revoke all on employee from system;

revoke insert, update on student_info from HR_teacher;

Revoke on column level:

Syntax:

Revoke update(salary,commission) on employees from system;

Transactional Control language:

TCL commands are used to manage the changes which are made by DML statements and perfoems logical transactions.

Commit: By using this command we can make the changes permanent

Syntax:

Commit;

Rollback: we can end the current transaction by using this command

Syntax:

Roolback;

Savepoint: By using this command we can implement partial rollback by setting a point.

Let us see how it works

Syntaxes to follw:

->First define a savepoint

SQL> Savepoint  <savepoint name>

->Create a row for example

SQL> insert into student values(21,‘ajay’,vijyawada);   //row created

->Then set savepoint  on above command

SQL>savepoint in_student;   //savepoint created

->Do rollback operation

SQL>rollback to in_student;//rollback complete