Oracle Interview Questions

How to find text in oracle stored procedure

USE : user_source table
select * from user_source where type = ‘PROCEDURE’ and lower(text) like lower(‘%text%’);

Oracle Articles Oracle Interview Questions

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;

Oracle 9i Tutorial Oracle Interview Questions

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 Oracle Interview Questions

Oracle 9i Tutorial : What is syntax of DELETE Statement Syntax

You can remove existing rows by using the DELETE statement.

Delete Statement Syntax below :

DELETE [FROM] table

[WHERE condition];

Oracle 9i Tutorial Oracle Interview Questions

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 Oracle Interview Questions

Oracle 9i Tutorial : INSERT Statement Syntax

INSERT Statement Syntax

INSERT statement is used to add new rows to table.

Syntax of INSERT statement below :

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 Oracle Interview Questions

Oracle 9i Tutorial : Data Manipulation Language

Data Manipulation Language ( DML )

A DML statement is executed when you:

–Add new rows to a table

–Modify existing rows in a table

–Remove existing rows from a table

Transaction

A transaction consists of a collection of DML statements that form a logical unit of work.

Oracle 9i Tutorial Oracle Interview Questions

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 Oracle Interview Questions

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 Oracle Interview Questions

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;

Oracle DECODE function

Syntax :

Example :

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

CREATE TABLE

ALTER TABLE

DROP TABLE

RENAME

You can remove existing rows by using the DELETE statement.

Delete Statement Syntax below :

DELETE [FROM] table

[WHERE condition];

UPDATE Statement Syntax

Updating Rows in a Table

Updating Rows Based on Another Table

INSERT Statement Syntax

Data Manipulation Language ( DML )

A DML statement is executed when you:

–Add new rows to a table

–Modify existing rows in a table

–Remove existing rows from a table

Transaction

A transaction consists of a collection of DML statements that form a logical unit of work.

Adding a Comment to a Table

DROP TABLE

RENAME

TRUNCATE

ALTER TABLE Statement

You Missed