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.

Read More...

DBMS Questions and Answers for Interview (Part-IV)

46. How do you analyse TKPROF ?

TKPROF filename.tra O/P file EXPLAIN = USR/PWD0

47. what parameter variables to be set to use TKPROF ?

SQL PROF

48. How many types of lockings are there ?

5 types of locks.

To lock is to temporarily restrict other user’s access to data. The restriction is placed on such data is called “a lock”. The modes are SHARE, SHARE UPDATE,EXCLUSIVE,ROW SHARE AND ROW EXCLUSIVE. Not all locks can be acquired in all modes.

49. What is a SHARE LOCK ?

A SHARE lock is one that permits other users to query data, but not to change it.

50. What is a SHARE UPDATE LOCK ?

A SHARE UPDATE lock is one that permits other users to both query and lock data.

51. What is an EXCLUSIVE LOCK ?

An EXCLUSIVE LOCK is one that permits other users to query data, but not to change it. It differs from the SHARE lock because it does not permit another user to place any type of lock on the same data; several users may place SHARE locks on the same data at the same time.

52 What is ROWSHARE, SHAREUPDATE and ROW EXCLUSIVE locks ?

With a ROW SHARE or SHARE UPDATE lock, no users can lock the whole table for exclusive access, allowing concurrent access for all users to the table. The two types of locks are synonymous, and SHARE UPDATE exists for compatibility with previous versions of ORACLE.

ROW EXCLUSIVE locks are similar to ROW SHARE but they prohibit shared locking, so only one user user may access the table at the same time.

53. What is a DEAD LOCK ?

A DEAD lock is a rare situation in which two or more user processes of a database cannot complete their tansactions.This occurs because each process is holding a resource that the other process requires (such as a row in a table) in order to complete.Although these situations occur rarely, ORACLE detects and resolves deadlocks by rolling back the work of one of the processes.

54. How do you analyse which resources has locked for what ?

Use MONITOR SESSION.

55. How to kill a SESSION ?

ALTER SESSION KILL ID, NUMBER FROM SQLDBA;

56. What are USER_EXITS ?

It is an utility in SQL*FORMS for making use of HOST 3 GL languages for the purpose like ONLINE PRINTING etc.

57. When will you use the trigger WHEN-NEW-FORM-INSTANCE ?

At FORMS STARTUP Oracle navigates to the first navigable item in the first navigable block. This trigger fires after successful completion of any Navigational trigger (i.e) It will not fire if the control retuns to the CALLING FORM from the CALLED FORM.

Usage: For initialization at FORMS STARTUP.

58. What is an INDEX ? Why are indexes used in a table ?

INDEX is a general term for an ORACLE / SQL feature used primarily to speed execution an impose UNIQUENESS upon certain data. INDEX provides a faster access method to one table’s data than doing a full table scan. There are several types of Indexes :

UNIQUE INDEX, COMPRESSED INDEX, CONCATENATED INDEX. An Index has an entry for each value found in the table’s Indexed field(s) ( except those with a NULL value ) and pointer(s) to the rows having that value.

59. What is an UNIQUE INDEX ?

An UNIQUE INDEX ia an index that imposes uniqueness on each value in indexes. The index may be one column or concatenated columns.

60 What is an COMPRESSED INDEX ?

A COMPRESSED INDEX is an index for which only enough index information is stored to identify unique enrties; information that an index stores with the previous or following key is “compressed” (truncated) and not stored to reduce the storage overhead required by an index.

61. What is an CONCATENATED INDEX ?

A CONCATENATED INDEX is one that is created on more than one column of a table. It can be used to guarentee that those columns are unique for every row in the table and to speed access to rows via those columns

62. What is a UNION, UNION ALL,INTERSECTION and MINUS operator ?

The UNION operator returns ALL DISTINCT ROWS selected by either query.

The UNION ALL operator returns ALL ROWS selected by either query including duplicates.

