Behavioral OCI Interview Questions & Answers:

1. What is difference between sap and oracle applications and which is now market leader?

Oracle is focused on helping computers do their jobs
better, while SAP is focused on helping people do their
jobs better
so u can guess now who is the market leader??

2. Can we use a having clause in groupby clause?

having cluase can only be used with group by clause.

if there are 500 records i will give rownumber<250 then it
fetch 249 records.

NVL2( string1, value_if_NOT_null, value_if_null )
if string1 is not null then it returns 'value_if_NOT_null'
and if string1 is null then it returns 'value_if_null'.


if there are four tables without using cartesian we have n-
1 join where n is number of tables. i.e 4-1=3 join required
four tables without using cartesian.

3. Tell me in function why return single value?

FUNCTION HAVING SINGLE RETURN PARAMEATER.IT IS USED FOR
CALICULATION PURPOSE.

EX: CRETE OR REPLACE FUNCTION [PARAMEATER] RETURN TYPE;
BEGIN
RETURN PARAMEATER;
END;

BUT FUNCTIONS RETURN MORE THAN ONE VALUES ALSO,BY USING
RECORD TYPES.

4. What is the main difference between foreign key and primary key?

primary key:- A primary contains unique and notnull.it means
a primary key column does not contain null values and
duplicates.A table containe only one primary key.using this
we can access the data from table easily.
foreign key:-a fk can contain duplicate values but it not
contain null values.using fk to establish relationship
between two tables. a parent table primary key should be
referenced by child table primary key.
one fk should be referenced by only one primary key the main difference between two keys is
pk is used to access the data from table.
fk is used to establish the relationship between two tables.
pk does not allowed duplicate and null values.
fk can allows duplicate values and does not allowed null
values

5. What is database normalization?

Normalization means the process in which raw data is
converted to filtered data.
Types of Normalization are:- 1NF,2NF,3NF.

1NF:- Table that contains one or more repeating groups
Identify and remove repeating groups.

2NF:-Describes a table that is fully functionally dependent
Full functional dependency - means that if B is
functionally dependent on A but not on any proper subset of
A, it is fully functionally dependent on A
Partial dependency - is if there are some attributes from A
that can be removed and the dependency still holds

3NF:- In 3NF we remove the transitive dependencies.
Transitive dependency - A->B and B->C implies that A->C
C is transitionally dependent on A through B

6. What is difference between SQL Server and Oracle?

ORACLE and SQLSERVER BOTH RDMS.oracle supports 11+1/2
E.F.codd rules out of 12 rules.but remaining rdms supports
only 5 rules.compare with sqlserver ,oracle has more secure
and high performance to retrieval the data from database.
but sql server supports only microsoft operating systems.
oracle can support every operating system.
using oracle pl/sql ,we can send block of queries to
oracleserver at a time.but IN sqlserver we can not send
multiple queries at a time to the server.

7. Suppose I have table that has student names in it. If I query on the names my result set should return as VALUE NOT ENTERED for null entries?

select nvl(ename,'value not available') from student;