Language SQL

Need help with SQL Question

Create a view vHW1_7_xxxx listing staff who have the highest salary in each of the branch city. You will need both Staff and Branch table. The output header should be (branchno, city, first_name, last_name,salary)

Here are the tables:

Staff

+---------+-------+--------+------------+------+------------+----------+----------+-----------+

| staffNo | fName | lName | position | sex | DOB | salary | branchNo | managerNo |

+---------+-------+--------+------------+------+------------+----------+----------+-----------+

| SA9 | Mary | Howe | Assistant | F | 1970-02-19 | 9000.00 | B007 | SG14 |

| SG14 | David | Ford | Supervisor | M | 1958-03-24 | 18000.00 | B003 | SG5 |

| SG37 | Ann | Beech | Assistant | F | 1960-11-10 | 12000.00 | B003 | SL21 |

| SG5 | Susan | Brand | Manager | F | 1940-06-03 | 24000.00 | B003 | SG14 |

| SL21 | John | White | Manager | M | 1945-10-01 | 30000.00 | B005 | XYZ1 |

| SL41 | Julie | Lee | Assistant | F | 1965-06-13 | 9000.00 | B005 | XYZ1 |

| XYZ1 | Huang | Austin | CEO | M | 1970-01-01 | 40000.00 | B002 | NULL |

+---------+-------+--------+------------+------+------------+----------+----------+-----------+

Branch

+----------+------------------+----------+----------+

| branchNo | street | city | postcode |

+----------+------------------+----------+----------+

| B002 | 56 Cover Drive | London | NW10 6EU |

| B003 | 163 Main Street | Glasgow | G11 9QX |

| B004 | 32 Manse Road | Bristol | BS99 1NZ |

| B005 | 22 Deer Road | London | SW1 4EH |

| B007 | 16 Argyll Street | Aberdeen | AB2 3SU |

| X001 | 1 Main Ave | Union | 07083 |

+----------+------------------+----------+----------+