JOBS4TIMES jobs4timesLogo

HOME JAVA MATERIAL ORACLE MATERIAL
INTERVIEW PANEL TOOLS UNIX/LINUX WEB SERVICES


Basic Introduction of Oracle

  1. Types of DataBases :

    1. FDBMS
    2. NDBMS
    3. RDBMS
  2. Data Types

  3. Types of SQL Statements :

    1. DDL(Data Definition Language) :
      1. create
      2. alter
      3. drop
    2. DML(Data Manipulation Language) :
      1. insert
      2. delete
      3. update
      4. select
      5. desc
    3. DCL(Data Control Language) :
      1. grant
  4. Operators :

    1. Arithmetic
    2. Relational
    3. Logical
    4. Like
    5. In
    6. Between
  5. Functions :

    1. Grouping Functions :
      1. min
      2. max
      3. avg
      4. Standard deviation
      5. variance
      6. stddev
    2. Character Functions :
      1. ascii()
      2. chr()
      3. lower()
      4. upper()
      5. concat()
      6. substr()
      7. initcap()
      8. length()
    3. Member Functions :
      1. abs( )
      2. power( )
      3. mod( )
      4. sqrt( )
    4. Mathematical Functions :
      1. log( )
      2. sin( )
      3. cos( )
      4. tan( )
    5. Date Functions :
      1. add_months( )
      2. last_day( )
      3. next_day( )
      4. months_between( )
  6. Set Commands

  7. Show Commands

  8. Constraints in Oracle :

    1. unique
    2. not null
    3. primary key
    4. null
  9. Constants in Oracle :

    1. sysdate
    2. user
    3. uid
    4. rowid
    5. distinct
  10. Clauses in sql :

    1. where
    2. order by
    3. group by
    4. having
    5. starting with
  11. Table Set Commands :

    1. union
    2. union all
    3. intersect
    4. minus
  12. VIEWS :

  13. Joins :

    1. self join
    2. equi join
    3. non-equi join
  14. Executing Multiple Queries from a file to sql * plus editor

  15. Bind Variables

PL/SQL

  1. Conditional Statements :

    1. if
    2. if-else
  2. Control Statements :

    1. while
    2. do-while
    3. for loop
  3. Data Types in pl/sql :

    1. number
    2. varchar
    3. varchar2
    4. date
  4. Structure of the pl/sql Program

  5. Procedures & Functions

  6. Triggers


ORACLE


 DataBase:

    1.Storing in the information about particular thing Using this database we can create the database
       and we can store th information and we can access the information and we can modify the database
    2.Using this database we can create any type of data & we can store the data in the form of information we have different types databases , we can create.
Ex: If we want to create database regarding employee , we can create the emp database.
        emp database describes complete details of emp using that information, we can complete our tasks in  time Using this database , we can modified the data & we can add &delete the data.
    We can perform when we  want to the modification before that we must contain details of existing database information
Data : Data is nothing but Collection of raw facts , using this data we can't get complete structure of the data.
Ex: Ramu , Ashok , 70 and etc.,

Information :

Information is nothing but giving the meaning full data about particular thing . Using this information we can decide whether this information set to our database or not .

Types of DataBases :

  1. FDBMS
  2. NDBMS
  3. RDBMS

File Management DBMS :

  1. In earlier days we have no systems at this time we did use File Management System.
  2. Using this File Management System entire data managed by file.
  3. Files contains Collection of Pages with database details using this File database, we can get entire information through Files.
  4. Come to the modifications very difficulty.
  5. For accessing the old record is difficulty.
  6. For deleting the old record is difficulty.
  7. Using this File Management System , Files will be waste for store in a data.
  8. If we get the small modification we have to re-enter the data again.
  9. For clear this drawbacks we design a database through Computer.
  10. Using with computer database entire data storing in Hard-disk instead of files.
  11. For storing the database we need a tool, that tool is Oracle.
  12. Oracle is one type of database Using this Oracle we can create our own database.
  13. What we created the database , we can perform all operations on this data base.
  14. Using this database we can clear all the problems in File Management System.
  15. Using this Oracle database we can store data permanently and modifications easily and we can access the data any time, any place.

NetWork DBMS :

  1. Using this network DBMS we can connect with the database through NetWork.
  2. In network we have no. of systems , every system connect with the main server
  3. Main server contains the database , rest of the systems does not contain database , It can access when ever it needs the main database data keep in server database.
  4. Server may be connect with only one system or thousands of systems.
  5. Our system requests to the server , server will response and send back results to the system, system will display that output.
  6. The entire data processing server only.

Relational DataBase Management System :

  1. Relational DataBase Management System representation of tables only.
  2. In RDBMS we can store the data in the form of tables.
  3. Ising this RDBMS we can create database easily.
  4. We can insert the data easily.
  5. We can modify the data easily.
  6. Using the RDBMS we can perform all operations on the table .
  7. One table contains columns.
  8. One table may contains 1 column or no. of columns .
  9. One table must contain 1 column , without column we cann't create table .
  10. Which database we are going to be create regarding that we can create the table .
  11. The table name must matched to our database name.
  12. The database may be emp, student .Cell shop etc.,
  13. In columns we will write the attributes of the databases.
  • Table definition : Collection of records, record is nothing but collection of columns of attributes.
  • Using this RDBMS entire data we keep it in the form of tables.
  • Table contains the no. of records , every record displays particular database details. One row means one record.
  • RDBMS performing one language that is called SQL.

SQL ( Structured Query Language ) :

  1. Using this sql, we can create the tables , we can modify the tables, we can drop the tables.
  2. In sql we have no. of queries for access the database we can apply the queries on the tables & we get the results.
  3. E.F.CODD -------sql founder
Query :
  • Query is nothing but condition or question to the database.
  • This Query required by the database & execute the query in database. After executing the query send result back to the client.
  • Ex: select empno from emp;
  • Using this sql we can apply no. of queries on the table.
SQL * PLUS :
  • sql*plus is a editor using this editor we can perform the all operations on table.
  • This editor helps for doing the operations are entire RDBMS. In entire RDBMS entire data should be in the form of tables
  • Using this editor we can write the sql queries & execute the queries , this editor displays the result of the query.
  • Using this sql * plus editor not only for SQL ,It can also used for writing the PL/SQL programs.
