Common Table Expressions
Learn how to separate a subquery from its enclosing query for more comprehensive and readable SQL code.
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:
-- 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:
-- 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 ...