Denormalizatsiya

CREATE TABLE mahsulotlar (
    id   INT PRIMARY KEY AUTO_INCREMENT,
    nom  VARCHAR(100) NOT NULL,
    narx DECIMAL(10,2) NOT NULL
);

CREATE TABLE buyurtmalar (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    mijoz    VARCHAR(100) NOT NULL,
    sana     DATE NOT NULL
);

CREATE TABLE buyurtma_qatorlari (
    buyurtma_id  INT NOT NULL,
    mahsulot_id  INT NOT NULL,
    soni         INT NOT NULL,
    narx_sotilgan DECIMAL(10,2) NOT NULL,   -- ATAYLAB nusxalangan
    PRIMARY KEY (buyurtma_id, mahsulot_id),
    FOREIGN KEY (buyurtma_id) REFERENCES buyurtmalar(id),
    FOREIGN KEY (mahsulot_id) REFERENCES mahsulotlar(id)
);

INSERT INTO mahsulotlar (nom, narx) VALUES
    ('Noutbuk', 8000000), ('Sichqoncha', 150000);

INSERT INTO buyurtmalar (mijoz, sana) VALUES
    ('Husanboy', '2026-01-10'), ('Malika', '2026-03-15');

INSERT INTO buyurtma_qatorlari VALUES
    (1, 1, 1, 8000000),
    (1, 2, 2, 150000),
    (2, 1, 1, 8000000);

-- Mahsulot narxi ko'tarildi
UPDATE mahsulotlar SET narx = 9500000 WHERE id = 1;

SELECT b.id AS buyurtma, b.sana, m.nom,
       q.narx_sotilgan AS sotilgan_narx,
       m.narx AS bugungi_narx
FROM buyurtma_qatorlari q
JOIN buyurtmalar b  ON b.id = q.buyurtma_id
JOIN mahsulotlar m  ON m.id = q.mahsulot_id
WHERE m.id = 1
ORDER BY b.id;

-- Har safar hisoblash
SELECT b.id, b.mijoz, SUM(q.soni * q.narx_sotilgan) AS jami
FROM buyurtmalar b
JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
GROUP BY b.id, b.mijoz
ORDER BY b.id;

ALTER TABLE buyurtmalar ADD COLUMN jami DECIMAL(12,2) NOT NULL DEFAULT 0;

UPDATE buyurtmalar b
SET jami = (
    SELECT COALESCE(SUM(q.soni * q.narx_sotilgan), 0)
    FROM buyurtma_qatorlari q
    WHERE q.buyurtma_id = b.id
);

SELECT id, mijoz, jami FROM buyurtmalar ORDER BY id;

-- Dasturchi qatorni o'chirdi, lekin jami ni yangilashni unutdi
DELETE FROM buyurtma_qatorlari WHERE buyurtma_id = 1 AND mahsulot_id = 2;

SELECT b.id, b.jami AS saqlangan,
       COALESCE(SUM(q.soni * q.narx_sotilgan), 0) AS haqiqiy
FROM buyurtmalar b
LEFT JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
WHERE b.id = 1
GROUP BY b.id, b.jami;

CREATE TABLE qatorlar_avtomatik (
    id           INT PRIMARY KEY AUTO_INCREMENT,
    soni         INT NOT NULL,
    narx         DECIMAL(10,2) NOT NULL,
    summa        DECIMAL(12,2) AS (soni * narx) STORED
);

INSERT INTO qatorlar_avtomatik (soni, narx) VALUES (3, 150000), (1, 8000000);

SELECT id, soni, narx, summa FROM qatorlar_avtomatik ORDER BY id;

-- Uni qo'lda o'zgartirib bo'lmaydi
INSERT INTO qatorlar_avtomatik (soni, narx, summa) VALUES (1, 100, 999);

ERROR 1906 (HY000): The value specified for generated column 'summa'
in table 'qatorlar_avtomatik' is not allowed

-- soni o'zgarsa, summa AVTOMATIK yangilanadi
UPDATE qatorlar_avtomatik SET soni = 5 WHERE id = 1;

SELECT id, soni, narx, summa FROM qatorlar_avtomatik WHERE id = 1;

CREATE TABLE izohlar (
    id             INT PRIMARY KEY AUTO_INCREMENT,
    matn           TEXT NOT NULL,
    muallif_id     INT NOT NULL,
    muallif_ismi   VARCHAR(100) NOT NULL,   -- nusxalangan
    yaratilgan     DATETIME NOT NULL
);

INSERT INTO izohlar (matn, muallif_id, muallif_ismi, yaratilgan) VALUES
    ('Ajoyib maqola', 1, 'Husanboy', '2026-01-10 10:00:00'),
    ('Rahmat',        1, 'Husanboy', '2026-01-11 11:00:00'),
    ('Foydali',       2, 'Malika',   '2026-01-12 12:00:00');

-- JOIN kerak emas
SELECT muallif_ismi, matn FROM izohlar ORDER BY id;

CREATE TABLE kunlik_hisobot (
    sana            DATE PRIMARY KEY,
    buyurtmalar     INT NOT NULL,
    jami_summa      DECIMAL(14,2) NOT NULL,
    yangilangan     DATETIME NOT NULL
);

INSERT INTO kunlik_hisobot (sana, buyurtmalar, jami_summa, yangilangan)
SELECT b.sana,
       COUNT(DISTINCT b.id),
       COALESCE(SUM(q.soni * q.narx_sotilgan), 0),
       '2026-03-16 03:00:00'
FROM buyurtmalar b
LEFT JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
GROUP BY b.sana;

SELECT sana, buyurtmalar, jami_summa FROM kunlik_hisobot ORDER BY sana;