Assignment

Consider the employee database with the following list of tables. Please create these tables with appropriate primary keys & foreign keys.

The list of tables is:

Company(cid, company_name, city, state) /* city is the company's location */

Employee(eid, ename, street, city, state, salary, cid) /* city is where the employee lives*/

Manages(eid, mid) /* mid is manager's ID*/

Problem 2. Insert at least three rows of data to each table. Make sure you keep the primary key and foreign key constraints. [20 points]

Problem 3. Please write ONE SQL statement for each of the following tasks using tables created in Problem 1. [60 points, 10 points each]

Task 1: Return the names of employees who lives in Baltimore. If you do not have Baltimore as a city name in Employee table, use any city name you have (e.g., New York).

Task 2: Return the names of all employees whose company is in Baltimore. If your Company table does not have companies located at Baltimore, use any city name in your Company table (e.g., New York).

Task 3: Return the number of companies located in each city.

Task 4: Return the names of cities with at least two companies located.

Task 5: Return the names of companies and the number of employees working at each company.

Task 6: Return the names of employees of Verizon who are managers. Hint: managers are also employees but their eid appears as mid in manages table. If you do not have Verizon as a company name, use any company name in your company table.