mysql - How would I right a select statement to query this database?

Question

How would I right a select statement if I wanted to find out how many people with the first name "Jim" have passed their test?

Also what about if I wanted to get the instructor Forename and Surname, the Client Forename and Surname and Date for every Test after 9:00 on 10/03/2015?

Here is the relational Schema for the database.

Client (clientNo, forename, surname, gender, address, telNo, proLicenceNo)

Instructor (instructorID, forename, surname, gender, address, telNo, licenceNo, carNo)

Car (carNo, regNo, model)

Lesson (clientNo, onDate, atTime, instructorID)

Test (clientNo, onDate, atTime, instructorID, centreID, status, reason)

Centre (centreID, name, address)

Primary keys are in bold and foreign keys are in italic. No foreign keys are allowed to be null.

Thanks! :)

Answer 1

Try joining between client and test table like:

SELECT c.forename, COUNT(c.forname)
FROM Client c INNER JOIN Test t
ON c.clientNo = t.clientNo
WHERE c.forename = 'Jim'
AND   t.status = 'PASS'
GROUP BY c.forname