Module Three Lab Three: Table Joins
1.In this lab, we continue working with our database we established in module 1 and furthered in module 2. Our goal is to practice the applications of JOIN clauses. We must change our file permissions through the shell script provided. The ‘chmod +x’ makes ‘change_perm.sh’ change the permissions on itself to make it executable as a program. And the ./change_perm.sh executes the script. Then we launch MySQL as usual by entering ‘mysql’ and pressing Enter.
Continuing we connect to the established database
USE delehant
and perform a SELECT Statement to verify we have connected to the right information.
SELECT * FROM Employee; 2.My next task was updating the “Branches” table name to match the chosen name of “Department” for this assignment. The assigned method of doing this is through an ALTER/RENAME statement.
ALTER TABLE Branches
RENAME TO Department;
3.With the Department table newly named, I then set about adding fields to the empty table. Without this, we would be unable to perform the necessary joins to satisfy the proper output.
a.To add our data, we will be using an INSERT statement. In this instance, the code is already provided.
INSERT INTO Department VALUES (1, 'Accounting'), (2, 'Human Resources'), (3, 'Information Systems'), (4, 'Marketing');
b.I then validate by a SELECT statement to check the table results. SELECT * FROM Department;
4.With our tables populated, we look to create joins between the Department and Employee
tables. Using our SELECT statement, it will join records of Departmet_Name with the first and last names of the employees that belong to each corresponding Department_ID we select. a.Starting with 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; b.Someone once said a good programmer is a lazy programmer. Not sure if I agree, but copy and paste can be practical if used with diligence. Identical select statements will work moving forward by simply changing Department_ID = to the corresponding number. Department 2 = Human Resources
SELECT First_Name, Last_Name, Department.Department_Name
1.In this lab, we continue working with our database we established in module 1 and furthered in module 2. Our goal is to practice the applications of JOIN clauses. We must change our file permissions through the shell script provided. The ‘chmod +x’ makes ‘change_perm.sh’ change the permissions on itself to make it executable as a program. And the ./change_perm.sh executes the script. Then we launch MySQL as usual by entering ‘mysql’ and pressing Enter.
Continuing we connect to the established database
USE delehant
and perform a SELECT Statement to verify we have connected to the right information.
SELECT * FROM Employee; 2.My next task was updating the “Branches” table name to match the chosen name of “Department” for this assignment. The assigned method of doing this is through an ALTER/RENAME statement.
ALTER TABLE Branches
RENAME TO Department;
3.With the Department table newly named, I then set about adding fields to the empty table. Without this, we would be unable to perform the necessary joins to satisfy the proper output.
a.To add our data, we will be using an INSERT statement. In this instance, the code is already provided.
INSERT INTO Department VALUES (1, 'Accounting'), (2, 'Human Resources'), (3, 'Information Systems'), (4, 'Marketing');
b.I then validate by a SELECT statement to check the table results. SELECT * FROM Department;
4.With our tables populated, we look to create joins between the Department and Employee
tables. Using our SELECT statement, it will join records of Departmet_Name with the first and last names of the employees that belong to each corresponding Department_ID we select. a.Starting with 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; b.Someone once said a good programmer is a lazy programmer. Not sure if I agree, but copy and paste can be practical if used with diligence. Identical select statements will work moving forward by simply changing Department_ID = to the corresponding number. Department 2 = Human Resources
SELECT First_Name, Last_Name, Department.Department_Name
