Week 1

:: Query 1 ::

Query:
SELECT *
FROM Wine;

Analysis: I would like to see every column and row in the Wine table for a report which I am designing.

Wine_ID Wine_name Vintage_yr Wine_qty Left_qty Volume Wine_amt Issue_Date Buy_Date BegDrink_yr EndDrink_yr
1 The Holy Trinity Barossa 1999 1 1 750 ml $33.00 12/15/2002 9/1/2003 2003 2008
2 Umbria Vitiano 2001 1 1 750 ml $10.00 5/31/2003 2/15/2003 2003 2003
3 Châteauneuf-du-Pape La Bernardine 2000 1 1 750 ml $40.00 11/30/2002 10/1/2003 2005 2010
4 Stonestreet Chardonnay 2000 1 1 750 ml $23.75   5/1/2003    
5 Korbel Rouge Non-vintage 2 2 750 ml $11.99 12/15/2002 10/25/2003 2004 2004
6 Justin Cal Ital 2000 1 1 750 ml $22.50 5/28/2001 6/1/2002 2004 2005
7 Justin Malbec 2001 1 1 750 ml $24.00   11/4/2003 2006 2008
8 Justin Obtuse 2002 1 1 750 ml $18.00   11/4/2003 2006 2008
9 Justin Cabernet Sauvignon 2001 1 1 750 ml $18.00   11/4/2003 2006 2008
10 Korbel Moscato Frizzante Non-vintage 2 2 750 ml $13.99   10/25/2003 2004 2004

 

:: Query 2 ::

Query:
SELECT Wine_name
FROM Wine;

Analysis: I would like to see every Wine_name from the Wine table.

Wine_name
The Holy Trinity Barossa
Umbria Vitiano
Châteauneuf-du-Pape La Bernardine
Stonestreet Chardonnay
Korbel Rouge
Justin Cal Ital
Justin Malbec
Justin Obtuse
Justin Cabernet Sauvignon
Korbel Moscato Frizzante

 

:: Query 3 ::

Query:
SELECT Wine_name, Vintage_yr
FROM Wine;

Analysis: I would like to see every Wine_name and its Vintage_yr from the Wine table side-by-side.

Wine_name Vintage_yr
The Holy Trinity Barossa 1999
Umbria Vitiano 2001
Châteauneuf-du-Pape La Bernardine 2000
Stonestreet Chardonnay 2000
Korbel Rouge Non-vintage
Justin Cal Ital 2000
Justin Malbec 2001
Justin Obtuse 2002
Justin Cabernet Sauvignon 2001
Korbel Moscato Frizzante Non-vintage

 

:: Query 4 ::

Query:
SELECT Wine_name
FROM Wine
WHERE Wine_amt=33;

Analysis: List all Wine_name whose wine_amt is exactly $33.

Wine_name
The Holy Trinity Barossa

 

:: Extra Credit ::

Query:
SELECT *
FROM Wine
WHERE (((Wine.BegDrink_yr)=[?]));

Analysis: This is a query to select rows of data from the Wine table with a certain BegDrink_yr to track which wine is ready/aged to be consumed by that particular beginning year. In this example, I used the year 2004 to generate the following:

Wine_ID Wine_name Vintage_yr Wine_qty Left_qty Volume Wine_amt Issue_Date Buy_Date BegDrink_yr EndDrink_yr
5 Korbel Rouge Non-vintage 2 2 750 ml $11.99 12/15/2002 10/25/2003 2004 2004
6 Justin Cal Ital 2000 1 1 750 ml $22.50 5/28/2001 6/1/2002 2004 2005
10 Korbel Moscato Frizzante Non-vintage 2 2 750 ml $13.99   10/25/2003 2004 2004

 

Query:
SELECT *
FROM Wine
WHERE (((Wine.EndDrink_yr)=[?]));

Analysis: This is a query to select rows of data from the Wine table with a certain EndDrink_yr to track which wine should be consumed before that particular end year is over. In this example, I used the year 2005 to generate the following:

Wine_ID Wine_name Vintage_yr Wine_qty Left_qty Volume Wine_amt Issue_Date Buy_Date BegDrink_yr EndDrink_yr
6 Justin Cal Ital 2000 1 1 750 ml $22.50 5/28/2001 6/1/2002 2004 2005