Week 3

:: Query 1a ::

Query:
SELECT Wine_ID, Left_qty, EndDrink_yr
FROM Wine
WHERE Left_qty>0
ORDER BY EndDrink_yr;

Analysis: This query would list the Wine_ID, Left_qty and EndDrink_yr in the order of EndDrink_yr starting from the earliest. This is useful when I want to see the number of wines remaining before a their EndDrink_yr ends. This will flag me how many should be consumed by a certain year.

Wine_ID Left_qty EndDrink_yr
4 1  
2 1 2003
15 4 2004
5 2 2004
18 2 2004
10 2 2004
17 14 2005
6 1 2005
14 12 2005
8 1 2008
9 1 2008
12 4 2008
13 1 2008
11 1 2008
7 1 2008
19 1 2008
1 1 2008
3 1 2010
16 4 2015

 

:: Query 1b ::

Query:
SELECT Wine_ID, Left_qty, EndDrink_yr
FROM Wine
WHERE Left_qty>0
ORDER BY EndDrink_yr DESC;

Analysis: This query would list the Wine_ID, Left_qty and EndDrink_yr in the order of EndDrink_yr starting from the latest year first.

Wine_ID Left_qty EndDrink_yr
16 4 2015
3 1 2010
8 1 2008
9 1 2008
12 4 2008
13 1 2008
11 1 2008
7 1 2008
19 1 2008
1 1 2008
6 1 2005
17 14 2005
14 12 2005
18 2 2004
10 2 2004
5 2 2004
15 4 2004
2 1 2003
4 1  

 

:: Query 1c ::

Query:
SELECT Wine.Wine_ID, Wine.Left_qty, Wine.EndDrink_yr FROM Wine WHERE (((Wine.Left_qty)>0)) ORDER BY Wine.Left_qty, Wine.EndDrink_yr;

Analysis: I would like to select Wine_ID, Left_qty and EndDrink_yr in the order of both Left_qty and EndDrink_yr. This would alert me immediately the least and most number of wine and their respective year they need to be consumed.

Wine_ID Left_qty EndDrink_yr
4 1  
2 1 2003
6 1 2005
19 1 2008
1 1 2008
8 1 2008
9 1 2008
7 1 2008
11 1 2008
13 1 2008
3 1 2010
5 2 2004
18 2 2004
10 2 2004
15 4 2004
12 4 2008
16 4 2015
14 12 2005
17 14 2005

 

:: Query 2a ::

Query:
SELECT [EndDrink_yr], Count(*) AS Total
FROM Wine
GROUP BY [EndDrink_yr];

Analysis: This statement is counting how many wine names in the Wine table fall for each EndDrink_yr and displaying the result by EndDrink_yr.

EndDrink_yr Total_wine_name
  1
2003 1
2004 4
2005 3
2008 8
2010 1
2015 1

 

:: Query 2b ::

Query:
SELECT [EndDrink_yr], Sum(Left_qty) AS Total_bottle_qty
FROM Wine
GROUP BY [EndDrink_yr];

Analysis: This statement would produce the total number of bottles by EndDrink_yr.

EndDrink_yr Total_bottle_qty
  1
2003 1
2004 10
2005 27
2008 11
2010 1
2015 4

 

:: Query 3 ::

Query:
SELECT Wine_ID, FORMAT(ROUND(Wine_qty*Wine_amt,2),'currency') AS Total_wine_amt
FROM Wine
ORDER BY [Wine_ID];

Analysis: I would like to see the total wine amount I'm spending per Wine_ID by multiplying the Wine_qty by the Wine_amt, formatted as currency with 2 decimal places.

Wine_ID Total_wine_amt
1 $33.00
2 $10.00
3 $40.00
4 $23.75
5 $23.98
6 $24.00
7 $24.00
8 $18.00
9 $18.00
10 $27.98
11 $24.00
12 $116.00
13 $32.00
14 $528.00
15 $156.00
16 $32.00
17 $528.00
18 $78.00
19 $32.00

 

:: Query 4 ::

Query:
DELETE *
FROM Wine
WHERE Wine_ID = 19;

Analysis: I would like to delete a row from the wine table with Wine_ID of 19. A probable reason for deleting a wine is when remaining quantity is zero and maybe move it to a wine archive table.

 

:: Extra Credit 1 ::

Query:
SELECT Sum(Left_qty) AS Total_bottles, EndDrink_yr
FROM Wine
WHERE EndDrink_yr Is NOT Null
GROUP BY EndDrink_yr
ORDER BY Sum(Left_qty);

Analysis: I would like to select sum of Left_qty and EndDrink_yr from the wine table where EndDrink_yr is not unknown grouped by EndDrink_yr and ordered by Sum(Left_qty).

Total_bottles EndDrink_yr
1 2010
1 2003
4 2015
10 2004
11 2008
27 2005

 

:: Extra Credit 2 ::

Query:
SELECT Sum(Left_qty) AS Total_bottles, EndDrink_yr
FROM Wine
GROUP BY EndDrink_yr
HAVING Sum(Left_qty)>1;

Analysis: I would like to select sum of Left_qty and EndDrink_yr from the wine table grouped by EndDrink_yr but the Sum(Left_qty) should be more than one to narrow down the SQL result set based on the sum total of the Left_qty column.

Total_bottles EndDrink_yr
10 2004
27 2005
11 2008
4 2015

 

:: Extra Credit 3a ::

Query:
DELETE *
FROM Winery
WHERE [Winery_ID]=14;

Analysis: I would like to delete a row from Winery table where Winery_ID is 14. I would use a similar statement in the event that a winery goes out of business or has a name change due to a merger.

 

:: Extra Credit 3b ::

Query:
DELETE *
FROM Website
WHERE [Winery_ID]=14;

Analysis: The removal of a record in the winery table will cause the deletion of the winery's website(s) in the Website table.