Execute the code contained within the “Ch07_ConstructCo_MySQL.txt” file attached.

  1. Review the tables created (JOB, EMPLOYEE, PROJECT, ASSIGNMENT) by the SQL statement. Create an ERD Diagram for the database tables showing the relationships between the tables. Be sure to identify any primary or foreign keys (40 points)
  2. Write SQL statements to return the following (All queries requesting a count, sum, or list must return unique records):
    1. Produce the first and last names of employees and number of years employed sorted by the highest number of years employed (EMP_HIREDATE) to the lowest. (10 points)
    2. Count the number of employees hired after January 1, 1996 (EMP_HIREDATE). (10 points)
    3. Return the Employee ID, First Name, Last Name, Job Description, and the sum of assignment hours (ASSIGN_HOURS) for employees that have more than 1 assignment in the assignment table. (10 points)
    4. For each project, return the project name and Job Descriptions of the employees that worked on them. (10 points)

Include SQL statements you used as well as returned results of statements. Submit your assignment as a Word or PDF document.

  • https://www.studypool.com/questions/download?id=2806338&path=uploads/questions/7365623/20230412184548data_week_5.txt&fileDownloadName=attachment_1