Birinchi normal shakl (1NF)

CREATE TABLE mijozlar_yomon (
    id         INT PRIMARY KEY,
    ism        VARCHAR(100),
    telefonlar VARCHAR(255),
    manzil     VARCHAR(255)
);

INSERT INTO mijozlar_yomon VALUES
    (1, 'Husanboy Qodirov', '901234567,935556677', 'Namangan, Navoiy 12'),
    (2, 'Malika Yusupova',  '911112233',           'Farg''ona, Mustaqillik 5'),
    (3, 'Kamola Rasulova',  '',                    'Andijon, Bobur 8');

-- "935556677 raqami kimga tegishli?" - qanday topamiz?
SELECT ism, telefonlar
FROM mijozlar_yomon
WHERE telefonlar LIKE '%935556677%';

CREATE TABLE mijozlar (
    id     INT PRIMARY KEY AUTO_INCREMENT,
    ism    VARCHAR(100) NOT NULL,
    shahar VARCHAR(50)  NOT NULL,
    kocha  VARCHAR(100) NOT NULL,
    uy     VARCHAR(20)  NOT NULL
);

CREATE TABLE telefonlar (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    mijoz_id INT NOT NULL,
    raqam    VARCHAR(20) NOT NULL,
    turi     ENUM('mobil', 'uy', 'ish') NOT NULL DEFAULT 'mobil',
    UNIQUE (raqam),
    FOREIGN KEY (mijoz_id) REFERENCES mijozlar(id) ON DELETE CASCADE
);

INSERT INTO mijozlar (ism, shahar, kocha, uy) VALUES
    ('Husanboy Qodirov', 'Namangan', 'Navoiy',       '12'),
    ('Malika Yusupova',  'Farg''ona', 'Mustaqillik', '5'),
    ('Kamola Rasulova',  'Andijon',  'Bobur',        '8');

INSERT INTO telefonlar (mijoz_id, raqam, turi) VALUES
    (1, '901234567', 'mobil'),
    (1, '935556677', 'ish'),
    (2, '911112233', 'mobil');

SELECT m.ism, t.raqam, t.turi
FROM mijozlar m
JOIN telefonlar t ON t.mijoz_id = m.id
WHERE t.raqam = '935556677';

-- 1. Telefon turini bilamiz
SELECT m.ism, COUNT(t.id) AS raqamlar, GROUP_CONCAT(t.turi ORDER BY t.id) AS turlari
FROM mijozlar m
LEFT JOIN telefonlar t ON t.mijoz_id = m.id
GROUP BY m.id, m.ism
ORDER BY m.id;

-- 2. Takroriy raqamni tizim rad etadi
INSERT INTO telefonlar (mijoz_id, raqam) VALUES (2, '901234567');

ERROR 1062 (23000): Duplicate entry '901234567' for key 'raqam'

-- 3. Shahar bo'yicha guruhlash oson
SELECT shahar, COUNT(*) AS mijozlar
FROM mijozlar
GROUP BY shahar
ORDER BY shahar;

CREATE TABLE talabalar_yomon (
    id   INT PRIMARY KEY,
    ism  VARCHAR(100),
    fan1 VARCHAR(50),
    fan2 VARCHAR(50),
    fan3 VARCHAR(50)
);

INSERT INTO talabalar_yomon VALUES
    (1, 'Husanboy', 'Matematika', 'Fizika', 'Ingliz tili'),
    (2, 'Malika',   'Matematika', NULL,     NULL);

-- "Fizikaga yozilganlarni top" - uchala ustunni tekshirish kerak
SELECT ism FROM talabalar_yomon
WHERE fan1 = 'Fizika' OR fan2 = 'Fizika' OR fan3 = 'Fizika';

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

CREATE TABLE talabalar (
    id  INT PRIMARY KEY AUTO_INCREMENT,
    ism VARCHAR(100) 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),
    FOREIGN KEY (fan_id)    REFERENCES fanlar(id)
);

INSERT INTO fanlar (nom) VALUES ('Matematika'), ('Fizika'), ('Ingliz tili');
INSERT INTO talabalar (ism) VALUES ('Husanboy'), ('Malika');
INSERT INTO yozilishlar VALUES (1, 1), (1, 2), (1, 3), (2, 1);

SELECT t.ism
FROM talabalar t
JOIN yozilishlar y ON y.talaba_id = t.id
JOIN fanlar f      ON f.id = y.fan_id
WHERE f.nom = 'Fizika';

-- To'rtinchi fan qo'shish - ALTER TABLE kerak emas
INSERT INTO fanlar (nom) VALUES ('Tarix');
INSERT INTO yozilishlar VALUES (2, 4);

SELECT t.ism, COUNT(*) AS fanlar_soni
FROM talabalar t
JOIN yozilishlar y ON y.talaba_id = t.id
GROUP BY t.id, t.ism
ORDER BY t.id;

-- Ism atomarmi? Bu ehtiyojga bog'liq
CREATE TABLE variantlar (
    id           INT PRIMARY KEY AUTO_INCREMENT,
    toliq_ism    VARCHAR(200),
    ism          VARCHAR(100),
    familiya     VARCHAR(100),
    otasining_ismi VARCHAR(100)
);

INSERT INTO variantlar (toliq_ism, ism, familiya, otasining_ismi) VALUES
    ('Qodirov Husanboy Baxtiyor o''g''li', 'Husanboy', 'Qodirov', 'Baxtiyor');

SELECT
    toliq_ism,
    CONCAT(familiya, ' ', LEFT(ism, 1), '.') AS qisqartma
FROM variantlar;

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

INSERT INTO mahsulotlar (nom, narx, xossalar) VALUES
    ('Noutbuk', 8000000, '{"ram": "16GB", "ekran": "15.6", "rang": "kulrang"}'),
    ('Telefon', 4000000, '{"ram": "8GB", "kamera": "48MP"}');

SELECT nom,
       JSON_VALUE(xossalar, '$.ram') AS ram,
       JSON_VALUE(xossalar, '$.kamera') AS kamera
FROM mahsulotlar
ORDER BY id;