1 Chapter 6

Chapter 6 Advanced S QL

Chapter Overview

Chapter 6 is based on the foundation formed in Chapter 5, moving from single-table

queries to multi-table joins, subqueries (both non-correlated and correlated),
establishing referential integrity, and derived tables. Views, triggers, stored procedures,
functions, embedded S QL, dynamic S QL, and Persistent Stored Modules are also
covered. This chapter also contains a detailed discussion of transaction integrity as
well as the S QL:2011 enhancements and extensions to S Q L and an overview of data
dictionaries. Chapter 5 is a prerequisite for this chapter.

Chapter Objectives

Specific student learning objectives are included at the beginning of the chapter. From
an instructor’s point of view, the objectives of this chapter are to:

1. Build the student’s S Q L skills and an appreciation of S Q L through many examples

of relational queries from S QL; demonstrate capabilities such as multiple-table data
retrieval (join and other operators such as difference, union, and intersection),
explicit and implicit joining, and built-in functions.

2. Illustrate the differences between the joining and subquery approaches to

manipulating multiple tables in S Q L.

3. Help the students understand the possibilities views (both dynamic and
materialized) offer in database management.

4. Discuss the S QL:2008, S Q L:2011, and S Q L:2016 enhancements to S Q L.

5. Briefly discuss the data dictionary facilities available in Oracle 12c.

6. Discuss triggers and stored procedures and provide examples of how these might
be used.

7. Understand the use of user-defined data types in large database installations.

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

2 Modern Database Management, Thirteenth Edition

Key Terms

Base table Join Procedure

Correlated subquery Materialized view Trigger
Dynamic view Natural join User-defined data type (U DT)

Equi-join Outer join Virtual table

Function Persistent Stored Modules
(SQ L/PSM)

Classroom Ideas

1. Have students program in a system that supports S QL along with this chapter. The
nuances of joining multiple tables, nesting subqueries, properly qualifying built-in
functions, and so forth are really only learned by writing a wide variety of non-trivial
queries. There are exercises at the end of the chapter that will provide such
practice for students.

2. If students have access to Oracle, have them take a look at the various data
dictionary views available to them as a user. You may also want to discuss the
various DBA views available and show these to the students during your lecture.
Remember that Teradata University for Academics, in collaboration with University
of Arkansas Walton College Enterprise Systems, provides access to Teradata S QL
for classroom use, and that you may set up access for yourself and your students.
The databases from the text are available, as are much larger data sets that you
may want to use. Teradata University’s home page is
https://www.teradata.com/University/Academics.

3. When discussing multiple table queries, always emphasize that there is more than
one way to write a query. For example, show the students a query using a join and
then the same query using subqueries.

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

3 Chapter 6

4. Emphasize the cases when a subquery is needed and cannot be substituted with a
join. A good example of this would be the case where one needs to find all
customers who have never purchased a product (using a subquery with the NOT IN
qualifier).

5. The capabilities and benefits of views are not as obvious to students as you might
expect. First, many relational systems place limitations on operations that may be
performed on views (for example, no or restricted modification). Second, views are
not just the relational term for temporary table or for subschemas (from the network
model); rather, views provide a capability for simpler query writing for casual users.
Finally, views may have negative performance implications compared to temporary,
intermediate tables explicitly created by a prewritten procedure. These points
should be emphasized in lectures or assignments. Consider having students create
both a view and a temporary table (or materialized view) in S Q L. In most S Q L
systems, a temporary table can be created by the use of a SAVE or KEEP clause at
the end of a query, or one can use a CREATE TABLE command that has a query
(rather than column definitions) as its object. Then, the same query can be run and
timed against both the view and temporary tables. As another alternative, have
students derive the view table as part of the FROM clause of a query. One way to
estimate the timing of each approach, given small data sets, is to use the EXPLAIN
command (or similar command with your R DBMS) on each version of the query to
show students how the query would be processed using each approach.

6. Develop an exercise for the students to explore the effects of a trigger. Have them
create and populate some tables, then write an insert trigger for one of the tables
that might impact other tables. You could then have the students insert some
records and see the results. It is important to show the effects of triggers through
examples that the students can try out, followed by problems that they would have
to solve by writing triggers. The same can be said for stored procedures.

7. The discussion on S QL/PSM might be a good place to introduce P L/SQ L before

introducing triggers and stored procedures.

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

4 Modern Database Management, Thirteenth Edition

Answers to Review Questions

6-1. Define each of the following key terms:

a. Dynamic view. A dynamic view is a table-like structure that is not stored

physically on a storage medium but is recreated based on existing base tables
and a stored specification at the time of use.

b. Correlated subquery. This type of subquery is processed outside in, rather than
inside out. That is, the inner query is executed for each row in the outer query,
and the inner query depends in some way on values from the current row in the
outer query. The results of the inner query will, in turn, affect the final results of
the outer query.

c. Materialized view. Materialized view is, in essence, a copy of data in base tables
physically stored based on a view definition.

d. Base table. A physically stored table that includes raw data.

e. Join. The most frequently used relational operation, which brings together data
from two or more related tables into one result table

f. Equi-join. A join in which the joining condition is based on equality between

values in the common columns. It produces a table of rows composed of
columns from two other tables, where common columns appear (redundantly) in
the result table.

g. Self join. A join that requires matching rows in a table with other rows in the
same table. This is a join that joins a table with itself and often occurs with the
presence of a unary relationship in the database, such as a Supervisor or
Manager of Employees within an Employee table.

h. Outer join. A join in which rows that do not have matching values in common
columns are nevertheless included in the result table. Outer joins return all the
values in one of the tables included in the join, regardless of whether a match
exists in the other table(s) or not.

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

5 Chapter 6

i. Virtualized table (or virtual table). A table that does not exist as a permanent
structure on a storage medium but that the DBMS creates as needed.

6-2. Match the following terms to the appropriate definitions:

b equi-join

d derived table

f natural join

c correlated subquery

a outer join

e trigger

6-3. Using an outer join instead of a natural join with examples:

Outer joins are often used in database maintenance to find rows that do not
have matching values in common columns. Null values appear in columns
where there is no match between tables. Another example would be a query
that returns all customers—whether they have placed orders in the last four
months or not—along with the date of the most recent order placed within the
last four months. Customers who have not placed an order would be returned
with a null value under most recent order.

For detailed query examples, see, for example, p. 256.

6-4. Explain the processing order of a correlated subquery:

Correlated subqueries use the result of the outer query to determine the
processing of the inner query. Thus, the inner query varies for each row
referenced in the outer query.

6-5. Purposes for correlated subqueries

A typical use of a correlated subquery is related to comparing an attribute value

an instance has (such as, for example, Age) to a benchmark value that is

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

6 Modern Database Management, Thirteenth Edition

specific to a group to which the instance belongs (such as the Average Age
within a team).

6-6. Relationship between EXISTS and correlated subqueries

Subqueries written using the EXISTS keyword are, in practice, correlated

subqueries: they also include a reference to the outer query in the inner query
and the value the inner query produces depends on the specific row of the outer
query being processed.

6-7. Explain that any query that can be written using the subquery approach can also
be written using the joining approach, but not vice versa:

While Oracle allows a subquery to return more than one column, most systems
allow pairwise joining of one and only one column in an inner query with one
column in an outer query. (An exception to this is when a subquery is used with
the EXISTS keyword.) You can display data only from the table(s) referenced in
the outer query. If you want to include data from the subquery in the result, then
you would have to use the join technique because data from the subquery
cannot be included in the results. The joining technique is useful when data from
several relations are to be retrieved and displayed, and the relationships are not
necessarily nested.

6-8. Conditions for using UNION:

Use UNION when you want to combine the output from multiple queries
together. However, each query involved must output the same number of
columns, and the columns must have the same data types.

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

7 Chapter 6

6-9. Purposes for creating views:

Views are used to create an additional layer of separation between data stored
on a physical storage medium and the structural mechanism that provides
access to the data. Views allow D BAs to give users and developers a
perspective to the data that fits their specific needs and does not allow them to
see data elements beyond those that they need for their specific task. Providing
access to the database through views tailored to the needs of a specific user
group reduces the risk of errors, security breaches, and violations of privacy.
The additional virtual layer also reduces the impact of possible changes to the
base tables—in such situations, it might be possible to avoid any externally
visible changes by simply changing the way a view is specified in relation to the
base tables.

6-10. Limiting updates when using a view:

When a view is used to update data in the base tables, it is essential to make
sure that the view does not create ambiguity in terms of the elements that the
update addresses. In practice, only views created with straightforward single
table, non-aggregate queries can be used for updates.

6-11. Using a view to save reprogramming effort:

