solve this sql query.

Comments

• BobW

  November 2001

  : 1)
  : take the emp tabel.
  : select ename, sal from emp where sal > (select sal from emp where ename= jones).
  : This is good.
  : Now i want 2 more columns with the above above o/p.
  : that's jones sal, and the difference b/w them.
  : the o/p should be like
  : Ename Sal jones_salary Difference
  : scott 200 100 100
  : mad 300 100 200
  : gor 101 100 1
  
  The following should work...
  
  select e.ename,e.sal,j.sal,(e.sal-j.sal) from emp j, emp e
  where j.ename='jones' and e.sal>j.sal
  
  I'm not sure I fully understand what you are trying to achieve in the second question, but I suspect a similar solution joining the table back to itself will work there also.
  
  
  
• jbocquet

  November 2001

  you already have a correct answer for 1)
  I think I can help for the second one.
  : 2)
  : consider table x
  : orderno odate file amt availablity
  : 9 27/3 rat 33 1
  : 9 27/3 bat 33 0
  : 10 12/2 rat 21 0
  : 10 12/2 cat 21 1
  : 11 3/4 bat 21 0
  : 11 3/4 cat 21 1
  :
  : create a view
  : that contains the fieds with details of file rat.
  : orderno odate 1-availability 0_availability amt
  : 9 27/3 rat bat 33
  : 10 12/2 cat rat 21
  If there exists always a 0 and a 1 availability,
  create view xx (orderno, odate, availability1, availability0, amt) as select a.orderno, a.odate, b.availability, a.availibility, a.amt from x a, x b where a.orderno = b.orderno
  and a.availability = 0 and b.availability = 1;
  
  If 0 or 1 availability may no exist, the solution is not the same for oracle and for standard databases (sql92).
  
  standard:
  create view xx (orderno, odate, availability1, availability0, amt) as select a.orderno, a.odate, b.availability, a.availibility, a.amt from x a full outer join x b on (a.orderno = b.orderno) where (a.availability = 0 or a.availability is null) and (b.availability = 1 or b.availability is null);
  
  oracle:
  create view xx (orderno, odate, availability1, availability0, amt) as select a.orderno, a.odate, b.availability, a.availibility, a.amt from x a, x b where a.orderno(+) = b.orderno (+)
  and (a.availability = 0 or a.availability is null) and (b.availability = 1 or b.availability is null);