Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Posts
    5

    need help with queries

    Ok, following tables exist:

    MECHANIC: mech_id, name, competence_level
    CAR: car_ref# , make, model, owner_name, value_cat, car_reg#
    JOB: job_name, difficulty_level, labour_cost
    BOOKING: car_ref#, mech_id, job_name, date


    Ok, i have problems creating the sql for the following example queries:

    List make, model of all cars that have difficulty_level 4 or above performed since 1/1/2004.

    List car_reg# and make of any car that was worked on by all mechanics


    Can somebody please let me know how to do this with SQL or maybe even in relational algebra??

    I am having a mental blockage and any help would be highly appreciated.

    Thx

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    List make, model of all cars that have difficulty_level 4 or above performed since 1/1/2004.
    Can't, there is no relationship between the relevant tables.

    List car_reg# and make of any car that was worked on by all mechanics
    Again, not all the relationships exist between tables.
    Last edited by certus; 01-14-04 at 18:17.

  3. #3
    Join Date
    Jan 2004
    Posts
    5
    well, that would explain things.. is there really no way?? doesn't the booking table give the relationships??

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    You may have to modify the query depending on the relationships.

    select c.*
    from car c
    INNER JOIN
    (select car_ref
    from bookings b
    INNER JOIN
    job j ON
    b.job_name = j.job_name AND
    (b.date > 1/1/2004 OR j.difficulty_level > 4)) v1 ON
    c.car_ref = v1.car_ref;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Jan 2004
    Posts
    5
    first of all thanks for the quick answers!

    would somebody mind explaining this in detail?? how did Certus come to a different opinion??

    thx

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is this, homework?

    we don't answer homework questions on this forum

    but the model is trivial, certus, BOOKING is a three-way intersection

    the queries are piece of cake

    List make, model of all cars that have [had a job of] difficulty_level 4 or above performed since 1/1/2004. --
    Code:
    select C.make
         , C.model
      from CAR C
    inner
      join BOOKING B
        on C.car_ref# = B.car_ref#
    inner
      join JOB J
        on B.job_name = J.job_name
     where B.date > '2004-01-01'
       and J.difficulty_level >= 4
    List car_reg# and make of any car that was worked on by all mechanics --
    Code:
    select C.car_reg#
         , C.make
      from CAR C
    inner
      join BOOKING B
        on C.car_ref# = B.car_ref#
    inner
      join MECHANIC M
        on B.mech_id = M.mech_id
    group
        by C.car_reg#
         , C.make
    having count(distinct M.mech_id)
         = ( select count(*)
               from MECHANIC )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2004
    Posts
    5
    cheers for the answer.. homework in the sence of school?? not exactly, i am just trying to improve my skills with some self study.. so i am just doing this for myself...

    guess thread can be closed..

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    good thing it's not homework, because there are a couple fatal flaws in there that i didn't tell you about, that will surely knock you down from an A to a B- or C

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2004
    Posts
    5

    Talking

    if its just a couple of flaws im sure my teacher (guess thats me) won't mind....

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    They're not flaws. They're features. Just ask Microsoft.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    I was tired when I read that question...really!

Posting Permissions

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