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
----------
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
Do help me out.
thanks in adv.
Comments
: 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.
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);