SQL ET POSTGRE 4.22 : Data manipulation solution

Congratulations to everyone who completed the previous challenge. In this video we walk through the solution step by step. We work on the conducteur_voiture database. Right-click on the database, choose Create Script, and start by formatting dates to the European style with SET DATESTYLE = 'SQL, European'; — not strictly required, but it makes day-month-year display easier to read.

Inserting drivers and cars

Mission 1 is to populate the conducteur table with seven drivers. We write a single INSERT INTO conducteur (numero, nom, prenom, departement, date_permis, email) VALUES (...), (...), (...);, where each tuple corresponds to one driver. Remember to quote string values (including dates in 'dd-mm-yyyy' format) and to leave integers unquoted. We end the query with a semicolon, run it and verify with SELECT * FROM conducteur — the seven drivers are loaded and ids have been incremented automatically. 
INSERT INTO voiture (marque, modele, annee, conducteur_id) VALUES
  ('Renault', 'Clio', 2015, 1),
  ('Ford',    'Mustang', 2000, 4),
  ...;

UPDATE voiture SET annee = 2010 WHERE id = 4;

DELETE FROM conducteur WHERE nom = 'Oberty';
  • Insert seven drivers in a single INSERT INTO.
  • Insert six cars in a single INSERT INTO, linking them via conducteur_id.
  • Use UPDATE voiture SET annee = 2010 WHERE id = 4.
  • Use DELETE FROM conducteur WHERE nom = 'Oberty'.
Mission 2 is the same pattern for the voiture table: a single INSERT INTO with the six car rows, each carrying the matching conducteur_id. Verify with SELECT * FROM voiture. Mission 3 is the update: UPDATE voiture SET annee = 2010 WHERE id = 4 — that is the row for the Ford Mustang. The check confirms the year is now 2010. Mission 4 deletes Rémi Oberty from the drivers: DELETE FROM conducteur WHERE nom = 'Oberty'; (you could also use WHERE prenom = 'Rémi'). A final SELECT * FROM conducteur shows that Rémi is gone. Well done.

Summary

This lesson demonstrates step-by-step solutions for SQL data manipulation operations on a driver-car database. You will learn how to insert records into the driver and vehicle tables using INSERT INTO statements, update existing data such as modifying a car's production year with UPDATE, and delete specific records from tables with DELETE FROM. Each operation is verified using SELECT queries to confirm successful execution.

Key points

  • Use INSERT INTO statements to add new records to tables by specifying field names and corresponding values in the correct order
  • Auto-increment primary keys (IDs) populate automatically during record insertion, so you only need to specify other columns
  • Use UPDATE statements with WHERE clauses to modify existing data in specific records
  • Use DELETE FROM statements with WHERE clauses to remove specific records from tables
  • Always verify data manipulation operations with SELECT queries after each INSERT, UPDATE, or DELETE to confirm changes were successful

FAQ

Why don't we specify the ID column when inserting data?

The ID column is an auto-increment primary key that automatically generates a unique value for each new record, so you only need to supply values for the other columns.

What is critical when using UPDATE or DELETE statements?

Always include a WHERE clause to target specific records; without it, the operation will affect all records in the entire table.

How can you verify that data manipulation operations completed successfully?

Execute a SELECT query on the table after each INSERT, UPDATE, or DELETE operation to visually confirm that the changes were applied correctly.