DBMS Questions and Answers for Interview (Part-I)

Leave a Comment

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;

0 comments:

Post a Comment