The INTERSECTION operator returns ONLY ROWS that are COMMON to both the queries.

The MINUS operator returns ALL DISTINCT ROWS selected only by the first query and not by the second.

63. What does ‘GROUP BY’ statement do ?

GROUP BY statement causes a SELECT statement to produce ONE SUMMARY ROW for all selected rows that have identical values in one or more specified column or expressions. Each expe\ressionin the SELECT clause must be one of the following :

1] A CONSANT

2] A Function without parameters

3] A GROUP function like SUM , AVG.

4] Matched IDENTICALLY to a expression in the ‘GROUP BY’ clause.

64. In 2 SELECT statements SELECT A FROM DUAL; and SELECT B FROM DUAL; What will be

the difference in using ‘UNION’ and ‘UNION ALL’ ?

UNION returns all distinct rows selected by either of the query, whereas UNION ALL returns ALL ROWS selected by either query including duplicates.

64. Give one example where you will use DATABASE TRIGGERS ?

For AUDITING purposes we use database triggers.

65. Do you have any idea about ROW-CHAINING ? How will you resolve the issue if there is row-

chaining in a table ?

When a row NO LONGER FITS WITHIN THE DATABLOCK, it is stored in more than one database block, and that therefore have several row pieces.

Resolving: Use ANALYZE to identify chained rows and also provides statistics on the chained rows. Eg: ANALYZE ledger LIST CHAINED ROWS INTO CHAINED_ROWS:

(CHAINED_ROWS is a user defined table)

For creating chained_rows run the UTLCHAIN.SQL script.

Read More...

DBMS Questions and Answers for Interview (Part-III)

31. When do we get a MUTATING ERROR ?

This happens with TRIGGERS. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of VIEWS or TEMPORARY TABLES so the database is selecting from one while updating the other.

32. How to DISABLE REFERENTIAL INTEGRITY ?

Use the DIABLE option in CREATE TABLE or ALTER TABLE or using

DISABLE { { UNIQUE (column) (column)... PRIMARY KEY | CONSTRAINT } [CASCADE] | ALL TRIGGERS;

NOTE : For diabling REFERENTIAL INTEGRITY we have to include CASCADE option.

33. How to know what all CONSTRAINTS are present in a table ?

Using the USER_CONSTRAINTS view we can get the type of constaints declared on a table.

Use ALL_CONSTRAINTS to list the constraints on all of the tables that the user have access. DBA_CONSTRAINTS lists all of the constraints in the database.

34. What is MASTER - DETAIL relationship ? Can we write a master-detail relationship programs

without using the setings at design time. If so how ?

It is an association between TWO BASE TABLE blocks - a MASTER block and a DETAIL block. The relationship between the blocks reflects a PRIMARY KEY - FOREIGN KEY relationship between the tables on which the blocks are based.

Yes. Using the SET_RELATION property.

35. What does BUFFER RECORDS option and ARRAY SIZE parameter ?

ARRAY SIZE - Specifies the minimum no. of records that get fetched each time forms goes to the database.

BUFFER RECORDS - Specifies the minimum no of records that should be placed in memory when records are fetched from the database. Even if you specify a low value of 3, the minimum per form is slightly over 300.

36. During VALIDATION WHAT CHECKS are done with respective to FIELDS / ITEMS ?

1] Data type, 2] Maximum length, 3] Fixed length, 4] Required and

5] Range Low value / Range High value.

37. What is the difference between PRIMARY KEY and UNIQUE KEY ?

The UNIQUE KEY column restricts entry of duplicate values but entry of NULL value is allowed.

In case of PRIMARY KEY columns entry of duplicate as well as NULL value is restricted.

38. What is the DIFFERENCE between PRE-QUERY and POST-QUERY ?

PRE-QUERY fires ONLY ONCE during EXECUTE-QUERY or COUNT-QUERY processing, just before Oracle Forms constructs and issues the SELECT statement to identify rows that match the query criteria.

