Chacun sa voiture⚓︎

On s'intéresse ici à la base de données car_database regroupant diverses informations sur des voitures :

marque et modèle,
numéro d'immatriculation (vehicle immatriculation number, vin dans les tables),
année de fabrication,
nom du propriétaire,
options,
etc...

Les différentes tables sont représentées ci-dessous :

La base car_database

Sur cette figure :

chaque tableau correspond à une table dont le nom est indiqué sur la première ligne ;
les lignes suivantes listent les attributs et leur type. varchar(255) signifie que l'attribut est un texte de 255 caractères au maximum ;
les clés primaires de chaque table sont indiquées en gras. Notez que la table ownerships a une clé primaire multiple ;
les clés étrangères sont représentées par des liaisons entre les tables.

Cette base est téléchargeable au format sqlite ici. Ce fichier a été simplifié à partir de cette source.

Quelles marques (brand en anglais) sont présentes dans la base ?
Votre réponse
SQL
Réponse
🗂️ Requête SQL
```
SELECT brand_name
FROM brands;
```
Quels sont les noms de famille des clients ?
Votre réponse
SQL
Réponse
🗂️ Requête SQL
```
SELECT last_name
FROM customers;
```
Quels sont les noms des modèles de la marque « Supreme » ?
Votre réponse
SQL
Réponse
🗂️ Requête SQL
```
SELECT model_name
FROM models
JOIN brands ON brands.brand_id = models.brand_id
WHERE brand_name = 'Supreme';
```
Quel est le prix d'achat (purchase_price) de la voiture possédée par « Maria Swabota » ?
Votre réponse
SQL
Réponse
🗂️ Requête SQL
```
SELECT purchase_price
FROM ownerships
JOIN customers ON ownerships.customer_id = customers.customer_id
WHERE last_name = 'Swabota' AND first_name = 'Maria';
```
Quelle est la date de fabrication (manufactured_date) de la voiture possédée par « Maria Swabota » ?
Votre réponse
SQL
Réponse
🗂️ Requête SQL
```
SELECT manufactured_date
FROM ownerships
JOIN customers ON ownerships.customer_id = customers.customer_id
JOIN cars ON ownerships.vin = cars.vin
WHERE last_name = 'Swabota' AND first_name = 'Maria';
```

Quelle est la marque de la voiture possédée par « Maria Swabota » ?

Votre réponse

SQL

Réponse

🗂️ Requête SQL

SELECT brand_name
FROM ownerships
JOIN customers ON ownerships.customer_id = customers.customer_id
JOIN cars ON ownerships.vin = cars.vin
JOIN models ON models.model_id = cars.model_id
JOIN brands ON models.brand_id = brands.brand_id
WHERE last_name = 'Swabota' AND first_name = 'Maria';

Quels modèles de voiture sont équipés de 4 roues motrices (elles ont un chassis dont le part_name vaut '4WD Chassis') ?
Votre réponse
SQL
Réponse
🗂️ Requête SQL
```
SELECT model_name
FROM models
JOIN options ON options.model_id = models.model_id
JOIN parts ON parts.part_id = options.chassis_id
WHERE part_name= '4WD Chassis';
```
Reprendre la requête précédente en donnant aussi la marque des véhicules.
Votre réponse
SQL
Réponse
🗂️ Requête SQL
```
SELECT model_name, brand_name
FROM models
JOIN brands ON brands.brand_id = models.brand_id
JOIN options ON options.model_id = models.model_id
JOIN parts ON parts.part_id = options.chassis_id
WHERE part_name = '4WD Chassis';
```
Combien de Ferrari ont été vendues ?
Votre réponse
SQL
Réponse
🗂️ Requête SQL
```
SELECT COUNT(ownerships.vin)
FROM ownerships
JOIN cars ON ownerships.vin = cars.vin
JOIN models ON models.model_id = cars.model_id
JOIN brands ON brands.brand_id = models.brand_id
WHERE brand_name = 'Ferrari';
```
Quelle voiture, de quel modèle, de quelle marque et appartenant à qui a coûté le plus cher ?

Astuce

Trier les résultats par prix décroissant et ne garder que le premier avec LIMIT 1.
Votre réponse
SQL
Réponse
🗂️ Requête SQL
```
SELECT purchase_price, model_name, brand_name, last_name, first_name
FROM ownerships
JOIN customers ON ownerships.customer_id = customers.customer_id
JOIN cars ON ownerships.vin = cars.vin
JOIN models ON models.model_id = cars.model_id
JOIN brands ON brands.brand_id = models.brand_id
ORDER BY purchase_price DESC
LIMIT 1;
```