SQL ET POSTGRE 4.21 : Data manipulation challenge

Now that we know how to insert, update and delete data, it is challenge time. You will be working on the conducteur_voiture database you built in the previous challenge. The first part of the challenge is to insert seven rows into the conducteur (driver) table. As a bonus difficulty, your goal is to perform this insertion in a single SQL statement — you have already seen how to chain multiple VALUES tuples with commas.

Cars, update and delete

The second part is to populate the voiture (car) table in the same way: six rows, ideally inserted in a single statement. Then comes the update: change the year of the Ford Mustang to 2010 — the current value in the table is 2000 and it needs to be corrected. Finally, you will delete Rémi Oberty from the conducteur table.
  • Insert seven drivers into conducteur.
  • Insert six cars into voiture with the matching conducteur_id.
  • Update Ford Mustang's year to 2010.
  • Delete Rémi Oberty from conducteur.
UPDATE voiture SET annee = 2010 WHERE marque = 'Ford' AND modele = 'Mustang';
DELETE FROM conducteur WHERE nom = 'Oberty';
Pause the video and try to solve all four tasks on your own. The full solution will be presented in the next lesson with the corresponding SQL queries. Good luck.

Summary

This lesson presents a practical SQL data manipulation challenge requiring students to insert driver and car records into their previously created tables, update the Ford Mustang's manufacturing year from 2000 to 2010, and delete a specific driver record. The exercise reinforces INSERT, UPDATE, and DELETE operations in PostgreSQL through hands-on application.

Key points

  • Master INSERT statements to populate both driver (conducteur) and car (voiture) tables with provided datasets
  • Apply UPDATE operations to modify existing records, specifically changing the Ford Mustang's year to 2010
  • Execute DELETE statements to remove specific records from tables, removing Rémy Oberti from the driver table
  • Integrate multiple CRUD operations in a realistic multi-table database scenario

FAQ

What tables are involved in this challenge?

The challenge uses two tables previously created: the 'conducteur' (driver) table and the 'voiture' (car) table, which form part of a driver-car management database.

What UPDATE operation is required in this challenge?

The Ford Mustang's manufacturing year must be updated from 2000 to 2010 using an UPDATE statement to correct the existing record.

Which record needs to be deleted and from which table?

Rémy Oberti must be deleted from the 'conducteur' (driver) table using a DELETE statement.