Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2007
    Posts
    37

    Question Unanswered: Some SQL queries...

    hi everyone! i have a table as follows

    Instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)

    what will be queries for-

    1. list all the instructors who are not mentors
    2. list all the instructors whose mentor has been employed for more that 20 years

    cheers in advance
    Last edited by rushdishams; 03-16-07 at 12:57.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, we don't do homework assignments on this site
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    37
    ha ha, mate, this is not home assignment. i am facing trouble while i am writing the queries on sql plus in oracle... but according to me, they should work in sql 2005. and moreover, i am not a student... for the first time i am going to take the course Database Systems in KUET...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rushdishams
    i am facing trouble while i am writing the queries on sql plus in oracle...
    if you want help, please show the SQL you've written, and show the error messages you're getting
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moving thread to oracle forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2007
    Posts
    37
    ok, i found the first one working in oracle 10g enterprise edition, but it does not work in 10g express edition. the first one can be done in 10g enterprise as follows-

    select * from instructor where instructor_id not in (select mentor_id from

    instructor where instructor_id!=mentor_id);

    but for the second one,which function should i use? i went through most of the date functions, can't understand which one i will use. using sysdate and date_hire with months_between as a condition- will that give any result? what do you think

    Thanks in advance...

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Instructor (instructor_id, instructor_name, salary, commission, date_hired)
    >select mentor_id from instructor where instructor_id!=mentor_id);
    How is it that you specify the table definition in post #1 (above) & select column (mentor_id which does NOT exist in table definition) in most recent post?
    You're On Your Own (YOYO)!
    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.

  8. #8
    Join Date
    Mar 2007
    Posts
    37
    i am sorry for that... mentor_id is inside that table, that was a typing mistake... but why the query doesn't run on 10g express but runs on 10g enterprise??

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It appears you are incapable or unwilling to use CUT & PASTE available in most environments.
    WRT 10g Express here is the functional equivalent of what you have said.
    My red car goes. My blue car does not go. Tell me what is wrong with my blue car.
    Again, if you post a COMPLETE cut & paste of what is happening in Express, somebody might give you a clue.
    You may have just another typo; but we (TINW) can't tell since you've provided NO meaningful information.
    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.

  10. #10
    Join Date
    Mar 2007
    Posts
    37
    hmm, eventually the functional equivalent is-
    My red car runs on road A, my read car doesn't run on road B. What's wrong with my red car? i did not change it into blue car or it is not like i forgot driving on road B.
    anyway, i uninstalled 10g express before joining this forum. so, if anyone can provide solution without COMPLETE cut and paste of what was happening in express, he/she may...

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by rushdishams
    ok, i found the first one working in oracle 10g enterprise edition, but it does not work in 10g express edition
    Don't be silly ... this query
    Code:
    SELECT * FROM instructor
     WHERE instructor_id NOT IN (SELECT mentor_id
                                   FROM instructor
                                  WHERE instructor_id != mentor_id);
    would work even on Oracle 7 (I don't have earlier versions to check it). It is ... well, just too simple. I can't imagine what would prevent it to run on Oracle 10g Express Edition. But, if you say so, could you prove it? A screenshot would be just fine.

    Oh, right ... you have uninstalled XE. So install it back again! I find it more appropriate for testing and studying tasks than Enterprise Edition.

  12. #12
    Join Date
    Mar 2007
    Posts
    37
    yeah, that is kinda weird... the output as far as i can remember is invalid sql statement!!!!

  13. #13
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Littlefoot
    Don't be silly ... this query
    Code:
     .... 
    instructor_id != mentor_id;
    Shouldn't that be instructor_id <> mentor_id?

    I didn't know Oracle allows this non-standard operator.

  14. #14
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Enjoy!
    Code:
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> select * from dept where 1 != 2;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            50 MANAGING       ZAGREB
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    SQL>
    (OK, I admit, it is Enterprise Edition, not Express Edition, but I *believe* it'll work on XE as well. I'll try to remember to check it and I'll post the result 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
  •