ORACLE INTERVIEW QUESTIONS =>PART3

1. What is data?

Data is a collection of raw, facts and figures that is use in different place we can store anything in database is called data.

2. What is database?

Collection of organized data which can easily managed access, understand, and update.

3. What is database management system?

Any programming environment used to create containers, to manage human data, can be conceptualized as a Data Management System

4. What is table?

It is a set of elements that is consist of rows and columns in which we can store our data. In any table we define specified no, of columns and there are many no. of rows are available in table that is use for store the data row wise.

5. What is column?

Column is a one element of table that is use logically vertical shape in table. We define specified no. of columns in one table then store data in it.

6. What is row?

Rows are also second element of table that is use for store the data in table as a horizontally shape. We can enter many rows in one table. In database terms we can say one record to one row in table.

7. What is primary key?

A primary is uniquely identifies a row of the table this key ensure two things one is that null value does not allow and duplicate value also not allowed in table. A table must always have one and only one primary key. Primary key value is normally employee code, Driving license code, NIC code etc.

8. What is foreign key?

A foreign key is a references or a link between one table and another. The foreign key can be used to cross reference tables. A foreign has a link with parent table (that have primary key value). Suppose we have one table with primary key and w have second table that have foreign key and have a relation with parent table than we must enter the values in second table that is totally related with parent table.

9. What is unique key?

Unique key does not accept duplicate values but accept null values in oracle. Unique key is very much related with primary key in primary key we cannot enter NULL value and also duplicate value. But on the other hand in unique key we can enter NULL value but not duplicate value. For example in employee database we set Driving license no. unique because one employee have unique license no. and some employees have not.

10. What is the difference between primary key and unique key?

Unique key does not accept duplicate values but accept null values and primary key does not accept both two either NULL  or duplicate data.

11. What is Null constraint?

The columns that have NULL constraint must fill with some data. A column in a table can be specified not null. It’s not possible to insert a null in such a column we cannot leave any column empty in tables that have NULL constraint. It should be fill with no matter with duplication of that.

12. What is the maximum no. of column in oracle table?

The maximum no. of columns is 255.

13. Is COMMIT statement can be use in database trigger?

We cannot use any transaction control statement (like commit, rollback, savepoint etc.) into the body of a trigger. But oracle provides a facility called Autonomous Transactions through which you can achieve this. You add that piece of the code, which contains commit, in some block and make that block as Autonomous Transaction and call or use that block into your trigger.

What is SQL statement?

SQL stands for Structured Query Language that is use for updating, deleting and inserting the  information from databases. SQL is an ANSI and ISO standard and is the de-facto standard database query language. Oracle database also support this language.

What are database triggers and how many types of triggers?

Database triggers are a set of code that run or fire automatically when the specific operation will performed like Insert, Update and Delete. They are store procedures that are auto configure and fire when certain events take place in database.

There are two types of triggers

1-      Tables level trigger

2-      Row level trigger

Table level trigger are fire at one time but row level trigger fire on every row or on any transaction in the table.

What are the means of values of: new and: old in the trigger?

These two: new and: old are use in database triggers. These are basically use for referencing the trigger value by using triggers. For example we want to update anything in table and this table have database trigger on After Update then we use these two keywords for updating the values again this table.

 What is the Mutating in the trigger?

The mutating will occur when in Oracle when the table that originally fired the triggering event is being accessed in the body of the trigger code, directly or implicitly in a procedure, or in a nested trigger called from the trigger body. This is because the table is in middle of a transaction, and referencing the same table again in the middle of the updating action causes the trigger to mutate.

 Can we write a trigger for view?

Yes we can write a trigger on view by using key word “instead of” but did not use Before or After option on trigger.

 Can we create a view on another view?

Yes we can create a view on another view.

What are the advantages of view?

1-      Restrict the data and column s of the base tables.

2-      Use complex quires easy and show the data of complex quires.

3-      Make different views according to requirement on many on table.

4-      We can hide business rules by using view.

What are function and how many types of functions?

Functions or user defined function are set of SQL/PLSQL code that are store in database and return the value. In function some data is manipulating by using some statement then return a specific value.

What is the difference between row level and Table level triggers?

Table level trigger are fire at one time but row level trigger fire on every row or on any transaction in the table.

