嘿,伙计们,我正在复习考试和过去的论文,谁能告诉我我是否在正确的轨道上。
考虑下面给出的保险数据库:
person (driver-id, name, address)<br>
car (license, model, year)<br>
accident (report-number, date, location)<br>
owns (driver-id, license) <br>
participated (driver-id, car, report-number, damage-amount)<br>
1)找出2004年发生交通事故的车主总数。
这是我想到的:
SELECT COUNT(driver-id)
FROM participated
WHERE participated.reportnumber=accident.reportnumber
AND accident.date = 2004
我在正确的轨道上吗?
不,这是不对的,你必须引用accident
表:
SELECT COUNT(`driver-id`)
FROM participated
INNER JOIN accident ON participated.reportnumber = accident.reportnumber
WHERE accident.date = 2004
您可以使用INNER JOIN
并连接事故表
SELECT COUNT(a.driver-id) as total
FROM participated a
LEFT JOIN accident b
ON a.reportnumber=b.reportnumber
WHERE b.date = 2004
SELECT COUNT(DRIVER_ID) FROM person
WHERE DIVER_ID IN(SELECT DRIVER_ID FROM OWNER WHERE DRIVER_ID IN(SELECT CAR FROM participated WHERE REPORT_NUMBER IN(SELECT DATE FROM ACCIDENT WHERE DATE =2004);