7.1 What does DDL stand for? List the SQL DDL statements.
7.2 What do es DML stand for? List the SQL DML statements.
7.3 Explain the meaning of the following expression: IDENTITY (4000, 5).
For this set of Review Questions, we will create and use a database for the Review
Wedgewood Pacific Corporation (WPC) that is similar to the Microsoft Access
database we created and used in Chapters 1 and 2. Founded in 1957 in Seattle,
Washington, WPC has grown into an internationally recognized organization. The
company is located in two buildings. One building houses the Administration,
Accounting, Finance, and Human Resources departments, and the second houses the
300 Part 3 Database Implementation
Production, Marketing, and Information Systems departments. The company database
contains data about employees; departments; projects; assets, such as computer
equipment; and other aspects of company operations.
The database will be named WPC and will contain the following four tables:
DEPARTMENT (DepartmentName, BudgetCode, OfficeNumber, Phone)
EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Phone, Email)
PROJECT (ProjectID, Name, Department, MaxHours, StartDate, EndDate)
ASSIGNMENT (ProjectID, EmployeeNumber, HoursWorked)
EmployeeNumber is a surrogate key that starts at 1 and increments by
1. ProjectID is a surrogate key that starts at 1000 and increases by 100.DepartmentName is the text name of the department, and is therefore not a surrogate key.
The WPC database has the following referential integrity constraints:
Department in EMPLOYEE must exist in Department in DEPARTMENT
Department in PROJECT must exist in Department in DEPARTMENT
ProjectID in ASSIGNMENT must exist in ProjectID in PROJECT
EmployeeNumber in ASSIGNMENT must exist in EmployeeNumber in EMPLOYEE
The relationship from EMPLOYEE to ASSIGNMENT is 1:N, M-O and the
relationship from PROJECT to ASSIGNMENT is 1:N, M-O.
The database also has the following business rules:
• If an EMPLOYEE row is to be deleted and that row is connected to any
ASSIGNMENT, the EMPLOYEE row deletion will be disallowed.
• If a PROJECT row is deleted, then all the ASSIGNMENT rows that are connected to
the deleted PROJECT row will also be deleted.
The business sense of these rules is as follows:
• If an EMPLOYEE row is deleted (e.g., if the employee is transferred), then someone
must take over that employee's assignments. Thus, the application needs someone
to reassign assignments before deleting the employee row.
• If a PROJECT row is deleted, then the project has been canceled, and it is unnecessary
to maintain records of assignments to that project.
The column characteristics for these tables are shown in Figures 1-26
(DEPARTMENT), 1-28 (EMPLOYEE), 2-29 (PROJECT), and 2-31 (ASSIGNMENT). The
data for these tables are shown in Figures 1-27 (DEPARTMENT), 1-29 (EMPLOYEE),
2-30 (PROJECT), and 2-32 (ASSIGNMENT).
If at all possible, you should run your SQL solutions to the following questions against
an actual database. Because we have already created this database in Microsoft Access,
you should use an SQL-oriented DBMS such as Oracle Database 11g, SQL Server 2008
R2, or MySQL 5.5 in these exercises. If that is not possible, create a new Microsoft Access
database named WPC-CH07.accdb, and use the SQL capabilities in these exercises. In all
the exercises, use the data types appropriate for the DBMS you are using.
Answer Review Questions 7.4-7.13 without running them on your DBMS.
7.4 Write a CREATE TABLE statement for the DEPARTMENT table.
7.5 Write a CREATE TABLE statement for the EMPLOYEE table. Email is required and is an
alternate key, and the default value of Department is Human Resources. Cascade
updates but not deletions from DEPARTMENT to EMPLOYEE.
7.6 Write a CREATE TABLE statement for PROJECT table. The default value for MaxHours
is 100. Cascade updates but not deletions from DEPARTMENT to EMPLOYEE.
Chapter 7 SQL for Database Construction and Application Processing 301
7.7 Write a CREATE TABLE statement for the ASSIGNMENT table. Cascade only
deletions from PROJECT to ASSIGNMENT; do not cascade either deletions or updates
from EMPLOYEE to ASSIGNMENT.
7.8 Modify your answer to Review Question 7.7 to include the constraint that StartDate be
prior to EndDate.
7.9 Write an alternate SQL statement that modifies your answer to Review Question 7.7 to
make the relationship between EMPLOYEE and ASSIGNMENT a 1:1 relationship.
7.10 Write an ALTER statement to add the column AreaCode to EMPLOYEE. Assume that
AreaCode is not required.
7.11 Write an ALTER statement to remove the column AreaCode from EMPLOYEE.
7.12 Write an ALTER statement to make Phone an alternate key in EMPLOYEE.
7.13 Write an ALTER statement to drop the constraint that Phone is an alternate key in
EMPLOYEE.
If you are using a DBMS, then at this point you should create a database named
WPC and run the SQL statements from Review Questions 7.4, 7.5, 7.6, and 7.8 only.
(Hint: Write and test an SQL script, and then run the script. Save the script as
DPB-e12-WPC-Create-Tables.sql for future use.) Do not run your answers to Review
Questions 7.7 or 7.9! After the tables are created, run your answers to Review
Questions 7.10 through 7.13. Note that after these four statements have been run the
table structure is exactly the same as it was before you ran them.
7.14 Write INSERT statements to add the data shown in Figure 1-30 to the DEPARTMENT
table. Run these statements to populate the DEPARTMENT table. (Hint: Write and test
an SQL script, and then run the script. Save the script as DBP-e12-WPC-Insert-
DEPARTMENT-Data.sql for future use.)
7.15 Write INSERT statements to add the data shown in Figure 2-32 to the EMPLOYEE
table. Run these statements to populate the EMPLOYEE table. (Hint: Write and test an
SQL script, and then run the script. Save the script as DBP-e12-WPC-Insert-
EMPLOYEE-Data.sql for future use.)
7.16 Write INSERT statements to add the data shown in Figure 2-30 to the PROJECT table.
Run these statements to populate the PROJECT table. (Hint: Write and test an SQL
script, and then run the script. Save the script as DBP-e12-WPC-Insert-PROJECTData.
sql for future use.)
7.17 Write INSERT statements to add the data shown in Figure 2-32 to the ASSIGNMENT
table. Run these statements to populate the ASSIGNMENT table. (Hint: Write and test an
SQL script, and then run the script. Save the script as DBP-e12-WPC-Insert-ASSIGNMENTData.
sql for future use.)
7.18 Why were the tables populated in the order shown in Review Questions 7.14-7.17?
7.19 Assume that you have a table named NEW_EMPLOYEE that has the columns Department,
Email, FirstName, and LastName, in that order. Write an INSERT statement to add all of the rows from the table NEW_EMPLOYEE to EMPLOYEE. Do not attempt
to run this statement!
7.20 Write an UPDATE statement to change the phone number of employee with
EmployeeNumber 11 to 360-287-8810. Run this SQL statement.
7.21 Write an UPDATE statement to change the department of employee with EmployeeNumber
5 to Finance. Run this SQL statement.
7.22 Write an UPDATE statement to change the phone number of employee with
EmployeeNumber 5 to 360-287-8420. Run this SQL statement.
302 Part 3 Database Implementation
7.23 Combine your answers to Review Questions 7.21 and 7.22 into one SQL statement. Run
this statement.
7.24 Write an UPDATE statement to set the HoursWorked to 60 for every row in ASSIGNMENT
having the value 10 for EmployeeNumber. Run this statement.
7.25 Assume that you have a table named NEW_EMAIL, which has new values of Email for
some employees. NEW_EMAIL has two columns: EmployeeNumber and NewEmail.
Write an UPDATE statement to change the values of Email in EMPLOYEE to those in
the NEW_EMAIL table. Do not run this statement.
7.26 Write one DELETE statement that will delete all data for project '2011 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 Write an SQL statement to join EMPLOYEE, ASSIGNMENT, and PROJECT using the
JOIN ON syntax. Run this statement.
7.29 Write an SQL statement to join EMPLOYEE and ASSIGNMENT and include all rows of
EMPLOYEE in your answer, regardless of whether they have an ASSIGNMENT. Run
this statement.
7.30 What is an SQL view? What purposes do views serve?
7.31 What is the limitation on SELECT statements used in SQL views?
7.32 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,
and then test the view with an SQL SELECT statement.
7.33 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, and then test the view with an SQL
SELECT statement.
7.34 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 employee name first name first.
Run this statement, and then test the view with an SQL SELECT statement.
7.35 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, and then test the view with an SQL SELECT statement.
7.36 Write an SQL statement to create a view named DepartmentEmployeeProjectAssignmentView
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, and then test the view with an
SQL SELECT statement.
7.37 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 ProjectHoursWorked-
ToDate. Run this statement, and then test the view with an SQL SELECT statement.
7.38 Describe how views are used to provide an alias for tables. Why is this useful?
7.39 Explain how views can be used to improve data security.
Chapter 7 SQL for Database Construction and Application Processing 303
7.40 Explain how views can be used to provide additional trigger functionality.
7.41 Give an example of a view that is clearly updatable.
7.42 Give an example of a view that is clearly not updatable.
7.43 Summarize the general idea for determining whether a view is updatable.
7.44 If a view is missing required items, what action on the view is definitely not allowed?
7.45 Explain the paradigm mismatch between SQL and programming languages.
7.46 How is the mismatch in your answer to Review Question 7.45 corrected?
7.47 Describe the SQL/PSM component of the SQL standard. What are PL/SQL and T-SQL?
What is the MySQL equivalent?
7.48 What is a trigger?
7.49 What is the relationship between a trigger and a table or view?
7.50 Name nine possible trigger types.
7.51 Explain, in general terms, how new and old values are made available to a trigger.
7.52 Describe four uses for triggers.
7.53 Assume that the View Ridge Gallery will allow a row to be deleted from WORK if the
work has never been sold. Explain, in general terms, how to use a trigger to accomplish
such a deletion.
7.54 Assume that the Wedgewood Pacific Corporation will allow a row to be deleted from
EMPLOYEE if the employee has no project assignments. Explain, in general terms,
how to use a trigger to accomplish such a deletion.
7.55 What is a stored procedure? How do they differ from triggers?
7.56 Summarize how to invoke a stored procedure.
7.57 Summarize the key advantages of stored procedures.
These Project Questions extend the Wedgewood Pacific Corporation database you
created and used in the Review Questions with two new tables named COMPUTER
and COMPUTER_ASSIGNMENT.