Oracle SQL Interview Questions and Answers.

1. How do I eliminate the duplicate rows ?

Ans: delete from table_name where rowid not in (select max(rowid) from table group by

duplicate_values_field_name);

or

delete duplicate_values_field_name dv from table_name ta where rowid <(select

min(rowid) from table_name tb where ta.dv=tb.dv);

2.How do I display row number with records?

Ans:Select rownum,emp.* from emp

3.Display the records between two range?

Ans:

select rownum, empno, ename from emp where rowid in (select rowid from emp where rownum

<=&upto minus select rowid from emp where rownum<&Start);

Enter value for upto: 10

Enter value for Start: 7

4.I know the nvl function only allows the same data type(ie. number or char or date

Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display,

instead of blank space. How do I write the query?

Ans:select nvl(to_char(comm.),'NA') from emp;

5. Find out nth highest salary from emp table?

Ans:SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM

EMP B WHERE a.sal<=b.sal);

or

SELECT * FROM (SELECT DISTINCT(SAL),DENSE_RANK() OVER (ORDER BY SAL DESC) AS RNK FROM EMP) WHERE RNK=&N

or

select min(sal) from (select distinct sal from emp order by sal desc) where rownum <=&n

6. Find out nth highest salary DEPT wise from emp table?

Ans:SELECT * FROM (SELECT DISTINCT(SAL),DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS RNK FROM EMP) WHERE RNK=&N

7. Display Odd/ Even number of records?

Ans:Odd number of records:

select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);

Even number of records:

select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp);

8.What are the more common pseudo-columns?

Ans: SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM

9.How To Display last 5 records in a table?

Ans: select * from (select rownum r, emp.* from emp) where r between (Select count(*)-5 from emp)

and

(Select count(*) from emp)

10.How To Display last record in a table?

Ans: select * from (select rownum r, emp.* from emp) where r in (Select count(*) from emp)

11. How To Display particular nth record in a table?

Ans: select * from (select rownum r, emp.* from emp) where r in (2) or r=2

12.How To Display even or odd records in a table?

Ans:select * from (select emp.* , rownum r from emp) where mod (r,2)=0

13. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Ans:Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT

statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING

behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a

query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

14.What is sub-query? Explain properties of sub-query?

Ans:Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed

arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of

parentheses. Sub-queries are generally used to return a single row as an atomic value, though they

may be used to compare values against multiple rows with the IN keyword.

A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT

statement if executed independently of the T-SQL statement, in which it is nested, will return a result

set. Meaning a subquery SELECT statement can standalone and is not depended on the statement in

which it is nested. A subquery SELECT statement can return any number of values, and can be found

in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a

T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery

can be used anywhere an expression can be used.

15.Properties of Sub-Query

Ans: A subquery must be enclosed in the parenthesis.

A subquery must be put in the right hand of the comparison operator, and

A subquery cannot contain a ORDER-BY clause.

A query can contain more than one sub-queries.

16. What are types of sub-queries?

Ans:Single-row sub query, where the subquery returns only one row.

Multiple-row sub query, where the subquery returns multiple rows,.and

Multiple column subquery, where the sub query returns multiple columns.

17. what is the out put for query select * from emp where rownum<=3

Ans: it display first 3 Records

18.what is the out put for query select * from emp where rownum=1;

Ans: it display first Record in the table

19. what is the out put for query select * from emp where rownum=2;

Ans: it will not display any record

20.what is the out put for query select * from emp where rownum>1;

Ans: even this also will not display the records. why because when it fetch the first record rownum is 1 so condition fail so it will not get first record when it fetches 2nd record rownum is again 1 because it didn't pick up first record so 2nd time also condition failed.

21. How to display Top N salaries in emp?

Ans: select * from (select distinct sal from emp order by sal desc) where rownum<=&n

22. How To display Last Record in emp table?

Ans: Select * from ( select rownum as rn,emp.* from emp) where rn in(select count(*) from emp)

23. How To display First and last Records in emp table?

Ans:select * from ( select rownum as rn,emp.* from emp) where rn in(1,(select count(*) from emp))

