Question: Below is the relational schema for which I have to write SQL queries. The tables are also pasted. region(region_id, region_name) grape_variety(variety_id, variety) wine(wine_id, wine_name, wine_type,
Below is the relational schema for which I have to write SQL queries. The tables are also pasted.
region(region_id, region_name)
grape_variety(variety_id, variety)
wine(wine_id, wine_name, wine_type, year, winery_id)
winery(winery_id, winery_name, region_id)
wine_type(wine_type_id, wine_type)
wine_variety(wine_id, variety_id, id)
1. List the name of the wineries that produce fortified wines.
2. List the names of the wines and the name of the winery that produces a white wine from the Riverland region.
3. List the names of all wineries that make Gris wine, but do not make Shiraz wine.
region table:
| region_id | region_name |
| 1 | 'All' |
| 2 | 'Goulburn Valley' |
| 3 | 'Rutherglen' |
| 4 | 'Coonawarra' |
| 5 | 'Upper Hunter Valley' |
| 6 | 'Lower Hunter Valley' |
| 7 | 'Barossa Valley' |
| 8 | 'Riverland' |
| 9 | 'Margaret River' |
| 10 | 'Swan Valley' |
grape_variety table
| variety_id | variety |
| 1 | 'Riesling' |
| 2 | 'Chardonnay' |
| 3 | 'Sauvignon' |
| 4 | 'Blanc' |
| 5 | 'Semillon' |
| 6 | 'Pinot' |
| 7 | 'Gris' |
| 8 | 'Verdelho' |
| 9 | 'Grenache' |
| 10 | 'Noir' |
| 11 | 'Cabernet' |
| 12 | 'Shiraz' |
| 13 | 'Merlot' |
| 14 | 'Dessert' |
| 15 | 'Muscat' |
| 16 | 'Sherry' |
| 17 | 'Port' |
| 18 | 'Champagne' |
| 19 | 'Sparkling' |
| 20 | 'Red' |
| 21 | 'White' |
wine table:
| wine_id | wine_name | wine_type | Year | winery_id |
| 1 | 'Archibald' | 2 | 1997 | 1 |
| 2 | 'Pattendon' | 3 | 1975 | 1 |
| 3 | 'Lombardi' | 4 | 1985 | 2 |
| 4 | 'Tonkin' | 2 | 1984 | 2 |
| 5 | 'Sears' | 5 | 1986 | 2 |
| 6 | 'Serrong' | 6 | 1995 | 2 |
| 7 | 'Mettaxus' | 5 | 1996 | 2 |
| 8 | 'Sears' | 4 | 1987 | 6 |
| 9 | 'Serrong' | 3 | 1981 | 6 |
| 10 | 'Chester' | 5 | 1999 | 7 |
| 11 | Chemnis | 2 | 1980 | 8 |
| 12 | Holdenson | 6 | 1979 | 9 |
| 13 | Skerry | 2 | 1975 | 10 |
| 14 | Sears | 5 | 1978 | 11 |
winery table:
| winery_id | winery_name | region_id |
| 1 | 'Hanshaw Estates Winery' | 2 |
| 2 | 'De Morton and Sons Wines' | 8 |
| 3 | 'Joness Premium Wines' | 3 |
| 4 | 'Borg Daze Premium Wines' | 5 |
| 5 | 'Binns Group' | 6 |
| 6 | 'Davie Brook Vineyard' | 3 |
| 7 | 'Eglington Creek Premium Wines' | 4 |
| 8 | 'McKay Station Vineyard' | 4 |
| 9 | 'Dennis and Sons Wines' | 5 |
| 10 | 'Beard Brothers Vineyard' | 8 |
| 11 | 'Rowley Brook Group' | 8 |
wine_type table:
| wine_type_id | wine_type |
| 1 | 'All' |
| 2 | 'Sparkling' |
| 3 | 'Fortified' |
| 4 | 'Sweet' |
| 5 | 'White' |
| 6 | 'Red' |
wine_variety
| wine_id | variety_id | id |
| 1 | 19 | 1 |
| 1 | 20 | 2 |
| 2 | 16 | 1 |
| 3 | 14 | 1 |
| 4 | 18 | 1 |
| 5 | 5 | 1 |
| 5 | 3 | 2 |
| 5 | 4 | 3 |
| 6 | 12 | 1 |
| 6 | 11 | 2 |
| 7 | 6 | 1 |
| 7 | 7 | 2 |
| 8 | 14 | 1 |
| 9 | 16 | 1 |
| 10 | 2 | 1 |
| 11 | 18 | 1 |
| 12 | 11 | 1 |
| 12 | 13 | 2 |
| 13 | 12 | 1 |
| 13 | 21 | 2 |
| 14 | 7 | 1 |
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts