Q-6

1)      Update salary column in emp table to zero initially salary column has NULL data.

How you will do this in PL/SQL.

2)       Types of Indexes.

3)      What do you mean by snapshot.

Ans) A snapshot is a table that contains the
    results of a query of one or more tables or views, often located on
    a remote database.

In Oracle 9i instead of snapshot we can use meterialized views which can act as snapshot.

Diff between view & snapshot is view is not a table its a virtual table to join one or more table or to prevent access from users on complete data on table. whenever we will access the view that brings the data from tables only not from view(it wont store any kind of data).

but the snapshot itself store the query result data. usually we will use to get remote data to replicate the changes made at remote site.

 

4)      How to read & write from a file.

5)      Cursors.

6)      Partitioning.

7)      Empno          mgrno

440                          0

333                           222

111                            777

888                           666

Having rows like this in emp table. Write a query for the below output

888                           666

444                           0

333                           222

111                           777

8) empid              ename

     1                         ram       

     2                         ram

     3                         ravi

     4                         ram

     5                         sagar

     6                         sam

     7                         sagar

   Write a query to get ename whose count should be greater than 3.

9)empno              ename                  depid

                1              ram                        2

                2              sagar                    2

                3              ramya                   2

                4              shobha                 1

    Depid                dname

1                     admin

2                     library

3                     PMO

Write a query to fetch a dname where depid should not be in emp table

10) Integrity constraints

11) PL/SQL  table

12)Global temporary table.

13)when we have a PL/SQL table Y we need global temporary table.

14)I have deleted the rows from the table & did commit, but I want to get back the history of rows.

15)Same record 2 users are updating will it update.

16)I want to declare a global variable for the entire session where you will declare.

17) I want to create a table through PL/SQL

Ans) Hi.....yes we can create a table by using a procedure  or a function but it must be done by Dynamic SQL. Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at runtime. lets say you want to create an EMP table at run time in a procedure. The table name must be like EMP_<location_name>. So to get this we can pass the location name at run time to the procedure. To create table in a procedure u can use the following syntax:

EXECUTE IMMEDIATE 
    'CREATE TABLE ' || 'EMP_' || loc ||
    '(
        empno   NUMBER(4) NOT NULL,
        ename   VARCHAR2(10),
        job     VARCHAR2(9),
        sal     NUMBER(7,2),
        deptno  NUMBER(2)
    )';
You can also drop the table using Execute immediate. for example:

EXECUTE IMMEDIATE 'DROP TABLE ' || 'EMP_' || loc;

This way you use DDL commands in a procedure or function.

 

18) Disable all triggers for a particular table

Ans)  alter trigger <trigger-name> disable

        alter table<tablename> disable trigger<triggername>

19)what do you mean by database triggers. Y we cannot use in database triggers.

20)Difference b/w procedure & function

Ans)   A FUNCTION is always returns a value using the return statement.

        A  PROCEDURE   may  return one or more values through parameters or may not return at all.

1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.
 
2. Function can be called from SQL statements where as procedure can not be called from the sql statements

3. Functions are normally used for computations where as procedures are normally used for executing business logic.

4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.

5. Function returns 1 value only. Procedure can return multiple values (max 1024).

6.Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.

7.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values

8. Stored procedure is precompiled execution plan where as functions are not.
 
9.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.

Functions can be called inside select stmt but not the procedures.

A Function can be used in the Sql Queries while a procedure can not be used in sql queries .that cause a major difference b/w function and procedures .

A function always return a values while procedure can return one or more values through Parameters.

A function can call directly by sql statement like select "func_name" from dual while procedure cannot.

Dml statement cannot be used in function,but it used in procedure.

A function returns a value where as a proceedure does not return a value

procedure can call in another project but function work in same project . 

We can't have any DDL,DML and TLC command inside a function, if that function is called from a query.But if the function is not called from query then we can have all transactional statement(DDL,DML and TLC ) inside a function.

function can return a single values at maximum, where as in case if procedure returns one or more than one value and might not return a even a single value........

 

 

21)Can we call a function through SQL query any restriction is there? If I want to call the function having DML statements how we can achieve this.

22) When executing a query query got strucked . How you will find out where actually it got strucked.

23) what are pseudocolumns.

Ans) psedocolumns are the columns which can not be manipulated.

     psedocoluns are only retrive. Examples are rowid,nextval etc  

     A pseudocolumn behaves like a table column, but is not 

     actually stored in the table. You can select from

     pseudocolumns, but you cannot insert, update, or delete

          their values.

        psoducolumn in the sence which is not created by the user explicitly but user can use those                     things  explicityly.

