src/main/resources/schema.sql - master - 86/practice

src/main/resources/schema.sql
68 строк2 KB
Danil Tkach
init
18 июн 2026, 20:12
18 июн 2026, 20:12e148f75
CREATE TABLE IF NOT EXISTS regions
(
    id   BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS countries
(
    id        BIGINT PRIMARY KEY AUTO_INCREMENT,
    region_id BIGINT       NOT NULL,
    name      VARCHAR(100) NOT NULL UNIQUE,
    INDEX idx_countries_region_id (region_id),
    CONSTRAINT fk_countries_regions
        FOREIGN KEY (region_id) REFERENCES regions (id)
            ON UPDATE CASCADE
            ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS product_types
(
    id   BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS sales
(
    id              BIGINT PRIMARY KEY AUTO_INCREMENT,
    country_id      BIGINT NOT NULL,
    product_type_id BIGINT NOT NULL,
    quantity        INT    NOT NULL,
    INDEX idx_sales_country_id (country_id),
    INDEX idx_sales_product_type_id (product_type_id),
    INDEX idx_sales_quantity (quantity),
    CONSTRAINT chk_sales_quantity CHECK (quantity >= 0),
    CONSTRAINT uq_sales_country_product UNIQUE (country_id, product_type_id),
    CONSTRAINT fk_sales_countries
        FOREIGN KEY (country_id) REFERENCES countries (id)
            ON UPDATE CASCADE
            ON DELETE RESTRICT,
    CONSTRAINT fk_sales_product_types
        FOREIGN KEY (product_type_id) REFERENCES product_types (id)
            ON UPDATE CASCADE
            ON DELETE RESTRICT
);

CREATE OR REPLACE VIEW v_sales_full AS
SELECT s.id,
       r.name AS region_name,
       c.name AS country_name,
       p.name AS product_type_name,
       s.quantity
FROM sales s
         JOIN countries c ON c.id = s.country_id
         JOIN regions r ON r.id = c.region_id
         JOIN product_types p ON p.id = s.product_type_id;

CREATE OR REPLACE VIEW v_max_sales_by_product AS
SELECT p.name AS product_type_name,
       c.name AS country_name,
       r.name AS region_name,
       s.quantity
FROM sales s
         JOIN product_types p ON p.id = s.product_type_id
         JOIN countries c ON c.id = s.country_id
         JOIN regions r ON r.id = c.region_id
         JOIN (SELECT product_type_id, MAX(quantity) AS max_quantity
               FROM sales
               GROUP BY product_type_id) m ON m.product_type_id = s.product_type_id AND m.max_quantity = s.quantity;