-3

Given two tables:

driver=(d_id,name,gender,birthday,address,country)

event_list=(o_id,d_id,odate,oplace)

The event list table represent the driving violations of the driver , o_id is the violation number(id), d_id is the driver id, odate is the date of the violation

The driver table represent the driver details, the birthday is the year of the birth e.g 1992

Write query that finds the name and the birth yaer of the oldest driver that have made at least one violation that lives in Haifa


My attempt:

SELECT DISTINCT name, birthday 
FROM driver INNER JOIN event_list ON driver.d_id=event_list.d_id
GROUP BY  name, birthday,address  
HAVING MIN(birthday) AND driver.address='haifa';

I have a problem with the minimum, the output is all the drivers that have made at least one violation that living in Haifa, but all of them, not the oldest one, how can I improve my code? I need only the oldest.

2

Try this code:

SELECT TOP 1 name, birthday    
FROM driver 
INNER JOIN event_list ON driver.d_id=event_list.d_id
WHERE driver.address='haifa'
ORDER BY birthday;
1

Tricky around the oldest driver but I'll have a go:

SELECT name, birthday 
FROM driver 
WHERE name in (SELECT name FROM driver where driver.address = 'haifa')
AND  d.id in (SELECT d.id FROM event_list)
AND birthday = (SELECT DISTINCT MAX(birthday)
                FROM driver  
                WHERE name IN (SELECT name FROM driver WHERE driver.address =  'haifa') AND  d.id in (SELECT d.id from event_list))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.