examples

rowid,rowno,currval,nextval,sysdate,uid,level.

RowID:  While storing in the database oracle generates one id for each row.

you can call based upon the rowid .ex: select * from emp where rowid=....

RowNo : This no is also generated by oracle itself. but you cant call based upon this one in select clause

ROWID - Hexa decimal number each and every row having unique.Used in searching

ROWNUM - It is a integer number also unique for sorting Normally TOP N Analysys.

Other Psudo Column are

NEXTVAL,CURRVAL Of sequence are some exampls

Rowid is permanent for the life time whereas rownum is not. What i mean is till the time the record is present in the table, the value of ROWID of a particular row in a table will always be same whereas the value of ROWNUM will vary.

Even ROWID is also not permanent for life time.Because If one exports and imports the table data then ROWID changes.

Rowid is a unique identifier, when a row is inserted into the table it generates a row id and will be removed when the row is deleted.

For each row returned by the query a Rownum, pseudocolumn is returned in which order oracle selects the rows from the table. Using rownum one can even limit the number of rows selected from the table.

Ex: select * from emp where rownum<10;

Row id:  Hexadecimal string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn.

 

Rownum: For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on. You can use ROWNUM to limit the number of rows returned by a query, as in this example:

 SELECT * FROM employees WHERE ROWNUM < 10;

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. This section describes these pseudocolumns:

- CURRVAL and NEXTVAL

- LEVEL

- ROWID

- ROWNUM

 

24)How to get all indexes which have been created on a table.

Ans)  select index_name from user_indexes

    where table_name = 'GNMCOMPANY';

 

 

 

 

 

 

Hi,

I'm preparing for an interview. I found these from some of the topic questions. The last time I used Oracle Forms/Reports was more than 2 years ago. My computer does not have a Personal Oracle so I can at least see the Help file, nor do I have yet the books to refer to.
It will be very helpful in my preparation if you can provide the answers for any the following questions.

1. How to use single data block to query multiple tables
2. How to see the select statement when we issue execute_query
3. How do you trap default forms processing ( DML)
4. What is purpose and order of firing the following triggers
- on fetch
- on select
5. What is the number of records buffered and Query array size properties of data block
6. What is the difference between object libraries and object groups
7. What is the difference between PL/SQL library and object library
8. What is the difference between pre-text-item and when-new-item-instance triggers
9. What is the order of firing the following triggers
- when-new-form-instance
- pre-text-item
(Both are in form-level)
10. What is the default validation unit of form module
11. What is the order of checking for a program unit from form module(local program unit, library, stored procedure)
13. what is the use of pre text/pre record/pre form
14. what is id_null function
15. what is the difference between call_form , open_form, new_form
- Which is restricted built-in and why?
- Which can't issue savepoint
16. which are the triggers will fire in the following situation:

I have three text items
1. Text-item1
1.key-next-item
Go_item (:text_item2);
Go_item (:text_item3);

2. Text-item2
1. Pre-text-item
2. When-validate-item
3. When-new-item-instance
4. Key-next-item
5. Post-text-item
6. Post-change

3. Text-item3
1. Pre-text-item
2. When-validate-item
3. When-new-item-instance
4. Key-next-item
5. Post-text-item
6. Post-change

If I press tab or enter key at text-item1 what are the triggers will fire and order of firing during the entire navigation.

If I change key-next-item trigger of text-item1

Go_item (:text_item2);
: Text_item2: ='Nagendra';
Go_item (:text_item3);

Then what are the triggers will fire and order of firing

