Module Three Lab-Morgan Muttitt (1).docx

Module Three Lab DAD 220 Morgan Muttitt Professor Andrew August 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. ALTER TABLE Branches RENAME Department;

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');

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 Using SELECT statements similar to the one above, perform joins to produce results for the following tables: Department 2 = Human Resources Department 3 = Information Systems 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

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. 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; 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 = 2; 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; 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 = 4;

4.) Populate the Employee table with information for ten new employees. 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.) INSERT INTO Employee VALUES (106, 'John', 'Travolta', 1, 'Exempt', 'Full-Time', 70000), (107,'George','Lopez',2,'Non-Exempt','Part-Time',55000), (108,'Morgan','Freeman',3,'Exempt','Full-Time',85000),

(109,'Adam','Lambert',4,'Non-Exempt','Part-Time',35000), (110,'Denzel','Washington',1,'Exempt','Full-Time',15000), (111,'Will','Smith',2,'Non-Exempt','Part-Time',30000), (112,'Will','Ferrell',3,'Exempt','Full-Time',25000), (113,'James','Jones',4,'Non-Exempt','Part-Time',35000), (114,'Margot','Robbie',1,'Exempt','Full-Time',89000), (115,'Britney','Spears',3,'Exempt','Full-Time',70000);

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. For Department 1: 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; For Department 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: 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: 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; 6.) Identify the resultant outputs of the commands that you’ve written: How many records are returned for employees in each department? Command for Department 1 (Accounting): 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; (Total of 9 employees.) Command for Department 2 (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; (Total of 3 employees.) Command for Department 3 (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 = 2;

(Total of 5 employees.) Command for Department 4 (Marketing): 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; (Total of 4 employees.) 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. 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. 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. Next, print the output of your file to the screen by following these steps:

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

Type pwd and press Enter , then type ls and press Enter again. This will list your files. Now, type cat HRandIS-Employees.csv and press Enter . 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. In this lab, I performed joins between Department tables and the Employee to obtain results for the number of each employee in each department and I used INNER JOIN to combine two tables based on the Department_ID column. For Department 1 (Accounting): 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; This join works by combining Employee and Department tables, joining them where the Department_ID in the Employee table matches the Department_ID in the Department table.this works for each department the same: Human Resources, Information Systems, and Marketing. Each department will filter out only including employees from each department. My commands I used were able to retrieve the Department table when selecting the Department name because of the way joins work in SQL. In this lab, Employee and Department tables were related through the Department_ID column. When I wrote the records of my query to a CSV file, there were only 8 records. A flat file consists of a single table of data. It is moving information from a database to another source. When you want to extract a flat file, you have to choose which files you want extracted, specify the format like CSV, and choosing the destination. The data can then be converted and easily read by others.