SQL Complex Queries

Search

Home Joomla! Java Flex Stelent/Oracle UCM Database Php Web

Quiz Downloads

You are here: Home Database SQL Tutorial

Complex Queries in SQL ( Oracle )

These questions are the most frequently asked in

interviews.

1. To fetch ALTERNATE records from a table. (EVEN NUMBERED)

select * from emp where rowid in (select
decode(mod(rownum,2),0,rowid, null) from emp);
2. To select ALTERNATE records from a table. (ODD NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,null
,rowid) from emp);
3. Find the 3rd MAX salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from
emp e2 where e1.sal <= e2.sal);
4. Find the 3rd MIN salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from
emp e2where e1.sal >= e2.sal);
5. Select FIRST n records from a table.
select * from emp where rownum <= &n;
6. Select LAST n records from a table
select * from emp minus select * from emp where rownum <= (select
count(*) - &n from emp);
7. List dept no., Dept name for all the departments in which there are no
employees in the department.
select * from dept where deptno not in (select deptno from emp);  
alternate solution:  select * from dept a where not exists (select * from emp
b where a.deptno = b.deptno);
altertnate solution:  select empno,ename,b.deptno,dname from emp a,
dept b where a.deptno(+) = b.deptno and empno is null;
MOST READ
8. How to get 3 Max salaries ? ARTICLES
select distinct sal from emp a where 3 >= (select count(distinct sal) from
emp b where a.sal <= b.sal) order by a.sal desc;
Computer
9. How to get 3 Min salaries ?
Awareness
select distinct sal from emp a  where 3 >= (select count(distinct sal) from
Home
emp b  where a.sal >= b.sal);
Complex

http://www.bullraider.com/database/sql-tutorial/7-complex-queries-in-sql[9/23/2013 7:05:47 PM]

SQL Complex Queries

10. How to get nth max salaries ?

Queries in SQL
select distinct hiredate from emp a where &n =  (select count(distinct sal) ( Oracle )
from emp b where a.sal >= b.sal);
Struts2
11. Select DISTINCT RECORDS from emp table.
Interview
select * from emp a where  rowid = (select max(rowid) from emp b where  Questions and
a.empno=b.empno); Answers
12. How to delete duplicate rows in a table? Java
delete from emp a where rowid != (select max(rowid) from emp b where  Programming
a.empno=b.empno); Interview
13. Count of number of employees in  department  wise. Questions
select count(EMPNO), b.deptno, dname from emp a, dept b where Informatica
a.deptno(+)=b.deptno  group by b.deptno,dname; Quiz Home

14.  Suppose there is annual salary information provided by emp table.

How to fetch monthly salary of each and every employee?

select ename,sal/12 as monthlysal from emp;

15. Select all record from emp table where deptno =10 or 40.

select * from emp where deptno=30 or deptno=10;

16. Select all record from emp table where deptno=30 and sal>1500.

select * from emp where deptno=30 and sal>1500;

17. Select  all record  from emp where job not in SALESMAN  or CLERK.

select * from emp where job not in ('SALESMAN','CLERK');

18. Select all record from emp where ename in

'BLAKE','SCOTT','KING'and'FORD'.

select * from emp where ename

in('JONES','BLAKE','SCOTT','KING','FORD');

19. Select all records where ename starts with ‘S’ and its lenth is 6 char.

select * from emp where ename like'S____';

20. Select all records where ename may be any no of  character but it
should end with ‘R’.

select * from emp where ename like'%R';

21. Count  MGR and their salary in emp table.

select count(MGR),count(sal) from emp;

22. In emp table add comm+sal as total sal  .

http://www.bullraider.com/database/sql-tutorial/7-complex-queries-in-sql[9/23/2013 7:05:47 PM]

SQL Complex Queries

select ename,(sal+nvl(comm,0)) as totalsal from emp;

23. Select  any salary <3000 from emp table. 

select * from emp  where sal> any(select sal from emp where sal<3000);

24. Select  all salary <3000 from emp table. 

select * from emp  where sal> all(select sal from emp where sal<3000);

