any SQL gurus out there?

Hello, this probably isnt the best place to ask but I can't find a more
suitable sql newsgroup so I hope y'all dont mind too much.

I have 2 tables; Cellar and Colour

CELLAR contains the wine name, its year and the no.of bottles.

Wine Year Bottles
Chardonnay 87 4
Fume Blanc 87 2
Pinot Noir 82 3
Zinfandel 84 9

COLOUR contains wine name and it's colour

Wine Colour
Chardonnay White
Fume Blanc White
Pinot NoirRed
Zinfandel Rose

This is from a past exam paper btw

One of the questions was:
Write the sql to count how many white wines there are in the table cellar.

The solution that the lecturers included is:

SELECT count(wine)

FROM cellar

WHERE colour='White'

Now i havent' been able to try out this sql yet but to me that looks wrong.

My solution would be:

SELECT count(wine)

FROM cellar

WHERE cellar.wine = colour.wine and colour.colour='White'

Can anyone tell me which one is correct, and if mine isn't correct then why
isn't it?

Thanks>> Write the sql to count how many white wines there are in the table
cellar. <<

The first answer is wrong; look at the missing table in the FROM
clause. And the quesiton is vague. Do I want the actual bottle count
or a count by wine_type

SELECT COUNT(DISTINCT type_wine) AS type_count, COUNT(*) AS
bottle_count
FROM Cellar AS C, WineColours AS W
WHERE C.wine_type = W.wine_type
AND W.colour = 'White' ;

I have a total of six bottles of whites in two varieties.|||> The first answer is wrong; look at the missing table in the FROM
> clause. And the quesiton is vague. Do I want the actual bottle count
> or a count by wine_type
> SELECT COUNT(DISTINCT type_wine) AS type_count, COUNT(*) AS
> bottle_count
> FROM Cellar AS C, WineColours AS W
> WHERE C.wine_type = W.wine_type
> AND W.colour = 'White' ;
> I have a total of six bottles of whites in two varieties.

thanks for your answer

yeh the question is poorly worded however I believe it simply refers to the
number of types, i.e 2 (chardonnay, fume blanc).|||Well, you don't need an "SQL guru" for that query. Since the question is
not really clear, you can pick the column you need.

SELECT COUNT(Distinct Wine) NumberOfWhiteWineBrands
, SUM(Bottles) NumberOfBottlesOfWhiteWine
FROM Cellar
INNER JOIN Colour
ON Colour.Wine = Cellar.Wine
WHERE Colour.Colour = 'White'

If it is the column "NumberOfWhiteWineBrands" that you need, then you
could also write

SELECT COUNT(*)
FROM Colour
WHERE Colour = 'White'
AND EXISTS (
SELECT 1
FROM Cellar
WHERE Cellar.Wine = Colour.Wine
)

HTH,
Gert-Jan

Jay wrote:
> Hello, this probably isnt the best place to ask but I can't find a more
> suitable sql newsgroup so I hope y'all dont mind too much.
> I have 2 tables; Cellar and Colour
> CELLAR contains the wine name, its year and the no.of bottles.
> Wine Year Bottles
> Chardonnay 87 4
> Fume Blanc 87 2
> Pinot Noir 82 3
> Zinfandel 84 9
> COLOUR contains wine name and it's colour
> Wine Colour
> Chardonnay White
> Fume Blanc White
> Pinot NoirRed
> Zinfandel Rose
> This is from a past exam paper btw
> One of the questions was:
> Write the sql to count how many white wines there are in the table cellar.
> The solution that the lecturers included is:
> SELECT count(wine)
> FROM cellar
> WHERE colour='White'
> Now i havent' been able to try out this sql yet but to me that looks wrong.
> My solution would be:
> SELECT count(wine)
> FROM cellar
> WHERE cellar.wine = colour.wine and colour.colour='White'
> Can anyone tell me which one is correct, and if mine isn't correct then why
> isn't it?
> Thanks