PL/SQL :
  1. PL/SQL is nothing but Procedure Language Structured Query Language , Using this pl/sql language we can write the programs.
  2. Using this programs we can create the database & perform the database actions .
  3. In pl/sql programs executes with in the help of sql * plus editor.
  4. Using the pl/sql we can write programs and this programs results storing in table.
  5. PL/SQL is a programming language, it contains data types
    1. number
    2. varchar
    3. varchar2
    4. date
  • In sql also contains data types , using the data types we can create the tables .
  • After giving the data types , to the columns.
  • After giving the data types , to the particular column then onwards that column doesn't accept another type of data.
  • If we want to store another type of data it shows that error.
    Ex:
  • empno number //declare
    empno='Ashok'; //wrong
    data types only applicable for column names
  • Table name never contains the data type.

Data Types :

  1. number
  2. varchar
  3. varchar2
  4. date

  1. number :
    1. Using this number data type , we can store numbers in a particular column in table.
    2. If we create one column of number data type,then that column allows only numbers, In that column it doesn't allows characters or any decimal points.
    3. Ex: empno number ;
      We can giving number data type we want to store decimal values then we can use this number data type , when we want to store decimal points then we have to give the range.In that range we gives the decimal values.
    4. Ex: empno number(5, 2) ;
  2. varchar :
    1. Using this data type we can store characters and strings in a particular column
    2. If we want to store string values then go for this data types.
    3. Ex: ename varchar ;
    4. In varchar data type one more facility is we can declare the number of characters we want to store , we can give the size of the characters in braces.
      Ex: ename varchar(20);
  3. varchar2
    1. Using this data type also we can store characters and strings.
    2. Using this data type it will save the memory.
    3. Ex: ename varchar2(20) ;
      ename varchar2 ;
Difference between varchar & varchar2 :
  1. varchar: Using this varchar data type when we give the size of the column value that number of bytes occupied by the for that column.
    If we declare size is 20 , but if we use only 10 characters then rest of the 10 characters memory will be waste.
    This memory not useful for another variable in memory.
  2. varchar2 :In this varchar2 datatype when we declare one variable with the size of a particular column then if we use that size completely then no problem.
    If we don't use the complete size of the declare time.Then rest of the memory useful to another variable or another program.
Date :
  1. Using this date data type we can give the dates do a particular columns.
  2. We can store the dates in columns as a value dates we can declare in the format of dd-mm-yy. Ex:
    joindate date ;
    joindate '10-Aug-95' ;
  3. Always we have to declare characters & dates in the form of single quote.
Clob data type (Character Large Object data) :
  • Using this data type we can store characters in the form of bytes , we can store group of characters , range is 2 GB.
Blob data type(Binary Large Object data ) :
  • When we want to store perticular images and files in the form of bytes, range is 4 GB .
  • We can store files and images in a table columns in the form of bytes .
  • Actually we don't store the files and imges into a perticular column.
  • We store files and images in a perticular place in a memory from that place we can create one pointer using this pointer we can store in a column values that pointeris called "Locator".

For Clob & Blob data types we can find the data with the help of locators.
Ex:
image blob ; //4GB
file clob ; //2GB


TYPES OF SQL STATEMENTS :

  1. Data Definition Language----------------------------> create,alter,drop
  2. Data Monipulation Languation----------------------->insert,delete,update,select,desc
  3. Data Control Language--------------------------------> grant,revoke
DDL commands:
  1. Create :
    syn: create table tablename(column datatype);
    Ex:create table student(sno number,sname varchar2(20),dob date );
  2. Alter: ------------add,modify,rename,drop
    Syn:- alter table tablename add columnname datatype;
    Ex:- alter table emp add phno number;
    Syn:- alter table tablename modify columnname datatype;
    Ex:- alter table emp modify sname varchar2(30);
    Syn:- alter table tablename rename column ocn to ncn ;
    Ex:- alter table emp rename column sno to studno;
    Syn:-alter table tablename drop column columnname;
    Ex:- alter table student drop column sno;
  3. Drop:
    Syn: drop table tablename;
    Ex: drop table emp;
DML commands:
  1. Insert : Syn:-insert into tablename values(columnvalue);
    Ex:-insert into student values(1,'apec');
    Syn2:- insert into student values(&no,'&sname');
  2. Delete :
    Syn: delete tablename where column=value;
    Ex:- delete emp where empno=10;
  3. Update:
    Syn:-update tablename set columnname=value where condition;
    Ex:-update student set sname='apec' where sno=1;
  4. Select:
    Syn:- select columnname from tablename;
    Ex:- select * from student;
  5. Desc:
    Syn:-desc tablename;
    Ex:- desc student;
DCL commands:
  1. Grant:
    Syn:-grant permissions on tablename to username;
    Ex:- grant alter on student to scott;
    Ex:- grant update on emp to bank;
    Ex:- grant alter,update on student to dsnr;


    Ex:- grant any commands on student to scott;


DDL COMMANDS :

create :

  1. create is a keyword.
  2. It is using for creating one table in a database, after executing create command table will be created.The table contains zero records and structure with zero values.
  3. Table contains number of columns , in create query we can give complete details of table.
  4. In create command we give the column names with data types.
  5. Syntax : create table tablename(columnname datatype);
    Ex1: create table employee(empno number , dob date, sal number );
    Ex2: create table bank(bname varchar2(30) , bal number );
    Ex3: create table cell(cname varchar2(30) , cost number(9,2) );
    Ex4: create table school(sname varchar2(20) , splace varchar2(30) );
    Ex5: create table college(cid number , cname varchar2(30) );
    Ex5: create table course(cid number , cname varchar2(30) );

