Reference no: EM133750002

Assignment: Branches Table Lab Report

Overview

Table joins are commonly used to return records from multiple tables in a normalized database. This is done to generate reports and perform ad-hoc queries. Since data is not always in one large table in a normalized database, we use joins to get supporting information for the records we are searching for. Joins connect tables by matching values of keys in the tables to retrieve related records, generally using the SELECT and WHERE commands. For example, if customer_ID is used in a customer table and in an order table, the join would use the CUSTOMER-ID to connect the CUSTOMER table and the ORDER table to create a combined record for each key value matched in the two tables.

Directions

The manager of a small marketing firm has hired new employees and has asked you to identify information about them. You've already entered some information for the new hires, but you'll need to enter more. After entering the remaining new information, perform a join that will give you the results requested by the manager. Then, you'll need to write the query to save the results and write them to a comma-separated values (CSV) file, which is a delimited text file that uses a comma to separate values so they can be archived.

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

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

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

II. Insert fields to the Department table so that you'll be able to perform joins on them.

A. INSERT INTO Department VALUES

a. (1, 'Accounting'),

b. (2, 'Human Resources'),

c. (3, 'Information Systems'),

d. (4, 'Marketing');

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

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

A. Department 1 = Accounting

a. 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;

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

a. Department 2 = Human Resources

b. Department 3 = Information Systems

c. Department 4 = Marketing

C. 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.

IV. Populate the Employee table with information for ten new employees.

A. Give them unique names and include attributes for all necessary fields. (Note: Please reference attributes from the lab in Module Two. Department ID values must be between 1 and 4.)

V. 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.

A. Take a screenshot to capture the updated results that the Employee and Department joins show to validate that they have run correctly.

You should have the same number of records as you do employees.

VI. Identify the resultant outputs of the commands that you've written:

A. How many records are returned for employees in each department?

VII. 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.

A. Enter the command listed below.

a. 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' FIELDS TERMINATED BY',' LINES TERMINATED BY '\r\n';

B. Print the file output to the screen.

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

b. You'll need to type the word quit after your MySQL prompt and then press Enter to exit to the Linux shell. Do not exit the virtual lab environment itself.

c. Next, print the output of your file to the screen by following these steps:

i. Type pwd and press Enter, then type ls and press Enter again. This will list your files.
ii. Now, type cat HRandIS-Employees.csv and press Enter.
iii. Capture these outputs in a screenshot to validate that you've successfully completed this step.

VIII. Reflection: Provide detailed insight on the prompts below by explaining your process along with how and why it ultimately worked.

A. Process

a. Explain how the joins you used in this assignment worked.

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

B. File creation and extraction

a. Identify how many records are in the file when you write the records of your query to a CSV file.

b. Explain, in detail, the process of extracting data to a flat file.