PL/SQL Interview Questions with Answers Page I


From freshersonline.com

Jump to: navigation, search

Interview Question Home


1. What's SQL

SQL is an English like language consisting of commands to store, retrieve, maintain & regulate access to your database.


2. What's NVL

NVL : Null value function converts a null value to a non-null value for the purpose of evaluating an expression. Numeric

Functions accept numeric I/P & return numeric values. They are MOD, SQRT, ROUND, TRUNC & POWER.


3. What's Date Functions

Date Functions are ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN & SYSDATE.


4. What's Character Functions

Character Functions are INITCAP, UPPER, LOWER, SUBSTR & LENGTH. Additional functions are GREATEST & LEAST. Group Functions

returns results based upon groups of rows rather than one result per row, use group functions. They are AVG, COUNT, MAX,

MIN & SUM.


5. What's TTITLE & BTITLE

TTITLE & BTITLE are commands to control report headings & footers.


6. What's COLUMN

COLUMN command define column headings & format data values.


7. What's BREAK

BREAK command clarify reports by suppressing repeated values, skipping lines & allowing for controlled break points.


8. What's COMPUTE

command control computations on subsets created by the BREAK command.


9. What's SET

SET command changes the system variables affecting the report environment.


10. What's SPOOL

SPOOL command creates a print file of the report.


11. What's JOIN

JOIN is the form of SELECT command that combines info from two or more tables.

Types of Joins are Simple (Equijoin & Non-Equijoin), Outer & Self join.

Equijoin returns rows from two or more tables joined together based upon a equality condition in the WHERE clause.

Non-Equijoin returns rows from two or more tables based upon a relationship other than the equality condition in the WHERE clause.

Outer Join combines two or more tables returning those rows from one table that have no direct match in the other table.

Self Join joins a table to itself as though it were two separate tables.


12. What's Union

Union is the product of two or more tables.


13. What's Intersect

Intersect is the product of two tables listing only the matching rows.


14. What's Minus

Minus is the product of two tables listing only the non-matching rows.


15. What's Correlated Subquery

Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be

Select, Update or Delete. Use CRSQ to answer multipart questions whose answer depends on the value in each row processed by parent

statement.


16. Explain CONNECT BY PRIOR?

Retrieves rows in hierarchical order eg.

select empno, ename from emp where.


17. Difference between SUBSTR and INSTR?

INSTR (String1, String2 (n, (m)),

INSTR returns the position of the m-th 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 n-th position of string1.


18. Explain UNION, MINUS, UNION ALL and INTERSECT?

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.


19. What is ROWID?

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


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

Using ROWID.

CONSTRAINTS

Personal tools