Topic: Mother of Mercy

[toasty0]

I'm prepping for an upcoming test. This is one of the questions
---------------------------------------------------------------------------------------------------

You are a database developer for your company. You are responsible for a Microsoft SQL Server 2005 database named VolunteerGroupData. Your company uses the database to track the volunteer groups that run concession stands during events at a sports stadium.

The database contains a table named GroupPayments that you use to record the amount of money paid to a group for an event. The table contains four columns:

* EventDate - a user-defined data type value to record the date of each event
* StandNum - a tinyint value to record the number assigned to each concession stand
* GroupNum - a smallint value to record the number assigned to each group
* Payment - a money value to record the amount of money earned by a group for an event

An employee plans to feature the three groups that earned the most money during July, 2005, in an article she is writing for a newsletter.

You need to use GroupPayments to determine which three groups she should feature in the article. Your solution should optimize query performance.

Which statement or statements should you use?

 SET ROWCOUNT 3;
SELECT GroupNum, SUM (Payment) AS GroupTot
FROM GroupPayments
WHERE EventDate BETWEEN 'July 1, 2005' AND 'August 1, 2005'
GROUP BY GroupNum
ORDER BY GroupTot;
 
 SET ROWCOUNT 3;
SELECT GroupNum, SUM (Payment) AS GroupTot, EventDate
FROM GroupPayments
WHERE EventDate BETWEEN 'July 1, 2005' AND 'July 31, 2005'
GROUP BY GroupNum
ORDER BY GroupTot;
 
 SELECT TOP (3) GroupNum, SUM (Payment) AS GroupTot
FROM GroupPayments
WHERE EventDate BETWEEN 'July 1, 2005' AND 'July 31, 2005'
GROUP BY GroupNum
ORDER BY GroupTot DESC;
 
 SELECT TOP (3) GroupNum, SUM (Payment) AS GroupTot, EventDate
FROM GroupPayments
WHERE EventDate BETWEEN 'July 1, 2005' AND 'August 1, 2005'
GROUP BY GroupNum
ORDER BY GroupTot;
 ---------------------------------------------------------------------------------------------------------------------------------

I'll post some others later.

[Bonk]

I'm prepping for an upcoming test. This is one of the questions

You got the questions first?    You be cheatin? 

Too bad SQL is not standardised, or I'd help, but queries that work on MSSQL, may or may not work on MySQL and may or may not work on Oracle... a real shame, I guess you call it "vendor lock-in" and they're all guilty of it.

[FCM_SFHQ_XC]

Wish I had the time to learn some of the differences in what works and what doesnt between MySQL and MS SQL, but that I guess will be a future plan somewhere down the line. In the meantime..

if(questionIsMSSQL && (notWorkingInMySQL || notWorkingInOracle)) //methods prevously returned boolean vars in if statement to determine if it works in MySQL and Oracle
{
   initiatePlanentarySelfDestruct(60);  //Self destruct method, input countdown in seconds. If it is MSSQL & not working elsewhere, best just remove the pretty blue planet in the inner solar system
}
else
{
   celebration(); //Celebrate, the end of the world will not come in 2012 since the question is not MSSQL or it at least works across the board
}

[toasty0]

Wish I had the time to learn some of the differences in what works and what doesnt between MySQL and MS SQL, but that I guess will be a future plan somewhere down the line. In the meantime..

if(questionIsMSSQL && (notWorkingInMySQL || notWorkingInOracle)) //methods prevously returned boolean vars in if statement to determine if it works in MySQL and Oracle
{
   initiatePlanentarySelfDestruct(60);  //Self destruct method, input countdown in seconds. If it is MSSQL & not working elsewhere, best just remove the pretty blue planet in the inner solar system
}
else
{
   celebration(); //Celebrate, the end of the world will not come in 2012 since the question is not MSSQL or it at least works across the board
}

 

[Dash Jones]

How do you know the questions before you even take the test?

[toasty0]

It's a prep exam question.

Can you imagine the what the real exam question will be like?

[Dracho]

www.transcender.com sells a ton of practice exams.  The CISSP prep exam I purchased from them (I am convinced) was invaluable in helping me pass that behemoth.

I'm also studying for the PMI exam with some of their tests, but am getting a detour next week through the CPISM and CPISA.

[toasty0]

They're good. I think for an MS test though MeasureUp is the test of choice with ExamCram coming up a close second. For anything not MS Transcender is head and shoulders above all other contenders.

[FCM_SFHQ_XC]

It's a prep exam question.

Can you imagine the what the real exam question will be like?

sometimes the prep exams are much harder then the actual exam too

[Sirgod]

Why not just print it out, and look at who made the most money? seems far easier that way.

Stephen

[toasty0]

It's a prep exam question.

Can you imagine the what the real exam question will be like?

sometimes the prep exams are much harder then the actual exam too

One can hope so.