What is the difference between Database triggers and Form level triggers?

Database trigger are written in database directly and fire on behalf of any transaction like Insert, Update and delete on table automatically. On the other hand we form level trigger are use in forms and fire on any level like item level, row level or on block level on requirement of application. Main difference in database trigger and form level triggers is that database trigger fire automatically and form level trigger fire on user or application requirement.

 What is the advantage of stored procedure over the database triggers?

Store procedure are compile one time and stay in database and wait for calling but on the other hand database trigger fire every time when we have to perfume any change in database.

 What are cascading of triggers?

Cascading of trigger means if we insert data in one table and that table have trigger on it then trigger fire. And in this trigger there is another table that we are using for insert the data in it and this table has also trigger on it then this trigger also fire. This is called cascading of triggers.

 What is Pseudo column?

Pseudo columns are database columns that are using for different purposes in oracle database like ROWNUM, ROWID, SYSDATE, UID, USER, ORA_ROWSCN, SYSTIMESTAMP,

 What is Dual and where it can be use?

Dual is a database table owner is sys and reside in data dictionary. It have only one column name DUMMY and one data X. It is normally use for select the values of Pseudo columns.

 Can we insert, update, delete, alter and drop DUAL table?

Yes we can Insert, update, delete alter and also drop Dual table in oracle.

 If we create a table with name DUAL it can be work same as DUAL?

Yes we create table with same name of DUAL and also it will work like same of dual table.

What are group functions and how many types of group function?

Group functions return the single result on based on many rows. Like count, sum, min, max, avg first, last these functions return one row result based on many rows.

What are cursors and how many types of cursors?

Cursor is a pointer variable in a memory and use for DML operations. Cursor basically is a private SQL memory area it is also use to improve the performance of the database.

There are two types of cursors.

1- Implicit cursor

2- Explicit cursor

Implicit cursor use oracles to manipulate the DML opperations and programmer have no control on this type of cursor. We use sql%notfound and sql%rowcount in implicit cursor. Explicit cursors are created by the programmer and programmer can control it by using these keywords Fetch, Open and close.

 Define Oracle cursor attributes.

There are five types of cursors attributes

1- %isopen      (Verify whether this cursor is open or not)

2- %found       (If cursor fetch the data then %found return true)

3- %notfound (If cursor fetches not data then %notfound return true)

4- %rowcount (It return no. of rows that are in cursor and also give position of record)

5- %bulk_rowcount (%bulk_rowcount is same like %rowcount but it is used in bulk)

How can we pass variable in cursors?

We can pass the variable in cursor by using parameter cursor. Just we give the variable in it like cursor C1 (my_variable number).

 What is the CASE statement and where we can use it?

CASE statement is just like IT-THEN-ELSE condition it is normally use when we are using some type of if else condition. When condition is founded true the case statement return the result and no further evaluating of data.

What is INDEX, when and where we can use it?

Index is using for performance tuning and also give us faster data retrieval of record from tables. Index can create on any column in table by default oracle B-Tree index.

Which operator that are not coming in Indexing?

NOT IN operator is not coming in indexing.

 How we can get limited rows in SQL?

We can get limited rows in oracle database by using ROWNUM or HAVING clause.

 What is the difference between Sub query and Co-related query?

One query contains more than one sub-query. The inner most query run first then second outer then third outer and so on. If we will not use relation on inner query to outer query then this query is called sub-query if we use relation then this query called Correlated query.

Example of Sub Query:

SELECT EMP_NAME, DEPTNO FROM EMP

WHERE EMP_NAME IN (SELECT EMP_NAME FROM DEPT)

Example of Correlated Query:

SELECT EMP_NAME, DEPTNO FROM EMP

WHERE EMP_NAME IN (SELECT EMP_NAME FROM DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO)

 What is exception handling and how we can handle the exception in Oracle?

Exception is an error situation which arises during program execution. When an error occurs exception is raised normally execution is stopped and control transfers to exception handling part. Exception handlers are routines written to handle the exception. The exceptions can be internally defined User-defined exception. In oracle we can handle exception by using these statements.

EXCEPTION WHEN

DUP_VAL_ON_INDEX

NOT_LOGGED_ON

NO_DATA_FOUND

TOO_MANY_ROWS

VALUE_ERROR

 Can we define more than one exception in one block?

