DBMS ( 6 )

501. What are the cursor attributes used in PL/SQL ?

%ISOPEN - to check whether cursor is open or not
% ROWCOUNT - number of rows fetched/updated/deleted.
% FOUND - to check whether cursor has fetched any row. True if rows
are fetched.
% NOT FOUND - to check whether cursor has fetched any row. True if
no rows are featched.
These attributes are proceeded with SQL for Implicit Cursors and
with Cursor name for Explicit Cursors.

502. What are % TYPE and % ROWTYPE ? What are the advantages of
using these over datatypes?

% TYPE provides the data type of a variable or a database column to
that variable.
% ROWTYPE provides the record type that represents a entire row of a
table or view or columns selected in the cursor.
The advantages are :
I. Need not know about variable's data type
ii. If the database definition of a column in a table changes, the
data type of a variable changes accordingly.

503. What is difference between % ROWTYPE and TYPE RECORD ?

% ROWTYPE is to be used whenever query returns a entire row of a
table or view.
TYPE rec RECORD is to be used whenever query returns columns of
different table or views and variables.
E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %
type );
e_rec emp% ROWTYPE
cursor c1 is select empno,deptno from emp;
e_rec c1 %ROWTYPE.

504. What are the different types of PL/SQL program units that can
be defined and stored in ORACLE database ?

Procedures and Functions,Packages and Database Triggers.

505. 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)

506. 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.

507. 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.

508. 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.

509. What is difference between Procedures and Functions ?

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

510. What is Database Trigger ?

A Database Trigger is procedure (set of SQL and PL/SQL statements)
that is automatically executed as a result of an insert in,update
to, or delete from a table.

511. Can the default values be assigned to actual parameters?

Yes

512. Can a primary key contain more than one columns?

Yes

513. What is an UTL_FILE.What are different procedures and functions
associated with it?

UTL_FILE is a package that adds the ability to read and write to
operating system files. Procedures associated with it are FCLOSE,
FCLOSE_ALL and 5 procedures to output data to a file PUT, PUT_LINE,
NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE.
Functions associated with it are FOPEN, ISOPEN.

514. What are ORACLE PRECOMPILERS?

Using ORACLE PRECOMPILERS, SQL statements and PL/SQL blocks can be
contained inside 3GL programs written in C,C++,COBOL,PASCAL,
FORTRAN,PL/1 AND ADA. The Precompilers are known as
Pro*C,Pro*Cobol,... This form of PL/SQL is known as embedded
pl/sql,the language in which pl/sql is embedded is known as the host
language. The prcompiler translates the embedded SQL and pl/sql
ststements into calls to the precompiler runtime library.The output
must be compiled and linked with this library to creater an
executable.

515. Differentiate between TRUNCATE and DELETE?

TRUNCATE deletes much faster than DELETE
TRUNCATE
DELETE
It is a DDL statement
It is a DML statement
It is a one way trip,cannot ROLLBACK
One can Rollback
Doesn't have selective features (where clause)
Has
Doesn't fire database triggers
Does
It requires disabling of referential constraints.

516. What is difference between a formal and an actual parameter?

The variables declared in the procedure and which are passed, as
arguments are called actual, the parameters in the procedure
declaration. Actual parameters contain the values that are passed to
a procedure and receive results. Formal parameters are the
placeholders for the values of actual parameters

517. What should be the return type for a cursor variable.Can we use
a scalar data type as return type?

The return type for a cursor must be a record type.It can be
declared explicitly as a user-defined or %ROWTYPE can be used. eg
TYPE t_studentsref IS REF CURSOR RETURN students%ROWTYPE

518. What are different Oracle database objects?

-TABLES
-VIEWS
-INDEXES
-SYNONYMS
-SEQUENCES
-TABLESPACES etc

519. What is difference between SUBSTR and INSTR?

SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4)
output BCDE INSTR provides character position in which a pattern is
found in a string. eg INSTR('ABC-DC-F','-',2) output 7 (2nd
occurence of '-')

520. 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.

521. What is difference between SQL and SQL*PLUS?

SQL*PLUS is a command line tool where as SQL and PL/SQL language
interface and reporting tool. Its a command line tool that allows
user to type SQL commands to be executed directly against an Oracle
database. SQL is a language used to query the relational database
(DML,DCL,DDL). SQL*PLUS commands are used to format query result,
Set options, Edit SQL commands and PL/SQL.

522. What are various joins used while writing SUBQUERIES?

Self join-Its a join foreign key of a table references the same
table. Outer Join--Its a join condition used where One can query all
the rows of one of the tables in the join condition even though they
don't satisfy the join condition.
Equi-join--Its a join condition that retrieves rows from one or more
tables in which one or more columns in one table are equal to one or
more columns in the second table.

523. What a SELECT FOR UPDATE cursor represent.?

SELECT......FROM......FOR......UPDATE[OF column-reference][NOWAIT]
The processing done in a fetch loop modifies the rows that have been
retrieved by the cursor. A convenient way of modifying the rows is
done by a method with two parts: the FOR UPDATE clause in the cursor
declaration, WHERE CURRENT OF CLAUSE in an UPDATE or declaration
statement.

524. What are various privileges that a user can grant to another
user?

-SELECT
-CONNECT
-RESOURCES

525. Display the records between two range?

select rownum, empno, ename from emp where rowid in (select rowid
from emp where rownum <=&upto minus select rowid from emp where rownum<&Start); 526. minvalue.sql Select the Nth lowest value from a table? select level, min('col_name') from my_table where level = '&n' connect by prior ('col_name') < 'col_name') group by level; Example: Given a table called emp with the following columns: -- id number -- name varchar2(20) -- sal number -- -- For the second lowest salary: -- select level, min(sal) from emp -- where level=2 -- connect by prior sal < dept="123" course="101;" current_credits="current_credits+v_numcredits" dv="tb.dv);" level =" '&n'"> 'col_name')
group by level;
Example:
Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- For the second highest salary:
-- select level, max(sal) from emp
-- where level=2
-- connect by prior sal > sal
-- group by level

590. Find out nth highest salary from emp table?

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT
(DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
For Eg:-
Enter value for n: 2
SAL
---------
3700

591. Suppose a customer table is having different columns like
customer no, payments.What will be the query to select top three max
payments?

SELECT customer_no, payments from customer C1
WHERE 3<=(SELECT COUNT(*) from customer C2
WHERE C1.payment <= C2.payment)

592. How you will avoid your query from using indexes?

SELECT * FROM emp
Where emp_no+' '=12345;
i.e you have to concatenate the column name with space within codes
in the where condition.
SELECT /*+ FULL(a) */ ename, emp_no from emp
where emp_no=1234;
i.e using HINTS