25. Select all the employee  group by deptno and sal in descending
order.

select ename,deptno,sal from emp order by deptno,sal desc;

26. How can I create an empty table emp1 with same structure as emp?

Create table emp1 as select * from emp where 1=2;

27. How to retrive record where sal between 1000 to 2000?

Select * from emp where sal>=1000 And  sal<2000

28. Select all records where dept no of both emp and dept table
matches.
select * from emp where exists(select * from dept where
emp.deptno=dept.deptno)

29. If there are two tables emp1 and emp2, and both have common
record. How can I fetch all the recods but common records only
once?
(Select * from emp) Union (Select * from emp1)

30. How to fetch only common records from two tables emp and emp1?
(Select * from emp) Intersect (Select * from emp1)

31.  How can I retrive all records of emp1 those should not present in
emp2?
(Select * from emp) Minus (Select * from emp1)

32. Count the totalsa  deptno wise where more than 2 employees exist.
SELECT  deptno, sum(sal) As totalsal
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 2

http://www.bullraider.com/database/sql-tutorial/7-complex-queries-in-sql[9/23/2013 7:05:47 PM]

SQL Complex Queries

Next >

37 Comments.

Ram Said... 2013-02-07 2

Answer to Aneela Question: select ename, hiredate,

tochar(hiredate, 'd'), tochar(hiredate, 'DAY') from emp order by
to_char(hiredate, 'd');

Naina Said... 2012-11-13 5

Hi, Thanks a lot...got to learn...I have a question... I have a table

with deptno and ename as columns.... It has values like Deptno
Ename 10 aaa 10 bbb 20 ccc 20 ddd I want my o/p like 10
aaa,bbb 20 ccc,ddd Is there any function to get this result.

lakshmi Said... 2012-10-23 9

can anybody tell me how to get dept wise highest sal?? thanks in
advance

Ashish Said... 2012-10-08 0

Output: we will get the below output through hierarchal queries.

empno manager1 manager2 manager3 manager4 7788 7699
3425 8756 2876
Thanks, Ashish Niranjan

Martin Jerenje Said... 2012-09-28 0

Brilliant. I like it Bravo

Deepak Said... 2012-09-21 3

Thank's , its very helpfull

http://www.bullraider.com/database/sql-tutorial/7-complex-queries-in-sql[9/23/2013 7:05:47 PM]

SQL Complex Queries

pooja Said... 2012-09-16 -2

it is very helpful.thanks

srujan Said... 2012-08-27 -3

What a nice article.. You made my day

FAIZAN KHANANI Said... 2012-08-16 0

SELECT CONFIRMDATE AS TRANSDATE, ITEMID , NAME AS

ITEMNAME, 0 AS DEBITQTY, QTY AS CREDITQTY, 0 AS
DEBITAMOUNT, LINEAMOUNT AS CREDITAMOUNT FROM
CUSTCONFIRMTRANS union all SELECT DELIVERYDATE AS
TRANSDATE, ITEMID , NAME AS ITEMNAME, 0 AS DEBITQTY,
QTY AS CREDITQTY, 0 AS DEBITAMOUNT, VALUEMST AS
CREDITAMOUNT FROM CUSTPACKINGSLIPTRANS from
SALESLINE INNER JOIN CUSTCONFIRMTRANS ON
SALESLINE.INVENTTRANSID =
CUSTCONFIRMTRANS.INVENTTRANSID INNER JOIN
CUSTPACKINGSLIPTRANS ON SALESLINE.INVENTTRANSID =
CUSTPACKINGSLIPTRANS.INVENTTRANSID
ITS MY QUERY..ITS NOT WORKING..KINDLY CHECK IT

Gaurav Said... 2012-07-24 4

Thank you very much..:)

shanu Said... 2012-07-22 2

very helpful!!!!!

Satyajit Said... 2012-07-18 7

Thanx a lot!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

ramesh Said... 2012-07-15 0

Thanks a lot!!!
It's very hepls people,who are try to learn!

