SQL Plus

ROWID is a pseudo column generated at the run time(during inserting value). Its a hexadecimal value which is unique identification for each record

Retrieves rows in hierarchical order.e.g. select empno, ename from emp where.

"connect by prior" is clause which is used in hierarchical queries.Example

select ename,empno,mgr,job

from emp

start with job='PRESIDENT'

connect by prior empno=mgr;

Database Link is a named path through which a remote database can be accessed

%IS OPEN,%FOUND,%NOT FOUND,%ROW COUNT

Correlated subquery is the subquery, where the subquery has been executed for the every row processed by the parent statement.

Drop Index Indexname

CHAR,

VARCHAR2,

NUMBER,

DATE,

RAW,

LONG and

LONG RAW.

select empno||' '||ename from emp;

select replace (column name,',','') from table name.

if a view is update, deleted , or inserted wil the changes be refelected on the base table

TOO_MANY_ROWS

NO_DATA_FOUND

INVALID_CURSORS

CURSOR_ALREADY_OPEN

DUP_VAL_ON_INDEX

A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.

Integrity constraint is a rule that restricts values to a column in a table.

Use the DISTINCT keyword right after SELECT...

i.e. SELECT DISTINCT customername FROM customer

select * from emp e where rownum=

(select max(rownum) from emp ee

where e.empno=ee.empno)

For sorting we use order by clause in select statement. This is used to sort data in ascending order or descending order.

To group data based on perticulr column we use groupby clause.

Both are used to get distinct values.

Insert/Update/Delete :- 3

Before/After:- 2

Row Level/Statement Level:-2

Hence 3*2*2

This is called dynamic SQL. An example would be:

set lines 90 pages 0 termout off feedback off verify off

spool drop_all.sql

select ?drop user ?||username||? cascade;? from dba_users

where username not in ("SYS?,?SYSTEM?);

spool off

Essentially you are looking to see that they know to include a command (in this case DROP USER...CASCADE;) and that you need to concatenate using the ?||? the values selected from the database.

SIGN (a): Returns 1 if a is positive or if a is 0, and -1 if a is less than 0.

A scrollable cursor, however, can move forward and backward, and can seek any desired record in the cursor. Such operations are common in applications that present results sets in scrolling windows. With a scrollable cursor, application developers do not need to create and manage their own buffer for the records.