San Francisco State University Employees Department Information MySQL Project

I’m working on a mysql question and need an explanation to help me study.

Part 1: Open the mydb database we created earlier and create a new department table with

two fields:

documentid (as an integer field)

Jsondocument (as a JSON field)

And add around 3 departments and each with 2 or 3 employees. See example on slide #39-41

of the JSONDataType PowerPoint. And you may use the online JSON validator at this link to

validate your JSON documents: https://jsonformatter.curiousconcept.com/#

Part 2: Use SQL to answer the following questions:

Q1: Select all the records in the table.

Q2: List the department names with their phones.

Q3: A query to show the deptName and number of employees of the department.

Q4: A query to show all the employees of a department of your choice using deptName as

criteria

Part 3 This relationship is modeled as a Department JSON object as:

Department:

deptID,

deptName,

deptRoom,

deptPhones:[phonenumber],

employees: [{empID, empName,empPhone,empEmails:[email]}]

Create a MongoDB Atlas database with a Department collection and add around 3 departments

and each with 2 or 3 employees. You may copy the documents from Assignment 8 to MongoDB.

Use MongoDB Compass query and answer the following questions.

Q1: Select all the documents in the collection and sorted by deptName.

Q2: List the department names with their phones.

Q3: A query to show the deptName and employees of the department.

Q4: A query to show all the employees of a department of your choice using deptName as

criteria.

Submit the query screenshots.