any SQL guru's out there?

Jay

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

Jul 23 '05 #1

--CELKO--

>> 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.

Jul 23 '05 #2

Jay

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).

Jul 23 '05 #3

Gert-Jan Strik

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

Jul 23 '05 #4