Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2003
    Posts
    10

    Unanswered: select statement

    how to write a sql statement on this query
    select all userid, username whose age is more than 10 years and number of login more than 20
    Please help me in solving this query

    Table Name USERS

    user_id number(2)
    user_name varchar2(20)
    DOB date
    total_login number(2)

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The homework tutor is three doors down the hall on the left side.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106

    Post

    Quote Originally Posted by aar_vadde
    how to write a sql statement on this query
    select all userid, username whose age is more than 10 years and number of login more than 20
    Please help me in solving this query

    Table Name USERS

    user_id number(2)
    user_name varchar2(20)
    DOB date
    total_login number(2)
    This isn't SQL101. Here's a starting point.... RTFM
    http://www.wsl.ch/relics/rauminf/riv...inalondon.html
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  4. #4
    Join Date
    Oct 2002
    Location
    BC Canada
    Posts
    61
    how to write a sql statement on this query
    select all userid, username whose age is more than 10 years and number of login more than 20
    Please help me in solving this query

    Table Name USERS

    user_id number(2)
    user_name varchar2(20)
    DOB date
    total_login number(2)

    select user_id, username from USERS where (Datediff(day,DOB,getdate()) >= 3650) AND total_login > 20

    Greg C

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    "select user_id, username from USERS where (Datediff(day,DOB,getdate()) >= 3650) AND total_login > 20"

    Looks like a Sybase/SQL Server solution. There's no datediff() function in Oracle, and getdate()'s Oracle equivalent is sysdate.

    -cf

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by chuck_forbes
    "select user_id, username from USERS where (Datediff(day,DOB,getdate()) >= 3650) AND total_login > 20"

    Looks like a Sybase/SQL Server solution. There's no datediff() function in Oracle, and getdate()'s Oracle equivalent is sysdate.

    -cf
    yeah! go back to your own forum!
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    Quote Originally Posted by The_Duck
    yeah! go back to your own forum!
    You guys kill me!!!! The_Duck, quacks me up!
    (I've been known to be fuped_duck, myself!)
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  8. #8
    Join Date
    Jan 2004
    Location
    Germantown, MD
    Posts
    34
    Try this:

    SELECT userid, username
    FROM users
    WHERE months_between(sysdate,dob)/12 > 10
    AND total_login > 20;

    -Rip
    Last edited by the Oracle; 08-26-04 at 16:38.
    "The world is a dangerous place, not because of those who do evil, but because of those who look on and do nothing. "
    - Albert Einstein

  9. #9
    Join Date
    Oct 2002
    Location
    BC Canada
    Posts
    61

    Ooops My bad

    Back to my forum I go.....

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    I didn't want to give out the answer necessarily, just note that the sol'n suggested wouldn't work. Sounded like it might be a better teaching situation for this member to seek out the answer themselves, although you probably say that about all of my RMAN questions lately:

    select user_id, username from USERS where (DOB + (10*365)) > sysdate AND total_login > 20

    -cf

  11. #11
    Join Date
    Jan 2004
    Location
    Germantown, MD
    Posts
    34
    True... I too believe in letting them figure it out on their own. But, I want to believe that they have expended all of their energy ALREADY trying to find it and as a last resort came here. Sometimes we come to this site for a quick answer when we need it fast. In all fairness, I will answer any question that I can. If I can't answer it, I won't pick on (or berate) the questioner. I remember a long, long time ago when I was a newbie and trying to keep my head above the water.

    ( :

    Quote Originally Posted by chuck_forbes
    I didn't want to give out the answer necessarily, just note that the sol'n suggested wouldn't work. Sounded like it might be a better teaching situation for this member to seek out the answer themselves, although you probably say that about all of my RMAN questions lately:

    select user_id, username from USERS where (DOB + (10*365)) > sysdate AND total_login > 20

    -cf
    "The world is a dangerous place, not because of those who do evil, but because of those who look on and do nothing. "
    - Albert Einstein

  12. #12
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106

    Question

    Quote Originally Posted by the Oracle
    True... I too believe in letting them figure it out on their own. But, I want to believe that they have expended all of their energy ALREADY trying to find it and as a last resort came here. Sometimes we come to this site for a quick answer when we need it fast. In all fairness, I will answer any question that I can. If I can't answer it, I won't pick on (or berate) the questioner. I remember a long, long time ago when I was a newbie and trying to keep my head above the water.

    ( :
    Man, here's a dumb question... Does the whole world have leap year, or is that like 'Independance Day' or 'Boxing Day' (what is a 'boxing day'?) - (Really, don't laugh - Do 'they' call it leap year, I really don't know.) The point being, did -cf account for the possible day differences in leap year when using 365 - or can that satisfy the solution in this case?


    Whoops, wrong quote - Just call me 'BOZO'!

    Meant to quote:
    I didn't want to give out the answer necessarily, just note that the sol'n suggested wouldn't work. Sounded like it might be a better teaching situation for this member to seek out the answer themselves, although you probably say that about all of my RMAN questions lately:

    select user_id, username from USERS where (DOB + (10*365)) > sysdate AND total_login > 20

    -cf
    Last edited by dbtoo2001; 08-25-04 at 20:28.
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  13. #13
    Join Date
    Jan 2004
    Location
    Germantown, MD
    Posts
    34
    Anyone who follows the Gregorian calendar does. Also, his will make a small mistake each year, but it won't be large enough to create a problem when looking for someone over 10 years old, because it will only be off by two days.

    It was a sensible question.

    ( :
    "The world is a dangerous place, not because of those who do evil, but because of those who look on and do nothing. "
    - Albert Einstein

  14. #14
    Join Date
    Dec 2003
    Posts
    1,074

    leap year

    I think this will take leap year into account, then:

    select user_id, username
    from USERS
    where months_between(sysdate, DOB) >= (10*12) AND total_login > 20

    -cf

  15. #15
    Join Date
    Jan 2004
    Location
    Germantown, MD
    Posts
    34
    might as well put 120 in instead of (10*12)... And, the >= wouldn't work, since he wants them to be OLDER than 10.

    Other than that, it looks great. And, familiar.

    ( :

    Quote Originally Posted by chuck_forbes
    I think this will take leap year into account, then:

    select user_id, username
    from USERS
    where months_between(sysdate, DOB) >= (10*12) AND total_login > 20

    -cf
    Last edited by the Oracle; 08-26-04 at 16:37.
    "The world is a dangerous place, not because of those who do evil, but because of those who look on and do nothing. "
    - Albert Einstein

Posting Permissions

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