This question's explanation and your answers so far.
関係データベースの“社員”表と“部署”表がある。“社員”表と“部署”表を結合し，社員の住所と所属する部署の所在地が異なる社員を抽出する。抽出される社員は何人か。 社員
| 社員ID | 氏名 | 部署コード | 住所 |
|---|---|---|---|
| H001 | 伊藤 花子 | G02 | 神奈川県 |
| H002 | 高橋 四郎 | G01 | 神奈川県 |
| H003 | 鈴木 一郎 | G03 | 三重県 |
| H004 | 田中 春子 | G04 | 大阪府 |
| H005 | 渡辺 二郎 | G03 | 愛知県 |
| H006 | 佐藤 三郎 | G02 | 神奈川県 |
部署
| 部署コード | 部署名 | 所在地 |
|---|---|---|
| G01 | 総務部 | 東京都 |
| G02 | 営業部 | 神奈川県 |
| G03 | 製造部 | 愛知県 |
| G04 | 開発部 | 大阪府 |
«1 person». The employees whose residence differs from the location are H002 and H003, i.e. 2 people; counting only 1 falls short — this is an undercount.
正解 Join the «Employee» and «Department» tables on the department code, and compare each employee's «Residence» with the «Location» of the department they belong to. Only the following 2 employees have a residence that differs from the location. H002 Shiro Takahashi: department G01 (General Affairs Dept.) is located in Tokyo, but his residence is Kanagawa Pref., so they differ. H003 Ichiro Suzuki: department G03 (Manufacturing Dept.) is located in Aichi Pref., but his residence is Mie Pref., so they differ. The remaining 4 all match (H001 = Kanagawa = Kanagawa, H004 = Osaka = Osaka, H005 = Aichi = Aichi, H006 = Kanagawa = Kanagawa). Therefore 2 employees are extracted, and the correct answer is «イ».
«3 people». Only H002 and H003 differ, i.e. 2 people. For example H006 (residence Kanagawa Pref.; department G02 Sales Dept. located in Kanagawa Pref.) matches, so H006 is not extracted and the total never reaches 3.
«4 people». This overcounts by also including the employees whose location matches their residence (H001, H004, H005, H006). In reality only 2 differ.