delete :

  1. deiete is a key word , using this keyword we can delete perticular row in a database table .
  2. Delete command using for delete rows only , but not table.
  3. Before executing the delete command that table name must be exist.If we give the unknown table name we willget an error
  4. syntax : delete tablename where columnname=value;
    Ex : delete employee where empno=10 ;
    empnoempnamesalcommission
    10a 100010
    20b200020
    30c300030
    40d400050
    After executing the above delete command empno=10 that record will be deleted, rest of the rows are still executing in the table.
    empnoempnamesalcommission
    20b200020
    30c300030
    40d400050


    Ex 2 : delete employee where empno=20 ;
    empnoempnamesalcommission
    10a 100010
    20b200020
    20b200040
    30c300030
    40d400050
    After executing the above delete command empno=20 , this condition is satisfied 2 records , so oracle will delete 2 records from the emp table.
    empnoempnamesalcommission
    10a 100010
    30c300030
    40d400050
    After executing delete query one message will be display , the message is " 2 rows deleted".

    Ex: delete employee where empno=50 ;
    empnoempnamesalcommission
    10a 100010
    20b200020
    30c300030
    40d400050
    sql>no rows deleted

    Ex: delete employee22 where empno=10 ;
    sql> table or view doesn't exist.
    Note : employee22 table not exists in database.

drop :

  1. Using this dropcommand we can drop the table from database. If we can delete entire table from the database then we should go for drop command.
  2. After executing the drop command , then table is permanently deleted from database entire table is deleted at a time.
  3. syntax : drop table tablename ;
    Ex1 : drop table employee ;
    Ex2 : drop table student ;
    Ex3 : drop table school ;
    Ex4 : drop table bank ;
    Ex:
    empnoempnamesalcommission
    10a 100010
    20b200020
    20b200040
    30c300030
    40d400050
    sql > drop table employee ;
    sql > table is droped ;

DML commands :

insert :

  1. Insert is a keyword , this command is using for inserting values in a table
  2. After creating the table we insert the values record by record .and using this command we insert any number of rows in a table.
  3. Once we execute insert command one row is created.
  4. Insert command must be use after table is created, otherwise it will show error on the sql prompt.
  5. syntax : insert into tablename values(column1value, column2value , .................. , columnNvalue);
    Ex1: insert into employee values(10,'ashok',1000,10) ;

    sql > select * from employee ;
    empnoempnamesalarycommission
    10ashok 100010
    sql > one row is created ;

  6. Ex2: insert into employee values(&empno, &empname, &salary, &commission ) ;
    For insert multiple records at a time we no need to write this query every time.
    empnoempnamesalarycommission
    10a 100010
    20b200020
    20b200040
    30c300030
    40d400050

alter :

  • Using this alter command we can modified the table and we can perform number of operations on the table.
    1. Add the new columns to the table
    2. Modify the existing data types
    3. Rename the existing column name
    4. Drop the column in a table

Add new columns to the table :
  1. Using this alter command we can modify the table , First we create the table with 3 columns then if we want to add one more column into our table then using this alter command. then our table contains 4 columns
  2. syntax : alter table tablename add columnname datatype ;
    Ex1 : alter table student add saddress varchar2(20) ;
    Ex2 : alter table employee add empjob varchar2(20) ;
    Ex3 : alter table bank add bname varchar2(20) ;
    Ex4 : alter table cell add cno number ;
    Ex5 : alter table college add ejoin date ;
    Example :
    empnoempnamesalary
    10a 1000
    20b2000
    20b2000
    30c3000
    40d4000
    Ex2 : alter table employee add empjob varchar2(20) ;
    empnoempnamesalaryempjob
    10a 1000Security guard
    20b2000service man
    20b2000employ
    30c3000Team Leader
    40d4000HR
Modify the existing data types :
  1. The 2nd function of alter command is modify , it is used for change the datatypes of the columns.
  2. if we create one table with 3 columns with 3 datatypes of some pecified sizes
  3. After createing the table if we want to increase or decrease of the column value size , After executing this query will changed the columns of the size .
  4. syntax : alter table tablename modify columnname datatype;
    Ex:create table student(sno number,sname varchar2(20),dob date );
    Ex : alter table student modify sname varchar2(40);
    sql > table altered
    Using this modify can changed datatype size is 20 to 40 size of sname in student table.
Rename the existing column name :
  1. The one more function of alter is rename , rename is a keyword.Using this keyword we can rename the column names.
  2. If we create one table with 3 columns with values , After existing the table if we don't satisfy with the existing column names then we can rename the column names .
  3. Rename is nothing but deleting the old name and giving the new name, after existing the rename command new column name appear instead of old column name .
  4. syntax : alter table tablename rename column oldcolumnname to newcolumnname ;
    Ex : alter table student rename column empno to employeeno ;
    sql > select * from employee ;
    employeenoempnamesalaryempjob
    10a 1000Security guard
    20b2000service man
    20b2000employ
    30c3000Team Leader
    40d4000HR

Drop the column in a table :
  1. The drop command is using for deleting a particular column in a table . If we create a one table with 3 columns in that if we want to delete any column then go for this command
  2. After executing the drop command the column will be permanently deleted from the table .
  3. After dropping the column corresponding values in that column will be deleted.
  4. syntax : alter table tablename drop column columnname ;
    Ex : alter table student drop column sno ;
    'sno' column will be deleting from student table with values.
Update :
  1. This command is using for doing the modifications on the column values.
  2. If we create one table with 4 columns then we can insert the values., after inserting the values if we want to do any modifications in the column values then go for this command.
  3. Using this command perform 2 operations.
    1. Replacing the new value with old value.
    2. Giving the values to the new columns .
  4. When we add the new columns to the table when that new columns does not contains values.
  5. If we want to send the values for the new column then go for update.
  6. Syntax : update tablename set columnname=value where condition ;
    Ex1 : update employee set empname='ashok' where empno=1 ;
    Ex2 : update student set sname='jobs' where sno=2;

Select :
  1. This command is using for display the table with values., this command is using for seeing the informatiom of table.
  2. This command is uesd for display the no. of records reliable in a table.
  3. First we create the table then we insert the values.
  4. After inserting the values we get the message like one row is inserted.
  5. If we want to check that row and we want see that values then go for select command
  6. This is non-action statement.
  7. After executing the select query it doesn't effect on database.
  8. After executing this query no modifications in the table .
  9. Using this select command we can see entire table at once.
  10. Using this select command we can select not entire table , we can select particular no. of columns also.
  11. When we use the select command we can access one column or more column or entire table we can access.
  12. syntax : select * from tablename ;
    * means displaying the all column names with values in a table.
    Ex: select sno, sname from student ;
