DBMS Questions and Answers for Interview (Part-I)

1. What is a View ? Why is it required to define a View ?

A View is a database object that is a logical representation of a table. It is derived from a table but has no storage space of its own and often may be used in the same manner as a table.

Advantage: 1. Security 2. Complex query can be replaced.

2. Can we create a View without a table ?

Yes, Using the FORCE option in the CREATE VIEW syntax.

Ex: CREATE FORCE VIEW view_name as SELECT column name,columnname..

FROM table_name;

3. What is the difference between a SYNONYM and a VIEW ?

A SYNONYM is a name assigned to a table or view that may thereafter be used to refer it. If you access to another user’s table, you may create a synonym for it and refer to it by the synonym alone, without entering the user’s name as a qualifier.

A View is a database object that is a logical representation of a table. It is derived from a table but has no storage space of its own and often may be used in the same manner as a table.

Difference: A View can be based on MULTIPLE Tables whereas a SYNONYM is based on a single object only.

4. What is SNAPSHOT ? What is a SNAPSHOT LOG ?

A SNAPSHOT is a means of creating a local copy of remote data. A snapshot can be used to replicate all or part of a single table, or to replicate the result of a query against multiple tables. The refreshes of the replicated data can be done automatically by the database ( at time intervals you specify ) or manually.Snapshot Log is the table associated with the Master Table of the Snap shot.

5. What is a DATABASE trigger ? What is a DATABASE Procedure ?

A DATABASE TRIGGER is a stored procedure associated with a table that ORACLE7 automatically executes on one or more specified events (BEFORE or AFTER an INSERT,UPDATE or DELETE) affecting the table. Triggers can execute for the table as a whole or for each affected row in the table.

A PACKAGED PROCEDURE is a built-in PL/SQL procedure that is available in all forms. Each packaged procedure executes a SQL*FORMS function, such as moving to a field or executing a query.

6. How to show MESSAGES in PROCEDURES for debugging purposes ?

DBMS_OUTPUT_PACKAGE allows you to use 3 debugging functions within your package. You must use “SET SERVER OUTPUT ON” before executing the procedure object you will be debugging.

PUT - Puts multiple O/P’s on same line.

PUT_LINE Puts each O/P on a separate line.

NEW_LINE Used with PUT; Signals the end of current O/P line.

7. What is the difference between DATABASE trigger and DATABASE procedure ?

DATABASE triggers are executed automatically in response to specific events. But the DATABASE procedures are to be explicitly invoked to execute the code contained in them.

8. What is a CURSOR ?

A work area in memory where ORACLE stores the current SQL statement. For a query , the area in memory also includes column headings and one row retrieved by the SELECT statement.

9. What are the attributes of IMPLICIT CURSOR ?

%ISOPEN, %ROWCOUNT, %FOUND and %NOTFOUND.

Attribute DML STATEMENT

RETURNS ROW RETURNS NO ROW

%ISOPEN FALSE FALSE

%ROWCOUNT TRUE FALSE ( ZERO )

%FOUND TRUE FALSE

%NOTFOUND FALSE TRUE

10. Can we pass a PARAMETER to CURSOR ? What is SQL%ROWCOUNT ?

We can pass parameter to CURSOR. Eg: OPEN CUSOR(‘VASAN’).

SQL%ROWCOUNT is used to count the number of rows returned by an SQL DML statement.It will return zero if the DML statement doesn’t return any row.

11. How to write a SQL statement that should have a best RESPONSE TIME ?

Use the ___________________ in the optimizer hint inorder to obtain a best response time. Use “FIRST_ROW” - Cost based Optimizer Hint.

12. What are OPTIMIZER HINTS ?

Specifies a hint string that Oracle Forms passes on to the RDBMS optimizer when constructing queries. Using the optimizer can improve the performance of database transactions.

13. What is the difference between %TYPE and %rowtype ?

%TYPE provides the datatype of a varible,constant or column. It is useful when you declare a variable that refers to a database column in the table.

%ROWTYPE attribute is based on a record variable that has the same structure as a row in a table or view or as a row fetched from a cursor.

14. Can we define structure like objects in PL/SQL ?

[ If the structure is what we define in ‘C’ then we can create objects of type structure using RECORD variable available in PL/SQL. ]

Yes, Using the PL/SQL tables. PL/SQL tables are temporary array like objects used in a PL/SQL block. PL/SQL tables can have one column and a primary key. The column data type can belong to any scalar data type, but the primary key must only belong to the type binary_integer.

Size - UNLIMITED.

15. Can we use a funtion inside an INSERT statement ?

Yes. Eg: INSERT INTO EMP(COMM ) VALUES ( SAL*0.05 ) WHERE DEPTNO = 20;