(Solved): MySQL Sample Database Diagram - Start a terminal session and run this command: mysql ...

MySQL Sample Database Diagram - Start a terminal session and run this command: mysql < mysqlsampledatabase.sql - Type mysql in the command line and begin working with SQL the way you've been in previous labs. - Write commands to use the classicmodels database and show its tables to verify that you're in the right place. 1. Retrieve employee tuples and identify the number of employees in San Francisco and New York. A. Command for San Francisco: select firstName, lastName, jobTitle, offices.city from employees inner join offices on employees.officeCode \( = \) offices. officeCode where state \( = \) ' 'CA' B. Write and run a command to return records from New York on your own. C. Validate the completion of this step with a screenshot of these two tables. 2. Retrieve order details for orderNumber 10330,10338 , and 10194 and identify what type of cardinality this represents in the entity relationship model. A. Retrieve the order details by running SELECT queries with WHERE clauses against the orders table. B. Validate the completion of this step with a screenshot. C. Then, reference the Module Four Lab ERD to assist in identifying relationships. A version with alternative text is available: D. Now, identify what type of cardinality this represents in the entity relationship model. 3. Delete records from the payments table where the customer number equals 103. A. Run a DESCRIBE statement to identify fields in the payments table first. B. Select the records from the payments table for customer number 103 before deleting them. i. Validate that the above instructions have worked with a screenshot. C. Delete the records from the payments table for customer number 103. D. Run a SELECT statement against the table to show that customer number 103 is no longer there. i. Validate the completion of this step with a screenshot. 4. Retrieve customer records for sales representative Barry Jones and identify if the relationships are one-to-one or one-to-many. A. Remember: SELECT, FROM, Inner Join, and WHERE. B. Use Barry's employeeNumber, 1504, and perform a join between the customer salesRepEmployeeNumber to retrieve these records. i. Validate the completion of this step with a screenshot. ii. Identify whether these entities demonstrate one-to-one or one-to-many relationships. 5. Retrieve records for customers who reside in Massachusetts and identify their sales rep and the relationship of entities. A. Remember: SELECT, FROM, Inner Join, and WHERE. B. Use employee.firstName and employee.lastName in your command. C. Identify whether these entities demonstrate one-to-one or many-to-many relationships. 6. Add one customer record with your last name using an INSERT statement. A. You may use the name of a celebrity or fictional character if you don't use your own name. Think of this as your signature. B. Complete these actions to get to the right place to enter this information: (1) Show databases, (2) use classicmodels, (3) show tables, (4) describe customers; i. You should now be seeing all of the fields that you'll need to fill in to complete this step. ii. Reference your Module Two lab or resources on how to populate these fields if you need to. iii. Fields you'll need to populate: customerNumber, customerName, contactLastName, contactFirstName, phone, addressLine1, addressLine2, city, state, postalCode, country, salesRepEmployeeNumber, and creditLimit. iv. Run a SELECT statement on the customers table, capture it in a screenshot, and put it in your template. 7. Reflection: Use the lab environment or the screenshots you've worked with for this step. Address the following in your reflection: A. Define how cardinality is applied to the databases you've been working with and why different numbers of records returned from the different offices. B. Compare and contrast the different queries you ran and how cardinality applies to them. C. Describe two of the crucial benefits of cardinality in this type of database.