Consider the schemas of the following relational database for a company. The company has different departments at different cities in different states:

employee(employee-id, dept-id, name, street-num, street-name, city, state, zip, salary)

department(dept-id, dept-name, city, state)

manager(manager-id, employee-id)

NOTES:

manager-id and employee-id in the manager relation are foreign keys to the employee relation.

dept-id in the employee relation is a foreign key to the department relation.

An employee belongs to a single manager only. That is, an employee only works for one manager. Some employees do not have a manager (in that case, they do not have an entry in the manager relation).

Write SQL statement for each of the following queries.

a. Create the employee, department, and the manager schemas, use appropriate data types. manager-id and employee-id are 9-digit integers.

b. Find the names and IDs of all employees who work for the Research Department (dept-name = Research) and who earn between $100,000 and $200,000 per annum