Desc :
  1. Using this command we can see the structure of the table , Structure is nothing but how many columns available in table and it display the data types also
  2. desc will display the result in the form of table. tha table contains number of columns with data types.
  3. syntax : desc tablename ;
    Ex : desc employee ;
    Ex : desc student ;

DCL( Data Control Language ):

  • Using with DCL we can perform to operations those are grant and revoke.

Grant :

  1. It is uesd for giving the permissions to the users in Oracle , we have different types users.
  2. Every user has create table their own databases .
  3. Every user can give the permissions on their created table .
  4. We can give the permissions through access one user to another user .
  5. Without permissions another user doesn't access the table.
  6. This command is used only giving the permission to the tables or users .
  7. syntax : grant permissions on tablename to username ;
    Ex : grant alter on student to scott ;
    Ex : grant update on emp to apec ;
    Ex : grant alter, update on bank to dsnr ;

Operators :

  1. Arithmetic Operators : (+, _ , * , / , %)
    Using this operators we can performs on the tables .
    1. Ex : select esal from emp where sal=sal+1 ;
  2. Relational Operators :( < , > , >= ,<= , = , != )
    Using this operators we can perform operations on the table.
    1. Ex : select esal from emp where sal=100 and eno=1 ;
      Ex1 : select esal from emp where eno=2 or eno=1 ;
      Ex2 : select esal from emp where ename='ashok' or eno=1 ;
      Ex3 : select esal from emp where sal <=100 ;
      Ex4 : select esal from emp where sal =100 ;
      Ex5 : select esal from emp where eno!=10 ;
      Ex6 : select esal from emp where sal <> 100 ; // not equals to
      Ex7 : select esal from emp where sal ~=100 ; //tild symbol
  3. Logical Operators : (and , or )
    Using this log operators we can perform the Operations on the table .
    Ex1 : select esal from emp where eno=10 and sal=1000 ;
    Ex2 : select esal from emp where eno=10 or sal=1000 ;
  4. Like Operator :
    1. Like is a operator , this operator is used for finding the string into the table.
    2. If we want find the name in Oracle table we can use this operator.
    3. Like operators contain using with to operation we can perform search the strings.
    4. Those operators are % , _
    5. % :
      • This operator is used for when we don't know about the complete name when use this operator.
      • % --- indicates number of characters after writing the characters
      • We can given one or more characters also
      • syntax : select * from emp where ename like a% ;
        empnoenamesalaryempjob
        10amp 1000Security guard
        20ashok2000service man
    6. _ :

      • underscore( _ ) search for single characters , when we give the underscore when we find only one character
      • We our name contains 5 characters then we have give the 5 underscores.
      • Which string we want to search that searching letter write first beside with underscores.
      • syntax : select * from emp where ename like a---- ;

        empnoenamesalaryempjob
        10aksha 1000Security guard
        20ashok2000service man
  5. In Operator :
    • Using with in operator we can find the records in the given range of values .
    • What ever we given the range in the range values written as output.
      empnoempnamesalaryempjob
      10a 1000Security guard
      20b2000employ
      30c3000Team Leader
      40d4000HR
      Ex: select * from employee where salary in(2000, 4000) ;
      empnoempnamesalaryempjob
      20b2000employ
      30c3000Team Leader
      40d4000HR
  6. Between Operator :
    • Using with between operator we can find the column values of the given ramge , if we want to find out the salaries between one range to another range then go for this operator.
      empnoempnamesalaryempjob
      10a 1000Security guard
      20b2000employ
      30c3000Team Leader
      40d4000HR
      Ex: select * from employee where salary between 2000 and 5000 ;
      empnoempnamesalaryempjob
      30c3000Team Leader
      40d4000HR

Order by Cluse :

  1. Using with order by class we can perform operations on the table.
  2. Using with command we can access the rows with 2 times
    1. ascending order
    2. descending order
  3. When we print the ascending order it will print the rows in that format, values can be display ascending order.
  4. We can perform ascending order operations with the help of one column.
  5. What ever giving the column name regarding that column name it will display the values with ascending order.
  6. Ex : select * from emp where empno orderby asc ;

    empnoempnamesalaryempjob
    10a 1000Security guard
    20b2000service man
    20b2000employ
    30c3000Team Leader
    40d4000HR

    Ex : select * from emp where empno orderby desc ;

    empnoempnamesalaryempjob
    40d4000HR
    30c3000Team Leader
    20b2000employ
    10a 1000Security guard

FUNCTIONS :                                                                           

Grouping Functions :

Grouping Functions are
  1. min( )
  2. max( )
  3. avg( )
empnoempnamesalaryempjob
10a 1000Security guard
20b2000employ
30c3000Team Leader
40d4000HR
  1. min() :

    min() function is used for finding the minimum value in the given table, this min( ) function will return which is the lowest value in the table on the given conditions.
    syntax : select min(salary) from employee ;
    empnoempnamesalaryempjob
    10a 1000Security guard
  2. max() :

    max() function is using for bring the highest salary in the given condition or column name.
    syntax : select max(salary) from employee ;
    empnoempnamesalaryempjob
    40d4000HR
  3. avg( ) :

    avg( ) function returns the average of the given values of the specified column name.
    syntax : select avg(salary) from employee ;
  4. stddev( ) :

    standard deviation function is used for finding the standard deviation of the given column.
    This function is applied for number column names , not for character column names.

    Syntax : select stddev(sal) from employee ;
  5. variance( ) :

    This function is using finding the variance of the column name.
    This function is applied for number column names , not for character column names.

    syntax : select varience(salary) from employee ;