17. How do you suppress the logon screen while running the form for the first time
18. What is primary canvas property of window and where it will be useful
19. I have when-button-pressed trigger at form , block and item level
If I want to execute first block, form then item level trigger what changes I have to make.
20. what is data parameter and text parameter
21. Can we re-generate a library that is currently accessing by some other session
22. Can we re-generate and save a library that has been attached with some forms but they are running currently.
23. Can we re-generate a library that has been attached with some forms , will the changes will reflect in the referenced forms
24. What is the use of transactional triggers
25. Can we modify a sub classed object ( from object group and from object libraries)
26. How to set forms default directory
27. What is the return data type of populate_group built-in
28. What is the difference between OLE object created at design time and runtime
29. Will the timer will expire during large query executing
30. What is the built-in package available to manipulate text files( forms)
31. Can we define a relation between two control blocks
32. If we change relation property from non-isolated to cascading what changes will occur
33. If we delete on-clear-details trigger in a relation what happens
34. What is the first trigger fires when we run a form
35. What is the use of enforce primary key property of data block
36. Can we put items other than buttons in the toolbars
37. Which object relates content and stacked canvases(window)
38. How to navigate from one form to other form(built-in)
39. How to copy values from list item to record group
40. In a non-isolated relation what is the order of firing the following triggers
1. on-populate-details
2. on-clear-details
3. pre-query
4. pre-select
41. How to find out the previous form id in multi form application(it's system variable)
42. How to use single LOV for multiple items

Multiple choice:

1. What is the size of Varchar in Oracle 8.0 ?
a. 2000
b. 4000
c. 254
d. none of the above

2. What is the size of Varchar in Oracle 7.0 ?
a. 2000
b. 4000
c. 254
d. none of the above

3. The default value the lpad function takes is
a. a space (' ')
b. an asterisk
c. The default value is not optional
d. None of the above

4. The no. of columns that may be used as composite primary key in oracle 8
a. 8
b. 16
c. 32
d. none of the above

5. which of the following is true about add_months
a. we can pass a numerical value in first parameter
b. we can pass a negative value in second parameter
c. Both a & b
d. None of the above

6. The latest date that can be stored in oracle 8
a. 31st Dec 4012 A.D
b. 31st Dec 4011 A.D
c. Dec 31st ,9999
d. None of the above.

7. What happens when the first date is greater than the second date that is passed to the months_between function in oracle 8.
a. It gives an error
b. It gives a negative value
c. None of the above

8. Regarding the Summary query which of the following is true
a. The order of the base column list in the select statement should be same in the Group by clause.
b. The order of the base column list in the select statement need not be same in the Group by clause.
c. None of the above

9. Regarding the Summary query which of the following is true
a. All the base table columns selected in the select list should be specified in the Group by clause.
b. All the base table columns selected in the select list need not be specified in the Group by clause.
c. None of the above.

10. How do u mask the user from entering irrelevant data ?
a. Synonym
b. View
c. Index
d. sequence

11. What does the length function returns when applied to column of char datatype ?

16. If you want to restrict the user, to enter the same values that has been stored in other table then what constraint do u use?
a. Entity integrity
b. Referential Integrity
c. Both a & b
d. None of the above

17. Which of the following is true about NULL?
a. when an arithmetic operation is performed on NULL, u will get the result as NULL
b. NULL is same as 0.
c. NULL is same as blank date.
d. None of the above

18. For a DDL statement, which of the following is true
a. A DDL statement is preceded and followed by commit.
b. All the DML statements gets committed even when u get an error after writing DDL statement.
c. Both a & b
d. None of the above.

19. Which of the following is true for update clause?
a. We can update two base tables simultaneously
b. U can use a subquery in SET clause of the UPDATE statement.
c. Both a & b
d. None of the above

20. Which of the following is true for delete?
a. Delete statement can be given without writing where clause.
b. We can delete two tables simultaneously
c. Both a & b
d. None of the above

22. In oracle 7 which of the following is true about manipulating the view
a. View based on two base tables can be manipulated
b. View having a column which contain operation can be manipulated
c. Both a & b
d. None of the above

23. In oracle 8 which of the following is true about manipulating the view
a. View based on two base tables can be manipulated
b. View having a column which contain operation can be manipulated
c. Both a & b
d. None of the above

24. Which of the following is true about packages
a. We can write a procedure in package body which has not been specified in package specification.
b. We cannot write a procedure in package body which has not been specified in package specification.
c. Both a & b
d. None of the above.

25. Which of the following is true about outer joins
a. The outer join symbol should be present on any one side of the join.
b. The outer join symbol may be present on both the sides of the join
c. The outer join return the rows from the two tables that donot have matching records in other table.
d. None of the above

27. What does OFA stands for ?
a. Oracle Flexible Architecture
b. Oracle Financials Applications
c. Optimal Flexible Architecture
d. None of the above

thanks in advance...

Q-5

1)    What is the difference between view and materialized view?

View - store the SQL statement in the database and let you use it as a table. Every time you access the view the SQL statement executes.

Materialized view - stores the results of the SQL in table form in the database. SQL statement only executes once and after that every time you run the query the stored result set is used. Pros include quick query results.

