solve this sql query.

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

  • : 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.


  • 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);


Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories