need help with queries

skouliki · #1 (**permalink**) 01-14-04, 15:43

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

certus · #2 (**permalink**) 01-14-04, 17:15

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.

skouliki · #3 (**permalink**) 01-15-04, 07:53

well, that would explain things.. is there really no way?? doesn't the booking table give the relationships??

r123456 · #4 (**permalink**) 01-15-04, 10:57

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;

skouliki · #5 (**permalink**) 01-15-04, 12:59

first of all thanks for the quick answers!

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

thx

r937 · #6 (**permalink**) 01-15-04, 13:40

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 )

skouliki · #7 (**permalink**) 01-15-04, 13:53

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..

r937 · #8 (**permalink**) 01-15-04, 14:05

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