View: This is a PSEUDO table that is not stored in the database and it is just a query. MATERIALIZED VIEWS: These are similar to a view but these are permanent stored in the database and often refreshed. This is used in optimization for the faster data retrieval and is useful in aggregation and summarization of data.

View is a logical reference to a database table. But Material View is actual table and we can refresh data in time intervals. If you made any change in database table that change will effect into view but not materialize view..

View: View is a logical or virtual memory which is based on select query and the simple view is the view in which we cannot make DML command if the view is created by multiple tables.

Materialize view: It works faster than simple its works as snap shot and used for security purposes and we can make DML command in materialize view.

Materialized view is physical duplicate data in a table. View is logical representation of table.

In view we cannot do DML commands where as it is possible in Materialized view.

 A view has a logical existence but a materialized view has a physical existence. Moreover a materialized view can be indexed, analyzed and so on....that is all the things that we can do with a table can also be done with a materialized view.

view is a logical or virtual table it doesn't have data on its own, but materialized view has a physical structure it stores data in local machine or on its own. Materialized view can be refreshed automatically or manually. But in view, if any changes happened in the base tables, we want to reflect the same in the view means view has to issue the select statement again to the database.

 

2)   What is MV and types of refresh methods used in it.

Materialized Views(also known as snapshots in prior releases) is a pre-computed table comprising aggregated or joined data from fact and possibly dimension tables. Also known as a summary or aggregate table.

It improves query performance by pre calculating expensive join and aggregation operations on the database prior to execution and storing the results in the database. The query optimizer automatically recognizes when an existing materialized view can and should be used to satisfy a request.

 

 Types of Refresh:

 

1)    Complete Refresh

When a complete refresh of a materialized view occurs, all data is removed and

then it is reloaded. Depending on the size of the materialized view, this could be a

time consuming operation.

 

2)     Fast Refresh

Some materialized views could be very large and the time required to regularly

perform a complete refresh may not be available. The alternative is a fast refresh

where only the changes to the detail table are applied against the materialized view.

New data loaded into any table in the warehouse is identified and any materialized

view referencing that table is automatically updated with the new data.

In order to perform a fast refresh operation, changes made to the data must be

recorded and this is achieved in one of two ways. If your data is only ever inserted

into the database using SQL*Loader direct path, then the refresh mechanism will

detect this and identify the new data to be loaded.

 

3)     Partition Change Tracking Refresh

Oracle Database 10g provides a component known as Partition Change Tracking

(PCT) which transparently detects when changes to partitions occur and then

determines whether the operation has made the data in the materialized view

inconsistent.

 

What is srw. package? This package extends reports ,Control report execution, output message at runtime, Initialize layout fields, Perform DDL statements  used to create or Drop  temporary table, Call User Exist, to format width of the columns, to page break the column, to set the colors.

SRW is a package name used while creating a report using rdf. It helps to perform action at runtime. for example  changing the color of the col. according to value fetched by the report   Calling a host program executing dynamic sql printing GUI messages at run time.

 

 

 

What is Ref. Cursor?

A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures functions packages etc.).

Difference between Ref Cursor and Normal Cursor

 

1)      In case of an normal explict cursor, the SQL query has to be defined at the time of declaring the cursor itself.  In case of REF Cursor, the cursor declartion is not associated with any SQL query, it is associated with a query at a later stage this brings in a lot of flexibility as different SQL queries can be associated with the cursor (one at a time, offcourse) programatically.  REF Cursors also provide the feature of passing parameters.  Though there is something dynamic with REF Cursor when compared to a normal explicit cursor, it is not a truly perfect dynamic cursor.  Truly perfect dynamic cursors are the one constructed using DBMS_SQL package.

2)      Generally ref cursor used for passing cursor parameter and also dynamically building the query. Normal cursor are static cursors and also we can't able to pass like parameter.

3)    T he normal cursor is a static cursor i.e we in the normal cursor the query is only assigned at the design time and it can't be change at the run time.

 Ref cursor supports the dynamic change in the cursor. It also help in memory allocation

Oracle autonomous transaction

An autonomous transaction is an independent transaction that is initiated by another transaction (the parent transaction). An autonomous transaction can modify data and commit or rollback independent of the state of the parent transaction.

The autonomous transaction must commit or roll back before the autonomous transaction is ended and the parent transaction continues.

An autonomous transactions is available from Oracle 8i.

An autonomous transaction is defined in the declaration of a pl/sql block. This can be an anonymous block, function, procedure, object method or trigger.
This is done by adding the statement 'PRAGMA AUTONOMOUS_TRANSACTION;' anywhere in the declaration block.

