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.
select vineyard
from Wines;
Turn the following relational algebra schema into SQL.
select *
from Wines
where year < 1950;
Turn the following relational algebra schema into SQL.
select *
from Wines, Harvests;
Turn the following relational algebra schema into SQL.
select vineyard
from Wines
where year < 1950;
Turn the following relational algebra schema into SQL.
select city
from Harvests, Producers
where weight > 1 and Producers.ProducerID = Harvests.ProducerID;
Turn the following relational algebra schema into SQL.
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.
or
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;
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";
