DAD 220 3-2 Lab Table Joins Asgnt

docx

School

Southern New Hampshire University *

*We aren’t endorsed by this school

Course

220

Subject

Mechanical Engineering

Date

Feb 20, 2024

Type

docx

Pages

Uploaded by GeneralIceRook13

DAD 220 – INTRODUCTION TO STRUCTURAL DATABASE Professor Chaunda Wilson ken 17th September 2023 Assignment 3 – Lab: Table Joins using Codio. A. Before you begin, type the following commands prior to typing MySQL to set file permissions. This will allow you to perform the file output creation: o chmod +x change_perm.sh o Press Enter . o ./change_perm.sh o Then, enter MySQL and reconnect to the employee information you entered in the previous lab. B. mysql> USE Ken; mysql> SHOW TABLES;

C. Write a SELECT statement for the Employee table to check that you’ve reconnected to the right information. mysql> SELECT * FROM Employee; 1. Update the name of the Branches table that you created in the previous lab to say "Department". o Use an ALTER statement to successfully RENAME the "Branches" table to "Department". o Capture these outputs in a screenshot to validate that you’ve successfully completed this step. 2 Insert fields to the Department table so that you’ll be able to perform joins on them. 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.

3 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. 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; o Usin g 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 o 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.

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

4. 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.)

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

6. Identify the resultant outputs of the commands that you’ve written: o How many records are returned for employees in each department? With each of the four SELECT statements that I utilized; the resulting outputs vary a little from the prior SELECT statement in (1) above. This is due to the new employees added to the Employee Table. The SELECT statement as used here just requests for the Employee First Name, Last Name, and Department Name. The FROM Statement specifies the table from which data is to be retrieved. In this case – Employee table in Ejiogu’s Database. The INNER JOIN statement is used to request all matching rows from Employee and Department Tables. The ON clause is used in the JOIN Statement to connect the tables by a designated field, but only in this case if the Employee Department ID is 1 or 2 or 3 or 4 in both tables respectively. The WHERE clause controls where the matches are called from. In this case from the Department ID column of the Employee Table. Records Returned for Employees in Each Department: Accounting: 5 Human Resources: 3 Information Systems: 3 Marketing: 6 The above Statements and clauses can be utilized to obtain more information as contained in the Tables. 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. o Enter the command listed below.

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

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'; o 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: o Type pwd and press Enter , then type ls and press Enter again. This will list your files. o Now, type cat HRandIS-Employees.csv and press Enter . o Capture these outputs in a screenshot to validate that you’ve successfully completed this step. 8 . Reflection : Provide detailed insight on the prompts below by explaining your process along with how and why it ultimately worked. o Process i. Explain how the joins you used in this assignment worked.

In this assignment, I only used the INNER JOIN function. It functions by choosing the matching rows (records) from tables to the Right and Left of the INNER JOIN clause. As MYSQL is still new to me, I had a lot of errors navigating this assignment, and thus did not want to complicate the assignment with the use of variety of JOINS. However, I shall keep attempting other joins in my practice. ii. Describe why the commands you used were able to retrieve the Department table when you selected the Department name. With the ON clause I was able to compare and link the two tables (Employee and Department) as they both shared a common FIELD – Department ID. According to e- book for DAD220 – zyBooks, the JOIN columns are specified via the ON clause. o 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 Six Records when I wrote the records of my query to the CSV file. This is because the query only requests for Data where Department ID is 2 (Human Resources) OR 3 (Information Systems). ii. Explain , in detail, the process of extracting data to a flat file. To extract data to a flat file, I used the SELECT statement. With this statement I was able to get the data I needed extracted to the flat file. While the INNER JOIN command enabled me to match entries from Employee Table and Department Table. With the ON clause, if Department ID column was 3, it connects the two Tables, but if it was 2 the OR operator made it possible to retrieve the matching rows from the Department and Employee tables. The INTO OUTFILE statement exports query results into an external file. It copies the records there and describes how the fields should be concluded. It is useful for creating backup copies of data, generating reports in specific formats, and exporting data for use in other applications. The use of apostrophes and commas was because files were being saved in CSV (comma separated value) file.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version