CHARACTER FUNCTIONS :

  1. Ascii() :

    This is one of the character function , using this function we can give the character it will return the ascii value .
    Syn:- select ascii(character) from anytable;
    Ex1:- select ascii('a') from emp;
    Ex2:- select distinct ascii('a') from student; // it is using stoping the repeated values from the result.
  2. Chr() :

    This function is using for return the character of the given value
    This function will take the argument is number , that number match to which character , that character should be return.

    Syn :- select chr(number) from table;
    Ex :- select chr(67) from emp;
  3. Lower() :

    This function is using for converting given string from upper case letter to small case letter.

    Syn :- select lower(character) from table;
    Ex:- select lower('APEC') from emp ;
  4. Upper() :

    This is using for converting the given string from lower case letters to upper case letters .

    Syn:- select upper(character) from table;
    Ex:- select upper('apec') from emp;
  5. Concat() :

    This function is using for combining the 2 strings .
    Using this function we give the two arguments first string each concatnate with 2nd string , the output is combination of that string.
    Syn :- select concat(str1,str2) from table ;
    select concat(name, surname) from table ;
    Ex :- select concat('apec','DSNR') from emp;
  6. SubStr() :

    Using this function we can find out the part of the string from the entire string .
    We give the index value regarding the index value we can access the part of the string.

    Syn :-select substr(string or columnname , position) from tablename ;
    Ex :- select substr('apec', 2) from emp ; //indexes starts from 1,2,3.........onwards
    empnoempnamesalaryempjob
    10ashok 1000Security guard
    20arunkumar2000employ
    30akshay3000Team Leader
    40saicharan4000HR
    Ex :- select substr(empname, 2) from emp ;
    empnoempnamesalaryempjob
    10ashok 1000Security guard
    20arunkumar2000employ
    30akshay3000Team Leader
    40saicharan4000HR
  7. InitCap() :

    This function is using for displaying the given string first character is capital , rest of the characters in lower case .

    Syn :- select initcap(string) from tablename;
    Ex :- select initcap('avanthi') from student ;
  8. Length() :

    This funtion is using for finding the length of the given string , We return the length of the string in the form of the number.

    Syn :- select length(string) from tablename ;
    Ex :- select length('dilsuknagar') from student ;

SHOW COMMANDS :

  1. show ti ;
  2. show heading ;
  3. show feedback ;
  4. show pagesize ;
  5. show linesize ;
  6. show serveroutput ;
  7. show sqlprompt ;
  8. show und ;

SET Commands :

  1. set ti on/off ;
    1. This command is using for displaying the time all the sql promt.
    2. ti is nothing but Time.
    3. When we want to display time on the console then go for set " ti " all.
    4. When we all mode it will display the time on the sql prompt.
    5. The time is display on the current system time .
    6. When we mode of set ti off at that time , time does not appear on the sql prompt.
    7. syntax : set ti on ; //for set the time
      syntax : set ti off ; //for stop the time.
      syntax : show ti ;
  2. set heading on/off ;
    1. Using thiscommand if we want to display the column names or if we want to not display the column names then go for heading command .
    2. Heading is nothing but display the column name or not .
    3. When we set heading on it will display the column names , When we set the heading off it will not display the column names , It will not display the column names but it will display only records with values.
    4. When we see the values at the time we don't know the column names.
    5. In a editor for all tables heading command is defaultly on .
    6. syntax : set heading on ;
      select * from employee ;
      empnoempnamesalaryempjob
      10ashok 1000Security guard
      20arunkumar2000employ
      30akshay3000Team Leader
      40saicharan4000HR

      set heading off ;
      10ashok 1000Security guard
      20arunkumar2000employ
      30akshay3000Team Leader
      40saicharan4000HR
      syntax : show heading ;
  3. set feedback on/off :
    1. This command is using for display the information about output.
    2. In general after executing the command , the output will be display like 4 rows selected, 1 row updated like this messages will be displayed.
    3. If we want to access this information like 4 rows selected then go for set feedback on ;
    4. So when we set feed back on , it will display the information about selected rows.
    5. When we set the feed back off , then it will not display the information like 3 rows selected , 5 rows updated like that does not appear.
    6. show feedback ; //it is show the status of the feedback command .
  4. set pagesize n :
    1. This command is using for setting the pagesize in the oracle .
    2. Oracle editor contains no. of pages , it will display the information with the help of pages.
    3. Pages are using for display the result of the table.
    4. syntax : set pagesize n ;
      Ex : set pagesize 40;
      syntax : show pagesize ;
  5. set linesize n :
    1. This command is using for set the linesize in the oracle editor , defaultly the linesize is 80 .
    2. If we want to increase the linesize , we have to set the command like set linesize n .
    3. n is nothing but no. of linesize .
    4. How many characters we want to display in a single line that no. we have to give .
    5. syntax : set linesize n ;
      Ex : set linesize 100 ;
  6. set serveroutput on/off :
    1. This command is using for display the output of perticular programs .
    2. In pl/sql we write the programs , after writing the program we will execute it .
    3. After execution we need to see the output , but before see the output we need to set the command is set server output on .
    4. When we are in on mode it will display the output after executing the program .
    5. When we are in off mode it will not display the output after executing the program.
    6. In editor defaultly set server output is always off mode only .
    7. syntax : set serveroutput on ;
      syntax : set serveroutput off ; //default
    8. on : we can get the result of pl/sql programs.
      off : we can't get the result of pl/sql programs.
  7. set sqlprompt name :
    1. This command is using for set the prompt in the sql editor .
    2. What ever give the message or string that message will display the sql prompt , after giving the name sql prompt display the given name instead of sql prompt .
    3. It will replace the sql prompt displays the our name.
    4. After set onwards our name work as like sql prompt , we can give the name with operators also .
    5. syntax : set sqlprompt 'apec>' ;
      syntax : set sqlprompt 'ashok$' ;
      ashok$ select * from tablename ;
  8. set und char :
    1. This command is using for set the underline of the column names.
    2. When we display the table data it will display the column names with the help of underscore character.
    3. If we don't satisfy with that character , we can change the characters , we can give our own character.
    4. In this command we give only one character, that character should be writing single code.
    5. syntax : set und char ;
      Ex : set und '$' ;
  9. set ttitle msg :
    1. This command is used for set the heading to the our table.
    2. What ever we give the message , that message will be appear as ttittle .
    3. ttittle is nothing but top tittle and setting the header to the table.
    4. ttittle information always display above the table , after executing the query ttittle will display with the result.
    5. syntax : set ttittle ashoksoft ;
      Ex : select * from employee ;
      empnoempnamesalaryempjob
      10ashok 1000Security guard
      20arunkumar2000employ
      30akshay3000Team Leader
      40saicharan4000HR
      When we print the date and name of the week it will display .
  10. set btitle msg :
    1. this command is using for display the information as a footer.
    2. btittle is nothing but bottom tittle , what ever we set the btittle msg , the msg will be display as a footer after executing the command.
    3. syntax : btittle msg ;
      Ex : btittle ashok123;