There isn't much involved in defining a PL/SQL block as an autonomous transaction. You simply include the following statement in your declaration section:
PRAGMA AUTONOMOUS_TRANSACTION;

 

 

 

 

Sample code:

PROCEDURE test_autonomous

IS

  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

   insert ....

   commit;

END test_autonomous;

Autonomous transactions can be used for logging in the database independent of the rollback/commit of the parent transaction.

 

 

 

Q-4

What is cursor?

For Oracle to process a SQL statement, it needs to create an area of memory known as the

context area;

A cursor is a handle, or pointer, to the context area.

Through the cursor, a PL/SQL program can control the context area and what happens to it as the statement is processed.

 

Cursors have two important features:

·         Cursors allow you to fetch and process rows returned by a SELECT statement one row at

a time.

·         A cursor is named so that it can be referenced.

How many types of cursors are available?

There are two types of cursors:

 

·         Oracle automatically declares an implicit cursor every time a SQL statement is executed.

The user is unaware of this and cannot control or process the information in an implicit

cursor.

 

·         The program defines an explicit cursor for any query that returns more than one row of

data. This means that the programmer has declared the cursor within the PL/SQL code

block. This declaration allows the application to sequentially process each row of data as

the cursor returns it.

FORMS60_PATH (REGEDIT) MAX LENGTH IS 255 characters. If it exceeds forms builder automatically close the application.

Implicit cursor

·         Any given PL/SQL block issues an implicit cursor whenever a SQL statement is executed, as long as an explicit cursor does not exist for that SQL statement.

·         A cursor is automatically associated with every DML (data manipulation) statement

(UPDATE, DELETE, INSERT).

·         All UPDATE and DELETE statements have cursors that identify the set of rows that will

be affected by the operation.

·         An INSERT statement needs a place to receive the data that is to be inserted into the database;

the implicit cursor fulfills this need.

·         The most recently opened cursor is called the SQL cursor.

 

The implicit cursor is used to process INSERT, UPDATE, DELETE, and SELECT INTO statements.

During the processing of an implicit cursor, Oracle automatically performs the OPEN,

FETCH, and CLOSE operations.

 

EXPLICIT CURSOR

The only means of generating an explicit cursor is to name the cursor in the DECLARE sectionof the PL/SQL block.

The advantage of declaring an explicit cursor over an indirect implicit cursor is that the explicitcursor gives the programmer more programmatic control. Also, implicit cursors are less efficient than explicit cursors, so it is harder to trap data errors.

The process of working with an explicit cursor consists of the following steps:

1. Declaring the cursor. This initializes the cursor into memory.

2. Opening the cursor. The declared cursor is opened, and memory is allotted.

3. Fetching the cursor. The declared and opened cursor can now retrieve data.

4. Closing the cursor. The declared, opened, and fetched cursor must be closed to release the memory allocation.

RECORD TYPES

A record is a composite data structure, which means that it is composed of one or more

Elements.

Records are very much like a row of a database table, but each element of the record

does not stand on its own.

 PL/SQL supports three kinds of records:

·         table-based,

·         cursor-based,

·          Programmer-defined.

A table-based record is one whose structure is drawn from the list of columns in the table.

A cursor-based record is one whose structure matches the elements of a predefined cursor.

To create a table-based or cursor-based record, use the %ROWTYPE attribute:

 

record_name table_name or cursor_name%ROWTYPE

PL/SQL TOPICS

1)      CUSRSOR , CURSOR FOR UPDATE

2)      TRIGGER , MUTATING TRIGGER

3)      FUNCTION, PROCEDURE, PACKAGE

4)      ARRAYS (COOLECTION)

5)      RECORDS

6)      BULK SQL

7)      NATIVE DYNAMIC SQL

8)      EXCEPTION

9)      ORACLE SUPPLIED PACKAGES

10)  REF CURSOR

 

SQL

1)      VIEWS AND MATERIALIZED VIEWS

2)      CORRELATED SUBQUERIES

3)      ANALITICAL FUNCTIONS

4)      ANY , ALL

5)      CUBE , ROLLUP , HIERARICAL QUERIES

6)       

 

Q-3

SQL interview questions and answers

By admin | July 14, 2008

1.        What are two methods of retrieving SQL?

We can retrieve the sql by using SELECT and CURSOR

2.       What cursor type do you use to retrieve multiple recordsets?