As discussed above in the context of Review Question 6-9, in the context of a

change to the base table(s), a view can still be maintained intact by changing
the specification of the view to be aligned with the new base table structure. In
essence, the view would serve in these cases as a conversion mechanism
between the user’s perspective of the database (which will be kept intact) and
the modified base table structure.

6-12. Purpose of the WITH CHECK OPTION in CREATE VIEW:

The WITH CHECK OPTION will create a condition the violations of which will
prevent a view from being updated.

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

8 Modern Database Management, Thirteenth Edition

For example, the following specification of ExpensiveStuff_V would prevent any

updates to ExpensiveStuff_V that would change the ProductStandardPrice so
that the specific row would not anymore be part of the view (i.e., have price
higher than $300).

CREATE VIEW ExpensiveStuff_V AS

SELECT ProductID,
ProductDescription,
ProductStandardPrice
FROM Product_T
WHERE ProductStandardPrice > 300
WITH CHECK OPTION;

6-13. Using derived tables:

Derived tables are used to create a temporary table that is treated as if it were
an actual table. This table is not persistent in that it goes away after the query in
which it was created is run.

6-14. Example of derived table usage:

One example of the use of a derived table would be to find all ships that were
loaded beyond capacity. In this example, a shipment’s weight is calculated by
computing the sum of the quantity order times the weight. The query follows:

SELECT Ship.ShipNo
FROM Ship, Shipment,
(SELECT ShipmentLine.ShipmentID,
SUM(Item.Weight*ShipmentLine.Quantity) AS Tweight
FROM ShipmentLine,Item
WHERE ShipmentLine.ItemNo = Item.ItemNo
GROUP BY ShipmentID) AS ShipWt
WHERE Ship.ShipNo = Shipment.ShipNo AND
Shipment.ShipmentID = ShipWt.ShipmentID AND Ship.Capacity <
ShipWt.Tweight;

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

9 Chapter 6

6-15. Data type incompatibility and UNION operation:

The simplest way to address this problem is to convert one of the data types.
For example, if one data type is a character and the other numeric, you could
use a function such as Oracle’s TO_CHAR to convert the numeric to a
character.

6-16. Using outer join with more than two tables:

The outer join is not easily implemented for more than two tables. The results
vary by R DBMS vendor and should be thoroughly tested before implementing.

6-17. Hidden triggers:

They are hard to see coming until they fail to fire. They may fire without
notification. Cascading triggers and endless loop triggers are also possible.

6-18. Triggers v s stored procedures:

ersu

Both triggers and stored procedures consist of blocks of procedural code.

Stored procedures are stored blocks of code that must be directly called upon to
operate against the data in the database. Triggers, in contrast, are stored in the
database and run automatically whenever a specified database event occurs
(e.g., INSERT, UPDATE, DELETE or ALTER TABLE commands).

6-19. SQ L/PSM (Persistent Stored Modules):

The purpose of Persistent Stored Modules (S QL/PSM) is to make S QL

computationally complete with structures such as IF-THEN, FOR, and WHILE,
essentially making available sequence, conditional structures, and iteration
(loops).

6-20. Advantages of S QL-invoked routines

Flexibility: Routines can be used for more purposes than constraints or triggers

Efficiency: Routines can be optimized to run more efficiently than generic S QL

statements

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

10 Modern Database Management, Thirteenth Edition

Shareability: Routines can be cached and shared between users.

Applicability: Routines may apply to the entire database and not just one
application.

6-21. Purpose of temporal extensions in S Q L:2011:

The primary purpose of the temporal extensions is to allow the creation of

relational tables with period definitions, which, in turn, allow a row to specify the
time period during which the rest of the attributes are valid. Once this is done,
time-related predicates can be used in query operations to allow for sophisticate
queries that can take time into account much better than without the temporal
extensions.

6-22. Key new features of S Q L introduced in S Q L:2016:

The key new features of S Q L:2016 are as follows: 1) more effective processing
of time-series data with Row Pattern Recognition; 2) trigonometric and logarithm
functions; 3) Java Script Object Notation support; and 4) Polymorphic Table
Functions for creating sophisticated custom functions.

6-23. Mechanisms for dealing with temporal issues before the extensions were
introduced:

Even before the temporal extensions, it was not atypical to see specifications of
valid time frames included in table structures. For example, there might have
been a separate PRODUCTPRICE table that included the following attributes:

PRODUCTPRICE(ProductID, PriceValidStartDate, PriceValidEndDate,

ProductPrice)

With this type of table structure, it was possible to emulate a mechanism for
dealing with time-dependent values, but it was much more difficult to use this
structure without the language components specifically designed for time.

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

11 Chapter 6

6-24. Data dictionary views for non-Oracle R DBMS:

This is left as an exercise for students. The textbook shows examples of

different system table names between Oracle and Microsoft S QL Server, which
would be one difference in the data dictionary facilities between vendors. Other
differences that might be apparent if students have hands-on access to both
vendor DBMS could be what system tables the students/users have access to
based on the database authorization and security setup.

Answers to Problems and Exercises

Note to Instructor: The solutions, which include S QL statements, are not

intended as the definitive answer to the questions; instead, each one of them is
an example of one possible correct solution. Instructors and students will
approach the problems using different S QL capabilities, achieving results that
are also correct. We illustrate the S QL statements with capitalized S QL Reserve
Words, and upper/lower case usage for data names, to be consistent with the
textbook treatment of naming conventions. Oracle results, when shown, will
display table and column names with all Upper Case letters.

Problems 6-25—6-30 are based on class scheduling relations in Figure 6-15.

6-25. Instructors qualified to teach I SM 3113:

SELECT FacultyID, FacultyName

FROM Faculty
WHERE Faculty.FacultyID = Qualified.FacultyID AND
Qualified.CourseID = 'ISM 3113';
6-26.

a. Display the course I D and course name for all courses with an ISM prefix:

Query:

SELECT CourseID, CourseName

FROM Course

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

12 Modern Database Management, Thirteenth Edition

WHERE CourseID LIKE 'ISM%';

b. Display the numbers and names of all courses for which Professor Berndt has
been qualified:

Query:
SELECT Course.CourseID,
Course.CourseName
FROM Faculty,
Course,
Qualified
WHERE Faculty.FacultyName = 'Berndt' AND

Faculty.FacultyID= Qualified.FacultyID AND

Course.CourseID=Qualified.CourseID;

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

13 Chapter 6

c. Display the class roster, including student name, for all students enrolled in
section 2714 of I SM 4212:

Query:
SELECT Student.StudentID,
Student.StudentName,
Section.CourseID,
Registration.SectionNo
FROM Student,
Registration,
Section
WHERE Section.SectionNo= Registration.SectionNo AND
Student.StudentID= Registration.StudentID AND
Registration.SectionNo=2714
ORDER BY StudentName;

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

14 Modern Database Management, Thirteenth Edition

6-27. Is any instructor qualified to teach I S M 3113 and not qualified to teach I S M
4930?

Query:

SELECT Faculty.FacultyID,
Faculty.FacultyName
FROM Faculty,
Qualified
WHERE Qualified.FacultyID=Faculty.FacultyID AND
Qualified.CourseID='ISM 3113'
MINUS
SELECT Faculty.FacultyID, Faculty.FacultyName
FROM Faculty, Qualified
WHERE Qualified.FacultyID=Faculty.FacultyID

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

15 Chapter 6

AND Qualified.CourseID='ISM 4930'

6-28. SQ L queries:

a. How many students were enrolled in section 2714 during semester I-2021?

SELECT COUNT(StudentID)
FROM Section
WHERE SectionNo = 2714;

b. How many students were enrolled in I SM 3113 during semester I-2021?

SELECT COUNT(StudentID)
FROM Section
WHERE CourseID = 'ISM 3113';

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

16 Modern Database Management, Thirteenth Edition

6-29. SQ L queries:

a. What are the names of the course(s) that student Altvater took during the
semester I-2021?

SELECT Course.CourseName
FROM Course, Section,Registration, Student
WHERE Course.CourseID = Section.CourseID AND
Section.SectionNo = Registration.SectionNo AND
Registration.StudentID = Student.StudentID AND
StudentName = 'Altvater' AND
Section.Semester = 'I-2021';

There are no courses that satisfy this condition. Using section 'II-2021' will
produce results.

b. List names of the students who have taken at least one course that Professor
Collins is qualified to teach.

SELECT Student.StudentID, Student.StudentName

FROM Registration, Section, Qualified, Faculty, Student
WHERE Student.StudentID = Registration.StudentID AND
Registration.SectionNo = Section.SectionNo AND
Section.CourseID = Qualified.CourseID AND
Qualified.FacultyID = Faculty.FacultyID AND
Faculty.FacultyName = 'Collins';

c. List the names of the students who took at least one course with “Syst” in its
name during the semester I-2021.

SELECT DISTINCT StudentName

FROM Student, Registration, Section, Course
WHERE Student.StudentID = Registration.StudentID AND
Registration.SectionNo = Section.SectionNo AND
Section.CourseID = Course.CourseID AND

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

17 Chapter 6

CourseName LIKE '%Syst%';

d. How many students did Professor Collins teach during the semester I-2021?

This query requires the use of the ASSIGNMENT relation mentioned in the
introduction to the section. Let’s assume ASSIGNMENT has two columns:
FacultyID and SectionNo, signifying that a specific faculty member has been
assigned to teach a specific section.

SELECT COUNT(StudentID)
FROMStudent, Registration, Section, Assignment, Faculty
WHERE Student.StudentID = Registration.StudentID AND
Registration.SectionNo = Section.SectionNo AND
Section.SectionNo = Assignment.SectionNo AND
Assignment.FacultyID = Faculty.FacultyID AND
Faculty.FacultyName = 'Collins' AND
Section.Semester = 'I-2021';

e. List the names of the courses that at least two faculty members are qualified to
teach.

SELECT CourseName
FROM Course
WHERE CourseID IN
( SELECT CourseID
FROM Qualified
GROUP BY CourseID
HAVING Count(FacultyID)>=2);

6-30.

a. Which students were not enrolled in any courses during semester I-2021?

Query:

SELECT DISTINCT StudentID, StudentNAME

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

18 Modern Database Management, Thirteenth Edition

FROM Student
WHERE NOT EXISTS
( SELECT *
FROM Registration, Section
WHERE Student.StudentID = Registration.StudentID AND
Registration.SectionNo = Section.SectionNo AND Semester=
'I-2021');
b. Which faculty members are not qualified to teach any courses?

SELECT FacultyName
FROM Faculty
WHERE FacultyID NOT IN
(SELECT FacultyID
FROM Qualified);

With the book data set, the query does not produce any results. You can test
this very easily by adding another instructor into the data set.

Problems and Exercises 6-31 – 6-41

Note to Instructor: Problems and Exercises 6-31–6-44 are based on Figure 6-

16 and continue a problem set from Chapter 5 (problems 5-46 to 5-56, based on
Figure 5-12). Please note that the Chapter 6 problem set alters the design of the
database from the earlier design shown in Chapter 5. It may be useful for
students to build this small database in a particular D BMS environment and
populate the tables with sample data as they work on the requested queries.
The SQ L illustrated in this sample solution is based on M S Access S QL.

6-31. List primary and foreign keys for all entities in Figure 6-16:

Tutor

Primary key: TutorID

Foreign key: none

Student

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

19 Chapter 6

Primary key: StudentID

Foreign key: none

MatchHistory

Primary key: MatchID

Foreign keys: TutorID references Tutor(TutorID)
StudentID references Student(StudentID)

TutorReport

Primary key: MatchID + Month (composite PK)

Foreign key: MatchID references MatchHistory(MatchID)

6-32. Write SQL to add MathScore to Student table:

Query:
ALTER TABLE Student
ADD COLUMN MathScore NUMBER(2,1);

6-33. Write SQL to add Subject to Tutor table:

Query:
ALTER TABLE Tutor
ADD COLUMN Subject VARCHAR(7)
CHECK (Subject IN ('Reading', 'Math', 'ESL'));

6-34. Suggested solution for tutors who tutor in more than one subject area:

One approach is to adjust the database design to allow tutors the ability to
declare more than one subject preference for tutoring. Based on the prior
specifications (including Problem and Exercise 6-33), the business rule
appeared to be that tutors only tutored in one subject, thus the subject could be
stored with tutor information. Under this new requirement, the database needs
the ability to track more than one subject per tutor. This can be accomplished by
adding two tables to the current design (TutorSubject, Subject) and by altering

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

20 Modern Database Management, Thirteenth Edition

the structure of the MatchHistory table to track the Subject involved in each
match of Tutor and Student, as noted in the revised E RD.

It is also possible to assume that (1) each tutor is certified only once or (2) that a
tutor must be certified in each of the subjects. A second assumption would
require that CertDate be moved to the TutorSubject relation. This is a good
opportunity to show how making an assumption without interviewing the
database user may result in an incorrect data model because the correct
business rule has not been uncovered.

After completing Exercise 6-33 (where subject values of “Reading, Math, and E
SL” were added to teachers’ certifications), some students may ask if it is
important for the database to track what Subject each Student is studying. This
inquiry can be used in class as an example of how an analyst can uncover
additional business rules that have not been initially elicited from the client in
interviews. You may expand this question to track student assessment scores
for math and E SL in addition to the existing READ score. This should lead to
consideration that, just as tutors may only be certified in one area, students may
elect to study in one to three areas, depending on their needs. The data model
would need to be expanded further to handle this.

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

21 Chapter 6

6-35. Students matched with a tutor with “Temp Stop” status in 2021:

SELECT MatchHistory.StudentID
FROM MatchHistory, Tutor
WHERE MatchHistory.TutorID = Tutor.TutorID AND
Tutor.Status = ‘Temp Stop’ AND
MatchHistory.StartDate >= #1/1/2021#)

6-36. Tutors who have not submitted a report in July:

(Answer provided in M S ACCESS S Q L). Students who build a practice

database to answer this problem should use a DATE format and may need to
add a day-of-month value to the data provided. Note that a left outer join is
necessary to pick up tutors who have never submitted a report.

First, a query named CH7P35 returns a list of all reports ever submitted for each
currently active student. The query is sorted by tutor and report dates for that
tutor.