POST-QUERY fires each time for records placed on the blocks list of records.

39. When do you use ON-DATABASE-RECORD triigger ?

Use an ON-DATABASE-RECORD to perform an action every time a record is first marked as an INSERT or UPDATE.

This trigger fires, as soon as Oracle Forms determines thro’ validation that the record should be processed by the next post or commit as an INSERT or UPDATE

40. What are RESTRICTED PACKAGED PROCEDURES ? Why are they restricted from using ?

Any PACKAGED PROCEDURE that affects the basic functions of SQL*FORMS is a RESRICTED PACKAGED PROCEDURE. You should use restricted packaged procedure only in KEY-TRIGGERS, USER-NAMED TRIGGERS that are invoked by KEY-TRIGGERS, and ON_NEW_FIELD_INSTANCE triggers. You should not use restricted packaged procedures in any of the following types of triggers.

• On-error,On-Database-Record,On-delete,On-insert,On-Lock,
• On-Message,On-New-Record,On-Remove-record,On-Update,
• On-Validate-Field, and On-validate-Record triggers.
• Post-Change triggers.
• Pre- and Post- Field, Pre- and Post- Record, Pre- and Post-Block, Pre- and Post-Form triggers.
• Pre- and Post-Query triggers.
• Pre- and Post-Insert, Pre- and Post-Update, Pre- and Post-Delete, Pre- and Post-Commit triggers.
• User-Named triggers that are invoked by any of the above triggers.

41. What is the DIFFERENCE between EXPLICIT CURSOR & IMPLICIT CURSOR ?

IMPLICIT CURSORS are automatically opened by issuing a SELECT statement. But the EXPLICIT cursors are to be opened using OPEN, fetching is done using FETCH and closing using CLOSE.

42. What is the difference between ROWID and ROWNUM ?

ROWID is the logical address of the row, whereas ROWNUM returns the sequence no. in which the row was retrieved when first feched from a table.

43. What is the RESULT of the statement ?

SELECT EMPNO, NAME,SAL FROM EMP WHERE ROWNUM >2;

Result : 0, No rows will be selected.

44. How do you evaluate performance ?

Using SQL TRACE. It is an utility that can monitor and report on database performance when one or more queries are run against the database.

It is used to gather statistics when running the query (i.e) reports on CPU time spent on the query, the total no. of rows processed and statistics related to parsing and cache performance.

45. What will EXPLAIN PLAN give ?

It is an utility that shows how Oracle will access data for a given query. Use EXPLAIN PLAN to determine the effective way to write queries and decide whether to INDEX CERTAIN COLUMNS or TO USE CLUSTERS.

It shows :

1] The type of query processed; SELECT, INSERT,UPDATE or DELETE.

2] The cost assigned by the COST BASED OPTIMIZER if it is in use.

3] The steps that are necessary to return the data.

4] The internal operations that were performed for each step.

5] The object accessed for each step.

Read More...

DBMS Questions and Answers for Interview (Part-II)

16. What is TRUNCATE table ?

TRUNCATE table is a DDL command used to remove all the rows from the specified table or cluster instantly. Eg: TRUNCATE TABLE table_name;

Advantage over DELETING:

a] It is a DDL statement and generates NO ROLLBACK information.

b] Doesn’t fire the tables DELETE TRIGGER.

c] Truncating the master table of a snapshot doesn’t record any changes in the tables snapshot log.

d] It’s more convinient than dropping and recreating the table.

e] D/R invalidates the table’s dependent objects than truncating the object.

f] D/R requires you to REGRANT the privileges on the table while truncating doesn’t.

g] D/R requires you to RECREATE the INDEXES, INTEGRITY CONSTRAINTS, TRIGGERS and STORAGE PARAMETER while truncating doesn’t.

17. What is ROWID ? What are its components ?

