INF 4401 – Introduction To Databases

Portail informatique

CI2 : SQL 1

Introduction to 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)

from Relational Algebra To SQL (∼45mn – medium)

Turn the following relational algebra schema into SQL.
Solution
select vineyard from Wines;

Turn the following relational algebra schema into SQL.
Solution
select * from Wines where year < 1950;

Turn the following relational algebra schema into SQL.
Solution
select * from Wines, Harvests;

Turn the following relational algebra schema into SQL.
Solution
select vineyard from Wines where year < 1950;

Turn the following relational algebra schema into SQL.
Solution
select city from Harvests, Producers where weight > 1 and Producers.ProducerID = Harvests.ProducerID;

Turn the following relational algebra schema into SQL.
Solution
select name from Producers intersect select name from Clients where city = "Paris";

Queries in SQL (∼45mn – medium)

Now, try to answer the following queries directly in SQL. Of course, it still helps to think about the relational algebra schema and to write it on a draft if necessary.

Give the name of the clients living in Lyon or Bordeaux.
select name from Clients where city = "Lyon" or city = "Bordeaux"

Get the WineID and ClientID of orders such that the quantity is more than 100.
select WineID, ClientID from Orders where quantity > 100;

Get the WineID of wines such that the vineyard is Chinon and it was ordered by the client with ID equal to 2039.
select WineID from Wines where vineyard = "Chinon" intersect select WineID from Orders where ClientID = 2039;
or
select Wines.WineID from Wines, Orders where vineyard = "Chinon" and ClientID = 2039 and Wines.WineID = Orders.WineID;

Get the vineyard of wines ordered on the 24/12/2022.
select vineyard from Wines, Orders where date = 24/12/2022 and Wines.WineID = Orders.WineID;

Get the vineyard of wines produced by Jean-Francois Coutelou.
select vineyard from Wines, Harvests, Producers where Wines.WineID = Harvests.WineID and Harvests.ProducerID = Producers.ProducerID and name = "Jean-Francois Coutelou";