Member Functions :

  1. abs( ) : This function is using for finding the absolute value of the given number.
    Ex : select abs(-10) from dual ;
    output : 10
  2. power( ) : This function is using for finding the power of the given number.
    Ex : select power(2,3) from dual ;
  3. mod( ) : This function is using for finding the remember of the given value.
    Ex : select mod(5,2) from dual ;
  4. sqrt( ) : This function is using for finding the sqrt of the given number.
    Ex : select sqrt(4) from dual ;


Mathematical Functions :

  1. log( ) : log function is using for finding the log value of the given numbers.
    Ex : select log(2,1) from dual ;
  2. sin( ) : sin function is using for finding the sin value of the given radians.
    Ex : select sin(90) from dual ;
  3. tan( ) : tan is using for finding the tan values of the given number.
    Ex : select tan(0) from dual ;

Date Functions :

  1. add_months( ) :
    • This functions using for displaying the details using the function. If we want to add_months to the existing date , then go for this functions.
    • In this finction it has 2 arguments .
      1. First argument is 'date'
      2. Second argument is 'number'
      that number is how many months we want to add.
    • syntax : select add_months(date , number) from student ;
      Ex : select add_months('1-jan-14',4) from student ;
      output : 1-may-14
  2. last_day( ) :
    • This function is using for finding the last day of the current month.
    • If months ends with the 30 , it will return 30 .
    • syntax : select last_day('1-jan-14') from student ;
      output : 31-jan-14
  3. next_day( ) :
    1. This function is using for finding the day of given date.
    2. In this function we give the arguments are first argument is date , then 2nd argument is name of the day .
    3. Using with 2 arguments it will return next day after the given dates.
    4. syntax : select next_day(date , day ) from student ;
      Ex : select next_day( '2-may-2013' , 'thursday' ) from student
      output : 9-may-13
  4. months_between( ) :
    1. This function is used for finding the different of two months.
    2. In this function we give the two arguments first argument is 'date' , second argument is 'date' .
    3. This function finds the difference of the given 2 months.
    4. syntax : select months_between(date1 , date2 ) from student .
      Ex : select months_between('1-jan-14','1-mar-14') from student .
      output : 2


Truncate :

  1. This command is using for deleting the all rows in a table.
  2. Truncate is nothing but clearing the table with out rows.
  3. delete command is using for deleting a single row.
  4. truncate command is using for deleting all rows in a given table name.
  5. When we execute the truncate then we give the table name.
  6. What ever we give the table name , in the table all rows are deleted.
  7. After executing the truncate command table contains 'zero' rows or no rows
  8. Truncate command doesn't delete the table but it will clear the table with zero rows.
    // delete=1 row delete
    // truncate = all rows deleted in a table
  9. syntax : truncate table tablename ;
    truncate table student ;
    output : all records or rows deleted
  10. After executing this command table contains zero rows.

Constants in Oracle :

  1. sysdate :
    1. This command is used for display the current date in the Oracle.
    2. After executing this command it will display the current day in date format.
    3. syntax : select sysdate from anytable;
      Ex : select sysdate from student ;
  2. user :
    1. This command is used for display the username which are operating the oracle .
    2. When we enter the oracle editor , we give the username , password values .
    3. When we ask the user it will return the username of the current editor.
    4. The output is display in the user name .
    5. syntax : select user from anytable ;
    6. Ex : select user from emp ; //output : scott
  3. uid :
    1. uid is nothing but userid , after executing the uid command it will return the id number.
    2. When we enter into the editor every user has one id number , when we ask the id number , it will return that number.
    3. syntax : select uid from anytable ;
      Ex : select uid from emp ;
  4. rowid :

    1. This is using for display in the rowid for every row , in oracle if we create one table oracle will give defaultly rowid.
    2. rowid is using for representing the row.
    3. rowid is using for identify in the row uniquely.
    4. every row contains one unique number.
    5. Using this unique number we can represents the row.
    6. syntax : select rowid from anytable;
      Ex : select rowid from emp ;
  5. distinct :

    1. distinct is a keyword using this keyboard , we can represent single values from the result .
    2. When we display the table if table contains repeated values it will display defaultly.
    3. If we want to control repeated values and if we want to display repeated values only one time then go for this command.
    4. distinct keyword is using for stopping the repeated values and display the repeated values only one time , not more than one time.
    5. syntax : select distinct salary from employee ;
      empnoempnamesalaryempjob
      10ashok 1000Security guard
      20arunkumar2000employ
      30akshay3000Team Leader
      40saicharan3000HR
      // 3000 doesn't display 2nd time why because distinct keyword controlled by second 3000 value.


