Question

PostgreSQL query code needed

Define a view Q5(beer,ingredient,type) that gives the name of beers that use ingredients whose origin in the Czech Republic.

Sample OUTPUT :

beer | ingredient | type
-----------------+--------------+-------
A Million Stars | Pilsner Malt | grain
Beer | Saaz | hop
Beer | Pilsner Malt | grain
Bohemian Lager | Saaz | hop
Bohemian Lager | Pilsner Malt | grain
Feels Good Man | Pilsner Malt | grain
Kamchatka | Saaz | hop
Simple Coercion | Pilsner Malt | grain
The Leader | Pilsner Malt | grain
(9 rows)
where beer = beers.name , ingredient = ingredients.name , type = ingredients.itype

constraint condition ingredient from Czech Republic : Czech Republic = countries.name ,

ingredients.code in table 'contains' trace to ingredients.id in table 'ingredients', ingredients.origin (number) trace to countries.id in table 'countries'

Some database example as:

Beers - sample partial database

id | name | vintage | style | abv | ibu | sold_in | volume | notes

| rating

-----+------------------------------------------------------+---------+-------+------+-----+---------+--------+-------------------------------

-------------+--------

1 | #100 Hopped Barley Wine | 2020 | 1 | 10 | 80 | bottle | 375 |

| 8

2 | 0.0 | 2020 | 2 | 0 | | bottle | 330 |

| 1

3 | 1750 Export Porter | 2018 | 3 | 10.5 | | can | 330 |

| 8

4 | 2 Solid 2 Gold | 2020 | 4 | 6.5 | | can | 440 |

| 6

5 | 2021 Vintage Ale | 2021 | 5 | 7.5 | | bottle | 355 |

| 6

6 | 30-G | 2021 | 6 | 7 | | can | 440 | single hop series

| 8

7 | 40 | 2020 | 6 | 6 | 65 | bottle | 355 | 40th anniversary ale

| 6

Contains - sample partial database

beer | ingredient

------+------------

6 | 26

9 | 46

9 | 177

10 | 239

10 | 299

12 | 253

14 | 8

14 | 68

Ingredients - sample partial database

id | itype | name | origin

-----+---------+-----------------------------------+--------

1 | hop | #10416 | 6

2 | hop | Admiral | 35

3 | hop | Agnus | 31

4 | hop | Ahtanum | 150

5 | hop | Amarillo | 150

6 | hop | Aloha | 150

7 | hop | Apollo | 150

8 | hop | Aramis | 40

9 | hop | Aurora | 125

10 | hop | Azacca | 150

11 | hop | Belma | 150

Countries - sample partial database

id | code | name

-----+------+--------------------

1 | AFG | Afghanistan

2 | ALB | Albania

4 | ARG | Argentina

6 | AUS | Australia

7 | AUT | Austria

11 | BEL | Belgium

Please assist in PostgreSQL query code thanks

Answer 1

The view Q5 can be created by joining the Beers, Contains, Ingredients, and Countries tables and filtering for ingredients whose origin is the Czech Republic. The SQL query selects the beer name, ingredient name, and ingredient type.

Step-by-step explanation:

To define the required PostgreSQL view Q5 that lists beers and their ingredients originating from the Czech Republic, you would write a SQL query that joins the necessary tables (Beers, Ingredients, Contains, and Countries) and filters the results based on the country of origin for the ingredients. Here is an example of how the PostgreSQL query code might look:

CREATE OR REPLACE VIEW Q5 AS
SELECT
b.name AS beer,
i.name AS ingredient,
i.itype AS type
FROM
Beers b
JOIN Contains c ON b.id = c.beer
JOIN Ingredients i ON c.ingredient = i.id
JOIN Countries ct ON i.origin = ct.id
WHERE
ct.name = 'Czech Republic';

This SQL statement creates a view named Q5. It selects the beer name, ingredient name, and ingredient type, joining the necessary tables on the specified columns and filters the results where the country name is 'Czech Republic'.