For multiple records we use explicit cursors

3.       What is the difference between a "where" clause and a "having" clause? - "Where" is a kind of restiriction statement. You use where clause to restrict all the data from DB.Where clause is using before result retrieving. But Having clause is using after retrieving the data.Having clause is a kind of filtering command.

4.       What is the basic form of a SQL statement to read data out of a table? The basic form to read data out of table is 'SELECT * FROM table_name; ' An answer: 'SELECT * FROM table_name WHERE xyz= 'whatever';' cannot be called basic form because of WHERE clause.

5.        What structure can you implement for the database to speed up table reads?- Follow the rules of DB tuning we have to: 1] properly use indexes ( different types of indexes) 2] properly locate different DB objects across different tablespaces, files and so on.3] create a special space (tablespace) to locate some of the data with special datatype ( for example CLOB, LOB and …)

6.       What are the tradeoffs with having indexes? - 1. Faster selects, slower updates. 2. Extra storage space to store indexes. Updates are slower because in addition to updating the table you have to update the index.

7.        What is a "join"? - 'join' used to connect two or more tables logically with or without common field.

8.       What is "normalization"? "Denormalization"? Why do you sometimes want to denormalize? - Normalizing data means eliminating redundant information from a table and organizing the data so that future changes to the table are easier. Denormalization means allowing redundancy in a table. The main benefit of denormalization is improved performance with simplified data retrieval and manipulation. This is done by reduction in the number of joins needed for data processing.

9.       What is a "constraint"? - A constraint allows you to apply simple referential integrity checks to a table. There are four primary types of constraints that are currently supported by SQL Server: PRIMARY/UNIQUE - enforces uniqueness of a particular table column. DEFAULT - specifies a default value for a column in case an insert operation does not provide one. FOREIGN KEY - validates that every value in a column exists in a column of another table. CHECK - checks that every value stored in a column is in some specified list. Each type of constraint performs a specific type of action. Default is not a constraint. NOT NULL is one more constraint which does not allow values in the specific column to be null. And also it the only constraint which is not a table level constraint.