Yes we can define more than one exception in one block in oracle form builder like we will       use DUP_VAL_ON_INDEX and NO_DATA_FOUND simultaneously

 What is SQL sequence and what are their attributes?

Sequence is an Oracle object that is using for generating the sequence of number. IT=T is normally using for column that have primary key.

“CREATE SEQUENCE TRANS_ID_SEQ

START WITH 1

MAXVALUE 99999999

MINVALUE 1

NOCYCLE

NOCACHE

NOORDER”

 What is CYCLE AND NO CYCLE in sequence?

When we use “CYCLE” option in sequence and sequence reaches its MAXVALUE, it will start again from the MINVALUE. That is not good when we are using the sequence for primary key. In “NOCYCLE” option cursor did not start from minimum value again.

Can we design one view on another view?

Yes we can design one view on the other view.

 What are aggregate functions in Oracle?

Aggregate function are using in oracle with group by clause and with having clause. Avg, count, max, min, sum,

  How can we sort out our data in oracle?

We can sort out or data in oracle by using Order by clause. If we sort our data then we use this Order by Roll_Num AEC and fro descending order we will use Order by Roll_Num  Desc.

What is a PL/SQL package and what are the advantages of PL/SQL Packages?

A package is a collection of PL/SQL elements that are grouped together within a special BEGIN-END syntax.  A package is a schema object that groups logically related PL/SQL types, items, and subprograms.

Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. The specification (spec for short) is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the spec.

 What are the query and how many types of queries?

A database query is some type of syntax that is sent to database and get some data or information from the database. It is some type of communicator that is using for giving something and taking something from database.

• Simple Query
• Advanced Query
• Saved Query
• Cross-project Query

 What is the union, intersect and minus operation in oracle?

Create  table MY_A

(

My_ID number

)

Table A values: (1, 2, 3)

Create table MY_B

(

My_ID number

)

Table A values: (1, 2, 5)

For             UNION:

Select MY_ID from MY_A;

UNION

Select MY_ID from MY_B;

Result: 1, 2, 3,5

For             INTERSECT:

Select MY_ID from MY_A;

INTERSECT

Select MY_ID from MY_B;

Result: 1, 2

For             MINUS:

Select MY_ID from MY_A;

MINUS

Select MY_ID from MY_B;

Result: 3

 What is the difference between UNION and UNION ALL?

In UNION  if two rows will match then result come only of  two rows but if we use UNION ALL then result will be four rows.

Create  table MY_A

(

My_ID number

)

Table A values: (1, 2, 3)

Create table MY_B

(

My_ID number

)

Table B values: (1, 2, 5)

For             UNION:

Select MY_ID from MY_A;

UNION

Select MY_ID from MY_B;

Result: 1, 2, 3,5

For             UNION ALL:

Select MY_ID from MY_A;

UNION ALL

Select MY_ID from MY_B;

Result: 1,1, 2,2, 3,5

Explain in detail use of OCI in oracle?

OCI is an API that provides functions you can use to access the database server and control SQL execution. OCI supports the data types, calling conventions, syntax, and semantics of the C and C++ programming languages. You compile and link an OCI program much as you would any C or C++ program.

 Oracle function can take OUT parameter?

Yes we use OUT parameter in function.

 In form Parameter can we define default value?

Yes you can define initial value in form parameter.

 What is the difference between NO_DATA_FOUND and %NOTFOUND?

“NO_DATA_FOUND” is a system defined exception. It is generated when no record found in implicit cursor. “%NOTFOUND” is used in cursor. If cursor returns no row then %NOTFOUND returns true and if returns row then %NOTFOUND is false.

 Write a statement to find out the no. of rows in oracle?

Select count(*) from emp;

 Which function is use for display the numeric figure in words?

SELECT TO_CHAR(TO_DATE(123,'J'),'JSP') to_words FROM   dual;

What is the difference between WHERE clause and HAVING clause?

WHERE clause use in simple select statement on the other hand HAVING clause use in group function statement like.

Select emp_name from  WHEER dept_code=123;

Select emp_name from emp HAVING count(emp_cod) > 2;

 Write a difference between SUBSTR and INSTR function?

SUBSTR provide some portion of string like from one word to 5th word and INSTR gives us location   of that particular word.