3-2 Lab Adams
docx
keyboard_arrow_up
School
Southern New Hampshire University *
*We aren’t endorsed by this school
Course
220
Subject
Mechanical_engineering
Date
May 21, 2024
Type
docx
Pages
6
Uploaded by ProfessorTiger3265
DAD 220 Module Three Lab Template Connect to Database
USE Adams
SELECT * from Employee;
Update the name of the Branches table.
ALTER TABLE Branches
i.
RENAME TO Department;
SHOW Tables;
Insert fields to the Department table.
INSERT INTO Department VALUES
(1, 'Accounting'), (2, 'Human Resources'), (3, 'Information Systems'), (4, 'Marketing');
SELECT * FROM Department;
Perform joins between the Department and Employee tables
.
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;
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;
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 = 3;
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 = 4;
2.
Populate the Employee table with information for 10 new employees
. INSERT INTO Employee VALUES
(107,'Krista','Romano',1,'Non-Exempt','Full-Time',80000),
(108,'Olivia','Burris',2,'Exempt','Part-Time',90000),
(109,'Monique','Vanlue',3,'Non-Exempt','Full-Time',50000),
(110,'Joett','Smith',4,'Exempt','Part-Time',60000),
(111,'Kristna','Ocampo',1,'Non-Exempt','Full-Time',70000),
(112,'Steven','Adams',2,'Exempt','Part-Time',80000),
(113,'Christie','Ford',3,'Non-Exempt','Full-Time',80500),
(114,'Sharon','Rainford',4,'Exempt','Part-Time',90000),
(115,'Lori','Mercer',1,'Non-Exempt','Full-Time',90500),
(116,'Willard','Looper',2,'Exempt','Part-Time',70500);
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
Perform a join across the Employee and Department tables
for each of the four departments. SELECT Employee.First_Name, Employee.Last_Name, Department.Department_Name
FROM Employee INNER JOIN Department ON Employee.Department_ID = Department.Department_ID
WHERE Employee.Department_ID = 1;
SELECT Employee.First_Name, Employee.Last_Name, Department.Department_Name
FROM Employee INNER JOIN Department ON Employee.Department_ID = Department.Department_ID
WHERE Employee.Department_ID = 2;
SELECT Employee.First_Name, Employee.Last_Name, Department.Department_Name
FROM Employee INNER JOIN Department ON Employee.Department_ID = Department.Department_ID
WHERE Employee.Department_ID = 3;
SELECT Employee.First_Name, Employee.Last_Name, Department.Department_Name
FROM Employee INNER JOIN Department ON Employee.Department_ID = Department.Department_ID
WHERE Employee.Department_ID = 4;
Identify the resultant outputs
of the commands you wrote and answer the following question:
How many records are returned for employees in each department? 6 employees in Accounting.
4 employees in Human Resources
3 employees in Information Systems.
4 employees in Marketing.
Create a CSV file
that contains only the records of employees in Human Resources and 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 OR Employee.Department_ID = 2 INTO outfile'/home/codio/workspace/HRandIS-Employees.csv' FIELDS TERMINATED BY',' LINES TERMINATED BY '\r\n';
To print your screen, start by refreshing your browser.
mysql> quit press enter
pwd press enter
cat HRandIS-Employee.csv press enter
Reflection
Process Explain
how the joins
you used in this assignment worked. Describe
why the commands
you used were able to retrieve the Department table when you selected the Department name.
The joins in my assignment were inner joins. Inner joins work by combine records from two tables whenever there are matching values in a field common to both tables, both my tables met the WHERE clause. I was able to retrieve the Department table when I selected Department
name because it is the table that contained the needed values.
Identify
how many records
are in the file when you write the records of your query to a CSV file. Explain
, in detail, the process of extracting data
to a flat file.
There were 7 records in the file when I wrote CSV file query. To extract data, you will need to specify the data you need. Then specify where you need the data to be extracted to. Finally, you
exit the MySQL environment to enter the Linux Shell. At this point in the process, you can choose to print the output onto your screen.
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