Directions

The manager of a small marketing firm has hired new employees. The manager asks you to find information about the new hires. You$$ve already entered some information for the new hires, but you$$ll need to enter more. After you enter the new information, do a join to get the results requested by the manager. Next, write the query to save the results and export them to a comma$-$separated values $($CSV$)$ file. The CSV file is a delimited text file that uses commas to separate values so they can be archived. Reread the instructions from the Module Two Lab Guidelines and Rubric to identify the data you will need.

Before you begin, complete the following tasks:

Look at the Additional Support section in the Resources area of this module for information to help you as you work through this lab.

Download the Three Lab Template for this assignment in What to Submit below. You will place the screenshots of your work in this document and submit the document for grading.

Check the Template Screenshot Example in Supporting Materials below to see how assignment screenshots should be created.

Connect to the employee information you entered in the Module Two lab to complete this activity. Then follow the steps below.

Specifically, you must address the following rubric criteria:

Update the name of the Branches table that you created in the previous lab to say "Department".

Use an ALTER statement to RENAME the Branches table to "Department".

Capture these outputs in a screenshot to validate that you successfully completed this step.

Insert fields to the Department table so that joins can be performed on tables.

INSERT INTO Department VALUES

$(1,$ 'Accounting'$),$

$(2,$ 'Human Resources'$),$

$(3,$ 'Information Systems'$),$

$(4,$ 'Marketing'$)$;

Write a SELECT statement for this table to prove this step and validate that it ran correctly with a screenshot.

Perform joins between the Department and Employee tables and show results for how many employees work in each of the four departments. This action will only provide information on the records that are already there.

Department $1=$ Accounting

Command: SELECT First$\_$Name, Last$\_$Name, Department.Department$\_$Name FROM Employee INNER JOIN Department

ON

Employee.Department$\_$ID $=$ Department.Department$\_$ID WHERE Employee.Department$\_$ID $=1$;

Using SELECT statements similar to that above, perform joins to produce results for the following tables:

Department $2=$ Human Resources

Department $3=$ Information Systems

Department $4=$ Marketing

Capture the results of these joins and validate your work by providing a screenshot. You should have the same number of records as you do employees.

Populate the Employee table with information for $10$ new employees.

Give the employees unique names and include attributes for all necessary fields. Note: Reference attributes from the lab in Module Two. Department ID values must be between $1$ and $4.$

Perform a join across the Employee and Department tables for each of the four departments. New and existing records should be displayed in the results.

Take a screenshot to capture the updated results that the Employee and Department joins show and validate that they have run correctly. You should have the same number of records as you do employees.

Identify the resultant outputs of the commands that you wrote and answer the following question:

How many records are returned for employees in each department?

Create a CSV file that contains only the records of employees in Human Resources and Information Systems. If you run this query multiple times, be sure to use a different file name each time. MySQL will not overwrite an existing file.

Enter the command listed below.

Command: select First$\_$Name, Last$\_$Name, Department.Department$\_$Name from Employee inner join Department on Employee.Department$\_$ID $=$ Department.Department$\_$ID where Employee.Department$\_$ID $=3$ OR Employee.Department$\_$ID $=2$ into outfile'$/$home$/$codio$/$workspace$/$HRandIS$-$Employees.csv$\text{'}$ FIELDS TERMINATED BY$\text{'},\text{'}$ LINES TERMINATED BY $\text{'}\backslash$r

$\text{'}$;

Print the file output to the screen.

In order to print your screen, start by refreshing your browser.

Type the word $$quit$$ after your MySQL prompt. Then press Enter to exit to the Linux shell. Do not exit the virtual lab environment.

Print the output of your file to the screen using these steps:

Type $"$pwd$"$ and press Enter. Then type $"$ls$"$ and press Enter again to list your files.

Next, type "cat HRandIS$-$Employees.csv$"$ and press Enter.

Capture these outputs in a screenshot to validate that you successfully completed this step.

Reflection: Provide detailed insight on the prompts below. Explain your process and how and why your process worked. Write your responses to the questions below in paragraph form.

Process

Explain how the joins you used in this assignment worked.

Describe why the commands you used were able to retrieve the Department table when you selected the Department name.

File creation and extraction

Identify how many records are