Description
You should submit a solution.zip file containing only the MySQL script files for the corresponding questions, namely Q1.sql, Q2.sql, …, Q16.sql.
Any submission with an incorrect format might lead to 0 points as the grading script relies strictly on the correct format.
We set 25 points for Question 1, and 5 points each for Question 2 to Question 16.
1. Consider a database schema that stores the following information:
employees: This is a table that contains the basic information of any employee:
EMPLOYEE_ID: The employee’s identifier that determines other attributes. A 6-digit integer.
FIRST_NAME: The first name of an employee (might be empty with only one word for the name information, where the name will be filled in the last name attribute). At most 20 characters.
LAST_NAME: The last name of an employee. It must not be empty. At most 25 characters.
EMAIL: The email of an employee. It must not be empty as a work email will be assigned (and the suffix can be omitted).
PHONE_NUMBER: The phone number of an employee. Might be empty. At most 20 characters, and character ‘.’ is allowed for a phone number.
HIRE_DATE: The date when the employee was hired for the first time.
JOB_ID: The current job id of an employee. It should be of the same format as that of jobs.JOB_ID. One must have a current job information. A 10-character string.
SALARY: The real annual salary of the employee. Normally should be guided by the minimum and maximum salary information described in the current job description, while some special cases that exceed the limit are allowed. A float number with at most 8 digits to the left of the decimal point, and 2 to the right of it (If not pointed out specifically, the “salary” refers to “annual salary”).
COMMISSION_PCT: The commission percentage. A float number from 0 to 100 with 2 digits to the right of the decimal point. It’s the attribute usually not used, and the typical value should be 0.00 here.
MANAGER_ID: Every employee should have a manager that guide him or her in the real work. However, an manager might not have any staff. The attribute should have the same format as the EMPLOYEE_ID.
DEPARTMENT_ID: Describes the department that the employee belongs to. Any employee should register his or her department, while some virtual department might not have any employee registered.
jobs: This table describes the jobs an employee has currently or in the past.
JOB_ID: The job identifier that determines other attributes of a job. Use a string no more than 10 characters.
JOB_TITLE: The string that describes the title of a job. No more than 35 characters and every job should have a title.
MIN_SALARY: The minimum salary that a specific job could have. Not in high precision and described with a 6-digit integer. It might be empty.
3 / 3