http://www.bullraider.com/database/sql-tutorial/7-complex-queries-in-sql[9/23/2013 7:05:47 PM]

SQL Complex Queries

varun Said... 2012-07-10 2

HELLO bro can u sent me the all advanced query in my mail

which is usefull for interview ,, my email id is
[email protected].

asma Said... 2012-07-09 5

thankx a lot

suchita Said... 2012-07-08 0

thanks.. its really nice

Sudarshan Said... 2012-07-04 5

To Sharad,
select count(sal) from emp; -- It will count the number of salaries
excluding nulls
select count(*) from emp; -- It will count number of records but this
includes null values too

Dhinesh Said... 2012-06-28 8

its very useful

waseem Said... 2012-06-27 3

its very helpful..Thanks a lot......I have a question i think i will got

through it. suppose i have emp table i want output like that
empno manager1 manager2 manager3 manager4 7788 7699
3425 8756 2876
means 7788 manager is '7699' and manager1 manager is '3425'
and manager2 manager is 8756 etc. i want like that pls give me a
hint
thanks

Mallikarjun Said... 2012-06-22 4

Its very very helpfull thanks..........

kumar Said... 2012-06-21 3

http://www.bullraider.com/database/sql-tutorial/7-complex-queries-in-sql[9/23/2013 7:05:47 PM]

SQL Complex Queries

Thanks budy.It helps alot

murali Said... 2012-05-25 4

thanks its good

Samsuddin Said... 2012-05-05 14

These are very helpful queries...Thanks a lot :) I have a question

in my mind...suppose there are three employees in a dept having
different salaries e.g; empA=1000, empB=1500 and
empC=1200...I require two columns one salaries of each
employee and percentage of salaries out of total salary empA
1000 27.03% empB 1500 40.54% empC 1200 32.43%

Shishir Said... 2012-04-27 0

Very helpful thanks

Shishir Said... 2012-04-27 1

its very helpful

sharad Said... 2012-03-27 -1

sir its too good ...

sir what the difference between two queries....
select count(sal) from emp;
select count(*) from emp;

Anurag Said... 2012-03-01 3

To bharath....hope this works for u

select e.employeeid,e.firstname||' '||lastname as
name,e.salary,d.departmentid from employees e join departments
d on (e.departmentid=d.departmentid)

Anurag Said... 2012-03-01 3

To aneela hope this works for you...

select firstname||lastname as Name,hiredate,tochar(hiredate,'DY')
from employees order by tochar(hire_date,'DY')

http://www.bullraider.com/database/sql-tutorial/7-complex-queries-in-sql[9/23/2013 7:05:47 PM]

SQL Complex Queries

Anbu Said... 2012-02-29 0

very good post than other site

aneela Said... 2012-02-21 5

its nice.but can u give me the answer for this query. Display the
name,hiredate and the day of week on which the employee
started.lable the column day order the result by the day of the
week. please send me the answer.please.

R. Azu Said... 2012-02-14 5

It is so nice

interview questions Said... 2012-02-11 0

hi. appreciate that all oracle learning its help lot....

Manish Said... 2012-01-23 0

Thanks , very good post

lakmi Said... 2012-01-23 -2

IT IS VERY HELPFUL TO ME N ALL.. NICE COLLECTION..

THANQQQ..

Varun Said... 2011-12-22 0

Good one it helps alot

bharath Said... 2011-12-07 3

thanks it really helps i have a query to be solved

display empno,ename,total salary from emp and deptno from dept
table and sort them using dname

vasavi Said... 3

http://www.bullraider.com/database/sql-tutorial/7-complex-queries-in-sql[9/23/2013 7:05:47 PM]

SQL Complex Queries

Thanks a lot!!! :)
It realy helps ppl like me, god bless!

Tell us what do you think

Comment is required

Name is required
Email is required
Website / blog

Submit Comment

Powered by Bullraider.com

YOU ARE HERE: HOME DATABASE SQL TUTORIAL TOP

http://www.bullraider.com/database/sql-tutorial/7-complex-queries-in-sql[9/23/2013 7:05:47 PM]