Mastering Advanced SQL Techniques with MySQL/

...

/

Common Table Expressions

Common Table Expressions

Learn how to separate a subquery from its enclosing query for more comprehensive and readable SQL code.

We'll cover the following...

In our running example, we consider car models and the assembly of their parts. In SQL, we modeled this example through a table called CarModel that we populated with sample cars:

MySQL
-- Generate a table for sample car models.
DROP TABLE IF EXISTS CarModel;
CREATE TABLE CarModel
(
    id           INT auto_increment,
    manufacturer TEXT DEFAULT NULL,
    name         TEXT DEFAULT NULL,
    `power (kW)` INT  DEFAULT NULL,
    PRIMARY KEY (id)
);
-- Populate the generated table with sample car models.
INSERT INTO CarModel (manufacturer, name, `power (kW)`)
VALUES ('Audi', 'A3', 81),
       ('Audi', 'A4', 100),
       ('Audi', 'A5', 110),
       ('Audi', 'A6', 210),
       ('Audi', 'A7', 150),
       ('Audi', 'A8', 210),
       ('Mercedes Benz', 'C63 AMG', 350),
       ('Mercedes Benz', 'S63 AMG', 450),
       ('Volkswagen', 'Golf VII', 92),
       ('Volkswagen', 'Golf VIII', 110);
TABLE CarModel;

Since these cars are assembled from different parts, we also record these in a separate table called CarPart. This also allows us to oversee our assembly progress. As the database engineer, we propose the following data model for the car parts:

MySQL
-- Generate a table for sample car model parts.
DROP TABLE IF EXISTS CarPart;
CREATE TABLE CarPart
(
    model    INT,
    name     TEXT,
    built_at DATE DEFAULT NULL,
    PRIMARY KEY (model, name(256)),
    FOREIGN KEY (model) REFERENCES CarModel (id)
);

The database table CarPart consists of three columns, namely model, name, and built_at. The first column, model, references the car model that this part belongs to as a foreign key (i.e., FOREIGN KEY (model) REFERENCES CarModel (id)). name records the name of the car part and constitutes the primary key of CarPart together with model (i.e., PRIMARY KEY (model, name(256))). For our example, we ...