The following is a list of queries that were run within the database for insertion, deletion, and updating. Aside from the queries asked by the tasks in the instruction paper, four additional inquiries at the bottom of this page were added during the insertion of the programmer and sales representative. This is due to database normalization, which introduced the employee department and employee position tables, which contain values from the appropriate department and position tables, as well as the employeeid field from the employee details table. As a result, the insertion into the employee details table would be incomplete without an insertion into the two newly constructed tables. SELECT Employee_Details.EmployeeID, Employee_Details.EmployeeLastName, Employee_Details.EmployeeFirstName, Employee_Details.StreetAddress, Employee_Details.City, Employee_Details.State, Employee_Details.ZipCode, Department.DepartmentName, Department_Head.ManagerName, Position.PositionName, Position.Salary FROM [Position] INNER JOIN ((Employee_Details INNER JOIN (Department INNER JOIN (Employee_Department INNER JOIN Department_Head ON Employee_Department.DepartmentID = Department_Head.DepartmentID) ON (Department.DepartmentID = Employee_Department.DepartmentID) AND (Department.DepartmentID = Department_Head.DepartmentID)) ON Employee_Details.EmployeeID = Employee_Department.EmployeeID) INNER JOIN Employee_Position ON Employee_Details.EmployeeID = Employee_Position.EmployeeID) ON Position.PositionID = Employee_Position.PositionID;
b) Insert New Position query
INSERT INTO [POSITION] ( PositionID, PositionName, Salary ) VALUES
('P06', 'Programmer III', 60000);
c) Insert New Programmer query
INSERT INTO EMPLOYEE_DETAILS ( EmployeeID, EmployeeLastname, EmployeeFirstname, StreetAddress, City, State, ZipCode ) VALUES
(402, 'Smith', 'Frances', '4545 Oxbow Lane', ' Anytown', 'AZ', 85331);
d) Insert New Sales Representative
INSERT INTO EMPLOYEE_DETAILS ( EmployeeID, EmployeeLastname, EmployeeFirstname, StreetAddress, City, State, ZipCode ) VALUES
(403, 'Smith', 'Greg', '18951 Silverlake Avenue', ' Sometown', 'AZ', 85332);
e) Update Employee
UPDATE EMPLOYEE_POSITION SET PositionID = 'P06' WHERE EmployeeID=206;
f) Update Vice President Salary
UPDATE [POSITION] SET salary = 80000 WHERE PositionID='P02';
g) Update Programmer I Salary
UPDATE [POSITION] SET salary = 40000 WHERE PositionID='P04';
h) Update Programmer II Salary
UPDATE [POSITION] SET salary = 50000 WHERE PositionID='P03';
i) Delete Sales Representative
DELETE * FROM employee_details WHERE employeeid=330;
j) Delete Programmer I
DELETE * FROM employee_details WHERE employeeid=209;
k) Insert New Sales Representative Department
INSERT INTO Employee_Department ( DepartmentID, EmployeeID ) VALUES ('D04', 403);
l) Insert New Sales Representative Position
INSERT INTO Employee_Position ( EmployeeID, PositionID ) VALUES (403, 'P05');
m) The Insert New Programmer Department
INSERT INTO Employee_Department ( DepartmentID, EmployeeID ) VALUES ('D03', 402);
n) The Insert New Programmer Position
INSERT INTO Employee_Position ( EmployeeID, PositionID ) VALUES (402, 'P04');