Results 1 to 4 of 4

Thread: sql queries..

  1. #1
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Lightbulb Unanswered: sql queries..

    Hello SQL developers..

    Here Iam looking for couple of queries for the desired output.
    1---> How to know all relations to and from a table.To be more clear..
    to know..what are the columns through which a table (say A) refers to other table(s) columns and which columns of table A is referred by others tables columns...
    Can it be possible to achive through a query/script.
    2-->How to get the output in the following format on emp table.
    Salary Range...........................Number Of Employees..............................
    1-1000......................................5....... ...............................................
    1001-2000..................................6........... ..........................................
    2001-3000...................................10......... .........................................
    ..
    Altogether..No.of employess in a given sal. range in above format..
    Could anyone please help me resolving this..
    thanks in advance..
    sincerely
    sridhar

  2. #2
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hello Sridhar!

    for 1.)
    PHP Code:
    select from ALL_CONSTRAINTS 
    where OWNER 
    'yourName' and CONSTRAINT_TYPE in ('P''R') ; 
    The table ALL_CONSTRAINTS shows all constraint definitions on accessible tables of your database.
    R = references (foreign key)
    P = primary key
    Regards,
    Julia

  3. #3
    Join Date
    May 2004
    Posts
    2

    Using LPAD, RPAD functions to set the total length

    For the second question, you can use

    SELECT RPAD('Salary_range',15,'.') "Salary Range"
    FROM Emp;

    Where 15 is the total length of the return value, if the length of range is less than 15, '.' will be added to the right.

    Salary range

    1-5000.........

    Good luck.

  4. #4
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    queries

    Thanks julla..
    But Actually I was looking at a query which will fetch me the details of foreign keys "from and to" a given table.Which I got now..here they are for reference..

    select
    a.tt,
    a.owner,
    b.table_name,
    a.constraint_name,
    b.column_name,
    b.position,
    a.r_constraint_name,
    c.column_name,
    c.position,
    c.table_name r_table_name,
    a.r_owner
    from
    (select
    owner,
    constraint_name,
    r_constraint_name,
    r_owner,1 tt
    from
    dba_constraints
    where
    owner=upper('&&owner')
    and table_name=upper('&&table_name')
    and constraint_type!='C'
    union
    select
    owner,
    constraint_name,
    r_constraint_name,
    r_owner,2
    from
    dba_constraints
    where
    (r_constraint_name,r_owner) in
    (select
    constraint_name,
    owner
    from
    dba_constraints
    where
    owner=upper('&owner')
    and table_name=upper('&table_name'))
    ) a,
    dba_cons_columns b,
    dba_cons_columns c
    where
    b.constraint_name=a.constraint_name
    and b.owner=a.owner
    and c.constraint_name=a.r_constraint_name
    and c.owner=a.r_owner
    and b.position=c.position

    and thanks to you feng..
    Actually I forgot to make it a point that "." are not part of actual output.
    Anyway..now I got the query which gives me the desired output.

    select case when sal>0 and sal<=1000 then
    "1-1000" when sal>1000 and sal<=2000 then
    "1000-2000" .......end "Range,count(sal)
    from emp
    group by
    case when sal>0 and sal<=1000 then
    "1-1000" when sal>1000 and sal<=2000 then
    "1000-2000" .......end;

    Thanks for the response.. this is just for the sake of reference.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •