Oracle DECODE function

Oracle DECODE function

Oracle decode function allow to transform data values at retrieval time.

Syntax :

DECODE(expr, search, result
[, search, result ]...
[, default ]

 Example :

 We have a column named Floor, with values of GF, FF, SF and TF. When we run SQL queries, we want to transform these values into Ground Floor, First Floor, Second Floor and Third Floor.

decode (
‘GF’,’Ground Floor’,
‘FF’,’First Floor’,
‘SF’,’Second Floor’,
‘TF’,’Third Floor’,

What are few Oracle DDL statements or commands ?

Below are few  DDL commands to create, alter, drop, and rename tables.


  • Create a table.
  • Create a table based on another table by using a subquery.


  • Modify table structures.
  • Change column widths, change column data types, and add columns.


  • Remove rows and a table structure.
  • Once executed, this statement cannot be rolled back.


  • Rename a table, view, sequence, or synonym.


  • Remove all rows from a table and release the storage space used by the table.


  •  Add a comment to a table or a column.
  • Query the data dictionary to view the comment.

Oracle 9i Tutorial : UPDATE Statement Syntax

UPDATE Statement Syntax

Update Statement is used to modify existing rows in table.

Below is Update State Syntax :

UPDATE  table
SET  column = value [, column = value, …]
[WHERE   condition];

In the syntax:

  table  is the name of the table
  column  is the name of the column in the table to populate
  value  is the corresponding value or subquery for the column
  condition  identifies the rows to be updated and is composed of column names   expressions, constants, subqueries, and comparison operators

Updating Rows in a Table

Specific row or rows are modified if you specify the WHERE clause.
   •All rows in the table are modified if you omit the WHERE clause.

Updating Rows Based on Another Table

You can use subqueries in UPDATE statements to update rows in a table. The example on the slide updates the COPY_EMP table based on the values from the EMPLOYEES table. It changes the department number of all employees with employee 200’s job ID to employee 100’s current department number.

Oracle 9i Tutorial : Adding Comments to Table

Adding a Comment to a Table

You can add a comment of up to 2,000 bytes about a column, table, view, or snapshot by using the COMMENT statement. The comment is stored in the data dictionary and can be viewed in one of the following data dictionary views in the COMMENTS column:
    COMMENT ON TABLE table | COLUMN table.column
      IS text‘;
In the syntax:
  table  is the name of the table
column  is the name of the column in a table
text  is the text of the comment
You can drop a comment from the database by setting it to empty string ():
    COMMENT ON TABLE  employees IS ‘;

Oracle 9i Tutorial : ALTER TABLE Statement

 ALTER TABLE Statement

         After you create a table, you may need to change the table structure because: you omitted column, your column definition needs to be changed, or you need to remove columns. You can do this by using the ALTER TABLE statement.
Use the ALTER TABLE statement to:
Add a new column
Alter table employee add(manager number(4));
Modify an existing column
Alter table employee modify(empname varchar2(10));
Define a default value for the new column
Alter table employee modify(hiredate date default sysdate);
Drop a column
                     – Alter table employee drop column manager;