我如何正确选择语句来查询这个数据库? [英] How would I right a select statement to query this database?

问题描述

如果我想知道有多少名字是Jim"的人通过了他们的测试，我该如何正确选择语句?

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?

另外，如果我想在 2015 年 3 月 10 日 9:00 之后获取每次测试的讲师姓名和姓氏、客户姓名和日期怎么办?

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、名字、姓氏、性别、地址、电话号码、proLicenceNo)

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

Instructor(instructorID，名字，姓氏，性别，地址，telNo，licenceNo，carNo)

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

汽车(汽车编号、注册编号、型号)

课程(clientNo、onDate、atTime、instructorID)

测试(clientNo、onDate、atTime、instructorID、中心ID、状态、原因)

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

中心(中心ID、姓名、地址)

主键为粗体，外键为斜体.外键不能为空.

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

谢谢！:)

推荐答案

尝试在客户端和测试表之间加入:

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

这篇关于我如何正确选择语句来查询这个数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！