Introduction to Structured Query Language - Database Processing: Fundamentals, Design, and Implementation - page 101

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. Write an SQL statement to join EMPLOYEE, ASSIGNMENT, and PROJECT using
the JOIN ON syntax. Run this statement.
N. 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.
2.65 Using Microsoft Access QBE, create and run new queries to answer the questions in
Project Question 2.64. 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.
The following questions refer to the NDX table data as described starting on page 74.
you can obtain a copy of this data in the Microsoft access database DBP-e13-NDX
.accdb from the text's Web site ( www.pearsonhighered.com/kroenke ) .
2.66 Write SQL queries to produce the following results:
a. The ChangeClose on Fridays.
B. The minimum, maximum, and average ChangeClose on Fridays.
C. The average ChangeClose grouped by TYear. Show TYear.
D. The average ChangeClose grouped by TYear and TMonth. Show TYear and TMonth.
e. The average ChangeClose grouped by TYear, TQuarter, TMonth shown in descending
order of the average (you will have to give a name to the average in order to sort by it).
Show TYear, TQuarter, and TMonth. Note that months appear in alphabetical and not
calendar order. Explain what you need to do to obtain months in calendar order.
F. The difference between the maximum ChangeClose and the minimum ChangeClose
grouped by TYear, TQuarter, TMonth shown in descending order of the difference
(you will have to give a name to the difference in order to sort by it). Show TYear,
TQuarter, and TMonth.
G. The average ChangeClose grouped by TYear shown in descending order of the aver-
age (you will have to give a name to the average in order to sort by it). Show only
groups for which the average is positive.
h. Display a single field with the date in the form day/month/year. Do not be con-
cerned with trailing blanks.
2.67 It is possible that volume (the number of shares traded) has some correlation with the
direction of the stock market. Use the SQL you have learned in this chapter to investigate
this possibility. Develop at least five different SQL statements in your investigation.
Case Questions
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 competi-
tion 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