7830TextProjectAnswersCh2

7830TextProjectAnswersCh2 - Chapter Two ANSWERS TO PROJECT...

Info iconThis preview shows pages 1–6. Sign up to view the full content.

View Full Document Right Arrow Icon
Page 2-1 Chapter Two ANSWERS TO PROJECT QUESTIONS The following questions refer to the NDX table of data on page 53. Uses Access database, dbp10_im_ch02_EOC.mdb 2.1 Write SQL queries to produce the following results: a. The ChangeClose on Fridays. SELECT ChangeClose FROM NDX WHERE TDayOfWeeK = 'Friday'; b. The minimum, maximum, and average ChangeClose on Fridays. SELECT MIN (ChangeClose) AS MinFridayChangeClose, MAX (ChangeClose) AS MaxFridayChangeClose, AVG (ChangeClose) AS AverageFridayChangeClose FROM NDX WHERE TDayOfWeeK = 'Friday'; c. The average ChangeClose grouped by TYear. Show TYear.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Page 2-2 Since TYear is being displayed, it makes sense to sort the results by TYear although this is not explicitly stated in the question. SELECT TYear, AVG (ChangeClose) AS AverageChangeClose FROM NDX GROUP BY TYear ORDER BY TYear; d. The average ChangeClose grouped by TYear and TMonth. Show TYear and TMonth. Since TYear and TMonth are being displayed, it makes sense to sort the results by TYear and TMonth although this is not explicitly stated in the question. SELECT TYear, TMonth, AVG (ChangeClose) AS AverageChangeClose FROM NDX GROUP BY TYear, TMonth ORDER BY TYear, TMonth;
Background image of page 2
Page 2-3 Unfortunately, the table NDX does not contain a numeric value of the month, so in order to sort the months correctly, we need to use the NDX_Full table. SELECT TYear, TMonth, AVG (ChangeClose) AS AverageFridayChangeClose FROM NDX_Full GROUP BY TYear, TMonth, TMonthNumber ORDER BY TYear, TMonthNumber;
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Page 2-4 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. SELECT TYear, TQuarter, TMonth, AVG (ChangeClose) AS AverageChangeClose FROM NDX GROUP BY TYear, TQuarter, TMonth ORDER BY AverageFridayChangeClose DESC; Unfortunately, as discussed about in 2.36, MS ACCESS cannot process the ORDER BY clause correctly.
Background image of page 4
The correct result, obtained from MS SQL Server 2000, is: In order to obtain the months in calendar order, we would have to use a numerical value for each month (1, 2, 3, …, 12) and sort by those values. f. The difference between the maximum ChangeClose and the minimum ChangeClose grouped by 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. SELECT TYear, TQuarter, TMonth, (MAX (ChangeClose) – MIN(ChangeClose)) AS DifChangeClose FROM NDX GROUP BY TYear, TQuarter, TMonth ORDER BY DifChangeClose DESC; Unfortunately, as discussed about in 2.36, MS ACCESS cannot process the ORDER BY clause correctly. The correct result, obtained from MS SQL Server 2000, is:
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 6
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 09/13/2009 for the course CS CS31 taught by Professor Camillo during the Spring '09 term at Grantham.

Page1 / 27

7830TextProjectAnswersCh2 - Chapter Two ANSWERS TO PROJECT...

This preview shows document pages 1 - 6. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online