7830TextProjectAnswersCh2

7830TextProjectAnswersCh2 - Chapter Two ANSWERS TO PROJECT...

Info icon This 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.
Image of page 1

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

View Full Document Right 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;
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;
Image of page 3

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

View Full Document Right 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.
Image of page 4
Page 2-5 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.
Image of page 5

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

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

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern