Chapter Two – Introduction to Structured Query Language
Page 2-61
A. What projects are in the PROJECT table? Show all information for each project.
/***** Question A – SQL-Query-02-A ************************/
SELECT * FROM PROJECT;
B. What are the ProjectID, ProjectName, StartDate, and EndDate values of projects in
the PROJECT table?
/***** Question B – SQL-Query-02-B ************************/
SELECT ProjectID, ProjectName, StartDate, EndDate
FROM PROJECT;
C. What projects in the PROJECT table started before August 1, 2018? Show all the
information for each project.
/***** Question C – SQL-Query-02-C ************************/
SELECT *
FROM PROJECT
Chapter Two – Introduction to Structured Query Language
Page 2-62
WHERE StartDate < #01-AUG-18#;
D. What projects in the PROJECT table have not been completed? Show all the
information for each project.
/***** Question D – SQL-Query-02-D ************************/
E. Who are the employees assigned to each project? Show ProjectID, Employee-
Number, LastName, FirstName, and OfficePhone.
/***** Question E – SQL-Query-02-E ************************/
Chapter Two – Introduction to Structured Query Language
Page 2-63
F. Who are the employees assigned to each project? Show ProjectID, ProjectName,
and Department. Show EmployeeNumber, LastName, FirstName, and OfficePhone.
Note the use of the aliases ProjectDepartment, and EmployeePhone)
Chapter Two – Introduction to Structured Query Language
Page 2-64
/***** Question F – SQL-Query-02-F ************************/
G. Who are the employees assigned to each project? Show ProjectID, ProjectName,
Department, and DepartmentPhone. Show EmployeeNumber, LastName,
FirstName, and OfficePhone. Sort by ProjectID in ascending order.
Note the use of the aliases ProjectDept, and EmpPhone.
Chapter Two – Introduction to Structured Query Language
Page 2-65
/***** Question G – SQL-Query-02-G ************************/
SELECT P.ProjectID, ProjectName,
D.DepartmentName AS ProjectDept,
H. Who are the employees assigned to projects run by the marketing department?
Show ProjectID, ProjectName, Department, and DepartmentPhone. Show
EmployeeNumber, LastName, FirstName, and OfficePhone. Sort by ProjectID in
ascending order.
Note the use of the aliases ProjectDepartment and EmployeePhone.
/***** Question H – SQL-Query-02-H ************************/
Chapter Two – Introduction to Structured Query Language
Page 2-66
I. How many projects are being run by the Marketing department? Be sure to assign an
appropriate column name to the computed results.
Note the use of the alias NumberOfMarketingProjects.
/***** Question I – SQL-Query-02-I ************************/
J. What is the total MaxHours of projects being run by the Marketing department? Be
sure to assign an appropriate column name to the computed results.
Note the use of the alias TotalMaxHoursForMarketingProjects.
/***** Question J – SQL-Query-02-J ************************/
Chapter Two – Introduction to Structured Query Language
Page 2-67
K. What is the average MaxHours of projects being run by the Marketing department?
Be sure to assign an appropriate column name to the computed results.
Note the use of the alias AverageMaxHoursForMarketingProjects.
/***** Question K – SQL-Query-02-K ************************/
SELECT AVG(MaxHours) AS AverageMaxHoursForMarketingProjects
FROM PROJECT
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.
Note the use of the alias NumberOfDepartmentProjects.
/***** Question L – SQL-Query-02-L ************************/
M. Who supervises each employee at Wedgewood Pacific? Include the names of
employees with no supervisor in the results of the query.
SELECT SUB.EmployeeNumber AS SubNumber, SUB.LastName AS SubLastName,
Chapter Two – Introduction to Structured Query Language
Page 2-68
SUPER.EmployeeNumber AS SuperNumber, SUPER.LastName AS SuperLastName
FROM EMPLOYEE AS SUB LEFT OUTER JOIN EMPLOYEE AS SUPER
ON SUB.Supervisor = SUPER.EmployeeNumber;
N. Write an SQL statement to join EMPLOYEE, ASSIGNMENT, and PROJECT using
the JOIN ON syntax. Run this statement.
SELECT E.*, A.*, P.*
FROM (EMPLOYEE AS E INNER JOIN ASSIGNMENT AS A
Chapter Two – Introduction to Structured Query Language
Page 2-69
O. 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.
Chapter Two – Introduction to Structured Query Language
Page 2-70
2.71 Using Microsoft Access QBE, create and run new queries to answer the questions in
exercise 2.70. Save each query using the query name format QBE-Query-02-##, where
the ## sign is replaced by the letter designator of the question. For example, the first
query will be saved as QBE-Query-02-A. HINT: In questions G and H, the default
approach of accepting all joins will not work, and you may have to delete some joins
from the initial QBE query.
Chapter Two – Introduction to Structured Query Language
Page 2-71
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?
Chapter Two – Introduction to Structured Query Language
Page 2-72
C. What projects in the PROJECT table started before August 1, 2018? 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.
E. Who are the employees assigned to each project? Show ProjectID, Employee-
Number, LastName, FirstName, and OfficePhone.
Chapter Two – Introduction to Structured Query Language
Page 2-73
F. Who are the employees assigned to each project? Show ProjectID, ProjectName,
and Department. Show EmployeeNumber, LastName, FirstName, and OfficePhone.
G. Who are the employees assigned to each project? Show ProjectID, ProjectName,
Department, and Department Phone. Show EmployeeNumber, LastName,
FirstName, and OfficePhone. Sort by ProjectID in ascending order.
This question is more complicated than it seems, in that the default approach of “accepting”
Chapter Two – Introduction to Structured Query Language
Page 2-74
H. Who are the employees assigned to projects run by the marketing department?
Show ProjectID, ProjectName, Department, and DepartmentPhone. Show
EmployeeNumber, LastName, FirstName, and OfficePhone. Sort by ProjectID in
ascending order.
This question is identical to question G except for the restriction to marketing department
projects. And, again, this question is more complicated than it seems, in that the default
approach of “accepting” all the joins in the QBE query yields an incorrect result. Without
Chapter Two – Introduction to Structured Query Language
Page 2-75
I. How many projects are being run by the Marketing department? Be sure to assign an
appropriate column name to the computed results.
J. What is the total MaxHours of projects being run by the Marketing department? Be
sure to assign an appropriate column name to the computed results.
Chapter Two – Introduction to Structured Query Language
Page 2-76
K. What is the average MaxHours of projects being run by the Marketing department?
Be sure to assign an appropriate column name to the computed results.
Chapter Two – Introduction to Structured Query Language
Page 2-77
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.
M. Who supervises each employee at Wedgwood Pacific? Include the names of
employees with no supervisor in the results of the query.
N. Write an SQL statement to join EMPLOYEE, ASSIGNMENT, and PROJECT using
the JOIN ON syntax. Run this statement.
Chapter Two – Introduction to Structured Query Language
Page 2-78
O. 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.
Chapter Two – Introduction to Structured Query Language
Page 2-79
❖ MARCIA’S DRY CLEANING CASE QUESTIONS
Marcia Wilson owns and operates Marcia’s Dry Cleaning, which is an upscale dry cleaner in a
well-to-do suburban neighborhood. Marcia makes her business stand out from the competition
by providing superior customer service. She wants to keep track of each of her customers and
their orders. Ultimately, she wants to notify them that their clothes are ready via e-mail. To
provide this service, she has developed an initial database with several tables. Three of those
tables are as follows:
In this database schema, the primary keys are underlined and the foreign keys are shown in
italics. Note that CUSTOMER contains a recursive relationship between ReferredBy and
CustomerID, where ReferredBy contains the CustomerID value of the existing customer who
referred the new customer to Marcia’s Dry Cleaning. The database that Marcia has created is
named MDC, and the three tables in the MDC database schema are shown in Figure 2-47.
FIGURE 2-47 – The MDC Database
The column characteristics for the tables are shown in Figures 2-48, 2-49, and 2-50. The
relationship between CUSTOMER and INVOICE should enforce referential integrity, but not
cascade updates or deletions, while the relationship between INVOICE and INVOICE_ITEM
should enforce referential integrity and cascade both updates and deletions. The data for these
tables are shown in Figures 2-51, 2-52, and 2-53.
Chapter Two – Introduction to Structured Query Language
Page 2-80
You will need to create and setup a database named MDC_CH02 for use with these case
questions. A Microsoft Access 2016 database named MDC_CH02.accdb, and SQL scripts for