JOIN - jadvallarni birlashtirish

SELECT
    t.ism,
    t.familiya,
    g.nomi AS guruh
FROM talabalar t
INNER JOIN guruhlar g ON g.id = t.guruh_id;

-- Barcha guruhlar, hatto talabasi bo'lmagani ham
SELECT
    g.nomi AS guruh,
    t.ism,
    t.familiya
FROM guruhlar g
LEFT JOIN talabalar t ON t.guruh_id = g.id;

-- Talabasi bo'lmagan guruhlar
SELECT g.nomi
FROM guruhlar g
LEFT JOIN talabalar t ON t.guruh_id = g.id
WHERE t.id IS NULL;

-- Hech qachon buyurtma bermagan mijozlar
SELECT m.ism, m.email
FROM mijozlar m
LEFT JOIN buyurtmalar b ON b.mijoz_id = m.id
WHERE b.id IS NULL;

SELECT
    m.ism                AS mijoz,
    b.id                 AS buyurtma,
    mh.nomi              AS mahsulot,
    be.soni,
    be.narx,
    be.soni * be.narx    AS jami,
    k.nomi               AS kategoriya
FROM buyurtmalar b
JOIN mijozlar m              ON m.id  = b.mijoz_id
JOIN buyurtma_elementlari be ON be.buyurtma_id = b.id
JOIN mahsulotlar mh          ON mh.id = be.mahsulot_id
JOIN kategoriyalar k         ON k.id  = mh.kategoriya_id
WHERE b.holat = 'tolangan'
ORDER BY b.yaratilgan DESC;

-- Har bir guruhdagi talabalar soni va o'rtacha bahosi
SELECT
    g.nomi                          AS guruh,
    COUNT(t.id)                     AS talabalar,
    ROUND(AVG(t.ortacha_baho), 2)   AS ortacha,
    MAX(t.ortacha_baho)             AS eng_yuqori
FROM guruhlar g
LEFT JOIN talabalar t ON t.guruh_id = g.id
GROUP BY g.id, g.nomi
ORDER BY ortacha DESC;

-- Eng ko'p sotilgan mahsulotlar
SELECT
    mh.nomi,
    SUM(be.soni)                AS sotilgan,
    SUM(be.soni * be.narx)      AS tushum,
    COUNT(DISTINCT b.mijoz_id)  AS mijozlar
FROM mahsulotlar mh
JOIN buyurtma_elementlari be ON be.mahsulot_id = mh.id
JOIN buyurtmalar b           ON b.id = be.buyurtma_id
WHERE b.holat IN ('tolangan', 'yetkazildi')
GROUP BY mh.id, mh.nomi
ORDER BY tushum DESC
LIMIT 10;

CREATE TABLE xodimlar (
    id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ism       VARCHAR(120) NOT NULL,
    lavozim   VARCHAR(80),
    rahbar_id INT UNSIGNED NULL,

    FOREIGN KEY (rahbar_id) REFERENCES xodimlar (id) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT INTO xodimlar (ism, lavozim, rahbar_id) VALUES
    ('Aziz Karimov',    'Direktor',       NULL),
    ('Dilnoza Yusupova','Bo''lim boshlig''i', 1),
    ('Sardor Aliyev',   'Dasturchi',      2),
    ('Malika Tosheva',  'Dasturchi',      2);

-- Har bir xodim va uning rahbari
SELECT
    x.ism      AS xodim,
    x.lavozim,
    r.ism      AS rahbar
FROM xodimlar x
LEFT JOIN xodimlar r ON r.id = x.rahbar_id;

-- Har bir o'lcham va rang kombinatsiyasi
SELECT o.nomi AS olcham, r.nomi AS rang
FROM olchamlar o
CROSS JOIN ranglar r;

SELECT ism, 'talaba' AS turi FROM talabalar
UNION
SELECT ism, 'o''qituvchi' FROM oqituvchilar
ORDER BY ism;

-- Takrorlarni ham qoldirish (tezroq)
SELECT shahar FROM talabalar
UNION ALL
SELECT shahar FROM oqituvchilar;

-- 1. Har bir talabaning fanlari va baholari
SELECT
    CONCAT(t.ism, ' ', t.familiya) AS talaba,
    f.nomi                          AS fan,
    tf.baho
FROM talabalar t
JOIN talaba_fan tf ON tf.talaba_id = t.id
JOIN fanlar f      ON f.id = tf.fan_id
ORDER BY talaba, fan;

-- 2. Hech qanday fan olmagan talabalar
SELECT t.ism, t.familiya
FROM talabalar t
LEFT JOIN talaba_fan tf ON tf.talaba_id = t.id
WHERE tf.talaba_id IS NULL;

-- 3. Har bir fan bo'yicha statistika
SELECT
    f.nomi                    AS fan,
    COUNT(tf.talaba_id)       AS talabalar,
    ROUND(AVG(tf.baho), 2)    AS ortacha_baho,
    SUM(tf.baho >= 4)         AS yaxshi_baholar
FROM fanlar f
LEFT JOIN talaba_fan tf ON tf.fan_id = f.id
GROUP BY f.id, f.nomi
ORDER BY ortacha_baho DESC;

-- 4. Mijozlar va ularning buyurtmalari summasi
SELECT
    m.ism,
    m.email,
    COUNT(DISTINCT b.id)                  AS buyurtmalar,
    IFNULL(SUM(be.soni * be.narx), 0)     AS jami_xarid,
    MAX(b.yaratilgan)                     AS oxirgi_buyurtma
FROM mijozlar m
LEFT JOIN buyurtmalar b           ON b.mijoz_id = m.id AND b.holat <> 'bekor'
LEFT JOIN buyurtma_elementlari be ON be.buyurtma_id = b.id
GROUP BY m.id, m.ism, m.email
ORDER BY jami_xarid DESC;