Week 2

:: Query 1a ::

Query:
SELECT Wine_ID, Wine_name
FROM Wine
WHERE Wine_name LIKE 'J*';

Analysis: I would like to see a list of Wine IDs and wine names from the wine table of wines starting with the letter "J".

:: Query 1b ::

Query:
SELECT Wine.Wine_ID, Wine.Wine_name
FROM Wine
WHERE (((Wine.Wine_name) Not Like 'J*'));

Analysis: I would like to see a list of Wine IDs and wine names from the wine table of wines not starting with the letter "J".

:: Query 2a ::

Query:
SELECT Wine_ID, Wine_name, Wine_amt
FROM Wine
WHERE Wine_amt In (33,40,18);

Analysis: I would like to see Wine IDs, Wine_names and Wine amounts from the Wine table with an amount of $33, $40 or $18.

:: Query 2b ::

Query:
SELECT Wine_ID, Wine_name, Wine_amt
FROM Wine
WHERE Wine_amt NOT IN (33,40,18);

Analysis: I would like to see Wine IDs, Wine_names and Wine amounts from the Wine table with an amount other than $33, $40 or $18.

:: Query 3 ::

Query:
SELECT *
FROM Wine
WHERE Issue_date Between #11/1/2002# And #12/31/2002#;

Analysis: I would like to see every row and column from the Wine table with an issue date between Nov. 1, 2002 and Dec. 31, 2002.

:: Query 4a ::

Query:
SELECT *
FROM Wine
WHERE BegDrink_yr=2003 AND Left_qty>0;

Analysis: I would like to see every row and column from the Wine table with a BegDrink_yr of 2003 and Left_qty of more than 0.

:: Query 4b ::

Query:
SELECT *
FROM Wine
WHERE Issue_date In (#5/28/2001#,#12/15/2002#,#11/30/2002#);

Analysis: I would like to see every row and column from the Wine table with an Issue_date of 5/28/2001, 12/15/2002, 11/30/2002.

:: Query 5a ::

Query:
INSERT INTO Wine
VALUES (15, '1999', 'E. Guigal Châtaeuneuf-du-Pape', 4, 4, '750 ml', 30, null, #11/30/2002#, #6/30/2003#, 2005, 2015);

Analysis: I would like to insert the following information:
Wine_ID: 15
Vintage_yr: 1999
Wine_name: E. Guigal Châtaeuneuf-du-Pape
Wine_qty: 4
Left_qty: 4
Volume: 750 ml
Wine_amt: $30
Harvest_date: unknown
Issue_date: 11/30/2002
Buy_date: 6/30/2003
BegDrink_yr: 2005
EndDrink_yr: 2015

:: Query 5b ::

Query:
INSERT INTO Wine ( Vintage_yr, Wine_name, Wine_qty, Left_qty, Volume, Harvest_date, Buy_date, BegDrink_yr, EndDrink_yr ) VALUES ('2001', 'Justin Justification', 1, 1, '750 ml', #10/8/2001#, #11/4/2003#, 2006, 2008);

Analysis: I would like to insert the following information:
Vintage_yr: 2001
Wine_name: Justin Justification
Wine_qty: 1
Left_qty: 1
Volume: 750 ml
Harvest_date: 10/8/2001
Buy_date: 11/4/2003
BegDrink_yr: 2006
EndDrink_yr: 2008

:: Extra Credit 1a (Distinct, with 1 column) ::

Query:
SELECT DISTINCT Vintage_yr
FROM Wine;

Analysis: I would like to select only one of each value in the Vintage_yr column.

:: Extra Credit 1b (Distinct, with 2 columns) ::

Query:
SELECT DISTINCT Left_qty, EndDrink_yr
FROM Wine
WHERE EndDrink_yr Is NOT Null;

Analysis: I would like to select unique combinations of Left_qty and EndDrink_yr columns to see how many bottles needs to be consumed by a certain ending year (where EndDrink_yr is not unknown).

:: Extra Credit 2 (Datediff) ::

Query:
SELECT Wine_name, datediff("m",Harvest_date,Issue_date) AS Months_aged
FROM Wine
WHERE Harvest_date And Issue_date Is Not Null;

Analysis: I would like to see a list of wines and the number of months it aged from harvest time (Harvest_date) to release time (Issue_date) where the Harvest_date and Issue_date is not null.

:: Extra Credit 3 (Dateadd) ::

Query:
SELECT Vintage_yr, Wine_name, Buy_date, DateAdd("d",30,[Buy_date]) AS Month_after
FROM Wine
WHERE Buy_date>#10/1/2003#;

Analysis: Adding 30 days to latest purchases (Buy_date later than 10/1/03) estimates when the credit card charges will be reflected on monthly bill.