10.     What types of index data structures can you have? - An index helps to faster search values in tables. The three most commonly used index-types are: - B-Tree: builds a tree of possible values with a list of row IDs that have the leaf value. Needs a lot of space and is the default index type for most databases. - Bitmap: string of bits for each possible value of the column. Each bit string has one bit for each row. Needs only few space and is very fast.(however, domain of value cannot be large, e.g. SEX(m,f); degree(BS,MS,PHD) - Hash: A hashing algorithm is used to assign a set of characters to represent a text string such as a composite of keys or partial keys, and compresses the underlying data. Takes longer to build and is supported by relatively few databases.

11.      What is a "primary key"? - A PRIMARY INDEX or PRIMARY KEY is something which comes mainly from 
database theory. From its behavior is almost the same as an UNIQUE INDEX, i.e. there may only be one of each value in this column. If you call such an INDEX PRIMARY instead of UNIQUE, you say something about 
your table design, which I am not able to explain in few words. Primary Key is a type of a constraint enforcing uniqueness and data integrity for each row of a table. All columns participating in a primary key constraint must possess the NOT NULL property.

12.     What is a "functional dependency"? How does it relate to database table design? - Functional dependency relates to how one object depends upon the other in the database. for example, procedure/function sp2 may be called by procedure sp1. Then we say that sp1 has functional dependency on sp2.

13.     What is a "trigger"? - Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time a data-modification operation occurs (i.e., insert, update or delete). Triggers are executed automatically on occurance of one of the data-modification operations. A trigger is a database object directly associated with a particular table. It fires whenever a specific statement/type of statement is issued against that table. The types of statements are insert,update,delete and query statements. Basically, trigger is a set of SQL statements A trigger is a solution to the restrictions of a constraint. For instance: 1.A database column cannot carry PSEUDO columns as criteria where a trigger can. 2. A database constraint cannot refer old and new values for a row where a trigger can.

14.     Why can a "group by" or "order by" clause be expensive to process? - Processing of "group by" or "order by" clause often requires creation of Temporary tables to process the results of the query. Which depending of the result set can be very expensive.

15.     What is "index covering" of a query? - Index covering means that "Data can be found only using indexes, without touching the tables"

16.     What types of join algorithms can you have?

17.     What is a SQL view? - An output of a query can be stored as a view. View acts like small table which meets our criterion. View is a precomplied SQL query which is used to select data from one or more tables. A view is like a table but it doesn't physically take any space. View is a good way to present data in a particular format if you use that query quite often. View can also be used to restrict users from accessing the tables directly.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

8. While doing an ascending order sort on a column having NULL values, where does
the NULLs show up in the result set? At the beginning or at the end? 

Ascending order sort - NULLs come last because Oracle treats NULLs are the largest possible values 

Descending order sort - NULLs come first 

* How to make NULLs come last in descending order sort? 

Add NULLS LAST to the order by desc clause

Eg: select col1 from table1 order by col1 desc NULLS LAST

9. Time of execution of an SQL Statement 

      Eg: set timing on select * from EMP

     After execution of each query we get the time take for it.

10. What is the Datatype of NULL in Oracle? 

     Datatype of NULL is "char(0)" and size is '0'

 

11. DATE Functions 

Gives date

select trunc(sysdate,'MM') from dual; 

select last_day(trunc(sysdate)) from dual 

Gives day

select to_char(trunc(sysdate,'MM'),'DAY') from dual; 

select to_char(last_day(trunc(sysdate)),'DAY') from dual;

12. Maximum length of all Oracle Objects : 

Maximum length of all Oracle Objects should not exceed 30 characters.


13. Query to find the nNth Highest salary

select min(sal) from (select distinct sal from emp order by sal desc) where rownum < n;

14. Oracle Functions - Replace versus Trim 

SQL> select replace('jose. antony@ yahoo.com',' ', null) as Replace1 from dual; 

REPLACE1
--------------------
jose.antony@yahoo.com         --Removes all spaces from in-between

SQL> select trim('jose. antony@ yahoo.com') as Trim1 from dual;


TRIM1
----------------------
jose. antony@ yahoo.com         --Removes spaces from both sides only.

15. In Oracle can we do any DMLs in a function which is called from a select statement? 

No. Oracle Throws Error 
      ORA-14551: cannot perform a DML operation inside a query
      ORA-06512: at "EMP.FNC_VACATION_UPDATE", line 11

16. In Oracle, what happens if there is no statement inside an Anonymous block? 

For Eg: 
Declare
Begin
End;
         Ans: It will throw Error.

Correct:
        Declare
        Begin
        NULL;
        End;

 

17. In Oracle, where does NULLs appear in an ascending order sort? First or Last? And why? 

NULLs appear Last in an ascending order sort. Oracle treats NULLs as the largest possible values in the Database. So, they appear last

18. How does NULLs work with Indexes? What are the workarounds? 

Indexes wont help in giving proper search results if there are NULL values in the indexed columns. So, it is always advisable to create the NVL() function based indexes for such columns.

19. In an Outer Join, what happens to columns that are not matched? 

All unmatched columns (cell values) returns NULLS.

20. What are Index Organized Tables (IOT)? 

Index organized tables are indexes which actually hold the data which is being indexed, unlike the 
indexes which are stored somewhere else ans have links to actual data.

21. User_Source table contains code for which all Objects?


a.     Functions
b.     Packages - both spec and body
c.     Procedures
d.     Triggers

23. Explain ROWID in Oracle. 

ROWID is a unique hexadecimal value which Oracle inserts to identify each record being inserted. It is 
used for all Full Table scans. 

Structure: 

OOOOOOFFFBBBBBBRRR

OOOOOO -    First six characters is the Object Number which idenities the Data Segment
FFF           - Next 3 characters is the Database File number
BBBBBB     - Next 6 characters shows the DataBlock number
RRR           -Next 3 characters identified the Row within the block

                                                           COOL INTERVIEWS

What is the use of CASCADE CONSTRAINTS?

A1.

When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

Text document for this answer

Why does the following command give a compilationerror? DROP TABLE &TABLE_NAME;

 

Question :  What is the difference between TRUNCATE and DELETE commands?

What is the difference between oracle,sql and sql server ?

• Oracle is based on RDBMS.
• SQL is Structured Query Language.
• SQL Server is another tool for RDBMS provided by MicroSoft.

why you need indexing ? where that is stroed and what you mean by schema object? For what purpose we are using view?

We cant create an Index on Index.. Index is stoed in user_index table.Every object that has been created on Schema is Schema Object like Table,View etc.If we want to share the particular data to various users we have to use the virtual table for the Base table...So tht is a view.

indexing is used for faster search or to retrieve data faster from various table. Schema containing set of tables, basically schema means logical separation of the database. View is crated for faster retrieval of data. It's customized virtual table. we can create a single view of multiple tables. Only the drawback is..view needs to be get refreshed for retrieving updated data.

Difference between Store Procedure and Trigger?

• we can call stored procedure explicitly.
• but trigger is automatically invoked when the action defined in trigger is done.
  ex: create trigger after Insert on
• this trigger invoked after we insert something on that table.
• Stored procedure can't be inactive but trigger can be Inactive.
• Triggers are used to initiate a particular activity after fulfilling certain condition.It need to define and can be enable and disable according to need.

What is the advantage to use trigger in your PL?

Triggers are fired implicitly on the tables/views on which they are created. There are various advantages of using a trigger. Some of them are:

• Suppose we need to validate a DML statement(insert/Update/Delete) that modifies a table then we can write a trigger on the table that gets fired implicitly whenever DML statement is executed on that table.
• Another reason of using triggers can be for automatic updation of one or more tables whenever a DML/DDL statement is executed for the table on which the trigger is created.
• Triggers can be used to enforce constraints. For eg : Any insert/update/ Delete statements should not be allowed on a particular table after office hours. For enforcing this constraint Triggers should be used.
• Triggers can be used to publish information about database events to subscribers. Database event can be a system event like Database startup or shutdown or it can be a user even like User loggin in or user logoff.

What the difference between UNION and UNIONALL?

Union will remove the duplicate rows from the result set while Union all does'nt.

What is the difference between TRUNCATE and DELETE commands?

Both will result in deleting all the rows in the table .TRUNCATE call cannot be rolled back as it is a DDL command and all memory space for that table is released back to the server. TRUNCATE is much faster.Whereas DELETE call is an DML command and can be rolled back.

Which system table contains information on constraints on all the tables created ? 
yes,
USER_CONSTRAINTS,
system table contains information on constraints on all the tables created

Explain normalization? 
Normalisation means refining the redundancy and maintains stablisation. there are four types of normalisation : 
first normal forms, second normal forms, third normal forms and fourth Normal forms.

How to find out the database name from SQL*PLUS command prompt?
Select * from global_name;
This will give the datbase name which u r currently connected to.....

What is the difference between SQL and SQL Server ?

SQLServer is an RDBMS just like oracle,DB2 from Microsoft
whereas 
Structured Query Language (SQL), pronounced "sequel", is a language that provides an interface to relational database systems. It was developed by IBM in the 1970s for use in System R. SQL is a de facto standard, as well as an ISO and ANSI standard. SQL is used to perform various operations on RDBMS.

What is diffrence between Co-related sub query and nested sub query?

Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.

Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.

For example,

Correlated Subquery:

select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)

