SQL for Database Construction and Application Processing - Database Processing: Fundamentals, Design, and Implementation - page 338

Create and run an SQL script named WPC -Delete-Data.sql to answer Review
Questions 7.26 through 7.27. Write the answers to Review Questions 7.26 and 7.27 as
SQL comments so that they cannot be run.
7.26 Write one DELETE statement that will delete all data for project '2013 Q3 Product Plan'
and all of its rows in ASSIGNMENT. Do not run this statement.
7.27 Write a DELETE statement that will delete the row for the employee named 'Smith'. Do
not run this statement. What happens if this employee has rows in ASSIGNMENT?
7.28 What is an SQL view? What purposes do views serve?
7.29 What is the limitation on SELECT statements used in SQL views?
Create and run an SQL script named WPC-Create-Views.sql to answer Review
Questions 7.30 through 7.35.
7.30 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.Phone as EmployeePhone. Run this statement
to create the view, and then test the view by writing and running an appropriate SQL
SELECT statement.
7.31 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.Phone 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.32 Write an SQL statement to create a view named CombinedNameEmployeePhoneView
that shows the values of EMPLOYEE.LastName, EMPLOYEE.FirstName, and
EMPLOYEE.Phone as EmployeePhone, but that combines EMPLOYEE.LastName and
EMPLOYEE.FirstName into one column named EmployeeName that displays the em-
ployee 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.33 Write an SQL statement to create a view named EmployeeProjectAssignmentView that
shows the values of EMPLOYEE.LastName as EmployeeLastName, EMPLOYEE.FirstName
as EmployeeFirstName, EMPLOYEE.Phone as EmployeePhone, and PROJECT.Name as
ProjectName. Run this statement to create the view, and then test the view by writing and
running an appropriate SQL SELECT statement.
7.34 Write an SQL statement to create a view named DepartmentEmployee
ProjectAssignmentView that shows the values of EMPLOYEE.LastName as
EmployeeLastName, EMPLOYEE.FirstName as EmployeeFirstName, EMPLOYEE
.Phone as EmployeePhone, DEPARTMENT.DepartmentName, Department.PHONE
as DepartmentPhone, and PROJECT.Name as ProjectName. Run this statement to
create the view, and then test the view by writing and running an appropriate SQL
SELECT statement.
7.35 Write an SQL statement to create a view named ProjectHoursToDateView that
shows the values of PROJECT.ProjectID, PROJECT.Name as ProjectName, PROJECT
.MaxHours as ProjectMaxHour, 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.36 Describe how views are used to provide an alias for tables. Why is this useful?
7.37 Explain how views can be used to improve data security.
7.38 Explain how views can be used to provide additional trigger functionality.
7.39 Give an example of a view that is clearly updatable.