ROWID is the logical address of a row, and it is unique within the database.The ROWID is broken into three sections: left,middle,, and right (corresponding to 00001F20,000C, AND 0001, just shown). The numbering is in hexadecimal notation.

The left section is the block in the file, the middle is the row sequence number within the block(numbering starts with 0, not 1), and the right is the file number within the database. Note that the file numbers are uniquewithin the whole database. The tablespace they are in is not relevant to the ROWID.

ROWID can be selected, or used in a where clause, but cannot be changed by an insert, update, or delete. However it can changeif the table it is in is exported and imported.

18. What is the differnce between REPLACE and TRASLATE ?

Syntax : REPLACE(string,if,then)

REPLACE replaces a character or characters in a string with 0 or more characters, if is a character or characters. Everytime it appears in a string, it is by the contents of then.

Eg: REPLACE(‘ADAH’,’A’,’BLAH’) - BLAHDBLAHH (Result)

Syntax: TRANSLATE(string,if,then)

TRANSLATE looks at each character in string, and then checks if to see if that character is there, if it is, TRANSLATE notes the position in if where it found the character, and then looks the same position in then. Whatever character it finds there it substitutes the character in string

Eg: TRANSLATE(‘RAMESH’,’RAM’,’SUR’) - SURESH(Result)

19. What is a LEVEL ?

LEVEL is a pseudo column, used with CONNECT BY. It is equal to 1 for a root, 2 for a child of root, 3 for a child of a child of a root and so on.

20. What is anonymous block in PL/SQL ?

The text of an Oracle Forms trigger is an anonymous PL/SQL block. It consists of

three sections :

• A declaration of variables, constants,cursors and exceptions which is optional.
• A section of executable statements.
• A section of exception handlers, which is optional.

Syntax: DECLARE

--- declarartive statements ( optional )

BEGIN

--- executable statements ( required )

EXCEPTION

--- exception handlers ( optional )

END;

21. Name any ORACLE defined EXCEPTION ?

CURSOR_ALREADY_OPEN, NO_DATA_FOUND, INVALID_NUMBER.

22. Can we define our OWN EXCEPTION ? How to raise it ?

In the DECLARATION part define a variable of type exception. In the excecution part call the exception using RAISE exception_name. In the exception part handle the exception using WHEN exception_name.

23. What is a PRAGMA ?

It is a directive to the COMPILER, rather than a piece of executable code. Eventhough it appears in the program, it is not executable. It gives instructions to the compiler.

24. Difference between CHAR and VARCHAR2 ?

CHAR(size) - It is a fixed length character data, size characters long. It is padded with BLANKS ON RIGHT to the full length of size. DEFAULT - 1 bytes, MAXIMUM - 255 bytes.

VARCHAR2(size) - It is a varable length char string having a maximum of size bytes.

MAXIMUM - 2000 bytes.

25. What is a CURSOR FOR LOOP ?

The CURSOR FOR LOOP lets you implicitly OPEN a cursor, FETCH each row returned by the query associated with the cursor and CLOSE the cursor when all rows have been processed.

26. What are the possible CONSTRAINTS defined on a TABLE ?

NOT NULL, UNIQUE KEY, PRIMARY KEY, FOREIGN KEY and CHECK constraints.

27. What is APPLICATION PARTITIONING ?

PL/SQL is the language used for both client-side Oracle forms applications and server-side database triggers and stored procedures and there is a PL/SQl engine in both Oracle forms Runform and the Oracle7 Server.

This means that you can take advantage of application patitioning to execute application code on either the client or the server.

Application partitioning allows you to optimize performance and resource usage by storing and executing procedures either locally or at the server, which makes the most sense for your particular application and configuration.

28. Difference between a STORED PROCEDURE and a STORED FUNCTION ?

Unlike procedures, FUNCTIONS returns a VALUE to the caller. This value is returned thro’ the RETURN command/keyword within the function.