CONSTRAINTS  IN   ORACLE :

  1. Unique
  2. Not Null
  3. Primary Key
  4. Null

  • constraints are using for giving the conditions on the column names in generally , if we create the table we don't give any instructions.
  • If we want to give any conditions while creating the table then go for this constructions.


  1. Unique :

    1. unique constraint is using for declaring the column .
    2. unique does not apply to the table names , only for columns.
    3. Which column contains the unique constraint that column value should be unique.
    4. column doesn't contains repetative values or redundency values.
    5. syntax : create table employee(eno number , ename varchar2(20) unique );
    Syn :- create table tablename (columnname datatype constraint) ;
    Ex :- create table emp2(eno number unique , ename varchar2(20)) ;
  2. Not Null :

    1. This constraint is using for giving the column names , it doesn't apply to table names.
    2. When we create the table , if we contain character datatypes instead of value , we can give the null .
    3. When we want to doesn't accept the null then go for this constraint.
    4. While creating the table to give the column names with the not null constraint.
    5. After giving this constraint it doesn't accept the null value , null value is nothing but empty value.
    Syn :- create table tablename (columnname datatype not null) ; Ex :-create table bank(bno number ,bname varchar2(20) not null ); Ex :- insert into bank values(1,'apec'); Ex :- insert into bank values(1, null); //wrong
  3. Primary Key :

    1. primary key is using for giving the conditions on the columns , when we declare one column with primary key it doesn't accept null values & repetative value.
    2. Primary key is using for making the relations to the table , we can connection with the one table to another table for access the information.
    3. primary key is very important role while making the relationships to the table.
    4. primary key is appling only column names .
    5. primary key does not apply to tables.
    6. Using with primary key if we want to make the relationship with the another table using with another key.
    7. For every table only one primary key column.
    Syn :- create table tablename (columnname datatype primarykey) ; Ex :- create table student (eno number primary key) ;
  4. NULL :

    1. null is a keyword in a Oracle.
    2. null is not a value , it indicates this column contains no value.
    3. When we insert in the values if we doesn't find any value to the given column then go for null.
    4. When we declare one column with not null constraint at the time we don't use the null key word.
    5. If we try to use the null keyword it will show error, and that row doesn't insert.
    Syn :- create table emp3(eno number , ename varchar2(20)) ; Ex :- insert into emp3 values(1,'apec') ; Ex :- insert into emp3 values(1, null) ; Ex :- insert into emp3 values(2, null) ;

Clauses in SQL :

clauses are using for accessing the records on the given certain conditions , give any condition regarding the condition is working.
empnoempnamesalaryempjob
10ashok 1000Security guard
20arunkumar2000employ
30akshay3000Team Leader
40saicharan4000HR
  1. where :
    1. syntax : select * from tablename where condition ;
    2. Ex : select * from employee where empno=10 ;
      empnoempnamesalaryempjob
      10ashok 1000Security guard
    3. Ex : select * from emloyee where empno>=20 ;
      empnoempnamesalaryempjob
      20arunkumar2000employ
      30akshay3000Team Leader
      40saicharan4000HR
  2. order by :
    1. This clause is using for displaying the data in the form of orders.
    2. The orders are ascending order or descending order .
    3. Ascending order is nothing but small to big , descending order is nothing but big value to small.
    4. syntax : select * from employee order by salary asc ;
      empnoempnamesalaryempjob
      10ashok 1000Security guard
      20arunkumar2000employ
      30akshay3000Team Leader
      40saicharan4000HR
    5. syntax : select * from employee order by salary desc ;
      empnoempnamesalaryempjob
      40saicharan4000HR
      30akshay3000Team Leader
      20arunkumar2000employ
      10ashok 1000Security guard
  3. group by :
    1. This function is using for displaying the data in the form of selecting multiple columns depending on one column.
    2. syntax : select * from employee group by salary ;
      Ex : select sum(salary) , count(salary ) from employee group by salary ;
  4. Having :
    1. This clause is using for finding the result of the given having condition.
    2. This will display the result of who are satisfy the condition.
    3. select * from employee having sno=10 ;
  5. starting with :
    1. Using with clause we can find out whether the given string is starting with the given character or not .
    2. We give the character as a argument in a single code.
    3. syntax : select * from employee where empname starting with('a');
      empnoempnamesalaryempjob
      10ashok 1000Security guard
      20arunkumar2000employ
      30akshay3000Team Leader


Table set commands :

  1. union :
    1. This command is using for when we want to combining the two tables union can display , display the data of same in two tables.
    2. syntax : select * from student union select * from student1 ;
  2. union all :
    1. Union all is nothing but combining the two tables given matched values or non-matched values.
    2. syntax : select * from student union all select * from student1 ;
      //combining the 2 tables with no conditions .
  3. intersect :
    1. intersect is using for combining the two tables as well as what are the same values contained in 2 tables.
    2. intersect will display the data should containing two tables.
    3. syntax : select * from table1 intersect select * from table2 ;
  4. minus :
    1. Minus is using for display in the 2 tables data which values does not match with the two tables that will display .
    2. syntax : select * from student minus select * from student1 ;

  • copying the data from one table to another table .
  • This is using for when we want create 2nd table with the first table structure .
  • In general if we want to create with same values means we have to create another table .
  • Oracle provides one feature copying the table entire table and structure with values.
  • No need to create the tables and inserting the values for the 2nd table.
  • In the second table contains all the rows and values should be same with the first table name.
  • When we copy the table & the structure also copied.
  • When we execute the desc command both structure are same.
  • syntax : create table tablename as select * from tablename ;
    Ex : create table student1 as select * from student ;


Tab :

  • This command is using for displaying the all tables information available in the oracle server.
  • It will display the all tablenames
  • syntax : select * from tab ;


User_Objects :

  • This command is using for displaying the all tables and views , which are available in the oracle server.
  • It is not only display the tablenames and also all those details it will give.
  • syntax : select * from user_objects ;


Opening the applications from the oracle editor :

  1. sql plus ------ console editor
  2. sql plusw ----- GUI editor
  3. select sql+plus


Views :

  1. view is nothing but table .
  2. When we want to create one table with same values then go for views.
  3. View accupies less memory then compare to table memory.
  4. We can perform all operations on views also.
  5. syntax : create view viewname as select * from tablename ;
    Ex : create view student 1 as select * from student ;
  6. syntax : insert into viewname values(c1value , c2value);
  7. syntax : delete viewname where condition ;
  8. syntax : drop view viewname ;
  9. syntax : update viewname set columnname=value where condition ;


Joins :

  1. Joins are using for combining the two tables information.
  2. When we want to see the 2 tables information at a time then go for joins.
  3. After joining the tables it will display the 2nd table information at a time in a single table .
  4. After joining the tables entire information in one table.
  5. Joins are 3 types :
    1. self join
    2. equi join
    3. non-equi join
    1. self join :
      1. self join is nothing but combining the same table with the 2 same names.
      2. syntax : select * from emp , emp ;
    2. equi join :
      1. Using this joins , we can combine the two tables with the equal condition.
      2. When we join this 2 tables before we will give the condition.
      3. After satisfy the condition only it will show the records.
      4. syntax : select e.empno , e.name , e.job , k.empno , k.ename , k.job from emp e , emp k where k.empno=e.empno ; // e & k are alias names
    3. non-equi join :
      syntax : select e.empno , e.ename , e.job , k.empno , k.ename , k.job from emp e , emp k where k.empno< > e.empno ;


