← К оглавлению занятия

✅ Решения

⚡ Кратко: решения

  • Задание 1 — CREATE TABLE products с ограничениями.
  • Задание 2 — INSERT INTO products VALUES ....
  • Задание 3 — UPDATE products SET stock_quantity = stock_quantity - 3 WHERE product_id = 1.
  • Задание 4 — CREATE VIEW product_overview AS SELECT ....
  • Задание 5 — таблица health_metrics с CHECK-ограничениями.
1

Решение задания 1

Создание таблицы 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
);
2

Решение задания 2

Вставка пяти строк.

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);
3

Решение задания 3

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

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

Решение задания 4

Создание представления 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;
5

Решение задания 5

Таблица мониторинга показателей здоровья.

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);