Tuesday, April 12, 2011

SQL*Plus and SQL - Part 2

What is difference between TRUNCATE & DELETE ?

TRUNCATE commits after deleting entire table i.e., can not be rolled back.
Database triggers do not fire on TRUNCATE

DELETE allows the filtered deletion. Deleted records can be rolled back or committed.
Database triggers fire on DELETE.

What is a join ? Explain the different types of joins ?

Join is a query which retrieves related columns or rows from multiple tables.

Self Join - Joining the table with itself.
Equi Join - Joining two tables by equating two common columns.
Non-Equi Join - Joining two tables by equating two common columns.
Outer Join - Joining two tables in such a way that query can also retrive rows that do not have corresponding join value in the other table.

Difference between SUBSTR and INSTR ?

INSTR (String1,String2(n,(m)),
INSTR returns the position of the mth occurrence of the string 2 in string1. The search begins from nth position of string1.

SUBSTR (String1 n,m)
SUBSTR returns a character string of size m in string1, starting from nth postion of string1.


INTERSECT returns all distinct rows selected by both queries.
MINUS - returns all distinct rows selected by the first query but not by the second.
UNION - returns all distinct rows selected by either query
UNION ALL - returns all rows selected by either query,including all duplicates.

What is ROWID ?

ROWID is a pseudo column attached to each row of a table. It is 18 character long, blockno, rownumber are the components of ROWID.

What is the fastest way of accessing a row in a table ?

Using ROWID.

What is difference between CHAR and VARCHAR2 ? What is the maximum SIZE allowed for each type ?

CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR it is 255 and 2000 for VARCHAR2.

How many LONG columns are allowed in a table ? Is it possible to use LONG columns in WHERE clause or ORDER BY ?

Only one LONG columns is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

What are the pre requisites ?
i. to modify datatype of a column ?
ii. to add a column with NOT NULL constraint ?

To Modify the datatype of a column the column must be empty.
To add a column with NOT NULL constrain, the table must be empty.

Where the integrity constrints are stored in Data Dictionary ?

The integrity constraints are stored in USER_CONSTRAINTS.

How will you a activate/deactivate integrity constraints ?

The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE constraint/DISABLE constraint.

If an unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE ?

It won't, Because SYSDATE format contains time attached with it.

How to access the current value and next value from a sequence ? Is it possible to access the current value in a session before accessing next value ?

Sequence name CURRVAL, Sequence name NEXTVAL.

It is not possible. Only if you access next value in the session, current value can be accessed.

What is CYCLE/NO CYCLE in a Sequence ?

CYCLE specifies that the sequence continues to generate values after reaching either maximum or minimum value. After pan ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.

NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum value.

What is a database trigger ? Name some usages of database trigger ?

Database trigger is stored PL/SQL program unit associated with a specific database table.
Usages are Audit data modificateions, Log events transparently, Enforce complex business
rules Derive column values automatically, Implement complex security authorizations.
Maintain replicate tables.

How many types of database triggers can be specified on a table ? What are they ?

Insert Update Delete

Before Row Yes Yes Yes
After Row Yes Yes Yes
Before Statement Yes Yes Yes
After Statement Yes Yes Yes

If FOR EACH ROW clause is specified, then the trigger for each Row affected by the

If WHEN clause is specified, the trigger fires according to the retruned boolean value.

Is it possible to use Transaction control Statements such a ROLLBACK or
COMMIT in Database Trigger ? Why ?

It is not possible. As triggers are defined for each table, if you use COMMIT of
ROLLBACK in a trigger, it affects logical transaction processing.

What are two virtual tables available during database trigger execution ?

The table columns are referred as OLD.column_name and NEW.column_name.
For triggers related to INSERT only NEW.column_name values only available.
For triggers related to UPDATE only OLD.column_name NEW.column_name values only
For triggers related to DELETE only OLD.column_name values only available.

What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?

Mutation of table occurs.

You have just compiled a PL/SQL package but got errors, how would you view the errors?


How to generate query result in Text file through SQL Plus prompt?


SQL>spool c:\emp_output.txt
SQL>select * from emp;
SQL>spool off

A text file with the file name of emp_output.txt will be created in your C:\ drive.

Note: Don't forget to issue "spool off" statement. This "spool off" tells oracle to stop spooling. If you forget, all the statements will be recorded into this file until you close SQL Plus.

People who read this post also read :

No comments:

Post a Comment

Thanks for your comments!