Results 1 to 11 of 11

Thread: Give me the queries for.....

  1. #1
    Expert Member
    Join Date
    Sep 2007
    Answers
    110

    Give me the queries for.....

    suppose i have 50 records of employee table.

    1) I want to find the person who is having 22nd highest salary.

    2) I want to delete a person which is in 39th row

    3) I want to Update rec no 45



  2. #2
    Junior Member
    Join Date
    Nov 2007
    Answers
    1
    for the first one:

    select e1.empname,sal from emp_table e1
    where (21=(select count(*) from emp_table e2 where e1.sal

    Thanks&Regards,
    Bhargavi

    1.
    select e1.empname,sal from emp_table e1
    where (21=(select count(*) from emp_table e2
    where e1.sal

    2.
    delete from emp_table where
    empname=(select empname from (select rownum r1,empname from emp_table) where r1='39');

    here in place of empname field u can use any primary key or unique key field.


    3.
    similar to delete operation u can do update also.

    update emp_table set empid='1005' where empname=(select empname from (select rownum r1,empname from emp_table) where r1='45');



    -Bhargavi

    for the first query

    where e1.sal
    sorry..
    i am not able to write less than symbol here. donno y..

    where e1.sal is less than e2.sal

    this is the condition..


    Bhargavi

    i am also using one table only.
    e1,e2 are just aliases used for emp_table.

    u just run that query. u will get the required result.


  3. #3
    Expert Member
    Join Date
    Sep 2007
    Answers
    110

    Re: Give me the queries for.....

    2nd, 3rd answer is ok. but 1st answer is not able to understand.
    There is only one table. from only that table i want to find 22nd highest salary. How it is?


  4. #4
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Give me the queries for.....

    That is a co-related query .In that a table alias is used for the same table and compared with itself. Performance wise co-related queries are the slowest as it executes once for each row in table.


  5. #5
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Give me the queries for.....

    1.select * from (select rownum rnum,sal from(select * from emp order by sal desc))
    where rnum=&rnum;

    2.delete from emp where (sal,rownum)=(select sal,rownum
    from (select rownum rn,sal from(select * from emp order by sal desc))
    where rn=&num);

    3.update emp set sal=20000 where (sal,rownum)=(select sal,rownum from (select rownum rnum,sal from(select * from emp))
    where rnum=&rnum);

    Try this


  6. #6
    Contributing Member
    Join Date
    Apr 2006
    Answers
    46

    Re: Give me the queries for.....

    there are several functions to do your job

    here i give u a sample query and it's o/p where i used dense_rank() and rank() function as see the o/p and then try to use it in yr query as needed.I have not done the full req.. of yr's do it yrself



    with t as
    (
    select 'ram' as emp,1 as dept,1000 as sal from dual
    union
    select 'shyam', 2,2000 from dual
    union
    select 'jadu',1,1000 from dual
    union
    select 'modhu',1,6000 from dual
    union
    select 'bp',2,7000 from dual
    )
    select
    emp,dept,sal,drn,rn
    from
    (select emp,dept,sal,dense_rank() over(partition by dept order by sal)drn
    ,rank() over(partition by dept order by sal)rn from t)

    -------------------------------------------------------------------------
    o/p is

    EMP DEPT SAL DRN RN
    jadu 1 1000 1 1
    ram 1 1000 1 1
    modhu 1 6000 2 3
    shyam 2 2000 1 1
    bp 2 7000 2 2

    hints.. use where clause in the query to get yr nth data

    cheers


  7. #7
    Junior Member
    Join Date
    Nov 2007
    Answers
    1

    Re: Give me the queries for.....

    Quote Originally Posted by rajaputra View Post
    suppose i have 50 records of employee table.
    1) i want to find the person who is having 22nd highest salary.
    2) i want to delete a person which is in 39th row
    3) i want to update rec no 45
    answer to question 1--------------- select top 1 salary from aaa where salary in(select top 22 salary from aaa order by salary desc) order by salary ashok choudhary software engineer sceptre solutions bangalore


  8. #8
    Junior Member
    Join Date
    Jan 2008
    Answers
    24

    Re: Give me the queries for.....

    1.select * from emp m
    where (select count(distinct sal) rank from emp s
    where s.sal>m.sal)+1=22;


    2.delete from emp
    where empno=(select empno from (select rownum rn,emp.* from emp)
    where rn=39);

    3.update emp
    set sal=10000
    where empno=(select empno from (select rownum rm,emp.* from emp) where rn=45);


  9. #9

    Re: Give me the queries for.....

    1.
    select distinct(a.salary)
    from emp a
    where 22 =
    (select count(distinct b.salary)
    from emp b
    where a.salary>=b.salary);


  10. #10
    Junior Member
    Join Date
    Jul 2008
    Answers
    6

    Re: Give me the queries for.....

    1 select min(salary)as 22nd_highestsalary
    from salary
    where salary in ( select top 22 salary
    from salary
    order by salary desc);


  11. #11
    Junior Member
    Join Date
    Jun 2011
    Answers
    15

    Re: Give me the queries for.....

    I am doing something similar, very helpful answers here.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact