Bir-ga-ko'p va bir-ga-bir

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

CREATE TABLE kitoblar (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    sarlavha   VARCHAR(200) NOT NULL,
    muallif_id INT NOT NULL,
    yil        SMALLINT,
    FOREIGN KEY (muallif_id) REFERENCES mualliflar(id)
);

INSERT INTO mualliflar (ism, mamlakat) VALUES
    ('Abdulla Qodiriy', 'O''zbekiston'),
    ('Cho''lpon',       'O''zbekiston'),
    ('Erkin Vohidov',   'O''zbekiston');

INSERT INTO kitoblar (sarlavha, muallif_id, yil) VALUES
    ('O''tkan kunlar',   1, 1926),
    ('Mehrobdan chayon', 1, 1929),
    ('Kecha va kunduz',  2, 1936);

SELECT m.ism AS muallif, k.sarlavha, k.yil
FROM kitoblar k
JOIN mualliflar m ON m.id = k.muallif_id
ORDER BY k.id;

-- INNER JOIN Erkin Vohidovni ko'rsatmaydi - uning kitobi yo'q
SELECT m.ism, COUNT(k.id) AS kitoblar_soni
FROM mualliflar m
LEFT JOIN kitoblar k ON k.muallif_id = m.id
GROUP BY m.id, m.ism
ORDER BY m.id;

-- muallif_id NOT NULL - kitob albatta muallifga tegishli
INSERT INTO kitoblar (sarlavha, muallif_id, yil) VALUES ('Nomsiz', NULL, 2000);

ERROR 1048 (23000): Column 'muallif_id' cannot be null

-- Mavjud bo'lmagan muallifga ishora ham mumkin emas
INSERT INTO kitoblar (sarlavha, muallif_id, yil) VALUES ('Sinov', 999, 2000);

ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails

-- Ikkala cheklov ham sxemada ko'rinadi
SELECT COLUMN_NAME AS ustun, IS_NULLABLE AS null_mumkinmi
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'kitoblar'
ORDER BY ORDINAL_POSITION;

CREATE TABLE foydalanuvchilar (
    id     INT PRIMARY KEY AUTO_INCREMENT,
    email  VARCHAR(100) NOT NULL UNIQUE,
    parol  CHAR(60)     NOT NULL
);

CREATE TABLE profillar (
    foydalanuvchi_id INT PRIMARY KEY,
    tavsif           TEXT,
    tugilgan_sana    DATE,
    avatar_url       VARCHAR(255),
    FOREIGN KEY (foydalanuvchi_id) REFERENCES foydalanuvchilar(id)
        ON DELETE CASCADE
);

INSERT INTO foydalanuvchilar (email, parol) VALUES
    ('[email protected]', 'xesh1'), ('[email protected]', 'xesh2');

INSERT INTO profillar (foydalanuvchi_id, tavsif) VALUES
    (1, 'Dasturchi, Namangan');

SELECT f.email, IFNULL(p.tavsif, '(profil to''ldirilmagan)') AS tavsif
FROM foydalanuvchilar f
LEFT JOIN profillar p ON p.foydalanuvchi_id = f.id
ORDER BY f.id;

CREATE TABLE bolimlar (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    nom       VARCHAR(100) NOT NULL,
    ota_id    INT NULL,
    FOREIGN KEY (ota_id) REFERENCES bolimlar(id)
);

INSERT INTO bolimlar (nom, ota_id) VALUES
    ('Kompaniya',      NULL),
    ('IT',             1),
    ('Dasturlash',     2),
    ('Test',           2),
    ('Moliya',         1);

SELECT b.nom AS bolim, IFNULL(o.nom, '(ildiz)') AS ota
FROM bolimlar b
LEFT JOIN bolimlar o ON o.id = b.ota_id
ORDER BY b.id;

SELECT
    (SELECT COUNT(*) FROM mualliflar) AS mualliflar,
    (SELECT COUNT(*) FROM kitoblar)   AS kitoblar,
    (SELECT COUNT(DISTINCT muallif_id) FROM kitoblar) AS kitobi_bor,
    (SELECT COUNT(*) FROM mualliflar m
     WHERE NOT EXISTS (SELECT 1 FROM kitoblar k WHERE k.muallif_id = m.id))
        AS kitobsiz;