Transactional Statements

-- Generate a table for sample car models.
DROP TABLE IF EXISTS CarPart;
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);
-- 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)
);
-- Populate the generated table with sample car model parts.
INSERT INTO CarPart (model, name, built_at)
VALUES (1, 'exterior', '2022-03-28'),
       (1, 'interior', NULL),
       (1, 'wheels', '2022-03-28'),
       (2, 'exterior', '2022-03-28'),
       (2, 'interior', '2022-03-28'),
       (2, 'wheels', '2022-03-28'),
       (3, 'exterior', '2022-03-27'),
       (3, 'interior', '2022-03-28'),
       (3, 'wheels', '2022-03-28'),
       (4, 'exterior', '2022-03-26'),
       (4, 'interior', NULL),
       (4, 'wheels', NULL),
       (5, 'exterior', '2022-03-28'),
       (5, 'interior', '2022-03-28'),
       (5, 'wheels', '2022-03-28'),
       (6, 'exterior', NULL),
       (6, 'interior', NULL),
       (6, 'wheels', NULL),
       (7, 'exterior', '2022-03-27'),
       (7, 'interior', NULL),
       (7, 'wheels', NULL),
       (8, 'exterior', NULL),
       (8, 'interior', NULL),
       (8, 'wheels', NULL),
       (9, 'exterior', '2022-03-26'),
       (9, 'interior', '2022-03-27'),
       (9, 'wheels', '2022-03-28'),
       (10, 'exterior', '2022-03-24'),
       (10, 'interior', '2022-03-26'),
       (10, 'wheels', '2022-03-28');
-- Update `built_at` for newly assembled parts of the car model with `id = 4`.
UPDATE CarPart
SET built_at = CURRENT_DATE
WHERE model = 4;