24. How to Diplay 1,5,8 records in emp table?

Ans: select * from ( select rownum as rn,emp.* from emp) where rn in (1,5,8)

25. In Oracle, can we add a Not Null column to a table with data? If "No" then how

can we do that?

Ans:No, we cannot add a Not Null column to a table with data. Oracle throws Error ORA-01758.

See example below!

Eg: alter table EMP add comm2 number not null

Error: ORA-01758: table must be empty to add mandatory (NOT NULL) column.

Workaround:

Provide a Default value to the column being added, along with the NOT NULL constraint. Then the

column will get added with the default value for all existing rows.

Eg: alter table EMP add comm2 number not null default 100 -- Comm2 will have 100 for all rows

26. While doing an ascending order sort on a column having NULL values, where does

the NULLs show up in the result set? At the beginning or at the end?

Ascending order sort - NULLs come last because Oracle treats NULLs are the largest possible values

Descending order sort - NULLs come first

* How to make NULLs come last in descending order sort?

Add NULLS LAST to the order by desc clause

Eg: select col1 from table1 order by col1 desc NULLS LAST

27. how to set Time of execution of an SQL Statement

first run this in sql prompt: set timing on

After execution of each query we get the time take for it

if you don't want run this : set timing off

28.What is the Datatype of NULL in Oracle?

Ans:Datatype of NULL is "char(0)" and size is '0'

29.Oracle Functions - Replace versus Trim

SQL> select replace('jose. antony@ yahoo.com',' ', null) as Replace1 from dual;

REPLACE1

--------------------

jose.antony@yahoo.com --Removes all spaces from in-between

SQL> select trim('jose. antony@ yahoo.com') as Trim1 from dual;

TRIM1

----------------------

jose. antony@ yahoo.com --Removes spaces from both sides only

30. Explain ROWID in Oracle?

ROWID is a unique hexadecimal value which Oracle inserts to identify each record being inserted. It is

used for all Full Table scans.

Structure:

OOOOOOFFFBBBBBBRRR

OOOOOO - First six characters is the Object Number which idenities the Data Segment

FFF - Next 3 characters is the Database File number

BBBBBB - Next 6 characters shows the DataBlock number

RRR -Next 3 characters identified the Row within the block

31. What is difference between Co-related sub query and nested sub query?

Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.

Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.

For example,

Correlated Subquery:

select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)

Nested Subquery:

select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)

32.What is the difference between TRUNCATE and DELETE commands?

Ans:Both will result in deleting all the rows in the table .TRUNCATE call cannot be rolled back as it is a DDL command and all memory space for that table is released back to the server. TRUNCATE is much faster.Whereas DELETE call is an DML command and can be rolled back.

33. How to find out the duplicate column

Ans: select column_name,count(*) from table_name having count(*)>1

if the result more than 1 then we can say that this column having duplicate records

34. How to find 2nd max salary from emp ?

Ans: select max(sal) from emp where sal not in(select max(sal) from emp)

35. How to find max salary department wise in emp table?

Ans:select deptno,max(sal) from emp group by deptno;

36. How to find 2nd max salary department wise in emp table?

select deptno,max(sal) from emp where (deptno,sal) not in(select deptno,max(sal) from emp group by deptno) group by deptno;

37. Table1 having 10 records and table2 having 10 records both tables having 5 matching records. then how many records will display in 1. equi join 2.left outer join 3. right outer join 4. full outer join

Ans: 1.in equi join matching records will display it means 5records will display

2.in left outer join matching 5 and non matching 5 records in left table so total 10 will display

3.

in right outer join matching 5 and non matching 5 records in right table so total 10 will display.

4.

in full outer join matching 5 and non matching 5 records in left table and non matching records in right table so total 15 will display

38.

EMP table, for those emp whose Hiredate is same, update their sal by "sal+500" or else for others keep the sal as it is, how to do it by SQL query?

Ans:UPDATE emp SET sal=sal+500 WHERE hiredate IN (SELECT hiredate FROM employees HAVING COUNT(*)>1 GROUP BY hiredate)