Week 4

:: Query 1a and 1b::

Queries:
1a: SELECT * FROM Address;
1b: SELECT * FROM Address_type_luv;

Analysis: I would like every column and every row of the Address and Address_type_luv tables that I added.

Query 1a: Address Table
Primary Key = Addr_ID Foreign Keys = Addr_type_ID, Winery_ID

Query 1b: Address type look-up-value Table
Primary Key = Addr_type_ID

:: Query 2 ::

Query:
SELECT Winery.Winery_ID, Winery.Winery_name, Address.RegionSubregion, Address.Country, Website.Web_url
FROM Winery, Website, Address
WHERE Winery.Winery_ID=Website.Winery_ID
And Winery.Winery_ID=Address.Winery_ID;

Analysis: This is a query to select Winery.Winery_ID, Winery.Winery_name from the Winery table, Address.RegionSubregion, Address.Country from the Address table and Website.Web_url from the Website table and join these data in one table for easy relation of winery, address and website.

Query 2

:: Query 3 ::

Query:
SELECT Winery.Winery_ID, Winery.Winery_name, Address_type_luv.Addr_type_code, Address.Street_addr, Address.City_name, Address.StateProv_name, Address.Zip_code, Address.Country
FROM (Winery INNER JOIN Address ON Winery.Winery_ID = Address.Winery_ID) INNER JOIN Address_type_luv ON Address.Addr_type_ID = Address_type_luv.Addr_type_ID
WHERE Address.Addr_type_ID =2;

Analysis: Using the inner join syntax, this query is to join the Winery, Address and Address_type_luv tables but added a where clause to narrow down the results to get Wineries with mailing address only.

Query 3

:: Query 4 ::

Query 4a:
SELECT Address_type_luv.Addr_type_ID, Address.Winery_ID, Address.Street_addr, Address.City_name, Address.StateProv_name, Address.Zip_code, Address.Country
FROM Address_type_luv LEFT JOIN Address ON Address_type_luv.Addr_type_ID=Address.Addr_type_ID;

Analysis: The resulting set will produce addresses for all types of address_type look-up-value (vineyard/winery, mailing, wine tasting).

Query 4a

Query 4b:
SELECT Winery.Winery_ID, Winery.Winery_name, Address.Street_addr, Address.City_name, Address.StateProv_name, Address.Zip_code, Address.Country
FROM Address RIGHT JOIN Winery ON Address.Winery_ID = Winery.Winery_ID;

Analysis: The resulting set will produce all wineries with or without their complete addresses.

Query 4b

:: Query 5 ::

Query:
SELECT Wine.Wine_ID, Wine.Wine_name, Winery.Winery_ID, Winery.Winery_name
FROM Wine LEFT JOIN Winery ON Wine.Winery_ID = Winery.Winery_ID
UNION Select Wine.Wine_ID, Wine.Wine_name, Winery.Winery_ID, Winery.Winery_name
FROM Wine RIGHT JOIN Winery ON Wine.Winery_ID = Winery.Winery_ID;

Analysis: This will combine the results from left outer join and right outer join into one result set. The left outer join and right outer join are applied on the wine and winery tables at the same time.

Query 5

:: Extra Credit 1 ::

Query 1a:
SELECT WY.Winery_ID, WY.Winery_name, A.RegionSubregion, A.Country, WS.Web_url
FROM Winery AS WY, Website AS WS, Address AS A
WHERE WY.Winery_ID=WS.Winery_ID And WY.Winery_ID=A.Winery_ID;

Query 1a

Query 1b:
SELECT WY.Winery_ID, WY.Winery_name, AT.Addr_type_code, A.Street_addr, A.City_name, A.StateProv_name, A.Zip_code, A.Country
FROM (Winery AS WY INNER JOIN Address AS A ON WY.Winery_ID=A.Winery_ID) INNER JOIN Address_type_luv AS [AT] ON A.Addr_type_ID=AT.Addr_type_ID
WHERE A.Addr_type_ID=2;

Query 1b

:: Extra Credit 2 ::

Query 2:
INSERT INTO Winery
VALUES (26, "Falesco");

Analysis: Just added a new winery in the winery table. Research may be incomplete or winery address is not available at time of recording.

:: Extra Credit 3 ::

Query 3:
SELECT WY.Winery_ID, WY.Winery_name, AT.Addr_type_code, A.Street_addr, A.City_name, A.StateProv_name, A.Zip_code, A.Country
FROM (Winery AS WY LEFT JOIN Address AS A ON WY.Winery_ID=A.Winery_ID) LEFT JOIN Address_type_luv AS [AT] ON A.Addr_type_ID=AT.Addr_type_ID;

Analysis: This can be used to see which winery needs updating to complete the database.

Query EC 3