PL/SQL Interview Questions with Answers Page II

From freshersonline.com

Interview Question Home

1. What's Multiple columns

Multiple columns can be returned from a Nested Subquery.

2. What's Sequences

Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating

a sequence number if the transaction is rolled back, then that sequence number is lost.

3. What's Synonyms

Synonyms is the alias name for table, views, sequences & procedures and are created for reasons of Security and Convenience. Two levels are Public - created by DBA & accessible to all the users. Private - Accessible to creator only. Advantages

are referencing without specifying the owner and Flexibility to customize a more meaningful naming convention.

4. What's 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.

5. What's 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.

6. Order of SQL statement execution

Where clause, Group By clause, Having clause, Order By clause & Select.

7. What's Transaction

Transaction is defined as all changes made to the database between successive commits.

8. What's Commit

Commit is an event that attempts to make data in the database identical to the data in the form. It involves writing or posting

data to the database and committing data to the database. Forms check the validity of the data in fields and records during a

commit. Validity check are uniqueness, consistency and db restrictions.

9. What's Posting

Posting is an event that writes Inserts, Updates & Deletes in the forms to the database but not committing these transactions to

the database.

10. What's Rollback

Rollback causes work in the current transaction to be undone.

11. What's Savepoint

Savepoint is a point within a particular transaction to which you may rollback without rolling back the entire transaction.

12. Set Transaction

Set Transaction is to establish properties for the current transaction.

13. What's Locking

Locking are mechanisms intended to prevent destructive interaction between users accessing data. Locks are used to achieve.

14. What's Consistency

Consistency : Assures users that the data they are changing or viewing is not changed until the are thro' with it.

15. What's 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 are given below.

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.

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

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.

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.

Share Update are synonymous with Row Share.

16. What's an SQL injection?

SQL Injection is when form data contains an SQL escape sequence and injects a new SQL query to be run.

17. What is difference between TRUNCATE & DELETE

TRUNCATE commits after deleting entire table i.e., cannot 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.

18. 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 retrieve rows that do not have corresponding join value

in the other table.

19. What is the sub-query?

Sub-query is a query whose return values are used in filtering conditions of the main query.

20. What is correlated sub-query?

Correlated sub-query is a sub-query, which has reference to the main query.