Rename the Table :

  1. If we want to change the table name from one into another name then go for this cmd .
  2. After rename in the table old name is does not appear.
  3. The new name will be apply to table.
  4. syntax : rename oldtablename to newtablename ;
    Ex : rename emp to emp1 ;


Opening application from sql * plus :

  1. syntax : $ applicationname
  2. Ex1 : $ notepad
  3. Ex2 : $ calc
  4. Ex3 : $ cmd //console or dos prompt opened


Executing multiple queries from a file to sql * plus editor :

  1. Using this procedure we can execute group of queries in a single statement.
  2. In generally editor we can execute only one query at a time.
  3. Using with sql editor , we can't execute group of queries at a time .
  4. If we want to execute group of queries in the form of file is possible .
  5. We write the all no. of queries as a file , we save that file as filename.sql
  6. sql is the execution of oracle programs
  7. Steps for executing the file :
    1. open the notepad
    2. write the first query
    3. after complete the query put the(/) instead of ( ; )
    4. after put the slash , write the 2nd query , later 2nd query put the slash.
    5. like this procedure how many queries we have
    6. After completing the queries , close the file , before closing the file save the file name as filename.sql
    7. Then we open the sql * plus editor.
    8. we give the path of the file.
    9. we execute the file in the editor.
      @filepath
      @c://commands.sql


Bind Variables :

  1. This variables are using for giving the values to the where conditions.
  2. At runtime to allocate the values to the variables.
  3. If we directly assign the values it will execute only one time.
  4. If we want to execute 2nd time , then we have to write the entire condition.
  5. general : select * from emp where sal=100 ;
    Use bind variable : select * from emp where sal='&sal' ;
  6. Characters (use bind variable) : select * from emp where ename='&name' ;


vsize( ) function :

  1. This function is using for displaying the size of the perticular variable .
  2. It will display the output of how many bytes accupied by the variable.
  3. syntax : select vsize(value) from dual ; //value=100


User environment : userenv( ) function :

  1. This function is using for describing the details of the user.
  2. This function as 2 arguments for every argument , it will display 1 output
  3. The arguments are terminal & language
  4. syntax : select userenv('terminal') , userenv('language') from dual ;


Set Command :

  1. set space n : This cmd is using for giving the spaces to the columns
  2. If we want to give space between the column names then go for space cmd. This default column space is "Zero"
  3. The highest column space is 10
  4. set the n value is 0 to 10
  5. set space n ;
    Ex : set space 5 ;
  • giving column names to the functions after executing .
  • In general when we execute function it will display the column name like function name only.
  • If we want to give the column name to the function then you can give.
  • syntax : select function-name variable-name from anytable ;
    Ex : select max(sal) highest from dual ;

PL/SQL :

  1. pl/sql is nothing but procedural language/sql , This language is using for writing the programs in the sql editor.
  2. We can write the all programs like C , C++ .... this language also provides variables data types , program is nothing but executing set of instructions , statement is nothing but group of words .
  3. We write pl/sql programs in sql * plus editor , sql * plus editor is using for writing pl/sql programs.
  4. In generally once we write the program can be compile & run the programs.
  5. In pl/sql programs after writing the program we save the file name as filename.sql
    add.sql OR sub.sql
  6. We can write the pl/sql programs in notepad also , after writing into the notepad , we save the filename as filename.sql
  7. We can save the file in oracle server Or we can save the file into our own given path like c,d,e drives.
  8. After saving the file we will execute the programs , After executing the program we can see tha output, But in pl/sql after executing the program we can't see the output.But program is executed
  9. Why because we want to see the output , we have to set the command is like "set serveroutput on" ;
    When serveroutput cmd is on it will display the output .
  10. When serveroutput is off , at the time output is not displayed.
  11. We have to said these settings before executing the pl/sql program .
  12. If we said after executing the program there is no use , before executing if we have errors in the file , it will show those are called compiletime errors .
  13. After clearing the errors we can executing the program.
  14. Using with the pl/sql we can do with the conditional statements.
    1. if
    2. if else
  15. control statements
    1. while
    2. do while
    3. for loop

Data Types in pl/sql :

  1. number
  2. varchar
  3. varchar2
  4. date
Structure of the pl/sql program : ......................
In the above structure describes how to write the pl/sql programs
  1. declare block :
    1. In this block we can declare the variables , we can declare the variables with the help of some specified data type.
    2. Every variable should have a one data type , every variable declaration ends with the semicolon.
    3. semicolon indicates end of the statement.
  2. begin block :
    1. This block is using writing the real code of the program.
    2. In begin part , we write the program & task of the program.
    3. For completion of this task it will use the declare part .
    4. Using the declare part is nothing but using the variables .
  3. end block : end is nothing but indicating the our pl/sql program is over .
  4. ' / ' is nothing but start the execution of the pl/sql program.It is for oracle server , not for pl/sql program.

How to declare the variable in pl/sql :

variablenamedatatype ;
xnumber ;
namevarchar(20) ;
jdatedate ;


Installing the variable in pl/sql :

variablenamedatatype :=value ;
xnumber:=30 ;
yvarchar(20) :='hai' ;
ddate:='10-may-13' ;


Output function in pl/sql : dbms_output.put_line( ) ;
  1. This is output procedure in pl/sql , it is using for displaying the out to the user.
  2. When we print the values Or when we want to display some information to the user using this procedure only.
  3. dbms_output is a package name , put_line is a function name

Write a one pl/sql program for display the welcome message :

  1. open the notepad
  2. declare begin dbms_output.put_line("welcome"); end; /
  3. save filename as "file.sql"
  4. close the file
  5. sql< @filepathc:\file.sql
    set serveroutput on
    output :welcome

If statement :

  1. It is one type of conditional statement , it is using for comparing the 2 values Or comparing the 2 variables.
  2. if condition is satisfied Or condition is true then if block will be executed.
  3. if condition is false then does not execute any code.
  4. if condition is true we get the output .
  5. if condition is false we have no output.