Nested Subquery:

select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)

WHAT OPERATOR PERFORMS PATTERN MATCHING?
Pattern matching operator is LIKE and it has to used with two attributes

1. % and

2. _ ( underscore )

% means matches zero or more characters and under score means mathing exactly one character

1)What is difference between Oracle and MS Access?
2) What are disadvantages in Oracle and MS Access?
3) What are feratures&advantages in Oracle and MS Access?
Oracle's features for distributed transactions, materialized views and replication are not available with MS Access. These features enable Oracle to efficiently store data for multinational companies across the globe. Also these features increase scalability of applications based on Oracle.

What is database?
A database is a collection of data that is organized so that itscontents can easily be accessed, managed and updated. open this url : http://www.webopedia.com/TERM/d/database.html

What is cluster.cluster index and non cluster index ? 
Clustered Index:- A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table may have only one clustered index.Non-Clustered Index:- A Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows in the disk. The leaf nodes of a non-clustered index does not consists of the data pages. instead the leaf node contains index rows.

How can i hide a particular table name of our schema?
you can hide the table name by creating synonyms.

e.g) you can create a synonym y for table x

create synonym y for x;

What is difference between DBMS and RDBMS?
The main difference of DBMS & RDBMS is RDBMS have Normalization. Normalization means to refining the redundant and maintain the stablization. 
the DBMS hasn't normalization concept.

What are the advantages and disadvantages of primary key and foreign key in SQL?

Primary key  Advantages

1) It is a unique key on which all the other candidate keys are functionally dependent

Disadvantage 1) There can be more than one keys on which all the other attributes are dependent on.

Foreign Key Advantage

1)It allows refrencing another table using the primary key for the other table

Which date function is used to find the difference between two dates?
datediff

for Eg: select datediff (dd,'2-06-2007','7-06-2007')

output is 5