Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
All Sections – The Access Workbench
SECTION THREE
CHAPTER OBJECTIVES
• Learn how to use Microsoft Access SQL
• Learn how to run queries in single and multiple tables, using both SQL and Query by
Example (QBE)
• Learn how to manually set table and relationship properties that Microsoft Access
SQL does not support
CHAPTER ERRATA
• [23-FEB-2017] In Access Workbench Exercise AW.3.3 question E on page 241,
the column name EmployeeID should be EmployeeNumber.
• [23-FEB-2017] In Access Workbench Exercise AW.3.3 question F on page 241,
the column name EmployeeID should be EmployeeNumber.
TEACHING SUGGESTIONS
• This section of “The Access Workbench” is the basic introduction to creating
relationships between tables. Demonstrate the Microsoft Access 2016 Relationship
window in class, and discuss each of the operations covered in the text.
• Make sure your students actually work through The Access Workbench steps before
attempting the Access Workbench Exercises.
• You may want to compare SQL and QBE queries in lecture.
• Note that Microsoft Access SQL requires number signs (#) around some data and
other data in WHERE clauses. See the, for example, question AW.3.1.C.
• Show your class that while most SQL queries translate properly into QBE queries
(and vice versa) there will be exceptions. For example, the first (AW.3.1.A)1SQL
query SELECT * FROM PROPERY; results in the QBE shown on the next page.
This is obviously incorrect since no fields have been selected! This seems to usually
occur when the SQL statement refers to all columns using the asterisk symbol.
All Sections – The Access Workbench
• Review the material on Page 140-141 about the versions of SQL used in Microsoft
Access. Be sure students understand that by default Microsoft Access uses an
earlier version of the ANSI standard—Microsoft calls it ANSI-89 SQL or Microsoft Jet
SQL (after the Microsoft Jet DBMS used by Microsoft Access). Since the current
standard version of SQL is SQL:2011, which is based on SQL-92 (which Microsoft
refers to as ANSI-92 SQL), and since ANSI-89 SQL differs significantly from SQL-92
• On the other hand, note the MS Microsoft Access 2016 does contain a setting that
allows you to use SQL-92 instead of the default ANSI-89 SQL. For a particular
Microsoft Access database, use File | Microsoft Access Options to open the
Microsoft Access Options dialog box, and then set the SQL Server Compatible
Syntax (ANSI-92) property in the Query Design settings of the Object Designers
page.
• Note that the solutions in this section do not include PROJECT 1700—2017 Q4 Tax
Preparation run by the Accounting department (as per the INSERT statement on
page 156). If your students used the WP.accdb file to try out the commands in
All Sections – The Access Workbench
ANSWERS TO ACCESS WORKBENCH EXERCISES
In the “Access Workbench Exercises” in Chapters 1 and 2, you created a database for
the Wedgewood Pacific (WP) of Seattle, Washington. In this set of exercises, you’ll:
• Create and run queries against the database by using Access SQL.
AW.3.1. Using Access SQL, create and run queries to answer the questions that follow. Save
each query using the query name format SQLQuery-AWE-3-1-## where the ## sign
is replaced by the letter designator of the question. For example, the first query will
be saved as SQLQuery-AWE-3-1-A.
A. What projects are in the PROJECT table? Show all information for each
project.
B. What are the ProjectID, ProjectName, StartDate, and EndDate values of
projects in the PROJECT table?
See the file: DBC-e08-WP-AW-03.accdb.
All Sections – The Access Workbench
C. What projects in the PROJECT table started before August 1, 2017? Show
all the information for each project.
D. What projects in the PROJECT table have not been completed? Show all the
information for each project.
See the file: DBC-e08-WP-AW-03.accdb.
All Sections – The Access Workbench
E. Who are the employees assigned to each project? Show ProjectID,
EmployeeNumber, LastName, FirstName, and Phone.
All Sections – The Access Workbench
F. Who are the employees assigned to each project? Show ProjectID,
ProjectName, and Department. Show EmployeeNumber, LastName,
FirstName, and Phone.
All Sections – The Access Workbench
G. Who are the employees assigned to each project? Show ProjectID,
ProjectName, Department, and Department Phone. Show EmployeeNumber,
LastName, FirstName, and Employee Phone. Sort by ProjectID, in ascending
order.
See the file: DBC-e08-WP-AW-03.accdb.
All Sections – The Access Workbench
H. Who are the employees assigned to projects run by the Sales and Marketing
Department? Show ProjectID, ProjectName, Department, and Department
Phone. Show EmployeeNumber, LastName, FirstName, and Employee
Phone. Sort by ProjectID, in ascending order.
See the file: DBC-e08-WP-AW-03.accdb.
/***** Question H - SQLQuery-AWE-3-1-H ************************/
SELECT P.ProjectID, ProjectName,
I. How many projects are being run by the Sales and Marketing Department?
Be sure to assign an appropriate column name to the computed results.
See the file: DBC-e08-WP-AW-03.accdb.
/***** Question I - SQLQuery-AWE-3-1-I ************************/
All Sections – The Access Workbench
J. What is the total MaxHours of projects being run by the Sales and Marketing
Department? Be sure to assign an appropriate column name to the
computed results.
See the file: DBC-e08-WP-AW-03.accdb.
/***** Question J - SQLQuery-AWE-3-1-J ************************/
SELECT SUM(MaxHours)
K. What is the average MaxHours of projects being run by the Sales and
Marketing Department? Be sure to assign an appropriate column name to the
computed results.
See the file: DBC-e08-WP-AW-03.accdb.
/***** Question K - SQLQuery-AWE-3-1-K ************************/
SELECT AVG(MaxHours)
L. How many projects are being run by each department? Be sure to display
each DepartmentName and to assign an appropriate column name to the
computed results.
All Sections – The Access Workbench
AW.3.2. Using Access QBE, create and run new queries to answer the questions in exercise
AW.3.1. Save each query using the query name format QBEQuery-AWE-3-1-##
where the ## sign is replaced by the letter designator of the question. For example,
the first query will be saved as QBEQuery-AWE-3-1-A.
A. What projects are in the PROJECT table? Show all information for each
project.
See the file: DBC-e08-WP-AW-03.accdb.
B. What are the ProjectID, ProjectName, StartDate, and EndDate values of
projects in the PROJECT table?
See the file: DBC-e08-WP-AW-03.accdb.
All Sections – The Access Workbench
C. What projects in the PROJECT table started before August 1, 2017? Show all
the information for each project.
See the file: DBC-e08-WP-AW-03.accdb.
D. What projects in the PROJECT table have not been completed? Show all the
information for each project.
All Sections – The Access Workbench
E. Who are the employees assigned to each project? Show ProjectID,
EmployeeNumber, LastName, FirstName, and OfficePhone.
F. Who are the employees assigned to each project? Show ProjectID,
ProjectName, and Department. Show the EmployeeNumber, LastName,
FirstName, and OfficePhone.
All Sections – The Access Workbench
G. Who are the employees assigned to each project? Show ProjectID,
ProjectName, Department, and DepartmentPhone. Show the
EmployeeNumber, LastName, FirstName, and OfficePhone. Sort by
ProjectID, in ascending order.
H. Who are the employees assigned to projects run by the Marketing
Department? Show ProjectID, ProjectName, Department, and Department
Phone. Show the EmployeeNumber, LastName, FirstName, and Employee
Phone. Sort by ProjectID, in ascending order.
All Sections – The Access Workbench
I. How many projects are being run by the Sales and Marketing Department?
Be sure to assign an appropriate column name to the computed results.
See the file: DBC-e08-WP-AW-03.accdb.
J. What is the total MaxHours of projects being run by the Sales and Marketing
Department? Be sure to assign an appropriate column name to the computed
results.
See the file: DBC-e08-WP-AW-03.accdb.
All Sections – The Access Workbench
K. What is the average MaxHours of projects being run by the Sales and
Marketing Department? Be sure to assign an appropriate column name to the
computed results.
L. How many projects are being run by each department? Be sure to display
each DepartmentName and to assign an appropriate column name to the
computed results.
See the file: DBC-e08-WP-AW-03.accdb.
All Sections – The Access Workbench
AW.3.3. WP has decided to keep track of computers used by the employees. In order to do
this, two new tables will be added to the database. The schema for these tables, as
related to the existing EMPLOYEE table, is:
EmployeeNumber is a surrogate key and never changes. Employee records are
never deleted from the database. SerialNumber is not a surrogate key because it is
not generated by the database. However, a computer’s SerialNumber never
changes, and, therefore, there is no need to cascade updates. When a computer is
at its end of life, the record in COMPUTER for that computer and all associated
records in COMPUTER_ASSIGNMENT are deleted from the database.
A. Figure 3-32 shows the column characteristics for the WP COMPUTER table.
Using the column characteristics, use Access SQL to create the COMPUTER
table and its associated constraints in the WP.accdb database. Are there any
table characteristics that cannot be created in SQL? If so, what are they? Use
the Access GUI to finish setting table characteristics, if necessary.
All Sections – The Access Workbench
The required work here is to create the COMPUTER table in the DBMS of choice
using SQL. Although Access SQL has limitations, there will be no SQL limitations in
SQL Server 2016, Oracle Database XE or MySQL 5.7. The following SQL code is for
SQL Server 2016.
CREATE TABLE COMPUTER(
SerialNumber Int NOT NULL,
Make Char(12) NOT NULL,
Note that Microsoft Access will correctly modify SQL Server 2016 data types into the
appropriate Microsoft Access datatypes. In the above SQL code, the following SQL
syntax will not work in Microsoft Access:
Therefore, the SQL statement for Microsoft Access is:
CREATE TABLE COMPUTER(
SerialNumber Int NOT NULL,
Make Char(12) NOT NULL,
All Sections – The Access Workbench
B. The data for the COMPUTER table are in Figure 3-33. Use Access SQL to
enter these data into your COMPUTER table.
INSERT INTO COMPUTER VALUES(
9871234, 'HP', 'ProDesk 600 G1', 'Intel i5-4690', 3.50, '16.0 GBytes', '1.0 TBytes'
All Sections – The Access Workbench
INSERT INTO COMPUTER VALUES(
9871240, 'HP', 'ProDesk 600 G1', 'Intel i5-4690', 3.50, '16.0 GBytes', '1.0 TBytes'
);
INSERT INTO COMPUTER VALUES(
9871241, 'HP', 'ProDesk 600 G1', 'Intel i5-4690', 3.50, '16.0 GBytes', '1.0 TBytes'
);
INSERT INTO COMPUTER VALUES(
6541006, 'Dell', 'OptiPlex 7040', 'Intel i7-6700', 3.40, '32.0 GBytes', '2.0 TBytes'
);
INSERT INTO COMPUTER VALUES(
6541007, 'Dell', 'OptiPlex 7040', 'Intel i7-6700', 3.40, '32.0 GBytes', '2.0 TBytes'
);
INSERT INTO COMPUTER VALUES(
6541008, 'Dell', 'OptiPlex 7040', 'Intel i7-6700', 3.40, '32.0 GBytes', '2.0 TBytes'
The result of this query is shown on the next page
All Sections – The Access Workbench
C. Figure 3-25 shows the column characteristics for the WP
COMPUTER_ASSIGNMENT table. Using the column characteristics, use
Access SQL to create the COMPUTER_ASSIGNMENT table and the
associated constraints in the WP.accdb database. Are there any table or
relationship settings or characteristics that cannot be created in SQL? If so,
what are they? Use the Access GUI to finish setting table characteristics and
relationship settings, if necessary.
The required work here is to create the COMPUTER_ASSIGNMENT table in the
