Friday, March 11, 2011

SQL*Plus and SQL - Part 3


Display the number value in Words
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
the output like,
SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal "Salary ",
(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))
"Sal in Words" from emp
/
Salary Sal in Words
------- ------------------------------------------------------
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.

Display Odd/ Even number of records
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6


Which date function returns number value?
months_between


Any three PL/SQL Exceptions?
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others

What are PL/SQL Cursor Exceptions?
Cursor_Already_Open, Invalid_Cursor


Other way to replace query result null value with a text
SQL> Set NULL ?N/A?
to reset SQL> Set NULL ??


What are the more common pseudo-columns?
SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM


What is the output of SIGN function?
1 for positive value,
0 for Zero,
-1 for Negative value.

What is Cursor ?

A Cursor is a handle ( a name or pointer) for the memory associated with a specific statement

What is the maximum number of triggers, can apply to a single table?
12 triggers

What is a Procedure ?

A Procedure consist of a set of SQL and PL/SQL statements that are grouped together as a unit to solve a specific problem or perform a set of related tasks.

What is difference between Procedures and Functions ?

A Function returns a value to the caller where as a Procedure does not.

What is a Package ?

A Package is a collection of related procedures, functions, variables and other package constructs together as a unit in the database.

What are the advantages of having a Package ?

Increased functionality (for example,global package variables can be declared and used by any proecdure in the package) and performance (for example all objects of the package are parsed compiled, and loaded into memory once)

What are the uses of Database Trigger ?

Database triggers can be used to automatic data generation, audit data modifications, enforce complex Integrity constraints, and customize complex security authorizations.

What are the differences between Database Trigger and Integrity constraints ?

A declarative integrity constraint is a statement about the database that is always true.
A constraint applies to existing data in the table and any statement that manipulates the table.

A trigger does not apply to data loaded before the definition of the trigger, therefore, it does not guarantee all data in a table conforms to the rules established by an associated trigger.

A trigger can be used to enforce transitional constraints where as a declarative integrity constraint cannot be used.


What is an Integrity Constrains ?

An integrity constraint is a declarative way to define a business rule for a column of a table.

Describe the different type of Integrity Constraints supported by ORACLE ?

NOT NULL Constraint - Disallows NULLs in a table's column.
UNIQUE Constraint - Disallows duplicate values in a column or set of columns.
PRIMARY KEY Constraint - Disallows duplicate values and NULLs in a column or set of columns.
FOREIGN KEY Constrain - Require each value in a column or set of columns match a value in a related table's UNIQUE or PRIMARY
KEY.
CHECK Constraint - Disallows values that do not satisfy the logical expression of the
constraint.

What is difference between UNIQUE constraint and PRIMARY KEY constraint ?

A column defined as UNIQUE can contain NULLs while a column defined as
PERIMETER KEY can't contain Nulls.

What is the maximum number of CHECK constraints that can be defined on a column ?

No Limit.