DBMS Questions and Answers for Interview (Part-V)

66. What is an OPTIIMIZER ?

OPTIMIZER is an utility used to determine how to access data requested in the query by the USER or APPLICATION PROGRAM. The output of an optimizer is EXECUTION PLAN.

67. How OPTIMIZATION is done by the Oracle in case of a query ?

1] RULE based, and 2] COST based.

68. What is a] RULE based optimization, b] COST based optimization ?

RULE based optimization USES A FIXED SET OF RULES to determine how to access the data.

COST based optimization USES STASTISTICS STORED IN THE DATA DICTIONARY WITH CERTAIN RULES to determine how to access the data.

Two modes - a] ALL_ROWS, B] FIRST_ROW. With the help of ALTER SESSION SET OPTIMIZER_GOAL = ALL_ROWS / FIRST_ROW, We can alter the modes of cost based optimizer.

69. The KEYWORD comes into the mind immediately when we talk about security ?????? in ORACLE 7.0 ?

GRANT.

Syntax GRANT privileges( SELECT,INSERT,UPDATE,DELETE,ALTER,INDEX) ON object TO

user WITH GRANT OPTION;

70 What KEWORD is used to withdraw the PRIVILEGE you have granted to other user ?

REVOKE

Syntax: REVOKE privileges ON object FROM users;

71 What is SINGLE INSTANCE ?

A single instance can run on a single machine.

72 What is MULTIPLE INSTANCES ?

A SINGLE MACHINE can run more than one instance at a time. Each instance is connected to its own database.

73 What is DISTRIBUTED PROCESSING ?

Different instances on different machines can communicate with each other using DATABASE LINKS and the DISTRIBUTED option. Oracle supports full two-phase commits which means that inserts, updates and deletes can occur on REMOTE database via a network running SQL*Net.

74 What is PARALLEL PROCESSING ?

The Oracle parallel server allows muliple instances to share a single database on a shared disk system. The instance can run on a parallel computer or on different computers in a cluster.

75. Difference between SQL and PL/SQL ?

SQL is the ANSI industry standard language, used to manipulate information in a relational database.

PL/SQL is the procedural language extension to Oracle’s SQL language.

SQL PL/SQL

1. It is flexible, Powerful and easy to learn. 2. It is a non-procedural language. It a] Processes set of records rather than just one at a time and b] Provides automatic navigation to the data. 3. It provides commands for avariety of tasks including : a] Querying data b] Creating,Updating and Replacing objects and Inserting, Updating and Deleting rows. 4] All RDBMS supports SQL Thus one can transfer the skills gained with SQL from one RDBMS to another. Programs written in SQL are portable, they can often be moved from one database to another with little modification.

1. PL/SQL block can contain any no. of SQL statements combined with the following : a] Flow of control statements such as IF..THEN, ELSE, EXIT and GOTO. b] Repetition statements such as FOR .. LOOP and WHILE .. LOOP. c] Assignment statements such as X := Y + Z 2. PL/SQL allows you to logically group a set of statements and send them to the RDBMS as a single block. 3. Procedural capabilities. 4. Improved performance. 5. Enhanced productivity 6. Portability 7. Integration with the RDBMS.

76. How to fetch description of a code in the base table block where code is a base table field and

the description is a non-base table field ?

Use SELECT with INTO clause to fetch the decription value into the NON-BASE table field.

77. What is the purpose of OUTER JOIN ?

An OUTER JOIN returns all the rows returned by simple join as well as those rows from one table that do not match any row from the other table. The symbol (+) represents the outer join.

78. Difference between EQUI JOIN and OUTER JOIN ?

EQUI JOIN returns rows from both the tables provided they both have the same column_name in the where clause. The symbol (=) represents the EQUI JOIN.

For OUTER JOIN see previous answer.

79. Define NORMALIZATION ?

NORMALIZATION is the process of putting things right, making them normal. It is a part of analysis necessary to understand a business, and build a useful application.

The normalization of data ensures

a] Minimization of duplication of data.

b] Providing flexibility to support different funtional requirements.

c] Enabling the model to be translated to database design.

STEPS INVOLVED IN NORMALIZATION

1] Ensure that all the ENTITIES are uniquely identified by a combination of attributes.

2] Remove repeated attributes or group of attributes, to place the entities in the first

normal form.

3] Remove attributes that are dependent on only part of the identifier.

4] Remove attributes that are dependent on attributes which are not part of the identifier.

80. Define REFERENTIAL INTEGRITY ?

REFERENTIAL INTEGRITY is the property that guarantees that values from one column depend on values from another column. This property is enforced through integruty constraints.

Referential integrity is the automatic enforcement of referential constraints that exists between a reference table and a referencing table. When referential integrity is enforced , the value of a foreign key exists as a primary key value in the reference table.

81. Explain OUTER JOIN with example ?

SELECT DEPT.DEPTNO,DNAME,JOB,ENAME FROM DEPT,EMP WHERE

DEPT.DEPTNO = EMP.DEPTNO(+) AND DEPTNO IN (30,40)

ORDER BY DEPT.DEPTNO;

82. Explain with example how to use a select statement with GROUP BY HAVING clause ? (or)

Where and when is the HAVING clause used and what does it have ?

The HAVING clause is coded after the GROUP BY clause in the query that is summarizing results by one or more grouping columns. The HAVING clause behaves the same as

the WHERE clause except that it is used to specify the conditions each returned group must satisfy. If one row in the group fails the condition of the HAVNG clause, the entire group is not returned as part of the result.

Ex: SELECT MAX(CUSTID), REPID FROM CUSTOMER GROUP BY REPID

HAVING COUNT(*) > 2;

83. How do you TUNE SQL statements ?

Use OPTIMIZER HINTS for tuning Sql statements.

84. What is the advantage of ENFORCE KEY ?

ENFORCE KEY field characterstic indicates the source of the value that SQL*FORMS

uses to populate the field

85. What is the Purpose of ERASE command ?

ERASE removes an indicated Global variable & releases the memory associated with it.