Week 4

:: Query 1a and 1b::

Queries:
1a: SELECT * FROM Winery;
1b: SELECT * FROM Website;

Analysis: I would like every column and every row of the Winery and Website tables that I added.

Query 1a: Winery Table
Primary Key=Winery_ID

Query 1b: Website Table
Primary Key=Web_ID Foreign Key=Winery_ID

:: Query 2 ::

Query:
SELECT Wine_ID, Vintage_yr, Wine_name, BegDrink_yr, Web_url
FROM Wine, Website
WHERE Wine.Winery_ID=Website.Winery_ID And BegDrink_yr=[?];

Analysis: This is a query to select Wine_ID, Vintage_yr, Wine_name from the Wine table and select Web_url from the Website table with a certain BegDrink_yr to track which wine is ready/aged to be consumed by that particular beginning year and join these data in one table. In this example, I used the year 2006 to generate the following:

Query 2

:: Query 3 ::

Query:
SELECT Wine.Wine_ID, Wine.Vintage_yr, Wine.Wine_name, Wine.EndDrink_yr, Website.Web_url
FROM Wine INNER JOIN Website ON Wine.Winery_ID=Website.Winery_ID
WHERE EndDrink_yr=[?];

Analysis: Using the inner join syntax, this is similar to Query 2 above but with EndDrink_yr. I used the year 2005 to generate the following:

Query 3

:: Query 4 ::

Query:
UPDATE Website
SET Web_url = "http://www." & [Web_url]
WHERE [Web_ID] In (12,15);

Analysis: This statement is to update the website address to add "http://www." in addition to the Web_url.

Query 4
From:
To:

:: Extra Credit 1 ::

Query:
UPDATE Wine, Winery
SET Wine.Wine_amt = Wine.Wine_amt*0.9, Winery.Winery_name = Winery.Winery_name & " Vineyards & Winery"
WHERE Wine.Winery_ID=Winery.Winery_ID And Wine.Wine_ID=14;

Analysis: This statement would update the Wine_amt from $44.00 to apply a 10% discount on the Wine table and update the Winery_name from Justin to Justin Vineyards & Winery on the Winery table where Winery ID is same on both tables and Wine_ID is 14.

Wine Table
Original
Updated
Winery Table
Original
Updated

:: Extra Credit 2 ::

Query 2a:
SELECT Sum(Wine.Left_qty) AS Total_bottles, Winery_name
FROM Wine, Winery
WHERE Wine.Winery_ID=Winery.Winery_ID
GROUP BY Winery.Winery_name;

Query 2b (Inner Join):
SELECT Sum(Left_qty) AS Total_bottles, Winery_name
FROM Wine INNER JOIN Winery ON Wine.Winery_ID=Winery.Winery_ID
GROUP BY Winery.Winery_name;

Analysis: I would like to select sum of Left_qty from the wine table and Winery_name from the winery table grouped by Winery_name where winery_ID of both Wine and Winery table is the same.

Query EC 2a

:: Extra Credit 3 ::

Query 3a:
UPDATE Winery SET Winery_ID = 26
WHERE Winery_ID=27;

Query 3b:
UPDATE Wine SET Winery_ID = 26
WHERE Winery_ID=27;

Analysis: The first query would update the Winery_ID (primary key) from the Winery table. This update query warrants the second update query to change the Winery_ID (foreign key) from the Wine table to correct the affected row/s of data.