💻 Примеры

💻 Примеры

Таблица 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 * FROM product_overview;

Таблица погоды и View

CREATE TABLE Weather (
    Id INT AUTO_INCREMENT PRIMARY KEY,
    WeatherDate DATE NOT NULL,
    DayTemperature INT CHECK (DayTemperature BETWEEN -30 AND 30),
    NightTemperature INT CHECK (NightTemperature BETWEEN -30 AND 30),
    WindSpeed DECIMAL(4, 1) CHECK (WindSpeed >= 0)
);

INSERT INTO Weather (WeatherDate, DayTemperature, NightTemperature, WindSpeed)
VALUES ('2025-06-13', 22, 14, 2.5),
       ('2025-06-14', 25, 16, 1.0),
       ('2025-06-15', 19, 11, 5.5),
       ('2025-06-16', 23, 13, 3.0),
       ('2025-06-17', 21, 12, 0.5);

UPDATE Weather
SET NightTemperature = NightTemperature + 1
WHERE WindSpeed <= 3;

CREATE VIEW Weather_Summary_View AS
SELECT
    Id,
    WeatherDate,
    DayTemperature,
    NightTemperature,
    WindSpeed,
    (DayTemperature + NightTemperature) / 2.0 AS AvgDailyTemperature,
    CASE
        WHEN WindSpeed < 2 THEN 'штиль'
        WHEN WindSpeed >= 2 AND WindSpeed < 5 THEN 'умеренный ветер'
        ELSE 'сильный ветер'
    END AS WindCategory
FROM Weather;

SELECT * FROM Weather_Summary_View;