Oracle Questions And Answer Page 3 


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;

 

 

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.

 

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.

 

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.

 

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