DAD 220 3-2 Table Joins

pdf

School

Southern New Hampshire University *

*We aren’t endorsed by this school

Course

220

Subject

Computer Science

Date

Feb 20, 2024

Type

pdf

Pages

Uploaded by CaptainPencil6470

Sarah Byerly DAD 220 Professor Venckus 09-16-2023 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: • chmod +x change_perm.sh • Press Enter . • ./change_perm.sh • Then, enter MySQL and reconnect to the employee information you entered in the previous lab. • Write a SELECT statement for the Employee table to check that you’ve reconnected to the right information. 1. Update the name of the Branches table that you created in the previous lab to say "Department". • Use an ALTER statement to successfully RENAME the "Branches" table to "Department". • 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.

Sarah Byerly DAD 220 Professor Venckus 09-16-2023 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; • Using SELECT statements similar to the one above, perform joins to produce results for the following tables: ii. Department 2 = Human Resources iii. Department 3 = Information Systems iv. 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. 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.)

Sarah Byerly DAD 220 Professor Venckus 09-16-2023 Employee_ID is a little off sequence. I duplicated an Employee_ID and had to fix it. I put 106 for Skylynn by mistake. INSERT INTO Employee VALUES (116, 'Skylynn', 'Hoover', 3, 'Exempt', 'Full-Time', 75000), (107,'Scott','Rollins',4,'Non-Exempt','Full-Time',80000), (108,'Luke','Skywalker',1,'NULL','Part-Time',95000), (109, 'Londyn', 'Hoover', 2, 'Exempt', 'Full-Time', 70000), (110,'Carol','Workman',3,'Non-Exempt','Part-Time',45000), (111,'VaShon','Byerly',4,'Exempt','Full-Time',55000), (112, 'Bob', 'White', 3, 'Non-Exempt', 'Part-Time', 65000), (113,'Sally','Yoder',4,'NULL','Full-Time',45000), (114,'Levi','Smithson',3,'Exempt','Full-Time',40000), (115,'Jim','Dopper',4,'Non-Exempt','Part-Time',30000); 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. • 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: • How many records are returned for employees in each department? For Department 1 (Accounting) Total employees = 3 SELECT First_Name, Last_Name, Department.Department_Name FROM Employee INNER JOIN Department ON

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

Sarah Byerly DAD 220 Professor Venckus 09-16-2023 -> Employee.Department_ID = Department.Department_ID WHERE Employee.Department_ID = 1; For Department 2 (Human Resources) Total employees = 2 SELECT First_Name, Last_Name, Department.Department_Name FROM Employee INNER JOIN Department ON -> Employee.Department_ID = Department.Department_ID WHERE Employee.Department_ID = 2; For Department 3 (Information Systems) Total employees = 6 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; For Department 4 (Marketing) Total employees = 6 SELECT First_Name, Last_Name, Department.Department_Name FROM Employee INNER JOIN Department ON -> Employee.Department_ID = Department.Department_ID WHERE Employee.Department_ID = 4; 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. • Enter the command listed below.

Sarah Byerly DAD 220 Professor Venckus 09-16-2023 v. 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'; • Print the file output to the screen. vi. 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. vii. Next, print the output of your file to the screen by following these steps: 8. Type pwd and press Enter , then type ls and press Enter again. This will list your files. 9. Now, type cat HRandIS-Employees.csv and press Enter . 10. Capture these outputs in a screenshot to validate that you’ve successfully completed this step. 11. Reflection : Provide detailed insight on the prompts below by explaining your process along with how and why it ultimately worked. • Process viii. Explain how the joins you used in this assignment worked. I performed joins between the Employee and Department tables in order to obtain the number of employees in each department. I used INNER JOIN to combine the two tables using the Department_ID column found in both tables. Department 1 (Accounting): This combines the Employee and Department tables joining the records where Department_ID matches in both tables. I used the WHERE clause to filter out all departments but Accounting.

Sarah Byerly DAD 220 Professor Venckus 09-16-2023 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; Department 2 (Human Resources): This combines, just like the previous one, the Employee and Department tables joining the records where Department_ID matches in both tables. I used the WHERE clause to filter out all departments but Human Resources. SELECT First_Name, Last_Name, Department.Department_Name FROM Employee INNER JOIN Department ON -> Employee.Department_ID = Department.Department_ID WHERE Employee.Department_ID = 2; Department 3 (Information Systems): This combines, just like the previous two, the Employee and Department tables joining the records where Department_ID matches in both tables. I used the WHERE clause to filter out all departments but Information Systems. 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; Department 4 (Marketing): Lastly, this combines, just like the previous three, the Employee and Department tables joining the records where Department_ID matches in both tables. I used the WHERE clause to filter out all departments but Marketing. SELECT First_Name, Last_Name, Department.Department_Name FROM Employee INNER JOIN Department ON

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

Sarah Byerly DAD 220 Professor Venckus 09-16-2023 -> Employee.Department_ID = Department.Department_ID WHERE Employee.Department_ID = 4; The results of adjusting the Depatment_ID in the WHERE clause and using these joins we were able to retrieve each department separately and the employees that work in therm. ix. Describe why the commands you used were able to retrieve the Department table when you selected the Department name. Due to the way joins work in SQL, I was able to extract the information from the Department table when I selected the Department name. Common columns and/or keys are what relate one table to another in relational databases. The Department_ID is the relationship between the Employee and Department tables. Using the INNER JOIN to combine the two tables based on Department_ID I was able to establish a temporary table that had the information needed from both original tables. When selecting the Department name using Department.Department_ID in the queries the Department table is what it referred to because of the join. This, ultimately, created a new set of results that combined the relevant columns from both of the tables. The Deparatment_Name column from the Department table along with First_Name and Last_Name columns from the Employee table are what created the temporary results. • File creation and extraction x. Identify how many records are in the file when you write the records of your query to a CSV file. 8 Records total (Human Resource = 2 and Information System = 6) xi. Explain , in detail, the process of extracting data to a flat file. Transferring data to a flat file involves writing query results to a delimited text file. In this lab exercise I needed to pull specific employee information pertaining to the Human Resource and Information System departments and I needed to save it as a CSV file. In order to do this, I used the following query: select First_Name, Last_Name, Department.Department_Name from Employee inner join Department on

Sarah Byerly DAD 220 Professor Venckus 09-16-2023 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'; The first thing I did was create a SELECT statement that pulled the selected data from the relevant tables. I joined the Employee and Department tables using the Department_ID column and narrowed the data down to only include employees from the Human Resource and Information System departments. I then used the INTO OUTFILE clause to determine the filename and path of the output file. The filename for this lab was HRandIS-Employees.csv. A comma-separated value file is indicated by the file extension ‘.csv’. Next, I used the FIELDS TERMINATED BY ‘,’ clause to specify the fields in each row should have a comma to separate them. LINES TERMINATED BY ‘ \r\ n’ clause is what indicates every row needs to end with a line feed and carriage return. This is Windows standard terminator for a line. Finally, I ran the query to retrieve the necessary data and put it into the specified file. After executing all of this (running the query and creating the CSV file) I can now see the contents of the file by using the cat command in the Linux shell (cat HRandIS-Employees.csv). This command sends the information in the file to the screen, which allowed me to verify the data was formatted as CSV and extracted successfully.

DAD 220 3-2 Table Joins

Related Documents