HOME ABOUT US PRODUCTS SERVICES SUPPORT DOWNLOADS ORDER NOW
 

 

 Contact Us  |  Help  |  FAQ  |  Updates  |  File Uploader

  ADMIN LOGIN
 

 
 
SQL COMMANDS FOR ORACLE

 

 
CREATE TABLE
 
                       Syntax :  Create table table_name (col_1 datatype,
                                                                                        Col_2 datatype,
                                                                                            …….
                                                                                                  …….);
 
                       Example :->
                                       Create table employees ( name varchar2(25) not null,
                                                                                       Emp_id varchar2(10),
                                                                                       Dept_no number(2),
                                                                                       Salary number(6),
                                                                                       Hire_date date
                                                                                                                  );
ALTER TABLE
To Modify A Column:-
                       Syntax:-    Alter table table_name modify col_name datatype.
                       Example:-  Alter table employees modify varchar2(25);
 

To Rename A Table:-
                       Syntax:-    Alter table table_name rename to new_name;
                       Example:-  Alter table employees rename to emp;
 

To Rename A Column:-
                       Syntax:-   Alter table table_name RENAME COLUMN col_name to
                                        New_name;
                       Example:-  Alter table employees RENAME COLUMN dept_no to
                                         Dept_id;
     
CREATE INDEX:-
                       Syntax:-    Create index index_name ON table_name(col_1,col_2,…….);
                       Example:-  Create index emp_idx ON employees(emp_id);
 
COMPARISON CONDITIONS:-
Between Condition:-
                      Example:- Select * from employees where salary between 5000 and 15000;

 

NULL Condition:-
                       Example:-  Select * from employees where dept_no IS NULL;
 

Like Operator:-
                       Example:-  Select * from employees where name like ‘A%’;
                The above query will show the rows having name starting with letter ‘A’
 

In Condition:-
                       Example:-  Select * from employees where salary IN(5000,10000,15000);
 

NOT IN Condition:-
                       Example:-  Select * from employees where salary NOT IN(5000,10000);
 
CREATING USER:-              
                       Syntax:-     Create user user_name identified by password;
                       Example:-   Create user sysadm identified by sysadm;
 

Granting Privilges To A User:-
                        Syntax:-     Grant priv_name to user_name;
                        Example:-   Grant DBA to SYSADM;
 
GROUP FUNCTIONS AND GROUP BY CLAUSE:-

Max(), Min(), Avg() and count() functions:-
Example:-           Select max(salary), min(salary), avg(salary) from employees;

Example:-           Select count(*) from employees;
Example:-           Select count(dept_no) from employees;
 
Group by clause:-          
                       Example:- Select dept_no, max(salary) from employees group by dept_no;
                 
Having Clause:-
                       Example:-  Select dept_no,max(salary) from employees group by dept_no
                                                Having sum(salary)<50000;
 
INSERTING AND UPDATING TABLES:-            
Insert Into Tables:-
                        Syntax:-    Insert into table_name(col_1, col_2,…..)
                                                         Values(value1,value2,…….);
                        Example:-  Insert into employees values(‘abc’,’sys_1’,1,10000,’
                                                     10-mar-2004’);
               

Updating tables:-
                        Syntax:-    Update table_name col_1=value, col_2=value……;
                        Example:-  Update employees
                                         Set emp_id=’sys_1’, dept_no=1 where name=’TOM’;
 
 SELECTING ROWS FROM MULTIPLE TABLES:-                           
                         Example:-  Select name, emp_id,salary,d.dept_no, dept_name
                                          From employees E, departments D
                                          Where e.dept_no=d.dept_no;
The above query will show the employee name, salary, department no and department name of all the employees.
Aliases E for table employees and D for table departments is used for avoiding error of column ambiguity because dept_no is common in both tables.
The condition e.dept_no=d.dept_no is used for avoiding duplicate records.
 
DELETING DUPLICATE RECORDS FROM A TABLE:-    
Example1:- Suppose you want to delete the rows from employees table having duplicate values for NAME. Then use the following query:-

Delete from employees e1 where rowid!=(select max(rowid) from employees e2 where e1.name=e2.name);
 
And if you want to delete duplicate emp_id also then->
Delete from employees e1 where rowid!=(select max(rowid) from employees e2 where e1.name=e2.name AND e1.emp_id=e2.emp_id);
 
QUERING INFORMATION OF TABLES AND INDEXES:- 
Tables’s information:-
Example  Select table_name from user_tables;
The above query will show the names of all the tables in the schema.
 
Select table_name from user_tables where table_name like ‘A%’;
The above query will show the tables having name starting with letter ‘A’.
 

Tables’s structure information:-

