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

2. How to Select last N records from a Table?

select * from (select rownum a, CLASS_CODE,CLASS_DESC from clm)
where a > ( select (max(rownum)-10) from clm)

Here N = 10

The following query has a Problem of performance in the execution of the following query where the table ter.ter_master have 22231 records. So the results are obtained after hours.

Cursor rem_master(brepno VARCHAR2) IS
select a.* from ter.ter_master a
where NOT a.repno in (select repno from ermast) and
(brepno = 'ALL' or a.repno > brepno)
Order by a.repno

What are steps required tuning this query to improve its performance ?

- Have an index on TER_MASTER.REPNO and one on ERMAST.REPNO

- Be sure to get familiar with EXPLAIN PLAN. This can help you determine the execution path that Oracle takes. If you are using Cost Based Optimizer mode, then be sure that your statistics on TER_MASTER are up-to-date. -Also, you can change your SQL to:

SELECT a.*
FROM ter.ter_master a
WHERE NOT EXISTS (SELECT b.repno FROM ermast b
WHERE a.repno=b.repno) AND
(a.brepno = 'ALL' or a.repno > a.brepno)
ORDER BY a.repno;

3. Indexes

           Indexes are optional structures associated with tables used to speed query execution and/or guarantee uniqueness. Create an index if there are frequent retrieval of fewer than 10-15% of the rows in a large table and columns are referenced frequently in the WHERE clause. Implied tradeoff is query speed vs. update speed. Oracle automatically update indexes. Concatenated index max. is 16 columns.

4. TTITLE & BTITLE - controls report headings & footers

5. Examine this code

71. BEGIN
72. theater_pck.v_total_seats_sold_overall := theater_pck.get_total_for_year;
73. END;

For this code to be successful, what must be true?

• Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist only in the body of the THEATER_PCK package.
• Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCK package.
• Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of the THEATER_PCK package.
• Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist in the specification of the THEATER_PCK package.

6. How to implement ISNUMERIC function in SQL *Plus ?

Method 1 :

Select length (translate (trim (column_name),' +-.0123456789',' ')) from dual ;

Will give you a zero if it is a number or greater than zero if not numeric (actually gives the count of non numeric characters)

Method 2 :

select instr(translate('wwww',
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X')
FROM dual;

It returns 0 if it is a number, 1 if it is not.

7. Data types

• Max. columns in a table is 255. Max. Char size is 255, Long is 64K & Number is 38 digits.
• Cannot Query on a long column.
• Char, Varchar2 Max. size is 2000 & default is 1 byte.
• Number(p,s) p is precision range 1 to 38, s is scale -84 to 127.
• Long Character data of variable length upto 2GB.
• Date Range from Jan 4712 BC to Dec 4712 AD.
• Raw Stores Binary data (Graphics Image & Digitized Sound). Max. is 255 bytes.
• Mslabel Binary format of an OS label. Used primarily with Trusted Oracle.

8. What is the difference between Truncate and Delete interms of Referential Integrity ?

DELETE   -  removes one or more records in a table, checking referential Constraints (to see if there are dependent child records) and firing any DELETE triggers. In the order you are deleting (child first then parent) There will be no problems.

TRUNCATE   -  removes ALL records in a table. It does not execute any triggers. Also, it only checks for the existence (and status) of another foreign key Pointing to the table. If one exists and is enabled, then you will get The following error. This is true even if you do the child tables first.
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

You should disable the foreign key constraints in the child tables before issuing the TRUNCATE command, then re-enable them afterwards.

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

10. Integrity

Assures database data and structures reflects all changes made to them in the correct sequence. Locks ensure data integrity and maximum concurrent access to data. Commit statement releases all locks.

Types of locks

Data Locks         - protects data i.e. Table or Row lock.

Dictionary Locks  - protects the structure of database object i.e. ensures table's structure does not change for the duration of the transaction.

Exclusive Lock     - allows queries on locked table but no other activity is allowed.

Share Lock         - allows concurrent queries but prohibits updates to the locked tables.

Row Share         - allows concurrent access to the locked table but prohibits for a exclusive table lock.

Share Update     - are synonymous with Row Share.

Row Exclusive     - same as Row Share but prohibits locking in shared mode.

Shared Row Exclusive - locks the whole table and allows users to look at rows in the table but prohibit others from locking the table in share or updating them.

Internal Locks & Latches  - protects the internal database structures. They are automatic.