INF 4401 – Introduction To Databases

CI3 : SQL 2

Advanced SQL.

For these exercices, we keep the database from the previous lab. As a reminder, the schema was:

Wines(WineID: String, vineyard: String, year: Integer, degree: Float)
Harvests(WineID: String, ProducerID: String, weight: Float)
Producers(ProducerID: String, name: String, city: String)
Clients(ClientID: String, name: String, city: String)
Orders(orderID: String, date: Date, ClientID: String, WineID: String, quantity: Float)
Deliveries(orderID: String, status: String)

SQL JOIN (∼45mn – medium)

In this exercice, you are asked to answer the questions directly in SQL. Of course, you are free to draw a relational algebra diagram to help you.

Transform the following query into its equivalent query that uses the keyword JOIN. What does this query do?

select distinct vineyard
from Wines, Orders
where Wines.WineID = Orders.WineID;

This query returns the lists of vineyards that were ordered at least once.
Equivalent query:

select distinct vineyard
from Wines
join Orders
on Wines.WineID = Orders.WineID;

Transform the following query into its equivalent query that uses the keyword JOIN. What does this query do?

select distinct name
from Producers, Harvests
where Producers.ProducerID = Harvests.ProducerID and weight > 100;

This query returns the names of the producers who produced at least once more than 100T during a single harvest.
Equivalent query:

select distinct name
from Producers
join Harvests
on Producers.ProducerID = Harvests.ProducerID
where weight > 100;

Convert the following relational algebra diagram into its equivalent SQL query that uses the keyword JOIN. What does this query do?
Solution

This query gets the status of orders such that the quantity is less than 10.
Equivalent query:

select status
from Orders
join Deliveries
on Orders.orderID = Deliveries.orderID
where quantity < 10;

Convert the following relational algebra diagram into its equivalent SQL query that uses the keyword JOIN. What does this query do?
Solution

This query gives for each city the vineyards produced in this city.
Equivalent query:

select city, vineyard
from Producers
join Harvests
on Producers.producerID = Harvests.producerID
join Wines
on Wines.wineID = Harvests.wineID;

Write in SQL the following query using the keyword JOIN: Give the names of the clients who ordered something on the 24/12/2022.

select name
from Clients
join Orders
on Orders.clientID = Clients.clientID
where date = 24/12/2022;

Write in SQL the following query using the keyword JOIN: Give the vineyards of wines bought by people from Bordeaux.

select vineyard
from Wines
join Orders
on Wines.wineID = Orders.wineID
join Clients
on Orders.clientID = Clients.clientID
where Clients.city = "Bordeaux";

Grouping And Aggregation (∼45mn – medium)

For all the following queries, we want to write the SQL request that answers the query.

What is the oldest year in our wine collection?

select min(year)
from Wines;

What is the total weight produced by "Pierre Poujad"?

select sum(weight)
from Harvests
join Producers
on Harvests.producerID = Producers.producerID
where Producers.name = "Pierre Poujad";

What is the mean degree of wines produced in the city called Puimisson?

select avg(degree)
from Wines
join Harvests
on Harvests.wineID = Wines.wineID
join Producers
on Harvests.producerID = Producers.producerID
where Producers.city = "Puimisson";

What is the date of the most recent order with a status "delivered"?

select max(date)
from Orders
join Deliveries
on Orders.orderID = Deliveries.orderID
where status = "delivered";

Give for each clientID the total quantity of wine they ordered.

select clientID, sum(quantity)
from Orders
group by clientID;

For each city, what is the average quantity ordered?

select city, avg(quantity)
from Orders
join Clients
on Clients.clientID = Orders.clientID
group by city;

Give the list of cities where the total weight harvested is more than 10,000.

select city
from Producers
join Harvests
on Harvests.producerID = Producers.producerID
group by city
having sum(weight) > 10000;

Give the list of client ids who ordered more than 10 times since the 24/12/2022.

select clientID
from Orders
where date > 24/12/2022
group by clientID
having count(*) > 10;

Get the list of producers ids from the city Puimisson who always produce harvests of weight greater than 100 (i.e. the minimum harvest is more than 100).

select producerID
from Producers
join Harvests
on Harvests.producerID = Producers.producerID
where city = "Puimisson"
group by producerID
having min(weight) > 100;

Sort the wines by year, starting from the oldest wines.

select *
from Wines
order by year ASC;

What is the wine with the highest degree. In case of equality, we just output one of them without any preference.

select *
from Wines
order by degree DESC
LIMIT 1;

In which city was the maximal order in term of quantity passed? In case of equality, we pick the first one in the alphabet.

select city, max(quantity) as max_quantity
from Orders
join Clients
on Clients.clientID = Orders.clientID
group by city
order by max_quantity DESC, city ASC
limit 1;