Ch 7 WP Views & Stored Procedures

7.67 Write an SQL statement to create a view named EmployeePhoneView that shows the values of EMPLOYEE.LastName as EmployeeLastName, EMPLOYEE.FirstName as EmployeeFirstName, and EMPLOYEE.OfficePhone as EmployeePhone. Run this statement to create the view, and then test the view by writing and running an appropriate SQL SELECT statement.

7.68 Write an SQL statement to create a view named FinanceEmployeePhoneView that shows the values of EMPLOYEE.LastName as EmployeeLastName, EMPLOYEE. FirstName as EmployeeFirstName, and EMPLOYEE.OfficePhone as EmployeePhone for employees who work in the Finance Department. Run this statement to create the view, and then test the view by writing and running an appropriate SQL SELECT statement.

7.69 Write an SQL statement to create a view named CombinedNameEmployeePhoneView that shows the values of EMPLOYEE.LastName, EMPLOYEE.FirstName, and EMPLOYEE.OfficePhone as EmployeePhone but that combines EMPLOYEE.LastName and EMPLOYEE.FirstName into one column named EmployeeName that displays the employee name first name first. Run this statement to create the view, and then test the view by writing and running an appropriate SQL SELECT statement.

7.70 Write an SQL statement to create a view named EmployeeProjectAssignmentView that shows the values of EMPLOYEE.LastName as EmployeeLastName, EMPLOYEE.FirstName as EmployeeFirstName, EMPLOYEE.OfficePhone as EmployeePhone, and PROJECT.ProjectName as ProjectName. Run this statement to create the view, and then test the view by writing and running an appropriate SQL SELECT statement.

7.71 Write an SQL statement to create a view named DepartmentEmployeeProject-AssignmentView that shows the values of EMPLOYEE.LastName as EmployeeLastName, EMPLOYEE.FirstName as EmployeeFirstName, EMPLOYEE.OfficePhone as Employee-Phone, DEPARTMENT.DepartmentName, DEPARTMENT.Department-Phone as DepartmentPhone, and PROJECT.ProjectName as ProjectName. Run this statement to create the view, and then test the view by writing and running an appropriate SQL SELECT statement.

7.72 Write an SQL statement to create a view named ProjectHoursToDateView that shows the values of PROJECT.ProjectID, PROJECT.ProjectName, PROJECT.Max-Hours as ProjectMaxHours, and the sum of ASSIGNMENT.HoursWorked as ProjectHoursWorkedToDate. Run this statement to create the view, and then test the view by writing and running an appropriate SQL SELECT statement.

7.84 Create and test a user-defined function named FirstNameFirst that combines two parameters named FirstName and LastName into a concatenated value named Full-Name and displays, in order, the FirstName, a space, and the LastName. (Hint: Steve and Smith would be combined to read Steve Smith.)

7.85 Create and test a view called EmployeeDepartmentDataView that contains the employee name concatenated by the FirstNameFirst user-defined function in a field named EmployeeName, EMPLOYEE.Department, DEPARTMENT.OfficeNumber, DEPARTMENT.DepartmentPhone, and EMPLOYEE.OfficePhone as EmployeePhone. Run this statement to create the view, and then test the view by writing and running an appropriate SQL SELECT statement.

Write a procedure named ListEmployeeProjects which based on an employee number, outputs the employee's last name, first name and then any project names of projects that they have been assigned to. Some employees have no projects. For those employees, you should output the message Employee (give their employee number here) has no assigned projects.  For employees that have multiple projects, their employee names should only be output once. You need to utilize the techniques discussed in class to resolve how to process multiple projects. Your output should look as follows for these three runs of the procedure.

· For an employee who has no projects (EmpID = 5):

Employee 5 has no assigned projects

·  For an employee who has one project (EmpID = 9):

Employee Last Name: Jones Employee First Name: Heather Project Name: 2018 Q3 Tax Preparation

· For an employee who has many projects (EmpID = 6):

Employee Last Name: Evans Employee First Name: Ken Project Name: 2018 Q3 Marketing Plan Project Name: 2018 Q3 Portfolio Analysis Project Name: 2018 Q3 Tax Preparation Project Name: 2018 Q4 Production Plan Project Name: 2018 Q4 Marketing Plan Project Name: 2018 Q4 Portfolio Analysis

For submission, all queries need to be stored (in order) in a single .sql file. Identify each query by number with a comment. You are to model your SQL code based on the examples from Chapter 7.  Be sure to include your name as a comment at the beginning of the assignment.  Points will be determined at the time of grading.