Ko'p-ga-ko'p bog'lanish

CREATE TABLE talabalar (
    id  INT PRIMARY KEY AUTO_INCREMENT,
    ism VARCHAR(100) NOT NULL
);

CREATE TABLE fanlar (
    id     INT PRIMARY KEY AUTO_INCREMENT,
    nom    VARCHAR(50) NOT NULL UNIQUE,
    kredit TINYINT NOT NULL
);

CREATE TABLE yozilishlar (
    talaba_id INT NOT NULL,
    fan_id    INT NOT NULL,
    PRIMARY KEY (talaba_id, fan_id),
    FOREIGN KEY (talaba_id) REFERENCES talabalar(id) ON DELETE CASCADE,
    FOREIGN KEY (fan_id)    REFERENCES fanlar(id)    ON DELETE CASCADE
);

INSERT INTO talabalar (ism) VALUES ('Husanboy'), ('Malika'), ('Kamola');

INSERT INTO fanlar (nom, kredit) VALUES
    ('Matematika', 6), ('Fizika', 4), ('Ingliz tili', 3);

INSERT INTO yozilishlar (talaba_id, fan_id) VALUES
    (1, 1), (1, 2), (1, 3),
    (2, 1), (2, 3),
    (3, 2);

SELECT t.ism, GROUP_CONCAT(f.nom ORDER BY f.nom SEPARATOR ', ') AS fanlar
FROM talabalar t
JOIN yozilishlar y ON y.talaba_id = t.id
JOIN fanlar f      ON f.id = y.fan_id
GROUP BY t.id, t.ism
ORDER BY t.id;

SELECT f.nom AS fan, COUNT(y.talaba_id) AS talabalar_soni
FROM fanlar f
LEFT JOIN yozilishlar y ON y.fan_id = f.id
GROUP BY f.id, f.nom
ORDER BY talabalar_soni DESC, f.nom;

CREATE TABLE yozilishlar_toliq (
    talaba_id   INT NOT NULL,
    fan_id      INT NOT NULL,
    yozilgan    DATE NOT NULL,
    baho        TINYINT NULL,
    holat       ENUM('faol', 'tugatgan', 'tashlagan') NOT NULL DEFAULT 'faol',
    PRIMARY KEY (talaba_id, fan_id),
    FOREIGN KEY (talaba_id) REFERENCES talabalar(id),
    FOREIGN KEY (fan_id)    REFERENCES fanlar(id)
);

INSERT INTO yozilishlar_toliq (talaba_id, fan_id, yozilgan, baho, holat) VALUES
    (1, 1, '2026-09-01', 5,    'tugatgan'),
    (1, 2, '2026-09-01', NULL, 'faol'),
    (2, 1, '2026-09-02', 4,    'tugatgan'),
    (3, 2, '2026-09-01', NULL, 'tashlagan');

SELECT t.ism, f.nom AS fan, y.holat, IFNULL(y.baho, 0) AS baho
FROM yozilishlar_toliq y
JOIN talabalar t ON t.id = y.talaba_id
JOIN fanlar f    ON f.id = y.fan_id
ORDER BY t.id, f.id;

CREATE TABLE talabalar_yomon (
    id     INT PRIMARY KEY,
    ism    VARCHAR(100),
    fanlar VARCHAR(255)          -- '1,3,7' ko'rinishida
);

INSERT INTO talabalar_yomon VALUES
    (1, 'Husanboy', '1,2,3'),
    (2, 'Malika', '1,3'),
    (3, 'Kamola', '2');

-- "Matematika ga yozilgan talabalarni top" - qanday yozamiz?
SELECT ism, fanlar FROM talabalar_yomon WHERE FIND_IN_SET('1', fanlar) > 0;

-- Bog'lovchi jadval bilan xuddi shu savol
SELECT t.ism
FROM talabalar t
JOIN yozilishlar y ON y.talaba_id = t.id
WHERE y.fan_id = 1
ORDER BY t.id;

SELECT INDEX_NAME AS indeks, SEQ_IN_INDEX AS tartib, COLUMN_NAME AS ustun
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'yozilishlar'
ORDER BY BINARY INDEX_NAME, SEQ_IN_INDEX;

-- Talaba + fan + semestr: bir talaba bir fanni QAYTA o'qishi mumkin
CREATE TABLE semestrlar (
    id  INT PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE yozilishlar_semestr (
    talaba_id  INT NOT NULL,
    fan_id     INT NOT NULL,
    semestr_id INT NOT NULL,
    baho       TINYINT NULL,
    PRIMARY KEY (talaba_id, fan_id, semestr_id),
    FOREIGN KEY (talaba_id)  REFERENCES talabalar(id),
    FOREIGN KEY (fan_id)     REFERENCES fanlar(id),
    FOREIGN KEY (semestr_id) REFERENCES semestrlar(id)
);

INSERT INTO semestrlar (nom) VALUES ('2026-kuz'), ('2027-bahor');

INSERT INTO yozilishlar_semestr VALUES
    (1, 1, 1, 2),      -- yiqildi
    (1, 1, 2, 5);      -- qayta o'qib o'tdi

SELECT t.ism, f.nom AS fan, s.nom AS semestr, y.baho
FROM yozilishlar_semestr y
JOIN talabalar t  ON t.id = y.talaba_id
JOIN fanlar f     ON f.id = y.fan_id
JOIN semestrlar s ON s.id = y.semestr_id
ORDER BY s.id;