Lab 3
docx
keyboard_arrow_up
School
Southern New Hampshire University *
*We aren’t endorsed by this school
Course
220
Subject
Information Systems
Date
Dec 6, 2023
Type
docx
Pages
7
Uploaded by ProfessorHeat17493
3-2 LAB
Victoria Slater
1.
chmod +x change_perm.sh
Press Enter.
./change_perm.sh
Then, enter MySQL and reconnect to the employee information.
Write a SELECT statement for the Employee table to check that you’ve reconnected to
the right information.
2.
Update the name of the Branches table that you created in the previous lab to say
"Department".
3-2 LAB
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.
3.
Insert fields to the Department table so that you’ll be able to perform joins on them.
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.
4.
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
3-2 LAB
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 the one above, perform joins to produce
results for the following tables:
i.
Department 2 = Human Resources
ii.
Department 3 = Information Systems
iii.
Department 4 = Marketing
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
3-2 LAB
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.
1.
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.)
2.
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.
3-2 LAB
3-2 LAB
3.
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.
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.
ii.
Next, print the output of your file to the screen by following these steps:
1.
Type pwd and press Enter, then type ls and press Enter again. This
will list your files.
2.
Now, type cat HRandIS-Employees.csv and press Enter.
3.
Capture these outputs in a screenshot to validate that you’ve
successfully completed this step.
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
3-2 LAB
Reflection
Process:
i.
Joins I worked on in this assignment worked by following the information in the
rubric and selecting the employee table; then the first name, last name, department
name then the department ID.
ii.
The commands were able to retrieve the department table when you selected the
department name by pulling the data from the employee table first then from the
department table afterword.
File creation and extraction:
iii.
There were 8 records in the CSV file.
iv.
The process of extracting data to a flat file is by selecting the employees first name,
last name, department, the department name from the employee table. Then with the
inner join from the employee table we select the department ID 2 or 3. Once you have
the departments you output the file to codio as HRandIS-Employees.csv.