Category: Oracle Interview Questions

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.

select
decode (
floor,
‘GF’,’Ground Floor’,
‘FF’,’First Floor’,
‘SF’,’Second Floor’,
‘TF’,’Third Floor’,
‘NOT KNOWN’
)
from
apartment;

What are few Oracle DDL statements or commands ?

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

CREATE TABLE

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

ALTER TABLE

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

DROP TABLE

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

RENAME

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

TRUNCATE

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

COMMENT

  •  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 : INSERT Statement Syntax

INSERT Statement Syntax

INSERT statement is used to add new rows to table.


Syntax of INSERT statement below :

INSERT Statement Syntax









Only one row can be inserted at a time with this syntax.

INSERT statement can also be used for copying rows from another table.

Syntax below :

INSERT INTO table1 ( column11 , column12 , ….. )
SELECT column21 , column22 , …….
FROM
table2

 Example below :

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:
ALL_COL_COMMENTS
USER_COL_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
Syntax
    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 : Drop,Rename and Truncate table

DROP TABLE

  Remove rows and a table structure.
  Once executed, this statement cannot be rolled   back.
  -DROP TABLE EMPLOYEE

RENAME

  Rename a table, view, sequence, or synonym.
  -RENAME EMPLOYEE TO EMPL;

TRUNCATE

  Remove all rows from a table and release the   storage space used by the table.
  The DELETE statement removes only rows.
  -TRUNCATE TABLE EMPLOYEE;

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;