MYSQL USING MARIA DB

question 1 (6 points)

For workers’ safety, restaurants designate how long an employee may work in a specific work area. This is enforced in potentially hazardous areas. For example, restaurants may state that a worker may not work on a stir fry for more than four hours in a space of six hours.

Write a query that creates a sequence that you will later use to populate a primary key. The sequence should start at 1 and increment by 1.

Write a query that uses the sequence you just created to create a table named KITCHEN_SCHEDULE.

The table is to show the name of the work areas (such as stir fry, oven, boiler, stove top) and the minimum and maximum number of hours to work in such areas and in specific periods. For example, the stir fry worker can be scheduled for 2 to 4 hours every 6 to 8 hours. In addition to that, the worker should not exceed 12 hours total of 16 hours.

The Write a query that uses the sequence you just created to create a table named Write a query that uses the sequence you just created to create a table named KITCHEN_SCHEDULE. The table should have the following fields

1. Schedule identifier as an integer populated by a value from the sequence. This is the primary key.
2. Name of the work area as a variable character of length 50.
3. Minimum number of hours to work in the work area. For example, Stir Fry will be a minimum of 2 hours.
4. Maximum number of hours to work in a shift. For example, someone working on Stir Fry may not work continuously for more than 4 hours.
5. Period of control is an integer on how long the control period is. In this case, no more than 2 to 4 hours in 6 hours. 6 hours is the control period.
6. Day period control time. That would be the total amount of time to fit all the sessions per day. For example, the shift is 2 to 4 hours every 6 hours for 16 hours. That 16 hours is the working control period.

Use a check constraint to ensure that the hours do not exceed the maximum number of hours in a shift. Assume that the maximum is 12 for all work areas that will be recorded on this table.

Create a unique constraint to ensure there are no duplicate work areas.

Create an ordering index to order the work areas by maximum number of hours per shift.

Populate the table with values. You decide the work area and the hours for each. You may insert one or a few records.

With that done, write a query that draws data from this information by concatenating strings with data values. For example, your query will output a sentence such as

“Stir Fry schedule should be a 2 to 4 hours within 6 hours in a 16 hour period”

Feel free to alter this sentence as you want but use all values in the table.

For submission, provide the following

1. A screenshot showing the query that creates the sequence
2. A screenshot showing the query that creates the table
3. A screenshot of the query that creates the ordering index.
4. A screenshot showing the query that populates the KITCHEN_SCHEDULE table for the work areas.
5. A screenshot that shows the results of the query SELECT * FROM KITCHEN_SCHEDULE.
6. The query that prints the sentence of work areas and work duration.

Question 2 (4 points)

Based on the company database, write a query that shows the employee who works the least hours. It is a total of all hours for that employee regardless of the project.

HINT: You may create a subquery or with statement to show how many hours every ESSN worked. That way you know which employee it is before you write the query to get the names.

Question 3 (6 points)
Based on the company database, write a query that lists the following attributes and conditions from the EMPLOYEE table

1. The employee’s name concatenated as first, middle initial, and last. This column should be headed to Employees Name in uppercase.
2. Add a dot after the middle initial. For example ALICIA J. ZELAYA
3. Date of birth headed as BIRTH DATE
4. Address in upper case and headed ADDRESSS
5. Sex in uppercase headed as GENDER. Where sex = F report that as Female in uppercase. When sex is M report it as Male in upper case.
   Salary
6. Create a column named REPORTING ARRANGEMENT to show the name of the manager. For this column, if the employee is female the data on the field should be She reports to Managers name.
7. Department in upper case
8. Order the query output by ascending the first column.

For submission provide a screenshot of the query output. The output of the query should match the image below.

Question 4 (4 points)

The query below lists all studio managers and their schedules for managing studios. It uses the following relations

STUDIO {STUDIO_ID, STUDIO_CITY, STUDIO_NAME}

STUDIO_MANAGER {STUDIO_MANAGER_ID, FIRST_NAME, LAST_NAME, EMAIL_ADDRESS, PHONE_NUMBER}

MANAGER_ASSIGNMENTS {MANAGER_ASSIGNMENT_ID, STUDIO_ID,STUDIO_MANAGER_ID, START_DATE, END_DATE}

Rewrite the query to list only those managers managing a studio on March 5, 2025.

SELECT STUDIO.STUDIO_CITY, STUDIO.STUDIO_NAME, FIRST_NAME, LAST_NAME, EMAIL_ADDRESS, PHONE_NUMBER, START_DATE, END_DATE
FROM MANAGER_ASSIGNMENTS JOIN STUDIO ON MANAGER_ASSIGNMENTS.STUDIO_ID = STUDIO.STUDIO_ID
JOIN STUDIO_MANAGER ON MANAGER_ASSIGNMENTS.STUDIO_MANAGER_ID = STUDIO_MANAGER.STUDIO_MANAGER_ID

Submit a screenshot of the query and the query output.

Make sure of the following before you upload the file:

1. Use your name and Final Exam Part B as “File Name” (e.g., Last_Final_B).
2. Add your name and Final Exam Part B at the top of first page for example: // Last Final Exam Part B.
3. Make sure you upload the file in HTML format only. The file should include both the queries and the results of the query and your name at the top.
4. File submitted in DOC or any other format is not acceptable and will not be graded.
5. Hand written and scanned files are not acceptable and will not be graded.

Requirements: complete