oracle interview questions and answers free download

Oracle Interview Questions and Answers

26.  HOW MANY COLUMNS CAN A TABLE HAVE?

Ans: A Table can have 1000 columns.

27.  WHAT ARE DIFFERENT DATATYPES SUPPORTED BY SQL?

Ans: Char (size), Nchar (size), Varchar2 (size), Nvarchar2 (size) data

types for character values,

Number (precision, scale), Number, Number (n), Float, Float (binary precision) data types for numerical values, Date data type for date values,

Long, Raw (size), Long Raw, Clob, Blob, Nclob, Bfile for large objects.

28. WHAT IS DIFFERENCE BETWEEN LONG AND LOB DATATYPES?

Ans:    LOB     LONG

1) The maximum size is 4GB.

2) LOBs (except NCLOB) can be attributes of an object type.

3) LOBs support random access to data.

4) Multiple LOB columns per table or LOB attributes in an object type.      

1) The maximum size is 2GB.  2) LONGs cannot.    3) LONGs support only

sequential access.

4) Only one LONG column was allowed in a table

29. WHAT IS DIFFERENCE BETWEEN CHAR AND VARCHAR2 DATATYPES?

Ans: Varchar2 is similar to Char but can store variable no. Of

characters and while querying the table varchar2  trims the extra spaces from the column and fetches the rows that exactly match the criteria.

30.  HOW MUCH MEMORY IS ALLOCATED FOR DATE DATATYPE? WHAT IS DEFAULT

DATE  FORMAT IN ORACLE?

Ans: For Date data type oracle allocates 7 bytes Memory.

  Default Date Format is: DD-MON-YY.

31. WHAT IS RANGE FOR EACH DATATYPE OF SQL?

Ans:

Datatype          Range

Char   Varchar2  Number    Float     LONG, RAW, LONGRAW  Large Objects

(LOB’s) 2000 bytes  4000 bytes 

Precision 1 to 38 Scale -84 to 127  Precision 38 decimals Or 122 binary

precision   2 GB  4GB

32.  HOW TO RENAME A COLUMN?

Ans: We can’t rename a Column of a table directly. So we follow the

following steps.

To Rename a Column:

a)         Alter the table specifying new column name to be given and data type.

b)         Then copy the values in the column to be renamed into new column.

c)         drop the old column.

33.  HOW TO DECREASE SIZE OR CHANGE DATATYPE OF A COLUMN?

Ans: To Decrease the size of a Data type of a column

i.          Truncate the table first.

ii.         Alter the table column whose size is to be decreased using the same

name and data type but new size.

34. WHAT IS A CONSTRAINT? WHAT ARE ITS VARIOUS LEVELS?

Ans: Constraint: Constraints are representators of the column to

enforce data entity and consistency.There r two levels

1)Column-level constraints 2)Table-level constraints.

35. LIST OUT ALL THE CONSTRAINTS SUPPORTED BY SQL ?

Ans: Not Null, Unique, Check, Primary Key and Foreign Key or Referential Integrity.

36.  WHAT IS DIFFERENCE BETWEEN UNIQUE+NOT NULL AND PRIMARY KEY?

Ans: Unique and Not Null is a combination of two Constraints that can be present any number of times in a table and can’t be a referential key to any column of an another table where as Primary Key is single Constraint that can be only once for table and can be a referential key to a column of another table becoming a referential integrity.

37. WHAT IS A COMPOSITE PRIMARY KEY?

Ans: A Primary key created on combination of columns is called Composite Primary Key.

38. WHAT IS A CANDIDATE COLUMN? HOW MANY CANDIDATE COLUMNS CAN BE POSSIBLE PER COMPOSITE PRIMARY KEY?

Ans: It is a part of composite primary key.  Maximum 32 candidate key can be there in composite primary key.

39. HOW TO DEFINE A NULL VALUE?

Ans: A NULL value is something which is unavailable, it is neither zero

nor a space and any mathematical calculation with NULL is always NULL.

40. WHAT IS NULL?  A CONSTRAINT OR DEFAULT VALUE?

Ans: It is a default value.

41.  WHAT IS DEFAULT VALUE FOR EVERY COLUMN OF A TABLE?

Ans: NULL.

42.  WHAT IS CREATED IMPLICITLY FOR EVERY UNIQUE AND PRIMARY KEY COLUMNS?

Ans: Index.

43.  WHAT ARE LIMITATIONS OF CHECK CONSTRAINT?

Ans: In this we can't specify Pseudo Columns like sysdate etc.

44.  WHAT IS DIFFERENCE BETWEEN REFERENCES AND FOREIGN KEY CONSTRAINT?

Ans: References is used as column level key word where as foreign key

is used as table level constraint.

45. WHAT IS "ON DELETE CASCADE"?

Ans: when this key word is included in the definition of a child table  then whenever the records from the parent table is deleted automatically the respective values in the child table will be deleted.

46. WHAT IS PARENT-CHILD OR MASTER-DETAIL RELATIONSHIP?

Ans: A table which references a column of another table(using References)is called  as a child table(detail table) and a table  which is being referred  is called Parent (Master) Table .

47.  HOW TO DROP A PARENT TABLE WHEN IT’S CHILD TABLE EXISTS?

Ans: Using "on delete cascade".

48. IS ORACLE CASE SENSITIVE?

Ans: NO

49.  HOW ORACLE IDENTIFIES EACH RECORD OF TABLE UNIQUELY?

Ans: By Creating indexes and reference IDs.

50. WHAT IS A PSEUDO-COLUMN? NAME SOME PSEUDO-COLUMNS OF ORACLE?

Ans: Columns that are not created explicitly by the user and can be

used explicitly in queries  are called Pseudo-Columns.

Ex: currval,nextval,sysdate,new,old,sqlcode,sqlerrm,rownum,rowid,level