Example  Suppose you want to get structure(column name, data types etc) of a table Trnmast, then use the following command:-

 
Select column_name, data_type, data_length, data_precision from user_tab_columns where table_name=’TRNMAST’;
               

Index’s Information:-
Example:-  Select index_name from user_indexes where table_name=’TRNMAST’;
The above query will show all the indexes on the table TRNMAST.
If u want to know the information of columns on which index is created for a particular table then :->
Select index_name, column_name, column_position from user_ind_columns where table_name=’TRNMAST’;
 
ADDING DATAFILES:-                        

Alter tablespace SYSTEM add datafile ‘d:\oracle\oradata\db_name\system02.dbf’ size 50M;
 
CREATING ROLLBACK SEGMENTS AND VIEWING INFO:-
       Example:-  Create rollback segment rbs0 storage(maxextents unlimited);
 
        Select segment_name from dba_segments where segment_type =’ROLLBACK’;
 

Extending Rollback Segment :-
         Alter rollback segment rbs0 storage(maxextents unlimited);
 
INSERTING ROWS FROM ONE TABLE TO ANOTHER
         Example:-
         Create table emp1 as Select * from employees;
         You can also create a table by selecting specified columns from a table
         By the following statement:->
         Create table emp_nm as Select name from employees;
SINGLE ROW FUNCTIONS

CHARACTER FUNCTIONS:-

Lower(column/expression)          Converts alpha character values to Lowercase.

                                                           

UPPER(column/expression)         Converts alpha character values to Uppercase.

                                                           

INITCAP(column/expression)      Converts alpha character values to Uppercase for the first

                                                  letter of each World, All other letters in Lowercase.

                                                     

SUBSTR(column/exp,m[,n])        Returns specified characters from Character value

                                                 starting at character Position m, n characters long. If m

                                                 is Negative, the count starts from the end of character

                                                 value.

                                                 

LENGTH(column/exp)                 Returns the number of characters in the Expression.

                                                           

INSTR(column/exp,’string’,         Returns the numeric position of a named string. Optionally

                          [,m],[n])          you can provide a position  m to start searching and

                                                 occurrence n of the string.

                                                                       

LPAD(column,/exp,n,’string’)       Pads the character value right justified to a Total width of

                                                 n character positions.

RPAD(column/exp,n,’string’)        Pads the character value left justified to a Total width of

                                                  n character positions.
TRIM(leading/trailing/both,          Enable you to trim heading or trailing 
character from source)

NUMBER FUNCTIONS:-

ROUND(column/exp,n)                Round the column, expression or value to n decimal

                                                 Decimal places. If n is omitted, no decimal places. If n is

                                                 is negative numbers to the left of decimal point are

                                                  rounded.

                                                            

TRUNC(column/exp,n)                 Truncates the column, expression or value to n decimal

                                                  places. If n is omitted then n Defaults to zero.

                                                                       

MOD(m,n)                                   Returns the remainder of m divided n.

CEIL(column/exp)                        Round the number to the upper limit.

 

DATE FUNCTIONS:-

                                                    
MONTHS_BETWEEN(date1,           Finds the number between date1 and date2 Result           

                 ,date2)                                  can be negative or positive.

ADD_MONTHS(date,n)                 Adds n number of months to date. N must be

                                                 Integer negative or positive.

NEXT_DAY(date,’char’)                Finds the date of the next specified day of

                                                 The week following date.

LAST_DAY(date)                         Finds the date of the last day of the month

                                                 That contains date.

ROUND(date[,’fmt’])                    Returns date rounded to the unit specified by The format

                                                  model fmt. Fmt may be day, Month or year.

                                                                       

TRUNC(date[,’fmt’])                    Returns date with the time portion of the day Truncated

                                                 to the unit specified by fmt.

 

CONVERSION FUNCTIONS:-

TO_CHAR(number/date,[fmt])     Convert a number or date value to character String with

                                                  format model fmt.

TO_CHAR(char,[fmt])                 Converts a character string containing digits to a number

                                                  in the format specified by the

                                                                       

TO_DATE(char,[fmt])                  Converts a character string representing a date to a

                                                 date value according to the fmt.

    

GERNAL FUNCTIONS:-       

NVL(expr1,expr2)                         Converts a null value to an actual value.

NVL2(expr1,expr2,expr3)              If expr1 is not null, it returns expr2. If expr1 is null,

                                                    it  returns expr3.

NULLIF(expr1,expr2)                     Compares two expressions and returns null if they

                                                   are equal or the first exp if they are not equal.

                                                                       

COALESCE(exp1,exp2,..expn)      Returns the first non-null expression in the expression list

                                                                       

DECODE(col/exp,search1,result1   The DECODE function decodes an exp  in a way similar
,search2,result2…)                                to the if-then-else logic