SQL ET POSTGRE 3.13 : Data definition language

It is challenge time. You are going to create a new database named conducteur_voiture (drivers and cars) along with its tables. Your first mission is to create the database itself: it will be called conducteur_voiture. Then you will create a conducteur (driver) table with five columns: id, nom (last name), prenom (first name), departement and date_permis (driving licence date). The data types to use are indicated in the on-screen table.

What you will build

Your next mission is to create a voiture (car) table with a foreign key linking back to the driver. The table contains the columns marque (brand), modele (model), annee (year) and the foreign key conducteur_id.
  • Create the conducteur_voiture database.
  • Create the conducteur table (id, nom, prenom, departement, date_permis).
  • Create the voiture table with a foreign key on conducteur.
  • Add an email VARCHAR(50) column to conducteur.
  • Change the nom column type to VARCHAR(50) instead of 30.
Your fourth mission is to add an email column to the conducteur table — a sixth column of type VARCHAR(50). Finally, you will change the data type of the nom column in the conducteur table from VARCHAR(30) to VARCHAR(50). It is now over to you — pause the video and tackle the challenge. The solution will be presented in the next lesson.

Summary

This lesson provides a hands-on challenge on Data Definition Language (DDL) in PostgreSQL. You'll create a database called "conductors_cars" with two related tables: a "conductors" table (with ID, name, first name, department, and license date fields) and a "cars" table (with ID, brand, model, year, and a foreign key referencing the conductors table). The exercise also covers adding a new email column to the conductors table and modifying the name column data type from VARCHAR(30) to VARCHAR(50).

Key points

  • CREATE DATABASE – Establish a new PostgreSQL database for driver and vehicle management
  • CREATE TABLE with typed columns – Define table structures using appropriate data types (VARCHAR, date types, integers)
  • Foreign key relationships – Link tables by setting the cars table's conductor_id as a foreign key to the conductors table
  • ALTER TABLE – Dynamically add new columns (email) and modify existing column constraints
  • Data type constraints – Use VARCHAR with character limits (30, 50) to enforce data structure and storage efficiency
  • Relational database design – Apply foundational concepts for managing hierarchical data (drivers owning vehicles)

FAQ

What is Data Definition Language (DDL) in PostgreSQL?

DDL includes SQL commands that define and modify database structures, such as CREATE (databases and tables), ALTER (modify schemas), and DROP (remove objects).

Why use a foreign key between the cars and conductors tables?

Foreign keys maintain referential integrity—they ensure every car is linked to a valid conductor and prevent orphaned records if a conductor is deleted.

When would you modify a column's data type after table creation?

Use ALTER TABLE when business requirements change (e.g., expanding the name field from 30 to 50 characters) or to optimize storage and performance as data volume grows.