No answers, but these are useful questions for conducting Oracle interview. The readers are welcome to contribute their answers.
- What is an oracle instance?
- What is a view?
- What is referential integrity?
- Name the data dictionary that stores user-defined constraints?
- What is a collection of privileges?
- What is a snapshot?
- What is a synonym?
- What is a cursor?
- What is a sequence?
- What is a trigger?
- What is an exception?
- What is a partition of table?
- What are pseudo-columns in SQL? Provide examples.
- What are the Data Control statements?
- What is a schema?
- What is a type?
- What is a data model?
- What is a relation?
- Advantages of redo log files?
- What is an Archiver?
- What is a database buffer cache?
- What are the background processes in Oracle?
- %type and %rowtype are attributes for…?
- What are the steps in a two-phase commit?
- What is a union, intersect, minus?
- What is a join, explain the types of joins?
- What is a co-related sub-query?
- ODBC stands for…?
- Data-type used to work with integers is?
- Describe data models?
- Describe the Normalization principles?
- What are the types of Normalization?
- What is de-normalization?
121 Comments on Oracle interview questions
Hi Chetan,
Below is the solution for your query.
select * from (select sum(decode(t2, ‘A’, 1)) A,
sum(decode(t2, ‘B’, 2)) B,
sum(decode(t2, ‘C’, 3)) C
from oemp)
Hi,
Dear Satyam,
U r really Genies.
Thanx very very much.
This query help another also…….
Pl. give me ur mail id & Fwd on my mailid given above
Please……
Regards
Chetan
Great satyam,
But it should be
select * from (select sum(decode(t2, ‘A’, 1)) A,
sum(decode(t2, ‘B’, 2)) B,
sum(decode(t2, ‘C’, 3)) C
from oemp)
Great satyam,
But it should be
SELECT SUM(A) A,SUM(B) B,SUM(C) C
FROM
(
SELECT DECODE(T2,’A',1) A, DECODE (T2,’B',2) B, DECODE(T2,’C',3) C FROM OEMP
)
answer to :
How to check the rows of a table where anyone column of a table is null? Plz give me the query?
Thank you Vats, the query I am asking is I am having 3 columns in a table. Now I want the rows in which any one column is null it may be 1 or 2 or 3. Here, I don’t know the columns. How to manipulate the query?
For Example
Rownum col1 col2 col3
1 1 null 2
2 null 1 null
3 null null null
4 1 2 3
The expected result for me is
Rownum col1 col2 col3
1 1 null 2
2 null 1 null
3 null null null
I think this will elaborate more.
ANSWER::
SELECT * FROM table_name
WHERE col1 IS NULL
OR
col2 IS NULL
OR
col3 IS NULL
I would like to answer the following question:
What is a database buffer cache?
Database buffer cache is one of the required SGA component. It caches recent accessed data. It keeps the entry (transaction) in the buffer cache till it is written on the disk (which is done by DBWn background process) at a scheduled time or when ever possible by Oracle. Blocks in database buffer cache can be in the any of the following state: PINNED(it is an active transaction), CLEAN (instance is just started so it is empty or ti has been flushed to the disk and can be reused) , DIRTY (transaction is completed but blocks are waiting to be written on the disk by DBWn).
I have two tables A & B with one column each.and i am issuing the following statements in a session
insert into A values(’1′);
insert into B values(’2′);
insert into A values(’3′);
insert into B values(’4′);
Rollback;
Now i want data in table B should remain there .it should not be rolled backed?
How can i acheive this?
hi.
write a single query to disply only the alphabets from the given pattern below
“A,C&B$.@D”
when do we say >STARTUP MIGRATE?
what is REGULAR EXPRESSION IN ORACLE DATABASE?
what is object dependency?
Sourav,
To do selective rollback use Savepoints.
insert into A values(’1′);
insert into A values(’3′);
Savepoint A;
insert into B values(’2′);
insert into B values(’4′);
Rollback to A;
Commit;
Can you tell me if it is posssible to insert reocrds into a valueset through the backend -oracle? If so how and what will happen as when we enter values through the front-end it automatically goes for compiling hierarchies in the view request window?
How can I add a column between two columns (not at the last)
The following are table names followed by column names present in it
emp_company(ename,cname,salary,jdate)
company(cname,city)
manager(ename,mname)
employee(ename,city)
emp_shift(ename,shift)
where
ename=employee name
cname=company name
jdate=date of join
city in employee table means city in which employee resides
city in company table mean city in which company presents
mname=manager name
shift means the time in which employee works
now please give the queries for the following tasks
1.give the names of employees living in the same city where their manager is also living
2.give the salary of manager o0f employees living in city ‘bombay’
the queries are
select c.ename,c.mname,c.employee_city,d.city as manager_city
(
select a.ename,a.mname,b.city as employee_city
(select ename,mname from manager)a
left join
(select ename,city from employee)b
on
a.ename=b.ename
)
c
left join
(select ename,city from employee)d
on
a.mname=b.ename
where
c.employee_city=d.city
Second query is alot easier then this
What is the difference btw/ a string connection say (ODBC) and a direct connection?
hi can any1 tel me how to solve these 2 queries
1.display dptno. wise 1st three highest salaries in ascending order of deptno and descending order of sal
2.display range of sal wise no. of employees in the diff of rs.999 in the foll. format
range of sal count
0 15
1 17
2 4
3 9
where 0->0-999
1->1000-1999
2->2000-2999
hi can any1 tel me how to solve these 2 queries
1.display dptno. wise 1st three highest salaries in ascending order of deptno and descending order of sal
2.display range of sal wise no. of employees in the diff of rs.999 in the foll. format
range of sal count
0 15
1 17
2 4
3 9
where 0->0-999
1->1000-1999
2->2000-2999
Ques : in a table’s cloumn we have some positive values and some negative values
like +30, -40, +60 etc. how i can find the addition of positive values
and negative values in a single column.
Ans : this problem can be solved by using decode or case clause
Ques : Can we update two tables in a single statement? If yes, how?
Ans : Yes i can
Example : update act a,concept c set a.action =’vishwanat’ , c.description=’visnth’
where a.action_id =1 or c.concept_id=1;
Ques : ques no:116–>hello bibha
ANS : Select sal,deptno from (select sal,DEPTNO from emp order by sal desc) where rownum < 4;
i have a table num
create table num
(no number);
in no column we have 10 values -
1
1
1
2
null
2
4
null
null
5
when we write
select distinct no from num order by no desc;
output will be
null
5
4
2
1
if null is not equal to null,then why null is shown only once in output ? why not three times.
I would like to answer the following question:
in a table’s cloumn we have some positive values and some negative values
like +30, -40, +60 etc. how i can find the addition of positive values
and negative values in a single column.
Answer:
SELECT SUM(X.POS), SUM(X.NEG)
FROM
(SELECT
(CASE WHEN col1 > 0 then col1 else 0 ENd CASE ) POS
(CASE WHEN col1 < 0 then col1 else 0 END CASE ) NEG
FROM
Tab_name) X