CORRECTION TP Base de données (CCMP 2022)

1) SELECT name FROM material; 

2) SELECT name FROM material WHERE curie_temp < 500; 

3) SELECT * FROM material WHERE curie_temp < 600 or density < 8; 

4) SELECT MIN(kg_price), AVG(kg_price), MAX(kg_price) FROM price;

5) SELECT DISTINCT material.name FROM material 
JOIN price ON price.id_material = material.id_material 
ORDER BY material.name DESC; 

6) SELECT name, 1000 * kg_price FROM supplier 
JOIN price ON supplier.id_supplier = price.id_supplier 
WHERE price.id_material = 8713;

7) SELECT name, id_material, kg_price FROM supplier 
JOIN price ON supplier.id_supplier = price.id_supplier 
ORDER BY name, kg_price; 

8) SELECT supplier.name, material.name, kg_price FROM supplier 
JOIN price ON supplier.id_supplier = price.id_supplier 
JOIN material on price.id_material = material.id_material 
ORDER BY supplier.name, kg_price DESC; 

9) SELECT material.name FROM material
JOIN price on price.id_material = material.id_material
JOIN supplier on price.id_supplier = supplier.id_supplier
WHERE supplier.country = "France";

10) SELECT kg_price FROM price
WHERE id_material = (SELECT id_material FROM material WHERE name = "nickel");

11) SELECT name FROM supplier WHERE id_supplier IN 
(SELECT id_supplier FROM price WHERE id_material = 
(SELECT id_material FROM material WHERE name = "nickel"));

12) SELECT name, 1000 * kg_price FROM supplier
JOIN price ON supplier.id_supplier = price.id_supplier
WHERE price.id_material = 
(SELECT id_material FROM material WHERE name = "nickel");

13) SELECT name, 1000 * kg_price FROM supplier
JOIN price ON supplier.id_supplier = price.id_supplier
WHERE price.id_material = 8713 and price.kg_price = 
(SELECT MIN(kg_price) FROM price WHERE id_material = 8713);

14) SELECT name, 1000 * kg_price FROM supplier
JOIN price ON supplier.id_supplier = price.id_supplier
WHERE price.id_material = 
(SELECT id_material FROM material WHERE name = "nickel") 
and 
price.kg_price = 
(SELECT MIN(kg_price) FROM price WHERE id_material = (SELECT id_material FROM material WHERE name = "nickel"));

15) SELECT name, AVG(kg_price) FROM material
JOIN price ON material.id_material = price.id_material
GROUP BY price.id_material
ORDER BY price.kg_price DESC;

16) SELECT name, AVG(kg_price) FROM material
JOIN price ON material.id_material = price.id_material
GROUP BY price.id_material
HAVING AVG(price.kg_price) < 100
ORDER BY price.kg_price ASC;