PLSQl Interview Questions Part 4,

71. Which are initial RDBMS, Hierarchical & N/w database ?

Answer:

• RDBMS - R system
• Hierarchical - IMS
• N/W - DBTG
71. Difference between Oracle 6 and Oracle 7

Answer:

ORACLE 7	ORACLE 6
Cost based optimizer	Rule based optimizer
Shared SQL Area	SQL area allocated for each user
Multi Threaded Server	Single Threaded Server
Hash Clusters	Only B-Tree indexing
Roll back Size Adjustment	No provision
Truncate command	No provision
Distributed Database	Distributed Query
Table replication & snapshots	No provision
Client/Server Tech	No provision

73. What is Functional Dependency

Answer: Given a relation R, attribute Y of R is functionally dependent on attribute X of R if and only if each X-value has associated with it precisely one -Y value in R

What is Auditing ?

Answer: The database has the ability to audit all actions that take place within it. a) Login attempts, b) Object Accesss, c) Database Action Result of Greatest(1,NULL) or Least(1,NULL) NULL

91. What's the length of SQL integer ?

Answer: 32 bit length

92. What is the difference between foreign key and reference key ?

Answer: Foreign key is the key i.e.attribute which refers to another table primary key. Reference key is the primary key of table referred by another table.

91. Can dual table be deleted, dropped or altered or updated or inserted ?

Answer: Yes

92. If content of dual is updated to some value computation takes place or not ?

Answer: Yes

93. If any other table same as dual is created would it act similar to dual?

Answer: Yes

94. For which relational operators in where clause, index is not used ?

Answer: <> , like '%...' is NOT functions, field +constant, field||''

95. .Assume that there are multiple databases running on one machine.How can you switch from one to another ?

Answer: Changing the ORACLE_SID

96. What are the advantages of Oracle ?

Answer: Portability : Oracle is ported to more platforms than any of its competitors, running on more than 100 hardware platforms and 20 networking protocols. Market Presence : Oracle is by far the largest RDBMS vendor and spends more on R & D than most of its competitors earn in total revenue.This market clout means that you are unlikely to be left in the lurch by Oracle and there are always lots of third party interfaces available. Backup and Recovery : Oracle provides industrial strength support for on-line backup and recovery and good software fault tolerence to disk failure.You can also do point-in-time recovery. Performance : Speed of a 'tuned' Oracle Database and application is quite good, even with large databases.Oracle can manage > 100GB databases. Multiple database support : Oracle has a superior ability to manage multiple databases within the same transaction using a two-phase commit protocol.

97. What is a forward declaration ? What is its use ?

Answer: PL/SQL requires that you declare an identifier before using it.Therefore, you must declare a subprogram before calling it.This declaration at the start of a subprogram is called forward declaration.A forward declarationconsists of a subprogram specification terminated by a semicolon.

98. What are actual and formal parameters ?

Answer: Actual Parameters : Subprograms pass information using parameters.The variables or expressions referenced in the parameter list of a subprogram call are actual parameters.For example, the following procedure call lists two actual parameters named emp_num and amount: 
Eg.raise_salary(emp_num, amount);

Formal Parameters : The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters.For example, the following procedure declares two formal parameters named emp_id and increase: 
Eg.PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL;

101. What are the types of Notation ?

Answer: Position, Named, Mixed and Restrictions.

102. What all important parameters of the init.ora are supposed to be increased if you want to increase the SGA size ?

Answer: In our case, db_block_buffers was changed from 60 to 1000 (std values are 60, 550 & 3500) shared_pool_size was changed from 3.5MB to 9MB (std values are 3.5, 5 & 9MB) open_cursors was changed from 200 to 300 (std values are 200 & 300) db_block_size was changed from 2048 (2K) to 4096 (4K) {at the time of database creation}. The initial SGA was around 4MB when the server RAM was 32MB and The new SGA was around 13MB when the server RAM was increased to 128MB.

101. .If I have an execute privilege on a procedure in another users schema, can I execute his procedure even though I do not have privileges on the tables within the procedure ?

Answer: Yes

102. What are various types of joins ?

Answer: Types of joins are:

• Equijoins
• Non-equijoins
• self join
• outer join
102. What is a package cursor ?

Answer: A package cursor is a cursor which you declare in the package specification without an SQL statement.The SQL statement for the cursor is attached dynamically at runtime from calling procedures.

104. If you insert a row in a table, then create another table and then say Rollback.In this case will the row be inserted ?

Answer: Yes.Because Create table is a DDL which commits automatically as soon as it is executed.The DDL commits the transaction even if the create statement fails internally (eg table already exists error) and not syntactically.