Wednesday, July 13, 2011

oracle frequently asked questions


Interview questions & answers
1)What is the maximum number of columns in a table in Oracle?
Table or view can have upto 1000 columns. If it exceeds it shows the error like
ORA-01792: maximum number of columns in a table or view is 1000. To add new columns, we have to drop unused columns by using the syntax like:
ALTER TABLE DROP UNUSED COLUMNS (or)
MySQL: Maximum number of columns in one table - 3398; size of a table row - 65534 (BLOB and TEXT not included). Oracle: Unlimited rows@table. Maximum number of columns in one table - 1000. Up to 32 columns in index key.
 2) In Oracle varchar2 takes dynamic space for storage then why char is still in oracle?
The mejor defference between varchar2 and char is fixed length and variable length . varchar2 have varible length mean if we declare as 20 space and its use only 5 space the memory asigne only 5 . but in char takes daclare space while use any number space less than declare          [or]
char is used if we know that the length won’t exceed the specified range while varchar is used for varying range. If we want any string not exceeding 6 we use char(6) because we can put a constraint if someone is trying to enter more or less than 6.
 3)what is the difference between primary key, unique key, sorrougate key?
Surrogate key:- An ID column populated via a sequence is known as
a surrogate key.
Primary Key:
1) It creates clustered index by default 2) It doesn't allow nulls
Unique Key:
1) It creates non-clustered index by default.2) It allows only one null value.
4) What is difference between varchar and varchar2
varchar is ANSI standardvarchar2 is Oracle.in addition that varchar(10)-in this declaration it allocate 10 spaces in memory.if suppose ur using 4 charecter the extra space wasted. name varcher2(10)-in this declaration the extra space used by other operations automatically.
5) write a query to display the no.of people with the same job
select count(name) from emp e where 1>(select count(*) from emp x where x.job=e.job) [0r]
SELECT job COUNT(*)  FROM emp GROUP BY job;
6) What is the diffrence between and constraints and triggers?
Both are used for Business Rules...
But the major diff is... Triggers can’t fire on the Pre-Loaded data in the table where as Constraints can be. [or]
1.constraints are oracles predefined business rules.but triggers are user defined business rules.
2.constraints validates the data immediately but triggers validates the data when event occurs.
7) What is the Difference between Replace and Translate
replace function replaces entire sting as required but translate function translate character by character.
8) Create a query that display the last name,hire date and the day of the week on which the employee started. Label the column DAY. Order the results by the day of the week starting with Monday.
SELECT last_name hire_date TO_CHAR(hire_date 'DAY') AS DAYFROM employeesORDER BY DAY; [or]
select last_name hiredate to_char(hiredate 'day') day from emp [or]SELECT last_name hire_date to_char(hire_date 'DAY') DAY to_char(hire_date 'D') FROM employees
ORDER BY to_char(hire_date-1 'd');
9) create a query that will display the total no.of employees and, of that total, the no.of employees hired in 1995,1996,1997, and 1998. create appropriate column headings.
select Count(*) as NumberOfEmployees  EmployeesHiredIn1995_1996_1997_1998 =(select count(*) from employees where hiredate like ' 1995 'or hiredate like ' 1996 'or hiredate like ' 1997 'or hiredate like ' 1998 ')
from employees
10) what is the difference between rownum,rowed
Rownum is just the serial No of your output while Rowid is automatically generated unique id of a row and  it is generated at the time of insertion of row.
Rownum is numeric and rowid is 16 bit hexadecimal no.
11) What is Pro*C? What is OCI?
Pro *C: The Pro* c/C++ precompiler takes the SQL statement that embeded in C/C++ code convert into standard C/C++ code . when succefully precompile this code the result is a C or C++ programe that we compile and used to build the application that access the Oracle Application
OCI :- OCI refere to Oracle Call interface is set of Low Lavel API(Applcation Program Interface Call) used to intract with Oracle Database. By OCI one can use the operation such as Logon Fatch parse excute etc. Generally these are written in C/C++.
These can be written in any language.
11) What is the difference between "NULL in C" and "NULL in Oracle?"
The NULL in C treated as Zero or void. but in SQL NULL value is Non or blank represented it can't manuplated
12) Can we create sequence to a View??
S. We can create sequence to a view. If the view have writtable permission then the new values are Inserted/Updated/Deleted into TABLE.
13) Why do we need to use 'WHERE CURRENT OF' clause ?.
Ans)
14) What is the Life of an SQL Statement?
Till the time of execution the sql staement is live.After the execution it has gone.
But I dont think that any DB has mentioned about this.
14) What is the difference between Instance and a Database?
Instance is the memory structures and background processes used to interact with Database.
Instance = SGA + Background Process
Database is the physical files used to store information. the 3 types of physical files are: Datafiles Control File and Redo-Log fileS(minimum 2).
15) how many columns can be in the group by clause
A GROUP BY clause can display the number of columns that are used in the SELECT statement except for aggrerations.
16) Can you use a commit statement within a database trigger?
Yes we can use commit inside a trigger by using pragma_autonomous_transactions
 17) find the two minimum salaries among table
Select min(sal) from emp unionSelect min(sal) from emp where sal>(Select min(sal) from emp)[or]
select min(sal) from emp union select min(sal) from (select sal from emp where sal>(select min(sal) from emp)); [or]
select sal rownum from emp where rownum<=2order by sal;
 18) Table Level and Column Level constraints
We have two type of constraints - Table Level and Column Level. Which is better and why..?
If the same constraint is used in coloum leve or table leve then there is no difference in functanality i.e. if you are using primary key ore foreign key for the one column. This kind of constraint could be in table level or column leve and there will be no difference. But if you have to have a primary key and foreign key constraint for the multiple columns then you cannot use this constraint in column level these constraints are to be defined at table level.
SQL*PLUS is a command line tool whereas SQL and PL/SQL language interface and reporting tool. It’s a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. 
Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed. 
 21) What are different Oracle database objects?
TABLES  ,VIEWS ,INDEXES ,SYNONYMS ,SEQUENCES,TABLESPACES etc.
 22) What is a view ?
view is a window to see data from one or more tables.
23) What is difference between SUBSTR and INSTR?
SUBSTR: it extracts the string of determined length.whereras (i.e. it returns character for specified position)INSTR:it returns the number of named string.(i.e. it returns number )
 24) Explain what is mutation and what is mutating table and how this mutation problem is solved in a table
Mutation happens in case of triggers. A 'Mutating table' is a table which is being updated by Insert update or delete triggers.It can also be a table which is being updated when delete cascade is run.
Mutation occurs when a trigger is trying to update a row which it is using currently. To solve this either we have to use intermediate table or a view so that it can choose from one while updating the other.
you can avoid mutation by using autonomous transaction pragma
25) Create a query that displays the employees' last names and commision amounts.If an employee does not earn commmission, put "No Commission." Label the column COMM.
select last_ename comm nvl(to_char(comm) 'no commission') comm from emp where comm is null;










No comments:

Post a Comment