DAD 220 Module Three Lab
docx
keyboard_arrow_up
School
Southern New Hampshire University *
*We aren’t endorsed by this school
Course
DAD-220-X3
Subject
Mechanical_engineering
Date
Jun 4, 2024
Type
docx
Pages
7
Uploaded by MasterButterflyPerson397
DAD 220 Module Three Lab Template Overview
To complete this lab, go to your Codio virtual lab environment and start a new terminal session. Once there, connect to the employee information you entered in the Module Two lab
. Then perform the steps below to complete the activity. Manually enter any commands you are asked to write. At the end of each step in the activity, replace bracketed text with a screenshot, brief explanation, or both, as indicated. Size each screenshot and its explanation to fit about one-quarter of the page with the
description written below the screenshot. Review the Template Screenshot Example linked in the guidelines and rubric for this assignment to see an example of how screenshots for your assignment should look.
Create Joins Between Tables
1.
Update the name of the Branches table
that you created in the previous lab to say "Department".
A.
Use an ALTER statement to RENAME the Branches table "Department". B.
Capture these outputs in a screenshot to validate that you successfully completed this step.
(SIDE NOTE: Apparently, I DIDN’T NEED TO DO ALTER THE NAME because it was already set as department. I may have done this without realizing.)
2.
Insert fields to the Department table
so that joins can be performed on tables.
A.
INSERT INTO Department VALUES
(1, 'Accounting'), (2, 'Human Resources'), (3, 'Information Systems'), (4, 'Marketing');
B.
Write a SELECT statement for this table to prove this step and validate that it ran correctly with a screenshot. 3.
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.
A.
Department 1 = Accounting i.
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 that above, perform joins to produce results
for the following tables: i.
Department 2 = Human Resources ii.
Department 3 = Information Systems
iii.
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.
4.
Populate the Employee table with information for 10 new employees
. A.
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.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
5.
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 and validate that they have run correctly. You should have the same number of records as you do employees.
6.
Identify the resultant outputs
of the commands you wrote and answer the following question:
A.
How many records are returned for employees in each department? 7.
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. i.
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. i.
In order to print your screen, start by refreshing your browser.
ii.
Type the word "quit" after your MySQL prompt. Then press Enter
to exit to the Linux shell. Do not exit the virtual lab environment.
iii.
Print the output of your file to the screen using these steps: 1.
Type "pwd" and press Enter
. Then type "ls" and press Enter
again to list your files. 2.
Next, type "cat HRandIS-Employees.csv" and press Enter
. 3.
Capture these outputs in a screenshot to validate that you successfully completed this step. 8.
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.
A.
Process i.
Explain
how the joins
you used in this assignment worked.
The joins in the assignment worked by looking at the table data sets. First name,
last name, and department name were extracted from the employee table initially. Next, it used the department ID as the key to do an inner join with the department
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
table. We can designate the department we want to investigate using this procedure; for example, Information Services is marked as 3 in the table data.
ii.
Describe
why the commands
you used were able to retrieve the Department table when you selected the Department name.
By naming the data sources, the operations worked. They first mentioned obtaining data from the employee table, and then later on, from the department table.
B.
File creation and extraction
i.
Identify
how many records
are in the file when you write the records of your query to a CSV file.
There were a record of 8 files.
ii.
Explain
, in detail, the process of extracting data
to a flat file.
-
The following is the procedure for extracting data to a flat file: first, we get the employee table's names (first name, last name, department, and department name). Next, we choose the department from the personnel table from which to extract the department ID using an inner join. In this instance, we were asked
to select between departments 2 and 3. Following this choice, we exported the file as "HRandIS-Employee.csv" to the Codio workspace. By using this method, which only shows the entries we've specifically asked to see, we may view the requested information in a more understandable style.