SELECT MatchHistory.MatchID,
MatchHistory.EndDate,
MatchHistory.TutorID,
TutorReport.Month
FROM MatchHistory LEFT JOIN TutorReport ON
MatchHistory.MatchID = TutorReport.MatchID
WHERE (((MatchHistory.EndDate) IS NULL)) OR
(((MatchHistory.EndDate)>#6/30/2021#) AND ((TutorReport.Month) Is Null))
ORDER BY MatchHistory.MatchID, TutorReport.Month;

Next, a query is built to retrieve data from query Ch7P35. This second query
returns just those tutors with active students who have not turned in a July
report.

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

22 Modern Database Management, Thirteenth Edition

Students should be encouraged to modify or add records to the example in the

book in order to test their query thoroughly. As given, only Tutor 104 with a new
student who started in June has not submitted a report for July. In order to test
their query more completely, data should be created for tutors who have active
students and who have previously submitted monthly reports but have not
submitted one in June.

SELECT CH7P35.TutorID, CH7P35.Month

FROM CH7P35
WHERE (((CH7P35.Month) IS NULL)) OR
(((CH7P35.Month)>=#6/30/2021# And
(CH7P35.Month) NOT BETWEEN #7/1/2021# And #7/31/2021#));

Motivated students may add parameters to this query so it will work for any
month.

6-37. Table structure for Student and Tutor data:

Note: This solution assumes the creation of a PERSON table to store the
common contact data for STUDENTs and TUTORs, and the linking of the
STUDENT and TUTOR table to the PERSON table. Individual student answers
may vary from this proposed solution due to this assumption.

CREATE TABLE PERSON (

PersonID VARCHAR(5)
CONSTRAINT PERPERSID_PK PRIMARY KEY,
LastName VARCHAR(15),
FirstName VARCHAR(15),
MiddleInit VARCHAR(1),
PersonStrAddress VARCHAR(20),
PersonCity VARCHAR(20),
PersonState CHAR(2),
PersonZip VARCHAR(10),
PersonPhone VARCHAR(14),

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

23 Chapter 6

PersonEMail VARCHAR(25),
PersonType CHAR(1));
ALTER TABLE Student (
ADD COLUMN PersonID VARCHAR(5));
ALTER TABLE Student (
ADD CONSTRAINT STPERSONID_FK
PersonID REFERENCES Person(PersonID));
ALTER TABLE Tutor (
ADD COLUMN PersonID VARCHAR(5));
ALTER TABLE Tutor (
ADD CONSTRAINT TTUTORID_FK
REFERENCES Person(PersonID));

6-38. Determine the total number of hours and the total number of lessons Tutor 106
taught in June and July 2021:

SELECT SUM(Hours), SUM(Lessons)

FROM MatchHistory, TutorReport
WHERE TutorReport.MatchID = MatchHistory.MatchID AND
TutorID = 106 AND
Month IN ('6/21', '7/21');

6-39. List the Read scores of students who were ever taught by tutors whose status is
Dropped

SELECT StudentID, Read

FROM STUDENT
WHERE StudentID IN
(SELECT StudentID
FROM MatchHistory, Tutor
WHERE Tutor.TutorID = MatchHistory.TutorID AND
Status = 'Dropped’);

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

24 Modern Database Management, Thirteenth Edition

6-40. List all active students in June by name, including number of hours tutored and
number of lessons completed:

Query:
SELECT Student.StudentID,
MatchHistory.EndDate,
Person.LastName,
SUM(TutorReport.Hours) AS TotalHours,
SUM(TutorReport.Lessons) AS TotalLessons
FROM (Person INNER JOIN Student ON Person.PersonID =
Student.StudentID) INNER JOIN (MatchHistory LEFT JOIN
TutorReport ON MatchHistory.MatchID =
TutorReport.MatchID) ON Student.StudentID =
MatchHistory.StudentID
GROUP BY Student.StudentID, MatchHistory.EndDate,
Person.LastName
HAVING (((MatchHistory.EndDate) Is Null));

6-41. Tutors matched with a student group:

SELECT Student.Group, COUNT(TutorID)

FROM Student, MatchHistory
WHERE Student.StudentID = MatchHistory.StudentID
GROUP BY Student.Group;

6-42. Total number of lessons taught in 2021 by tutors in each of the three Status
categories:

Microsoft Access Query:

SELECT Tutor.Status, SUM(Lessons)

FROM Tutor, MatchHistory, TutorReport
WHERE Tutor.TutorID = MatchHistory.TutorID AND
MatchHistory.MatchID = TutorReport.MatchID AND
DatePart(“yyyy”,MatchHistory.StartDate) = 2021
GROUP BY Tutor.Status;

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

25 Chapter 6

6-43. Which tutors, by name, are available to tutor?

This answer assumes any active tutor may be available to accept a new
student:

Query:
SELECT Person.LastName,
Person.FirstName,
Tutor.Status
FROM Person INNER JOIN Tutor ON
Person.PersonID = Tutor.PersonID
WHERE (((Tutor.Status)= 'Active'));

This answer assumes a tutor is available only if currently unassigned a student:

SELECT T.TutorID,
Person.LastName,
Person.FirstName
FROM Person INNER JOIN Tutor AS T
ON Person.PersonID = T.PersonID
WHERE (((T.TutorID) IN
(SELECT MH.TutorID FROM MatchHistory MH
WHERE EndDate IS NOT NULL) AND
(T.TutorID) NOT IN
(SELECT MH.TutorID FROM MatchHistory MH
WHERE EndDate IS NULL)) AND ((T.STATUS)= 'Active'));

6-44. Which tutor needs to be reminded to turn in reports?

Query:
SELECT MatchHistory.TutorID
FROM MatchHistory
WHERE MatchHistory.MatchID NOT IN
(SELECT DISTINCT TutorReport.MatchID
FROM TutorReport);

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

26 Modern Database Management, Thirteenth Edition

Queries Based on the P VFC Database

Note to Instructor: Problems and Exercises 6-45—6-85 are based on the

extended version of the Pine Valley Furniture Company database (BigP VF C.md
b is the M S Access file version of this database; this extended database version
is available on pearsonhighered.com/hoffer). Please note that this version of the
database has a different structure than that in the textbook version of the
database (e.g., the salesperson information is in the extended version but not in
the textbook version). Some of the field names may also have changed due to
the version of the database you are using due to the reserved words of the D BM
S. When you first use the database, check the table definitions to see what the
exact field names and table structures are for the D BMS that you are using.
Also note that, where possible, solutions are presented in both M S Access and
Oracle SQ L syntax.

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

27 Chapter 6

6-45. Find customers who have not placed any orders:

Microsoft Access Query:

SELECT Customer_T.CustomerID
FROM Customer_T
WHERE Customer_T.CustomerID
NOT IN
(SELECT CustomerID from Order_T);

Oracle Query:

SELECT Customer_T.CustomerID
FROM Customer_T
WHERE Customer_T.CustomerID
NOT IN (SELECT CustomerID from Order_T);

6-46. List product line names and for each product line number of products and
average product price.

Microsoft Access Query:

SELECT ProductLineName,
COUNT(ProductID) AS [NbrOfProducts],
AVG(ProductStandardPrice) AS [Avg Price]
FROM Productline_T, Product_T
WHERE Product_T.ProductLineID = Productline_T.ProductLineID
GROUP BY Productline_T.ProductLineID, ProductLineName;

Oracle Query:

SELECT ProductLineName,
COUNT(ProductID),
AVG(ProductStandardPrice)
FROM Productline_T, Product_T
WHERE Product_T.ProductLineID = Productline_T.ProductLineID
GROUP BY Productline_T.ProductLineID, ProductLineName;

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

28 Modern Database Management, Thirteenth Edition

6-47. Modify previous query to include only those product lines the average price of
which is higher than $200.

Microsoft Access Query:

SELECT ProductLineName,
COUNT(ProductID) AS [NbrOfProducts],
AVG(ProductStandardPrice) AS [Avg Price]
FROM Productline_T, Product_T
WHERE Product_T.ProductLineID = Productline_T.ProductLineID
GROUP BY Productline_T.ProductLineID, ProductLineName
HAVING AVG(ProductStandardPrice)>200;

Oracle Query:

SELECT ProductLineName,
COUNT(ProductID),
AVG(ProductStandardPrice)
FROM Productline_T, Product_T
WHERE Product_T.ProductLineID = Productline_T.ProductLineID
GROUP BY Productline_T.ProductLineID, ProductLineName
HAVING AVG(ProductStandardPrice)>200;

6-48. List the names and number of employees supervised (label this value
HeadCount) for all the supervisors who supervise more than two employees:

Microsoft Access Query:

SELECT S.EmployeeName,
COUNT(E.EmployeeID) AS [HeadCount]
FROM Employee_T S,
Employee_T E
WHERE S.EmployeeID = E.EmployeeSupervisor
GROUP BY S.EmployeeName
HAVING COUNT(E.EmployeeID) > 2;

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

29 Chapter 6

Oracle Query:

SELECT S.EmployeeName,
COUNT(E.EmployeeID) AS HeadCount
FROM Employee_T S,
Employee_T E
WHERE S.EmployeeID = E.EmployeeSupervisor
GROUP BY S.EmployeeName
HAVING COUNT(E.EmployeeID) > 2;

Result:

EmployeeName HeadCount

Robert Lewis 3

You can verify this by running a simpler query that computes the number of
employees each employee supervises. From this query you would see that
there are three supervisors (those who supervise anyone), and the other two
supervisors supervise only one employee each.

6-49. Names of employees, employee birthdate, manager name, manager’s birthdate

for those employees born before their manager was born; label columns per
problem instructions:

Microsoft Access and Oracle Query:

SELECT E1.EmployeeName,
E1.EmployeeBirthdate,
E2.EmployeeName AS Manager,
E2.EmployeeBirthdate AS ManagerBirth
FROM Employee_T E1,
Employee_T E2
WHERE E1.EmployeeSupervisor = E2.EmployeeID
AND E1.EmployeeBirthdate < E2.EmployeeBirthdate;

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

30 Modern Database Management, Thirteenth Edition

P&E 6-49 Instance Diagram (with sample data for illustration purposes only; not
showing values from P VFC database)

6-50. Display order number, customer number, order date and items ordered for some
customer (example shows CustomerID=4, students may have chosen another
valid customer I D number in their solutions):

Microsoft Access Query:

SELECT Order_T.OrderID,
Order_T.CustomerID,
Order_T.OrderDate,
OrderLine_T.ProductID,
Product_T.ProductDescription,
OrderLine_T.OrderedQuantity

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

31 Chapter 6

FROM Product_T INNER JOIN (Order_T INNER JOIN Orderline_T ON

Order_T.OrderID = OrderLine_T.OrderID) ON
Product_T.ProductID =
OrderLine_T.ProductID
WHERE Order_T.CustomerID= 4;

Oracle Query:

SELECT Order_T.OrderID,
Order_T.CustomerID,
Order_T.OrderDate,
OrderLine_T.ProductID,
Product_T.ProductDescription,
OrderLine_T.OrderedQuantity
FROM OrderLine_T,
Order_T,
Product_T
WHERE Order_T.OrderID=OrderLine_T.OrderID AND
OrderLine_T.ProductID=Product_T.ProductID AND
Order_T.CustomerID = 4;

6-51. Display each item ordered for order #1, its standard price, and total price for
each item ordered:

Microsoft Access and Oracle Query:

SELECT OrderLine_T.ProductID,
Product_T.ProductStandardPrice,
OrderLine_T.OrderedQuantity*
Product_T.ProductStandardPrice AS LineTotal
FROM Product_T,OrderLine_T
WHERE Product_T.ProductID = OrderLine_T.ProductID AND
OrderLine_T.OrderID = 1;

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

32 Modern Database Management, Thirteenth Edition

6-52. Total number of employees working at each work center:

Microsoft Access Query:

SELECT WorkCenter_T.WorkcenterID,

COUNT(Employee_T.EmployeeID) AS [Nbr of Employees]

FROM WorkCenter_T, Worksin_T, Employee_T

WHERE WorkCenter_T.WorkcenterID = Worksin_T.WorkcenterID AND

Worksin_T.EmployeeID = Employee_T.EmployeeID

GROUP BY WorkCenter_T.WorkcenterID;

Oracle Query:

SELECT WorkCenter_T.WorkcenterID, COUNT(Employee_T.EmployeeID)

AS "Nbr of Employees"
FROM WorkCenter_T, Worksin_T, Employee_T
WHERE WorkCenter_T.WorkcenterID = Worksin_T.WorkcenterID AND
Worksin_T.EmployeeID = Employee_T.EmployeeID
GROUP BY WorkCenter_T.WorkcenterID;
6-53. List of work centers that employ at least one person who has the skill ‘QC1’:

Microsoft Access and Oracle Query:

SELECT WorkCenter_T.WorkcenterID

FROM WorkCenter_T

WHERE WorkcenterID IN (

SELECT WorkcenterID

FROM Worksin_T, Employee_T, EmployeeSkills_T

WHERE WorksIn_T.EmployeeID = Employee_T.EmployeeID AND

Employee_T.EmployeeID = EmployeeSkills_T.EmployeeID and

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

33 Chapter 6

EmployeeSkills_T.SkillID = 'QC1');

6-54. Total the cost of the order for order #1:

Microsoft Access and Oracle Query:

SELECT SUM(OrderLine_T.OrderedQuantity*
Product_T.ProductStandardPrice)
FROM Product_T,OrderLine_T
WHERE Product_T.ProductID = OrderLine_T.ProductID AND
OrderLine_T.OrderID = 1;
6-55. List for each vendor those materials that the vendor supplies the supply unit
price of which is at least four times the material standard price:

Microsoft Access and Oracle Query:

SELECT Vendor_T.VendorID, MaterialName

FROM Vendor_T, Supplies_T, Rawmaterial_T
WHERE Vendor_T.VendorID = Supplies_T.VendorID AND
Supplies_T.MaterialID = Rawmaterial_T.MaterialID AND
Supplies_T.Supplyunitprice >=
4*Rawmaterial_T.Materialstandardprice;
6-56. Calculate the total raw material cost (label TotCost) for each product compared
to its standard product price and display product ID, product description,
standard price, and the total cost in the result:

Microsoft Access and Oracle Query:

SELECT P.ProductID,
ProductDescription,
P.ProductStandardPrice,
SUM(U.QuantityRequired*R.ProductStandardPrice) AS
TotCost
FROM Product_T P,
Uses_T U,
RawMaterial_T P

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

34 Modern Database Management, Thirteenth Edition

WHERE P.ProductID = U.ProductID AND

U.MaterialID = R.MaterialID
GROUP BY P.ProductID, ProductDescription, P.ProductStandardPrice;

Or here is another interesting approach using a derived table in the SELECT list:

SELECT P.ProductID,
P.ProductDescription,
P.ProductStandardPrice,
TotCost
FROM Product_T P,
(SELECT P1.ProductID,
SUM(P1.ProductStandardPrice*U.QuantityRequired) AS TotCost
FROM Uses_T U, RawMaterial_T R, Product_T P1
WHERE U.MaterialID = R.MaterialID AND
U.ProductID = P1.ProductID
GROUP BY P1.ProductID) Cost_T
WHERE P.ProductID = Cost_T.ProductID;

6-57. For every order that has been received, display the order ID, the total dollar
amount owed on that order (you’ll have to calculate this total from attributes in
one or more tables; label this result Total_Due), and the amount received in
payments on that order (assume there is only one payment made on each
order). To make this query a little simpler, you don’t have to include those
orders for which no payment has yet been received. List the results in
decreasing order by the difference between total due and amount paid:

Query:

Just to help to verify the result, the following shows all 11 rows of the payment
table, and as assumed, there is only one payment per order, but not all orders
have payments. Note: Dates in this database sometimes change between
editions of the associated textbook, so your results may vary in terms of dates:

PayID OrderID DateC TypeC Amount CommentC

2 24 2021-03-10 D 25 Cash

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

35 Chapter 6

PayID OrderID DateC TypeC Amount CommentC

Cashiers
5 32 2021-03-11 D 3000
Check
7 39 2021-03-11 D 600 chk 1003
4 28 2021-03-10 D 25 Cash
1 1 2021-03-01 D 1000 chk101
9 51 2021-03-11 D 150 Cash
11 69 2021-03-11 D 200 chk3001
10 54 2021-03-11 D 2650 Check # 343
3 26 2021-03-10 D 222 Cash
6 34 2021-03-11 D 575 Chk1201
8 48 2021-03-11 D 1000 chk2301

Now the query

In Microsoft Access S QL:

SELECT OrderLine_T.OrderID,
SUM(OrderedQuantity*ProductStandardPrice) AS TotalDue,
PaymentAmount
FROM OrderLine_T, Product_T, Payment_T
WHERE OrderLine_T.ProductID = Product_T.ProductID AND
OrderLine_T.OrderID = Payment_T.OrderID
GROUP BY OrderLine_T.OrderID, PaymentAmount
ORDER BY SUM(OrderedQuantity*ProductStandardPrice) -
PaymentAmount DESC;

In Oracle S QL:

SELECT OrderLine_T.OrderID,
SUM(OrderedQuantity*ProductStandardPrice) AS
TotalDue,

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

36 Modern Database Management, Thirteenth Edition

PaymentAmount
FROM OrderLine_T,
Product_T,
Payment_T
WHERE OrderLine_T.ProductID = Product_T.ProductID AND
OrderLine_T.OrderID = Payment_T.OrderID
GROUP BY OrderLine_T.OrderID, PaymentAmount
ORDER BY TotalDue - PaymentAmount DESC;

6-58. List each customer who has bought computer desks and the number of units
bought by each customer:

The first solution is a better approach to this request as it will retrieve a result set
of computer desks, whether or not the material and description of said desk is
“oak” as is currently shown in the P VFC data set. The second solution is an
alternative approach that relies upon the only computer desk description found
in the current P VF C data set.

Microsoft Access Query:

SELECT Customer_T.CustomerID,
CustomerName,
SUM(OrderedQuantity) as UnitsBought
FROM OrderLine_T,
Order_T,
Product_T,
Customer_T
WHERE ProductDescription LIKE '*Computer Desk*' AND
Order_T.OrderID = OrderLine_T.OrderID AND
Product_T.ProductId = OrderLine_T.ProductID AND
Customer_T.CustomerID = Order_T.CustomerID
GROUP BY Customer_T.CustomerID, CustomerName;

Oracle Query:

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

37 Chapter 6

SELECT Customer_T.CustomerID,
CustomerName,
SUM(OrderedQuantity) as UnitsBought
FROM OrderLine_T,
Order_T,
Product_T,
Customer_T
WHERE ProductDescription LIKE '%Computer Desk%' AND
Order_T.OrderID = OrderLine_T.OrderID AND
Product_T.ProductId = OrderLine_T.ProductID AND
Customer_T.CustomerID = Order_T.CustomerID
GROUP BY Customer_T.CustomerID, CustomerName;

6-59. List each customer who bought at least one product that belongs to product line
Basic in March 2021. List each customer only once.

Microsoft Access Query:

SELECT DISTINCT C.CustomerID, C.CustomerName

FROM Customer_T C,
Order_T O,
OrderLine_T OL,
Product_T P,
ProductLine_T PL
WHERE C.CustomerID = O.CustomerID AND
O.OrderID = OL.OrderID AND
OL.ProductID = P.ProductID AND
P.ProductLineID = PL.ProductLineID AND
PL.ProductLineName = 'Basic' AND
DatePart("m",OrderDate) = 3 AND
DatePart("yyyy", OrderDate) = 2021;

Oracle Query:

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

38 Modern Database Management, Thirteenth Edition

SELECT DISTINCT C.CustomerID, C.CustomerName

FROM Customer_T C,
Order_T O,
OrderLine_T OL,
Product_T P,
ProductLine_T PL
WHERE C.CustomerID = O.CustomerID AND
O.OrderID = OL.OrderID AND
OL.ProductID = P.ProductID AND
P.ProductLineID = PL.ProductLineID AND
PL.ProductLineName = 'Basic' AND
SUBSTR(O.OrderDate,4,6)='MAR-21';

6-60. Modify your answer to Problem and Exercise 6-59 so that you include the
number of products in product line Basic that the customer ordered in March
2021.

Microsoft Access Query:

SELECT DISTINCT C.CustomerID, C.CustomerName,

COUNT(P.ProductID)
FROM Customer_T C,
Order_T O,
OrderLine_T OL,
Product_T P,
ProductLine_T PL
WHERE C.CustomerID = O.CustomerID AND
O.OrderID = OL.OrderID AND
OL.ProductID = P.ProductID AND
P.ProductLineID = PL.ProductLineID AND
PL.ProductLineName = 'Basic' AND
DatePart("m",OrderDate) = 3 and DatePart("yyyy", OrderDate) =
2021
GROUP BY C.CustomerID, C.CustomerName;

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

39 Chapter 6

Oracle Query:

SELECT DISTINCT C.CustomerID,

C.CustomerName,
COUNT(P.ProductID)
FROM Customer_T C,
Order_T O,
OrderLine_T OL,
Product_T P,
ProductLine_T PL
WHERE C.CustomerID = O.CustomerID AND
O.OrderID = OL.OrderID AND
OL.ProductID = P.ProductID AND
P.ProductLineID = PL.ProductLineID AND
PL.ProductLineName = 'Basic' AND
SUBSTR(O.OrderDate,4,6)='MAR-21'
GROUP BY C.CustomerID, C.CustomerName;

6-61. Modify your answer to Problem and Exercise 6-60 so that the list includes the
number of products each customer bought in each product line in March 2021.

Microsoft Access Query:

SELECT DISTINCT C.CustomerID, C.CustomerName,

COUNT(P.ProductID)
FROM Customer_T C,
Order_T O,
OrderLine_T OL,
Product_T P,
ProductLine_T PL
WHERE C.CustomerID = O.CustomerID AND
O.OrderID = OL.OrderID AND
OL.ProductID = P.ProductID AND
P.ProductLineID = PL.ProductLineID AND

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

40 Modern Database Management, Thirteenth Edition

PL.ProductLineName = 'Basic' AND

DatePart("m",OrderDate) = 3 and DatePart("yyyy", OrderDate) =
2021
GROUP BY C.CustomerID,
C.CustomerName,
PL.ProductLineID,
PL.ProductLineName;
Oracle Query:

SELECT C.CustomerID,
C.CustomerName,
PL.ProductLineName,
COUNT(P.ProductID)
FROM Customer_T C,
Order_T O,
OrderLine_T OL,
Product_T P,
ProductLine_T PL
WHERE C.CustomerID = O.CustomerID AND
O.OrderID = OL.OrderID AND
OL.ProductID = P.ProductID AND
P.ProductLineID = PL.ProductLineID AND
SUBSTR(O.OrderDate,4,6)='MAR-21'
GROUP BY C.CustomerID,
C.CustomerName,
PL.ProductLineID,
PL.ProductLineName;

6-62. List in alphabetical order the names of all employees (managers) who are now
managing people with skill I D B S12. List each such manager’s name only once,
even if that manager manages several people with this skill:

Microsoft Access and Oracle Query:

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

41 Chapter 6

SELECT DISTINCT M.EmployeeName

FROM Employee_T M,
Employee_T E,
EmployeeSkills_T ES
WHERE SkillID = 'BS12' AND ES.EmployeeID = E.EmployeeID AND
E.EmployeeSupervisor = M.EmployeeID
ORDER BY 1;

6-63. Display the salesperson name, product finish, and total quantity sold (label as
TotSales) for each finish by each salesperson:

Microsoft Access and Oracle Query:

SELECT DISTINCT SalespersonName,

ProductFinish,
SUM(OrderedQuantity) AS TotSales
FROM Salesperson_T,
OrderLine_T,
Product_T,
Order_T
WHERE Salesperson_T.SalespersonID = Order_T.SalespersonID AND
Order_T.OrderID = OrderLine_T.OrderID AND
OrderLine_T.ProductID = Product_T.ProductID
GROUP BY SalespersonName, ProductFinish;

6-64. Write a query to list the number of products produced in each work center (label
this result ‘TotalProducts’). If a work center does not produce any products,
display the result with a total of 0:

Microsoft Access and Oracle Query:

SELECT WorkCenter_T.WorkCenterID,
COUNT(ProductID) AS TotalProducts
FROM WorkCenter_T LEFT OUTER JOIN ProducedIn_T
ON WorkCenter_T.WorkCenterID=ProducedIn_T.WorkCenterID
GROUP BY WorkCenter_T.WorkCenterID;

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

42 Modern Database Management, Thirteenth Edition

6-65. Develop a list of all the P VFC customers by name with the number of vendors in
the same state as that customer (label this computed result NumVendors):

Microsoft Access and Oracle Query:

SELECT CustomerName, COUNT(VendorID) AS NumVendors

FROM Customer_T C LEFT OUTER JOIN Vendor_T V
ON C.CustomerState = V.VendorState
GROUP BY CustomerName;

6-66. OrderIDs for customers lacking payment:

Microsoft Access Query (Note that Access does not support MINUS):

SELECT Order_T.OrderID
FROM Order_T LEFT OUTER JOIN Payment_T ON
Order_T.OrderID = Payment_T.OrderID
WHERE Payment_T.PaymentID IS NULL;

Oracle Query:

SELECT OrderID
FROM Order_T
MINUS
SELECT OrderID
FROM Payment_T;

6-67. Names of states where customers reside, but have no salesperson residing in
that state:

First, a query solution using MINUS operator (does not work with Microsoft
Access):

SELECT CustomerState
FROM Customer_T
MINUS
SELECT SalesPersonState
FROM Salesperson_T;

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

43 Chapter 6

Next, a solution using OUTER JOIN operation:

SELECT DISTINCT CustomerState

FROM Customer_T
LEFT OUTER JOIN Salesperson_T ON
CustomerState = SalespersonState
WHERE Salesperson_T.SalespersonState IS NULL
ORDER BY Customer_T.CustomerState;

Student answers will vary as to which approach is chosen as the “most natural,”
especially based on their experiences or level of comfort with mathematical set
manipulations.

6-68. Produce a list of all the products (show product description) and the number of
times each product has been ordered:

This query requires an outer join because some products may not have been
ordered. Because many S QL systems do not have an outer join operator, often
this type of query must use the UNION command. The following answer uses
this second approach because it will work with almost any system. Also, note
that the question wants the number of times a product has been ordered, not the
total quantity ordered:

Microsoft Access Query:

SELECT Product_T.ProductID,
ProductDescription,
COUNT(*) as TimesOrdered
FROM Product_T INNER JOIN OrderLine_T ON
Product_T.ProductID = OrderLine_T.ProductID
GROUP BY Product_T.ProductID, ProductDescription
UNION
SELECT ProductID,
ProductDescription,
0

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

44 Modern Database Management, Thirteenth Edition

FROM Product_T
WHERE EXISTS
(SELECT * FROM OrderLine_T
WHERE (OrderLine_T.ProductID =
Product_T.ProductID))=FALSE;
Oracle Query:

SELECT Product_T.ProductID,
ProductDescription,
COUNT(*) as TimesOrdered
FROM Product_T,OrderLine_T
WHERE Product_T.ProductID = OrderLine_T.ProductID
GROUP BY Product_T.ProductID, ProductDescription
UNION
SELECT ProductID,
ProductDescription,
0
FROM Product_T
WHERE NOT EXISTS
(SELECT *
FROM OrderLine_T
WHERE OrderLine_T.ProductID =
Product_T.ProductID);

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

45 Chapter 6

6-69. Display the customer I D, name, and order I D for all customer orders. For those
customers who do not have any orders, include them in the display once:

Query (Does not work with Access):

SELECT c.CustomerID,
CustomerName,
NVL(OrderID,0)
FROM Customer_T c LEFT OUTER JOIN Order_T o ON
c.CustomerID = o.CustomerID;
OR
SELECT CUST.CustomerID,
CustomerName,
OrderID
FROM Customer_T CUST LEFT OUTER JOIN Order_T ORD ON
CUST.CustomerID = ORD.CustomerID
ORDER BY CUST.CustomerID;

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

46 Modern Database Management, Thirteenth Edition

6-70. Display the Employee I D and Employee Name for those employees who do not
possess the skill Router. Display the results in order by EmployeeName:

Microsoft Access and Oracle Query:

SELECT EmployeeID,
EmployeeName
FROM Employee_T
WHERE EmployeeID NOT IN
(SELECT ES.EmployeeID
FROM EmployeeSkills_T ES, Skill_T S
WHERE SkillDescription = 'Router' AND
ES.SkillID = S.SkillID)
ORDER BY EmployeeName;

6-71. Name of customer 16, and other customers in same zip code

Microsoft Access and Oracle Query:

SELECT C1.CustomerName,
C2.CustomerName,
C1.CustomerPostalCode
FROM Customer_T C1, Customer_T C2
WHERE C1.CustomerID = 16 AND
C1.CustomerPostalCode = C2.CustomerPostalCode AND

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

47 Chapter 6

C2.CustomerID <> 16;

6-72. P&E 6-71 re-written for all customers

Microsoft Access and Oracle Query:

SELECT C1.CustomerName,
C2.CustomerName AS CName2,
C1.CustomerPostalCode
FROM Customer_T C1, Customer_T C2
WHERE C1.CustomerPostalCode = C2.CustomerPostalCode AND
C2.CustomerID <> C1.CustomerID;

6-73. Display the customer I D, name, and order I D for all customer orders. For those
customers who do not have any orders, include them in the display once, with a
0 value for OrderID:

Microsoft Access and Oracle Query:

SELECT Customer_T.CustomerID,
CustomerName,
OrderID
FROM Customer_T,
Order_T
WHERE Customer_T.CustomerID = Order_T.CustomerID
UNION
SELECT CustomerID, CustomerName, 0
FROM Customer_T
WHERE NOT EXISTS
(SELECT * FROM Order_T
WHERE Order_T.CustomerID = Customer_T.CustomerID);

Or replace last three lines above with:

WHERE CustomerID NOT IN

(SELECT CustomerID FROM Order_T);

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

48 Modern Database Management, Thirteenth Edition

6-74. Show the customer I D and name for all the customers who have ordered both
products with I Ds 3 and 4 on the same order:

Microsoft Access and Oracle Query:

SELECT C.CustomerID,
CustomerName
FROM Customer_T C,
Order_T O1,
OrderLine_T OL1
WHERE C.CustomerID = O1.CustomerID AND
O1.OrderID = OL1.OrderID AND
OL1.ProductID = 3 AND
O1.OrderID IN
(SELECT OrderID
FROM OrderLine_T OL2
WHERE OL2.ProductID = 4);

Or here is another interesting approach using derived tables (color for some
parentheses helps to show the nesting of SELECTs):

SELECT DISTINCT(CustomerID), CustomerName

FROM Customer_T
WHERE CustomerID IN
(SELECT CustomerID
FROM (SELECT p3.OrderID
FROM (SELECT OrderID
FROM OrderLine_T
WHERE ProductID = 3) p3,
(SELECT OrderID
FROM OrderLine_T
WHERE ProductID = 4) p4
WHERE p3.OrderID = p4.OrderID) Orders, Order_T
WHERE Orders.OrderID = Order_T.OrderID);

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

49 Chapter 6

6-75. Customer names of customers who have ordered (on same or different orders)
both products 3 and 4:

Microsoft Access Query:

SELECT C1.CustomerID, C1.CustomerName FROM

Customer_T C1
WHERE (C1.CustomerID IN
(SELECT C.CustomerID
FROM Customer_T C,Order_T O1, OrderLine_T OL1
WHERE C.CustomerID = O1.CustomerID AND
O1.OrderID = OL1.OrderID AND
OL1.ProductID = 4))
AND
(C1.CustomerID IN
(SELECT C.CustomerID
FROM Customer_T C,Order_T O1, OrderLine_T OL1
WHERE C.CustomerID = O1.CustomerID AND
O1.OrderID = OL1.OrderID AND
OL1.ProductID = 3))

Oracle Query:
SELECT C.CustomerID,
CustomerName
FROM Customer_T C,
Order_T O1,
OrderLine_T OL1
WHERE C.CustomerID = O1.CustomerID AND
O1.OrderID = OL1.OrderID AND
OL1.ProductID = 3
INTERSECT
SELECT C.CustomerID,
CustomerName

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

50 Modern Database Management, Thirteenth Edition

FROM Customer_T C,
Order_T O1,
OrderLine_T OL1
WHERE C.CustomerID = O1.CustomerID AND
O1.OrderID = OL1.OrderID AND
OL1.ProductID = 4;

6-76. List vendor I D, vendor name, material I D, material name, and supply unit price
for those materials that are provided by more than one vendor:

Microsoft Access and Oracle Query:

SELECT v.VendorID, v.VendorName, rm.MaterialID,

rm.MaterialName, s.SupplyUnitPrice
FROM Vendor_T v, Supplies_T s, Rawmaterial_T rm
WHERE v.vendorID = s.VENDORID AND
s.materialID = rm.materialID AND s.materialID IN
(SELECT MaterialID
FROMSupplies_T s2
WHERE s2.MaterialID = rm.MaterialID
GROUP BY s2.MaterialID
HAVING COUNT(s2.VendorID)>1)
ORDER BY rm.MaterialID;

6-77. Review first query in Correlated Subqueries section in Chapter:

This query will not yield the desired result when there are two or more products
that have the same largest standard price. You would need to re-write the query
to use the comparison >= operator.

6-78. List the I Ds and names of all products that cost less than the average product
price in their product line.

Microsoft Access and Oracle Query:

SELECT P.ProductID, P.ProductDescription

FROM Product_T P

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

51 Chapter 6

WHERE ProductStandardPrice < (

SELECT AVG(ProductStandardPrice)
FROM Product_T P1
WHERE P1.ProductLineID = P.ProductLineID);

6-79. List the I Ds and names of those sales territories that have at least 50 percent
more customers as the average number of customers per territory.

Microsoft Access and Oracle Query:

SELECT T.TerritoryID, T.TerritoryName, COUNT(D.CustomerID)

FROM Territory_T T, DoesBusinessIn_T D
WHERE T.TerritoryID = D.TerritoryID
GROUP BY T.TerritoryID, T.TerritoryName
HAVING COUNT(D.CustomerID) >= 1.5 *
( SELECT AVG(CustCount)
FROM (SELECT COUNT(D1.CustomerID) AS CustCount
FROM DoesBusinessIn_T D1
GROUP BY D1.TerritoryID));

6-80. List the order number, product ID and ordered quantity for all customer orders
for which the ordered quantity is greater than the average ordered quantity of
that product: (Hint: This involves a correlated subquery.)

Microsoft Access Query:

SELECT Order11.OrderID,
Order11.OrderedQuantity,
Order11.ProductID
FROM PRODUCT_T INNER JOIN OrderLine_T AS Order11 ON
PRODUCT_T.ProductID = Order11.ProductID
WHERE (((Order11.OrderedQuantity)>(
SELECT AVG(OrderedQuantity) FROM OrderLine_T x1
WHERE x1.ProductID = Order11.ProductID)) AND
((PRODUCT_T.ProductID)=[Order11].[ProductID]));
Oracle Query:

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

52 Modern Database Management, Thirteenth Edition

SELECT Order11.OrderID,
Order11.OrderedQuantity,
Order11.ProductID
FROM Product_T,OrderLine_T Order11
WHERE Product_T.ProductID = Order11.ProductID AND
Order11.OrderedQuantity >
(SELECT AVG(OrderedQuantity)
FROM OrderLine_T x1
WHERE x1.ProductID = Order11.ProductID);

6-81. List the salesperson that has sold the most computer desks:

Microsoft Access Query:

It is easiest to create a subquery first. The first query we will call tsales:

SELECT Salesperson_T.SalespersonID, SUM(

OrderLine_T.OrderedQuantity) AS [TotSales]
FROM OrderLine_T, Order_T,Product_T, Salesperson_T,
DoesBusinessIn_T
WHERE Order_T.OrderID=[OrderLine_T].[OrderID] AND
OrderLine_T.ProductID=[Product_T].[ProductID] AND
Product_T.ProductDescription="Oak Computer Desk" AND
Order_T.CustomerID=[DoesBusinessIn_T].[CustomerID]
AND Salesperson_T.SalesTerritoryID=
[DoesBusinessIn_T].[TerritoryID]
GROUP BY Salesperson_T.SalespersonID;

Next, find the SalesPersonID for the salesperson who had the highest sales:

SELECT SalespersonID
FROM tsales
WHERE totsales = (SELECT MAX(totsales) FROM tsales);

Oracle Query:

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

53 Chapter 6

SELECT Salesperson_T.SalespersonID,
SUM(OrderedQuantity) AS TotSales
FROM OrderLine_T,
Order_T,
Product_T,
DoesBusinessIn_T,
Salesperson_T
WHERE Order_T.OrderID = OrderLine_T.OrderID AND
OrderLine_T.ProductID = Product_T.ProductID AND
ProductDescription = 'Oak Computer Desk' AND
Order_T.CustomerID = DoesBusinessIn_T.CustomerID AND
Salesperson_T.SalesTerritoryID =
DoesBusinessIn_T.TerritoryID
GROUP BY Salesperson_T.SalespersonID;

Save the result as tsales and run to find the salesperson with the most computer
desk sales:

SELECT Salesperson_ID
FROM tsales
WHERE totsales = (SELECT MAX(totsales) FROM tsales);

6-82. Display in product I D order the product I D and total amount ordered of that
product by the customer who has bought the most of that product; use a derived
table in a FROM clause to answer this query. Note that the result of this query is
a subset (first and last columns) of the prior query result.

Microsoft Access and Oracle Query:

SELECT F1.ProductID,
MAX(F1.ProdCustTotal) as TotOrdered
FROM (SELECT ProductID, CustomerID, SUM(OrderedQuantity)
AS ProdCustTotal
FROM OrderLine_T OL2, Order_T O2
WHERE OL2.OrderID = O2.OrderID

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

54 Modern Database Management, Thirteenth Edition

GROUP BY ProductID, CustomerID) F1

GROUP BY F1.ProductID
ORDER BY F1.ProductID;

Result:

ProductID TotOrdered
1 9
2 26
3 12
4 4
5 10
6 4
7 4
8 2
10 9
13 2
14 10
17 5
20 1

6-83. Employee information for all employees in each state who were hired before the
most recently hired person in that state:

Microsoft Access and Oracle Query:

SELECT *
FROM Employee_T E1
WHERE EmployeeDateHired <
(SELECT MAX(EmployeeDateHired)
FROM Employee_T E2
WHERE E1.EmployeeState = E2.EmployeeState);

6-84. Marketing Manager cross-selling analyses:

a. List the IDs for all the products that have sold in total more than 20 units across
all orders:

Microsoft Access and Oracle Query for part a.:

SELECT X.ProductID

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

55 Chapter 6

FROM OrderLine_T X
GROUP BY X.ProductID
HAVING SUM(X.OrderedQuantity) > 20;

Result for part a.:

ProductID

b. List all the IDs for the orders that include products that satisfy this first query
along with the number of products on those orders (see result below for an
example). Only orders with three or more products.

Microsoft Access and Oracle Query for part b.:

SELECT Y.OrderID,
COUNT(Y.ProductID) AS NumProductsOnOrder
FROM OrderLine_T Y
WHERE Y.OrderID IN
(SELECT T.OrderID
FROM OrderLine_T T
WHERE T.ProductID IN
(SELECT X.ProductID
FROM OrderLine_T X
GROUP BY X.ProductID
HAVING SUM(X.OrderedQuantity) > 20))
GROUP BY Y.OrderID
HAVING COUNT(Y.ProductID) >= 3;

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

56 Modern Database Management, Thirteenth Edition

Result for part b.:

OrderI
NumProductsOnOrder
D
2 3
4 4
1 3

c. What are the (other) products sold on the orders in the Part b. result:

Microsoft Access and Oracle Query for part c.:

SELECT DISTINCT P.ProductID,

P.ProductDescription
FROM OrderLine_T O,
Product_T P
WHERE P.ProductID = O.ProductID AND
O.OrderID IN
(SELECT Y.OrderID
FROM OrderLine_T Y
WHERE Y.OrderID IN
(SELECT T.OrderID FROM OrderLine_T T
WHERE T.ProductID IN
(SELECT X.ProductID
FROM OrderLine_T X
GROUP BY X.ProductID
HAVING SUM(X.OrderedQuantity) > 20))
GROUP BY OrderID
HAVING COUNT(Y.ProductID) >= 3)
ORDER BY P.ProductID;

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

57 Chapter 6

Result for part c.:

ProductID ProductDescription
2 Birch coffee table
3 Oak computer desk
4 Entertainment center
5 Writer’s desk
6 8-Drawer dresser
8 48” bookcase
10 96” bookcase
14 Writer’s Desk

6-85. For each product display, in ascending order by product I D, the product I D and
description along with the customer I D and name for the customer who has
bought the most of that product; also show the total quantity ordered by that
customer (who has bought the most of that product). Use a correlated subquery:

Microsoft Access and Oracle Query:

SELECT P1.ProductID,
P1.ProductDescription,
C1.CustomerID,
C1.CustomerName,
SUM(OL1.OrderedQuantity) AS TotOrdered
FROM Customer_T C1,
Product_T P1,
OrderLine_T OL1,
Order_T O1
WHERE C1.CustomerID = O1.CustomerID AND
O1.OrderID = OL1.OrderID AND
OL1.ProductID = P1.ProductID
GROUP BY P1.ProductID, ProductDescription,
C1.CustomerID, CustomerName
HAVING SUM(OL1.OrderedQuantity) >= ALL
(SELECT SUM(OL2.OrderedQuantity)
FROM OrderLine_T OL2, Order_T O2
WHERE OL2.ProductID = P1.ProductID AND
OL2.OrderID = O2.OrderID AND

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

58 Modern Database Management, Thirteenth Edition

O2.CustomerID <> C1.CustomerID

GROUP BY O2.CustomerID)
ORDER BY P1.ProductID;

Result:

ProductID ProductDescription CustomerID CustomerName TotOrdered

1 Cherry end table 4 Eastern Furniture 9
2 Birch Coffee table 4 Eastern Furniture 26
3 Oak Computer desk 3 Home Furnishings 12
4 Entertainment center 16 ABC Furniture Co. 4
5 Writer’s desk 15 Janet’s Collection 10
6 8-Drawer dresser 4 Eastern Furniture 4
7 48” bookcase 4 Eastern Furniture 4
8 48” bookcase 3 Home Furnishings 2
10 96” bookcase 4 Eastern Furniture 9
13 Nightstand 13 Ikards 2
14 Writer’s desk 15 Janet’s Collection 10
High back leather Contemporary
17 1 5
chair Casuals
20 Armoire 8 Dunkins Furniture 1

Suggestions for Field Exercises

6-86. This question is self-explanatory.

6-87. Three differences that you may want to help the students discover:

o Primary and foreign keys are specified with the CREATE TABLE or ALTER
TABLE commands in S QL*PLUS. M S Access uses a graphical interface to
create tables and to establish primary and foreign keys.
o MS Access S QL does not include the CREATE VIEW or DROP VIEW
commands. Instead, the query that would be used as part of the CREATE VIEW
syntax is saved as a query, which can be accessed later through new select
statements by simply specifying the query name.
o MS Access has a keyword, DISTINCTROW, which is not found in other D BMS
SQ L implementations.

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

59 Chapter 6

Five similarities between Oracle S QL and M S Access SQ L:

o Both use the semicolon to mark the end of a statement and cause it to execute.
o Basic SQ L syntax is very similar, using the keywords SELECT, FROM, WHERE,
GROUP BY, ORDER BY, and HAVING in the same fashion.
o Both use brackets to specify the order of expression evaluation.
o Subqueries and correlated subqueries can be written in both.
o Both are insensitive to spacing and line breaks within a statement.

Project Questions

6-88. Write and execute queries.

The answers will depend on the students’ and the instructor’s choices.

6-89. Identify opportunities for using triggers.

The answers will depend on the students’ and the instructor’s choices.

6-90. Create a strategy for reviewing your database implementation with the
appropriate stakeholders.

When reviewing the database implementation, it is essential that the key

stakeholders are able to evaluate the database content without the risk of
getting confused by the seemingly disjointed structure of a normalized relational
database. One possible way to address this is to do “virtual denormalization” by
creating views for the data needs that have to be addressed with relational joins.
This way, responding to operationally simple data needs can be demonstrated
in a simple way, instead of the need to create complex joins throughout the
process.

In addition, it is essential that the key stakeholders understand that the database
has been designed to be used as a foundation for applications, by experts, or
through sophisticated front-end tools that have been configured automatically.
You don’t want to scare people away at this stage of the process.

Copyright © 2022, 2019, 2016 Pearson Education, Inc.

60 Modern Database Management, Thirteenth Edition

As discussed in the context of Chapter 5, the review should be done by

individuals who are best aware of the technical details of the tasks being
affected. It is likely that this varies depending on the section of the database.
Thus, for certain tasks Alex Martin is the best person to discuss the detailed
data needs and for others, Pat Smith. Overall approval by Mr. Forondo is
needed, but only after detailed reviews by the subject area experts.

Copyright © 2022, 2019, 2016 Pearson Education, Inc.