Functions don’t use the IN, OUT | IN OUT arguments, which are available for PROCEDURES.

29. How to RUN PROCEDURES from SQL PROMPT ?

Use EXECUTE Procedure_name command.

30. How to TRAP ERRORS in procedures ?

Use SHOW_ERRORS. this will display all the errors associated with the most recently created procedural object. This command will check the VIEW_ERRORS data dictionary for the ERRORS associated with the most recent compilation attempt for that procedural object.

SHOW_ERRORS will display the LINE and COLUMN NO. for each error, as well as the text of the error message. Eg: SELECT LINE, POSITION,TEXT FROM USER_ERRORS WHERE

NAME = ‘balance_check’ AND

TYPE = PROCEDURE/FUNCTION/PACKAGE

ORDER BY SEQUENCE;

NOTE: We can use ALL_ERRORS & DBA_ERRORS to view errors.

TRAPPING ERORS: DBMS_OUTPUT package allows you to use 3 debugging functions within your package. You must 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.

Read More...

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;

Read More...

Sample Interview Questions on RDBMS

DBMS INTERVIEW QUESTIONS

QUESTION 1:
What is database?
ANSWER:
A database is a logically coherent collection of data with some inherent meaning, representing some aspect of real world and which is designed, built and populated with data for a specific purpose.
QUESTION 2:
What is DBMS?
ANSWER:
• Redundancy is controlled.
• Unauthorised access is restricted.
• Providing multiple user interfaces.
• Enforcing integrity constraints.
• Providing backup and recovery.
QUESTION 4:
What is a Database system?
ANSWER:
The database and DBMS software together is called as Database system.
QUESTION 5:
Disadvantage in File Processing System?
ANSWER:
• Data redundancy & inconsistency.
• Difficult in accessing data.
• Data isolation.
• Data integrity.
• Concurrent access is not possible.
• Security Problems. .
QUESTION 6:
Describe the three levels of data abstraction?
ANSWER:
The are three levels of abstraction:
• Physical level: The lowest level of abstraction describes how data are stored.
• Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.
• View level: The highest level of abstraction describes only part of entire database.
QUESTION 7:
Define the "integrity rules"
ANSWER:
There are two Integrity rules.
• Entity Integrity: States that “Primary key cannot have NULL value”
• Referential Integrity: States that “Foreign Key can be either a NULL value or should be Primary Key value of other relation.
QUESTION 8:
What is extension and intension?
ANSWER:
Extension -It is the number of tuples present in a table at any instance. This is time dependent.
Intension - It is a constant value that gives the name, structure of table and the constraints laid on it.
QUESTION 9:
What is System R? What are its two major subsystems?
ANSWER:
System R was designed and developed over a period of 1974-79 at IBM San Jose Research Center . It is a prototype and its purpose was to demonstrate that it is possible to build a Relational System that can be used in a real life environment to solve real life problems, with performance at least comparable to that of existing system.
Its two subsystems are
• Research Storage
• System Relational Data System.
QUESTION 10:
How is the data structure of System R different from the relational structure?
ANSWER:
Unlike Relational systems in System R
• Domains are not supported
• Enforcement of candidate key uniqueness is optional
• Enforcement of entity integrity is optional
• Referential integrity is not enforced
QUESTION 11:
What is Data Independence?
ANSWER:
Data independence means that “the application is independent of the storage structure and access strategy of data”. In other words, The ability to modify the schema definition in one level should not affect the schema definition in the next higher level.
Two types of Data Independence:
• Physical Data Independence : Modification in physical level should not affect the logical level.
• Logical Data Independence : Modification in logical level should affect the view level.
NOTE: Logical Data Independence is more difficult to achieve
QUESTION 12:
What is a view? How it is related to data independence?
ANSWER:
A view may be thought of as a virtual table, that is, a table that does not really exist in its own right but is instead derived from one or more underlying base table. In other words, there is no stored file that direct represents the view instead a definition of view is stored in data dictionary.
Growth and restructuring of base tables is not reflected in views. Thus the view can insulate users from the effects of restructuring and growth in the database. Hence accounts for logical data independence. .
QUESTION 13:
What is Data Model?
ANSWER:
A collection of conceptual tools for describing data, data relationships data semantics and constraints.
QUESTION 14:
What is E-R model?
ANSWER:
This data model is based on real world that consists of basic objects called entities and of relationship among these objects. Entities are described in a database by a set of attributes.
QUESTION 15:
What is Object Oriented model?
ANSWER:
This model is based on collection of objects. An object contains values stored in instance variables with in the object. An object also contains bodies of code that operate on the object. These bodies of code are called methods. Objects that contain same types of values and the same methods are grouped together into classes.
QUESTION 16:
What is an Entity?
ANSWER:
It is a 'thing' in the real world with an independent existence.
QUESTION 17:
What is an Entity type?
ANSWER:
It is a collection (set) of entities that have same attributes.
QUESTION 18:
What is an Entity set?
ANSWER:
It is a collection of all entities of particular entity type in the database.
QUESTION 19:
What is an Extension of entity type?
ANSWER:
The collections of entities of a particular entity type are grouped together into an entity set.
QUESTION 20:
What is Weak Entity set?
ANSWER:
An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of its partial key and primary key of its parent entity, then it is said to be Weak Entity set.
QUESTION 21:
What is an attribute?
ANSWER:
It is a particular property, which describes the entity.
QUESTION 22:
What is a Relation Schema and a Relation?
ANSWER:
A relation Schema denoted by R(A1, A2, …, An) is made up of the relation name R and the list of attributes Ai that it contains. A relation is defined as a set of tuples. Let r be the relation which contains set tuples (t1, t2, t3, ..., tn). Each tuple is an ordered list of n-values t=(v1,v2, ..., vn).
QUESTION 23:
What is degree of a Relation?
ANSWER:
It is the number of attribute of its relation schema.
QUESTION 24:
What is Relationship?
ANSWER:
It is an association among two or more entities.
QUESTION 25:
What is Relationship set?
ANSWER:
The collection (or set) of similar relationships.
QUESTION 26:
What is Relationship type?
ANSWER:
Relationship type defines a set of associations or a relationship set among a given set of entity types.
QUESTION 27:
What is degree of Relationship type?
ANSWER:
It is the number of entity type participating.
QUESTION 28:
What is Data Storage - Definition Language?
ANSWER:
The storage structures and access methods used by database system are specified by a set of definition in a special type of DDL called data storage-definition language.
QUESTION 29:
What is DML (Data Manipulation Language)?
ANSWER:
This language that enable user to access or manipulate data as organised by appropriate data model.
• Procedural DML or Low level: DML requires a user to specify what data are needed and how to get those data.
• Non-Procedural DML or High level: DML requires a user to specify what data are needed without specifying how to get those data.
QUESTION 30:
What is VDL (View Definition Language)?
ANSWER:
It specifies user views and their mappings to the conceptual schema.
QUESTION 31:
What is DML Compiler?
ANSWER:
It translates DML statements in a query language into low-level instruction that the query evaluation engine can understand.
QUESTION 32:
What is Query evaluation engine?
ANSWER:
It executes low-level instruction generated by compiler.
QUESTION 33:
What is DDL Interpreter?
ANSWER:
It interprets DDL statements and record them in tables containing metadata.
QUESTION 34:
What is Record-at-a-time?
ANSWER:
The Low level or Procedural DML can specify and retrieve each record from a set of records. This retrieve of a record is said to be Record-at-a-time.
QUESTION 35:
What is Set-at-a-time or Set-oriented?
ANSWER:
The High level or Non-procedural DML can specify and retrieve many records in a single DML statement. This retrieve of a record is said to be Set-at-a-time or Set-oriented.
QUESTION 36:
What is Relational Algebra?
ANSWER:
It is procedural query language. It consists of a set of operations that take one or two relations as input and produce a new relation.
QUESTION 37:
What is Relational Calculus?
ANSWER:
It is an applied predicate calculus specifically tailored for relational databases proposed by E.F. Codd. E.g. of languages based on it are DSL ALPHA, QUEL.
QUESTION 38:
How does Tuple-oriented relational calculus differ from domain-oriented relational calculus
ANSWER:
The tuple-oriented calculus uses a tuple variables i.e., variable whose only permitted values are tuples of that relation. E.g. QUEL
The domain-oriented calculus has domain variables i.e., variables that range over the underlying domains instead of over relation. E.g. ILL, DEDUCE.
QUESTION 39:
What is normalization?
ANSWER:
It is a process of analysing the given relation schemas based on their Functional Dependencies (FDs) and primary key to achieve the properties
• Minimizing redundancy
• Minimizing insertion, deletion and update anomalies.
QUESTION 40:
What is Functional Dependency?
ANSWER:
A Functional dependency is denoted by X Y between two sets of attributes X and Y that are subsets of R specifies a constraint on the possible tuple that can form a relation state r of R. The constraint is for any two tuples t1 and t2 in r if t1[X] = t2[X] then they have t1[Y] = t2[Y]. This means the value of X component of a tuple uniquely determines the value of component Y.
QUESTION 41:
When is a functional dependency F said to be minimal?
ANSWER:
• Every dependency in F has a single attribute for its right hand side.
• We cannot replace any dependency X A in F with a dependency Y A where Y is a proper subset of X and still have a set of dependency that is equivalent to F.
• We cannot remove any dependency from F and still have set of dependency that is equivalent to F.
QUESTION 42:
What is Multivalued dependency?
ANSWER:
Multivalued dependency denoted by X Y specified on relation schema R, where X and Y are both subsets of R, specifies the following constraint on any relation r of R: if two tuples t1 and t2 exist in r such that t1[X] = t2[X] then t3 and t4 should also exist in r with the following properties
• t3[x] = t4[X] = t1[X] = t2[X]
• t3[Y] = t1[Y] and t4[Y] = t2[Y]
• t3[Z] = t2[Z] and t4[Z] = t1[Z]
where [Z = (R-(X U Y)) ]
QUESTION 43:
What is Lossless join property?
ANSWER:
It guarantees that the spurious tuple generation does not occur with respect to relation schemas after decomposition.
QUESTION 44:
What is 1 NF (Normal Form)?
ANSWER:
The domain of attribute must include only atomic (simple, indivisible) values.
QUESTION 45:
What is Fully Functional dependency?
ANSWER:
It is based on concept of full functional dependency. A functional dependency X Y is full functional dependency if removal of any attribute A from X means that the dependency does not hold any more.
QUESTION 46:
What is 2NF?
ANSWER:
A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully functionally dependent on primary key.
QUESTION 47:
What is 3NF?
ANSWER:
A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the following is true
• X is a Super-key of R.
• A is a prime attribute of R.
In other words, if every non prime attribute is non-transitively dependent on primary key.
QUESTION 48:
What is BCNF (Boyce-Codd Normal Form)?
ANSWER:
A relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint that for every FD X A, X must be a candidate key.
QUESTION 49:
What is 4NF?
ANSWER:
A relation schema R is said to be in 4NF if for every Multivalued dependency X Y that holds over R, one of following is true
• X is subset or equal to (or) XY = R.
• X is a super key.
QUESTION 50:
What is 5NF?
ANSWER:
A Relation schema R is said to be 5NF if for every join dependency {R1, R2, ..., Rn} that holds R, one the following is true
• Ri = R for some i.
• The join dependency is implied by the set of FD, over R in which the left side is key of R.

Read More...