Using ONLY Entity Relationship Diagram to Query MYSQL Format [BRAIN CHALLENGE]

Question

Normally we query using tables and schema but in my case I have to use Entity Relationship Diagram ONLY to query on a piece of paper in MYSQL Format.

These are really making things complicated. I require your help for example this question.

QUESTION:

Answer 1

Based on the data above INNER JOINs would be written like this:

SELECT *
FROM Wines
    INNER JOIN Carry ON Wines.wine_id = Carry.wine_id

Notice that diagram relationship between Wines and Caries shows 0 to many (1..1 - 0..*). Also notice that wine_id is not listed in the Carry table column list but implied throught the relation.

Next you want to know price for today (hint: since table Carry table has price_start_date and price_end_date it implies that prices are not fixed and you need to use these fields):

WHERE price_start_date <= CURDATE() AND CURDATE() <= price_end_date

To get prices below $15:

WHERE price_start_date <= CURDATE() AND CURDATE() <= price_end_date
    AND price_on_carry < 15

Question 1 query would be something like below (you will need to add relevant column names):

SELECT *
FROM Wines
    INNER JOIN Carry ON Wines.wine_id = Carry.wine_id
WHERE price_start_date <= CURDATE() AND CURDATE() <= price_end_date
    AND price_on_carry < 15
    AND Wines.color = 'Red'

Note: English is not my first language so I am confused by "whether or not they are from Canada", specifically if we need to include information about whether the wine is from Canada or not. I assumed that we don't need to include this information.