Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
307 views
in Technique[技术] by (71.8m points)

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

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! :)

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

2.1m questions

2.1m answers

60 comments

57.0k users

...