What are the differences between unique key and primary key?
Primary key cannot contain null value whereas unique key can contain one and only one null value
In case of sql server, with default options, primary key is created as a clustered index while unique key is created as a non clustered index
A unique key is similar to primary key but we can have more than one unique key per table
What are the types of joins in Informatica and in Oracle?
There are four types of joins in oracle
equi join
non equi join
self join
outer join
Joins in informatica
master join (right outer join)
detailed join (left outer join)
normal join
What is the difference between RowId and RowNum?
RowId is the physical address of a row. If we know the RowId, we can read entire row.
RowNum is the temporary number allocated during query execution
What are pseudo columns? What are the various types of pseudo columns?
Pseudo columns are columns which are not in the table but they can be used in sequel queries as if they are part of the table
RowNum
RowId
Sysdate
User
Currval
Nextval
What are the various types of Statements in Oracle?
The various Statements that are available in Oracle are
Data Manipulation Language (DML) Statements
Data Definition Language (DDL) Statements
Transaction Control (TC) Statements
Session Control (SC) Statements
System Control (SC) Statements
What are the various types of DML Statements?
Select
Update
Delete
Insert
Merge
What are various AGGREGATOR FUNCTIONS?
SUM
AVG
MIN
MAX
COUNT
STDDEV
VARIANCE
FIRST
LAST
What are the types of joins in oracle?
Equijoins or simple joins or inner joins
Non-equijoin
Outer join
Self join
SUM
AVG
MIN
MAX
COUNT
STDDEV
VARIANCE
FIRST
LAST
What are the types of joins in oracle?
Equijoins or simple joins or inner joins
Non-equijoin
Outer join
Self join
Difference between char and varchar?
Char is a fixed length data type where as varchar is a variable length data type. So in case of char, though we entered less size for a column, it is going to allocate max size and in case of varchar, it is not going to allocate the maximum size.
Char is a fixed length data type where as varchar is a variable length data type. So in case of char, though we entered less size for a column, it is going to allocate max size and in case of varchar, it is not going to allocate the maximum size.
Difference between varchar and varchar2
Varchar and varchar2 are variable length datatypes. Varchar datatype has a size of 2000 bytes whereas varchar2 has a size of 4000 bytes.
Varchar is in ascii whereas varchar2 is in unicode.
Varchar and varchar2 are variable length datatypes. Varchar datatype has a size of 2000 bytes whereas varchar2 has a size of 4000 bytes.
Varchar is in ascii whereas varchar2 is in unicode.
What is Normalisation? Define 1NF, 2NF and 3NF.
It is the process of reducing the complex data structure into a simpler one by removing the redundancy.
First normal form: First normal form state that each field is atomic.
Second normal form: Second normal form states that data redundancy can be reduced if all the non key attributes which are dependent on one of the primary keys of a composite primary key are put to a separate table along with depended primary key . This should also satisfy the 1nf. For example, if a table has attributes like partid, city, state and country and partid, country for a composite primary key and city, state depend on country then the table is sepeated as two different tables as attributes having partid, country and country, state, city.
Third normal form: Third normal form states that if a dependency exist between non key attributes, then these attributes are isolated to a different table. This should also satisfy the 1nf + 2nf.
It is the process of reducing the complex data structure into a simpler one by removing the redundancy.
First normal form: First normal form state that each field is atomic.
Second normal form: Second normal form states that data redundancy can be reduced if all the non key attributes which are dependent on one of the primary keys of a composite primary key are put to a separate table along with depended primary key . This should also satisfy the 1nf. For example, if a table has attributes like partid, city, state and country and partid, country for a composite primary key and city, state depend on country then the table is sepeated as two different tables as attributes having partid, country and country, state, city.
Third normal form: Third normal form states that if a dependency exist between non key attributes, then these attributes are isolated to a different table. This should also satisfy the 1nf + 2nf.
