PLSQL interview questions - part2

oracle pl sql interview questions for 3+ years experience

21.What are the components of physical database structure of Oracle database?

Ans: Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

22.Query to delete duplicate row from a table

Ans: Delete from emp where rowid not in (Select min(rowid) from emp Groupby emp_dept)

23.What is a cursor its attribute and types?

Ans: The Oracle Engine uses a work area for its internal processing in order to execute an SQL statement. This work area is private to SQL operation and is called Cursor.

Types of Cursor:

Implicit Cursor: If the Oracle engine opened a cursor for its internal processing then it is know as implicit cursor. It is invoked implicitly.

Explicit Cursor: A cursor which is opened for processing data through a PL/SQL block is know as Explicit Cursor.

Attributes Of a Implicit Cursor:

%ISOPEN —returns TRUE if cursor is open else FALSE.

Syntax is SQL%ISOPEN

%ROWCOUNT--- returns number of records processed from cursor syntax is SQL %ROWCOUNT %FOUND---- returns TRUE if record is fetched successfully else FALSE, syntax is SQL%FOUND %NOTFOUND-- returns TRUE if record is not fetched successfully else FALSE syntax is SQL%NOTFOUND Attributes Of a Explicit Cursor %ISOPEN—returns TRUE if cursor is open else FALSE. Syntax is cursorname%ISOPEN %ROWCOUNT--- returns number of records processed from cursor syntax is cursorname %ROWCOUNT %FOUND---- returns TRUE if record is fetched successfully else FALSE, syntax is cursorname %FOUND %NOTFOUND-- returns TRUE if record is not fetched successfully else FALSE syntax is cursorname %NOTFOUND

24.What are inline views?

Ans: Inline view is Sub-query(queries written in a where clause of SQL statements.). It is a query whose return values are used in filtering conditions of the main query.

25.How can we refresh a snapshot?

Ans: Refreshing Snapshots: A snapshot can be refreshed automatically or manually. If a snapshot has to be automatically refreshed then refresh clause must be specified in the CREATE SNAPSHOT. The FAST, COMPLETE or FORCE specifies the type of REFRESH used for automatic refresh. For automatic refresh we can specify the START WITH and NEXT parameter to decide the time interval for the next update.
COMPLETE refresh: In complete refresh the snapshot query is executed and places the result in the snapshot.
FAST refresh : In this only the changes made to the master table will be updated to the snapshot. The corresponding log file is used to update. Fast refresh will be done only if * The snapshot is a simple snapshot. * The snapshot's master table has a snapshot log \ * The snapshot log was created before the snapshot was last refreshed or created.
FORCE refresh : In this ORACLE decides how to refresh the snapshot at the scheduled refresh time. If a fast refresh is possible it performs a fast refresh else it does a complete refresh.

26.What is a tablespace?

Ans: A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

27.Is sequence cyclic?

Ans: Yes

28.Select nth highest value from a list of values ?

Ans: SELECT a.emp_name,a.sal FROM emp a WHERE &n - 1= (SELECT COUNT(DISTINCT sal) FROM emp b WHERE b.sal > a.sal )

29.What are triggers and its types?

Ans: A trigger is a piece of code attached to a table that is executed after specified DML statements executed on that table. There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, STATEMENT, TABLE, INSERT, UPDATE, DELETE and ALL key words: For eg: BEFORE ALL ROW INSERT AFTER ALL ROW INSERT BEFORE INSERT AFTER INSERT

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

Ans: 12 triggers(Oracle).

31.Difference between rowid and rownum?

Ans: ROWID is pseudo column in every table. The physical address of the rows is use to for the ROWID.IN HEXADECIMAL representation, ROWID is shown as 18 character string of the following format BBBBBBBBB.RRRR.FFFF (block, row, file) FFFF is the fileid of the datafile that contains the row. BBBBBBBBB is the address of the datablock within the datafile that contains the row. RRRR is the ROW NUMBER with the data block that contains the row. They are unique identifiers for the any row in a table. They are internally used in the construction of indexes.
Rownum is the sequential number of rows in the result set object.

32.What is the fastest query method for a table?

Ans: By rowid

33.What is the difference of a LEFT JOIN and an INNER JOIN statement?

Ans: A LEFT JOIN will take ALL values from the first declared table and matching values from the second declared table based on the column the join has been declared on. An INNER JOIN will take only matching values from both tables

34.How can I avoid a divide by zero error?

Ans: Use the DECODE function. This function is absolutely brilliant and functions like a CASE statement, and can be used to return different columns based on the values of others.

35.Is view updatable?

Ans: Only if the view is a simple horizontal slice through a single table.

36.What is Dual ?

Ans: The DUAL table is a table with a single row and a single column used where a table is syntactically required.

37.What is the difference between CHAR and VARCHAR ?

Ans: CHAR is fixed length character type at storage level, and that VARCHAR will be variable length.

38.Do we use commit in triggers.

Ans: No

39.How will the fetch the last inserted record in any table ?

Ans: select column 1, column 2.... From where rowid = (select max(rowid) from table);

40.What are constraints and its types?
Integrity Constraint : An integrity constraint is a declarative way to define a business rule for a column of a table. An integrity constraint is a statement about a table's data that is always true.

Types of integrity constraints : The following integrity constraints are supported by ORACLE:

1. NOT NULL : disallows nulls (empty entries) in a table's column

2. UNIQUE : disallows duplicate values in a column or set of columns

3. PRIMARY KEY : disallows duplicate values and nulls in a column or set of columns

4. FOREIGN KEY : requires each value in a column or set of columns match a value in a related table's UNIQUE or PRIMARY KEY.

5. CHECK : disallows values that do not satisfy the logical expression of the constraint


                     Part1                     Part2                     Part3                     Part4                     Part5