Home » SQL & PL/SQL » SQL & PL/SQL » sql query
sql query [message #198994] Thu, 19 October 2006 09:36 Go to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello everyone
here i want to get details of employee having sal than the highest salary of dept no 30.
here is table:

CLIENT NAME SAL DEPT_NO EMP JOB
------ ---------------- ---------- ---------- ---------- --------
C00001 Ivan Bayross 150000 10 2000 clerk
C00002 Vandana Saitwal 19000 30 200 manager
C00003 Paramada Jaguste 149000 30 3500 clerk
C00004 Basu Navindgi 400000 10 500 manager
C00005 Ravi Sreedharan 4000 20 5000 analyst
C00006 Rukmini 200000 40 500 manager
C00007 Sumant 14000 20 700 analyst
C00008 Sushil 18000 40 200 manager
C00009 Rupak 300000 20 200 clerk
C00010 Rupali 90000 40 600 analyst
C00011 Ramesh 190000 10 3980 manager

CLIENT NAME SAL DEPT_NO EMP JOB
------ ---------------- ---------- ---------- ---------- --------
C00012 Rupesh 80000 30 580 clerk
C00013 Ritika 376532 20 390 supreme
C00014 Shiva 600000 30 300 manager

14 rows selected

SQL>select max(sal) from emp1 where dept_no=30;

MAX(SAL)
----------
600000

SQL>select * from emp1 where sal>(select max(sal) from emp1 where dept_no=30);

no rows selected

please tell me where i had done mistake?
i would be thankful if any one could make me understand about my mistake.


Re: sql query [message #198997 is a reply to message #198994] Thu, 19 October 2006 09:53 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Give the "second" emp table another alias:

select * from emp1 
where sal> (select max(sal) from emp2 where dept_no=30)


[Updated on: Thu, 19 October 2006 09:53]

Report message to a moderator

Re: sql query [message #199004 is a reply to message #198994] Thu, 19 October 2006 10:29 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
hi,

your query is retrieving correct.
i.e, no rows selected.

Because 6,00,000 for dept no 30 is higest and no salary is greater than this.

Re: sql query [message #199033 is a reply to message #199004] Thu, 19 October 2006 15:51 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Haha, you're right! I didn't notice... Embarassed
Previous Topic: CTL file options
Next Topic: insert into .... select * from.....exception errors
Goto Forum:
  


Current Time: Sun Dec 04 00:47:23 CST 2016

Total time taken to generate the page: 0.10493 seconds