💻 Примеры

💻 Примеры

Создание таблицы products

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) DEFAULT 'General',
    price DECIMAL(8, 2) CHECK (price >= 0),
    stock_quantity INT CHECK (stock_quantity >= 0),
    supplier_id INT
);

Вставка данных

INSERT INTO products (product_name, category, price, stock_quantity, supplier_id)
VALUES ('Laptop', 'Electronics', 1200.00, 10, 1),
       ('Office Chair', 'Furniture', 150.00, 25, 2),
       ('Pen', 'Stationery', 1.50, 100, 3),
       ('Desk Lamp', 'Furniture', 45.00, 15, 2),
       ('Notebook', 'Stationery', 3.00, 200, 3);

Обновление остатков

UPDATE products
SET stock_quantity = stock_quantity - 3
WHERE product_id = 1;

Создание представления product_overview

CREATE VIEW product_overview AS
SELECT
    product_name,
    category,
    price * stock_quantity AS stock_value,
    CASE
        WHEN stock_quantity < 20 THEN 'Low Stock'
        ELSE 'In Stock'
    END AS stock_status
FROM products;

Запрос к представлению

SELECT product_name, stock_value, stock_status
FROM product_overview
WHERE stock_status = 'Low Stock';

Пример таблицы мониторинга здоровья

CREATE TABLE health_metrics (
    metric_id INT AUTO_INCREMENT PRIMARY KEY,
    person_name VARCHAR(100) NOT NULL,
    measurement_date DATE NOT NULL,
    heart_rate INT CHECK (heart_rate BETWEEN 30 AND 220),
    systolic_bp INT CHECK (systolic_bp BETWEEN 70 AND 250),
    diastolic_bp INT CHECK (diastolic_bp BETWEEN 40 AND 150),
    weight_kg DECIMAL(5, 2) CHECK (weight_kg > 0),
    notes TEXT
);

INSERT INTO health_metrics (person_name, measurement_date, heart_rate, systolic_bp, diastolic_bp, weight_kg)
VALUES ('Alice', '2025-06-15', 72, 120, 80, 65.50),
       ('Bob', '2025-06-16', 68, 115, 75, 78.00),
       ('Charlie', '2025-06-17', 75, 122, 82, 70.25);