Re: Sql Northwind DB
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 09/07/04
- Previous message: arva: "Re: Sql Northwind DB"
- In reply to: arva: "Re: Sql Northwind DB"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 07 Sep 2004 11:26:09 +0200
On Tue, 7 Sep 2004 01:25:00 -0700, arva wrote:
>Thanks for guiding me ..
>I have solved the queries but 5,10 Question i unable to
>solve. I am posting all my answer.
>Please check and let me know is it o.k. or not
>and give the solution for 5,10 query
Hi Arva,
You can check your queries yourself: just execute them in the Northwind
database and check the results.
I can't check them - somehow, it seems that I have a different version
from Northwind than yours. For example, I have no "state" column in the
Customers table and the Orders table has only data for the years 1996,
1997 and 1998.
(snip)
>5.Customer Perks Program. Management would like to setup
>a Platinum Buyer Club for all customers who have
>consistently placed 10 or more orders each year for the
>previous 10 years. Provide a SQL statement and sample
>result set that will list the qualifying Customers, the
>average placed orders during a year, and the total number
>of orders placed each year. This SQL statement should
>bring back one record per Customer for the Program.
The basic solution is to use a NOT EXISTS (subquery). The subquery will be
based on the solution to #4. But you do need a better way to extract the
year from the OrderDate: check out the subject Date and Time Functions in
Books Online (BOL).
Another approach, probably performing better, would be to modify #4 to
operate on 10 years, group it by employee and year and then find employees
that are listed 10 times. This will also require a function to get the
year out of a date; in addition, you'll have to use a derived table. From
the set of assignments, I'm not sure if that subject has already been
covered in your class. But you could check it out in BOL, play around
until you get it working and surprise your teacher!
(snip)
>10. Employee Bonuses. Employees are given a bonus
>each year for the total orders and total order purchase
>price. Provide a SQL statement and sample result set that
>lists each employee, the total order counts, and total
>order purchase price for the past 2 years broken down by
>year. Include each active employee regardless of those
>who have not been associated with orders taken over the
>past 2 years.
If you want it listed as
Employee Orders_LastYr Orders_2YrsAgo Price_LastYr Price_2YrsAgo
Arva 17 5 1765,80 500,25
...
you have (at least <g>) two possibilities.
One of the ways to solve this is to include subqueries in the select list.
Another way includes the use of an outer join (so that employees without
orders don't get excluded) and a construction like COUNT(CASE ...).
Anopther way to present this data is as follows:
Employee Year Orders Price
Arva 2003 5 500,25
Arva 2004 17 1765,80
...
For this, you'll also need outer join. Plus, you need to join in the same
table twice, but with different join conditions.
Like I said - no full solutions, but hints. Enough, I believe, to enable
you to find the solutions. But hopefully not so much that you won't learn
anything in the process.
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Previous message: arva: "Re: Sql Northwind DB"
- In reply to: arva: "Re: Sql Northwind DB"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
