QA 12: SQL va ma'lumotlar bazasi — QA uchun SELECT, JOIN, GROUP BY va data tekshiruvi

🧪 QA Engineer — Zero to Hero

QA 12: SQL va ma'lumotlar bazasi — QA uchun SELECT, JOIN, GROUP BY va data tekshiruvi

Holat: 2026-yil oktabr. Darsdagi barcha so'rovlar SQLite 3.51 (sqlite3 CLI) da ishga tushirib tekshirilgan — jadvallardagi natijalar haqiqiy chiqish. PostgreSQL bilan farqlar alohida belgilangan.

Bu darsdan keyin siz: UI yoki API'da ko'rgan narsangizni ma'lumotlar bazasida o'zingiz tekshira olasiz.

• DemoBozor o'quv bazasini bir daqiqada ko'tarib, unda erkin mashq qilasiz;
• SELECT, WHERE, JOIN, GROUP BY, HAVING, subquery va window funksiyalarni QA ko'zi bilan ishlatasiz;
• ma'lumotlardagi "jim" bug'larni (yetim yozuvlar, to'lovsiz buyurtmalar, noto'g'ri summalar) bitta so'rov bilan topasiz;
• test ma'lumotni xavfsiz yaratasiz va tranzaksiya bilan bazani buzmasdan eksperiment qilasiz;
• NULL, BETWEEN, NOT IN va JOIN ko'payishi kabi klassik tuzoqlarga tushmaysiz.

Mundarija

1. Nega QA'ga SQL kerak
2. Relyatsion baza asoslari
3. DemoBozor o'quv bazasini o'rnatish
4. SELECT asoslari: WHERE, ORDER BY, LIMIT, DISTINCT
5. Agregatlar: COUNT, SUM, GROUP BY, HAVING
6. JOIN'lar: jadvallarni bog'lash
7. Subquery, EXISTS, CASE va window funksiyalar
8. Sana va vaqt: UTC, Toshkent va BETWEEN tuzog'i
9. QA so'rovlari: ma'lumotlardagi bug'larni ovlash
10. Test ma'lumot: INSERT, UPDATE, DELETE va tranzaksiyalar
11. Cheklovlar, indekslar va EXPLAIN
12. PostgreSQL farqlari, SQL injection va MongoDB
13. Amaliy mashqlar
14. Xulosa
15. Manbalar

1. Nega QA'ga SQL kerak {#nega-sql}

Tasavvur qiling: DemoBozor ilovasida buyurtma berdingiz va ekranda yashil "Buyurtma qabul qilindi" chiqdi. Test o'tdimi?

Hali yo'q. Ekran faqat frontend nima deb o'ylayotganini ko'rsatadi. Haqiqat esa bazada: buyurtma to'g'ri summa bilan yozildimi, mahsulot qoldig'i kamaydimi, to'lov qatori paydo bo'ldimi, yetkazish narxi qoidaga mosmi? Buni ko'rishning eng ishonchli yo'li — SQL.

🧠 Esda tuting: Ko'p jamoalarda QA umumiy muhitlarda (staging, ayniqsa production) faqat o'qish (SELECT) huquqiga ega bo'ladi. Yozish (INSERT/UPDATE/DELETE) — faqat o'zingizning lokal yoki maxsus test bazangizda.

🎯 Intervyuda: "QA'ga SQL'ning qaysi qismi kerak?" — Eng ko'p SELECT + WHERE + JOIN + GROUP BY/HAVING; test muhitida test ma'lumot uchun INSERT/UPDATE/DELETE; xavfsiz eksperiment uchun tranzaksiya (BEGIN … ROLLBACK). Bonus ball: NULL qanday ishlashini tushuntirib bera olish.

2. Relyatsion baza asoslari {#asoslar}

Relyatsion baza ma'lumotni jadvallarda saqlaydi, jadvallar esa kalitlar orqali bog'lanadi. Excel'ga o'xshaydi, lekin qat'iy qoidalari bor.

DemoBozor bazasining tuzilishi:

users (buyer / seller / admin)
  │ 1
  ├──< orders.user_id          1 foydalanuvchi → ko'p buyurtma
  │       │ 1
  │       ├──< order_items.order_id ──> products.id
  │       └──< payments.order_id       1 buyurtma → 0..N to'lov urinishi
  └──< products.seller_id      1 sotuvchi → ko'p mahsulot

SQL buyruqlari 5 oilaga bo'linadi:

3. DemoBozor o'quv bazasini o'rnatish {#ornatish}

Biz SQLite ishlatamiz: server kerak emas, butun baza bitta fayl. Asosiy sintaksis PostgreSQL bilan deyarli bir xil, farqlarni 12-bo'limda ko'ramiz.

Quyidagi skriptni demobozor.sql nomi bilan saqlang. Jadval va ustunlar kursning boshqa darslarida ham aynan shunday ishlatiladi.

-- DemoBozor o'quv bazasi (SQLite 3.39+)
-- Diqqat: seed'da QA topishi kerak bo'lgan ATAYIN qo'yilgan xatolar bor.
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id          INTEGER PRIMARY KEY,
  phone       TEXT UNIQUE NOT NULL,
  full_name   TEXT NOT NULL,
  role        TEXT CHECK (role IN ('buyer', 'seller', 'admin')) DEFAULT 'buyer',
  is_premium  INTEGER DEFAULT 0,
  created_at  TEXT
);

CREATE TABLE products (
  id          INTEGER PRIMARY KEY,
  name        TEXT NOT NULL,
  category    TEXT,
  price       INTEGER NOT NULL,          -- so'm
  stock       INTEGER NOT NULL,
  seller_id   INTEGER REFERENCES users(id)
);

CREATE TABLE orders (
  id              INTEGER PRIMARY KEY,
  user_id         INTEGER REFERENCES users(id),
  status          TEXT CHECK (status IN ('CREATED','PENDING_PAYMENT','PAID','PACKING',
                                         'SHIPPED','DELIVERED','CANCELLED','RETURNED')),
  payment_method  TEXT CHECK (payment_method IN ('UZCARD','HUMO','VISA','MASTERCARD','CASH')),
  promo_code      TEXT,
  subtotal        INTEGER,               -- mahsulotlar summasi
  discount        INTEGER,
  delivery_fee    INTEGER,
  total           INTEGER,               -- subtotal - discount + delivery_fee
  created_at      TEXT                   -- UTC
);

CREATE TABLE order_items (
  id          INTEGER PRIMARY KEY,
  order_id    INTEGER REFERENCES orders(id),
  product_id  INTEGER REFERENCES products(id),
  quantity    INTEGER CHECK (quantity BETWEEN 1 AND 99),
  unit_price  INTEGER                    -- buyurtma paytidagi narx
);

CREATE TABLE payments (
  id          INTEGER PRIMARY KEY,
  order_id    INTEGER REFERENCES orders(id),
  amount      INTEGER,
  status      TEXT CHECK (status IN ('SUCCESS', 'FAILED', 'REFUNDED')),
  provider    TEXT,
  created_at  TEXT
);

INSERT INTO users (id, phone, full_name, role, is_premium, created_at) VALUES
  (1,  '+998901234567',     'Aziza Karimova',   'buyer',  0, '2026-08-20 07:10:00'),
  (2,  '+998911112233',     'Bekzod Rahimov',   'buyer',  1, '2026-08-21 12:00:00'),
  (3,  '+998933334455',     'Dilnoza Yusupova', 'buyer',  0, '2026-08-25 15:45:00'),
  (4,  '+998945556677',     'Jasur Toshmatov',  'seller', 0, '2026-07-01 09:00:00'),
  (5,  '+998977778899',     'Malika Ergasheva', 'seller', 0, '2026-07-03 10:30:00'),
  (6,  '+998990001122',     'DemoBozor Admin',  'admin',  0, '2026-07-01 08:00:00'),
  (7,  '+99890123456',      'Sardor Aliyev',    'buyer',  0, '2026-09-28 18:05:00'),
  (8,  '+998951234567',     'Nodira Saidova',   'buyer',  1, '2026-09-30 11:20:00'),
  (9,  '+998881239876',     'Otabek Nazarov',   'buyer',  0, '2026-10-02 06:40:00'),
  (10, '+998 90 123 45 67', 'Aziza Karimova',   'buyer',  0, '2026-10-06 13:00:00');

INSERT INTO products (id, name, category, price, stock, seller_id) VALUES
  (1, 'Elektr choynak 1.7L',             'Maishiy texnika', 289000,  15,  4),
  (2, 'Paxta ko''ylak',                  'Kiyim',           125000,  40,  5),
  (3, 'Simsiz quloqchin',                'Elektronika',     399000,   8,  4),
  (4, 'Telefon g''ilofi',                'Aksessuar',        50000, 120,  4),
  (5, 'Gilam 2x3 m',                     'Uy-ro''zg''or',  1250000,   3,  5),
  (6, 'Kitob: Ingliz tili grammatikasi', 'Kitoblar',         65000,   0,  5),
  (7, 'Atlas ro''mol',                   'Kiyim',           180000,  -2,  5),
  (8, 'Non savati',                      'Uy-ro''zg''or',    35000,  25,  5);

INSERT INTO orders (id, user_id, status, payment_method, promo_code, subtotal, discount, delivery_fee, total, created_at) VALUES
  (1,  1, 'DELIVERED',       'UZCARD',     NULL,       289000, 28900, 20000,  280100, '2026-09-02 05:15:00'),
  (2,  2, 'SHIPPED',         'HUMO',       NULL,       497000, 49700,     0,  447300, '2026-09-10 09:30:00'),
  (3,  1, 'PAID',            'VISA',       'KUZ2026',  285000, 42750, 20000,  262250, '2026-10-01 04:00:00'),
  (4,  3, 'PAID',            'MASTERCARD', NULL,       300000, 30000, 20000,  290000, '2026-10-02 08:12:00'),
  (5,  2, 'PAID',            'UZCARD',     NULL,      1250000,     0,     0, 1250000, '2026-10-03 13:20:00'),
  (6,  7, 'PENDING_PAYMENT', 'HUMO',       NULL,       150000, 15000, 20000,  155000, '2026-10-04 10:00:00'),
  (7,  9, 'DELIVERED',       'VISA',       'KUZ2026',  399000, 99750,     0,  299250, '2026-10-04 11:30:00'),
  (8,  3, 'PACKING',         'CASH',       NULL,       105000,     0, 20000,  125000, '2026-10-05 07:00:00'),
  (9,  1, 'CANCELLED',       'UZCARD',     NULL,       180000,     0, 20000,  200000, '2026-10-06 09:45:00'),
  (10, 2, 'DELIVERED',       'HUMO',       NULL,       289000,     0, 20000,  309000, '2026-10-07 12:00:00'),
  (11, 9, 'PAID',            'UZCARD',     NULL,       115000,     0, 20000,  135000, '2026-10-08 16:25:00');

INSERT INTO order_items (id, order_id, product_id, quantity, unit_price) VALUES
  (1,   1, 1, 1, 289000),
  (2,   2, 3, 1, 399000),
  (3,   2, 4, 2,  49000),
  (4,   3, 2, 2, 125000),
  (5,   3, 8, 1,  35000),
  (6,   4, 2, 2, 125000),
  (7,   4, 4, 1,  50000),
  (8,   5, 5, 1, 1250000),
  (9,   6, 6, 2,  65000),
  (10,  6, 8, 1,  35000),
  (11,  7, 3, 1, 399000),
  (12,  8, 8, 3,  35000),
  (13,  9, 7, 1, 180000),
  (14, 10, 1, 1, 289000),
  (15, 11, 4, 1,  50000),
  (16, 11, 6, 1,  65000),
  (17, 999, 4, 1, 50000);

INSERT INTO payments (id, order_id, amount, status, provider, created_at) VALUES
  (1,  1,  280100, 'SUCCESS',  'UZCARD',     '2026-09-02 05:16:10'),
  (2,  2,  447300, 'SUCCESS',  'HUMO',       '2026-09-10 09:31:02'),
  (3,  3,  262250, 'SUCCESS',  'VISA',       '2026-10-01 04:01:30'),
  (4,  4,  290000, 'SUCCESS',  'MASTERCARD', '2026-10-02 08:13:00'),
  (5,  7,  299250, 'SUCCESS',  'VISA',       '2026-10-04 11:31:40'),
  (6,  9,  200000, 'SUCCESS',  'UZCARD',     '2026-10-06 09:46:00'),
  (7,  9,  200000, 'REFUNDED', 'UZCARD',     '2026-10-06 14:00:00'),
  (8,  10, 309000, 'SUCCESS',  'HUMO',       '2026-10-07 12:01:15'),
  (9,  11, 135000, 'FAILED',   'UZCARD',     '2026-10-08 16:26:00'),
  (10, 11, 130000, 'SUCCESS',  'UZCARD',     '2026-10-08 16:28:30');

Bazani yaratish va ichiga kirish:

sqlite3 demobozor.db < demobozor.sql   # jadvallar + ma'lumotlar
sqlite3 demobozor.db                   # interaktiv rejim

CLI ichidagi nuqta bilan boshlanadigan buyruqlar SQL emas, sqlite3 dasturining o'z sozlamalari:

sqlite> .tables            -- jadvallar ro'yxati
sqlite> .schema orders     -- jadval tuzilishi
sqlite> .headers on        -- ustun nomlarini ko'rsatish
sqlite> .mode box          -- chiroyli jadval (yoki .mode markdown)
sqlite> .nullvalue NULL    -- NULL'ni bo'sh joy emas, "NULL" deb ko'rsatish
sqlite> .quit

⚠️ Diqqat: Seed ichida QA topishi kerak bo'lgan 10 ta atayin qo'yilgan xato bor. Ularni 9-bo'limda birga ovlaymiz. O'zingizni sinamoqchi bo'lsangiz, o'qishni davom ettirishdan oldin bir nechtasini mustaqil topishga urinib ko'ring.

💡 Maslahat: Pul summalari INTEGER (so'm) da saqlangan. Pul bilan ishlaganda REAL/float ishlatmang: 0.1 + 0.2 kabi hisoblar yaxlitlash xatosi beradi. Real tizimlarda yo eng kichik birlikdagi butun son, yo NUMERIC/DECIMAL ishlatiladi.

4. SELECT asoslari: WHERE, ORDER BY, LIMIT, DISTINCT {#select}

SELECT — "qaysi ustunlarni", FROM — "qaysi jadvaldan", WHERE — "qaysi qatorlarni":

SELECT id, full_name, phone
FROM users
WHERE role = 'buyer';

QA ko'zi allaqachon ishlay boshladi: 7- va 10-qatordagi telefonlar boshqalariga o'xshamaydi. Eslab qoling — 9-bo'limda qaytamiz.

Taqqoslash operatorlari

SELECT name, price
FROM products
WHERE price BETWEEN 100000 AND 300000
ORDER BY price;

O'zbekcha apostrof — SQL'dagi eng "xavfli" belgi

SQL'da satr bitta qo'shtirnoq (') ichida yoziladi. "Ko'ylak" so'zidagi apostrof satrni muddatidan oldin yopib qo'yadi, shuning uchun uni ikki marta yozamiz:

SELECT id, name
FROM products
WHERE name LIKE '%ko''ylak%'
   OR category IN ('Kitoblar', 'Aksessuar');

🇺🇿 Mahalliy kontekst: "O'g'iloy", "Ma'mur", "Sa'dulla" kabi ismlar — har bir o'zbek ilovasi uchun tayyor test ma'lumoti. Agar backend SQL'ni satrlarni yopishtirib yasasa, bunday ism yo xatoga, yo xavfsizlik teshigiga olib keladi. Bu haqda 12-bo'limda va QA 24 da.

NULL bilan ishlash

NULL — "noma'lum". Unga = ishlamaydi, faqat IS NULL / IS NOT NULL:

SELECT id, user_id, status
FROM orders
WHERE promo_code IS NOT NULL;

ORDER BY, LIMIT, DISTINCT

SELECT name, price FROM products ORDER BY price DESC LIMIT 3;   -- eng qimmat 3 ta
SELECT DISTINCT status FROM orders ORDER BY status;             -- takrorlanmas statuslar

DISTINCT natijasi: CANCELLED, DELIVERED, PACKING, PAID, PENDING_PAYMENT, SHIPPED — bazada CREATED va RETURNED holatidagi buyurtma yo'q. Bu ham QA uchun ma'lumot: shu holatlarni test qilish uchun test ma'lumot yaratish kerak bo'ladi.

Tuzoq: AND va OR ustuvorligi

Vazifa: "300 000 dan qimmat, PAID yoki DELIVERED buyurtmalar". Qavssiz yozilgan variant:

SELECT id, status, total
FROM orders
WHERE status = 'PAID' OR status = 'DELIVERED' AND total > 300000;

AND OR'dan kuchliroq bog'lanadi, shuning uchun SQL buni PAID OR (DELIVERED AND total > 300000) deb tushundi — arzon PAID buyurtmalar ham chiqib qoldi. To'g'ri variant qavs bilan:

SELECT id, status, total
FROM orders
WHERE (status = 'PAID' OR status = 'DELIVERED') AND total > 300000;

💡 Maslahat: AND va OR aralashsa — har doim qavs qo'ying. Bu nafaqat sizning so'rovingiz uchun, balki dasturchi kodini review qilishda ham tez-tez topiladigan bug.

LIKE nozikliklari

SELECT name FROM products WHERE name LIKE '%GILAM%';      -- natija: Gilam 2x3 m
SELECT name FROM products WHERE name LIKE 'Non _avati';   -- natija: Non savati

SQLite'da LIKE lotin harflari uchun katta-kichik harfni farqlamaydi. **PostgreSQL'da LIKE farqlaydi** — u yerda ILIKE ishlatiladi. Bir bazada o'tgan so'rov boshqasida bo'sh natija qaytarishi mumkin.

🧠 Esda tuting: SQL'ning mantiqiy bajarilish tartibi: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Shu sababli standart SQL'da WHERE ichida SELECT'dagi alias'ni ishlatib bo'lmaydi. SQLite bunga ruxsat beradi, PostgreSQL esa xato beradi — bunga tayanmang.

5. Agregatlar: COUNT, SUM, GROUP BY, HAVING {#agregatlar}

Agregat funksiyalar ko'p qatorni bitta qiymatga yig'adi:

SELECT COUNT(*)            AS buyurtmalar,
       SUM(total)          AS jami_summa,
       ROUND(AVG(total))   AS ortacha,
       MIN(total)          AS eng_kichik,
       MAX(total)          AS eng_katta
FROM orders
WHERE status <> 'CANCELLED';

GROUP BY — guruhlab sanash

SELECT status, COUNT(*) AS soni, SUM(total) AS summa
FROM orders
GROUP BY status
ORDER BY soni DESC, status;

HAVING — guruhlarni filtrlash

WHERE qatorlarni guruhlashdan oldin, HAVING guruhlarni guruhlashdan keyin filtrlaydi:

SELECT user_id, COUNT(*) AS buyurtmalar
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 2
ORDER BY buyurtmalar DESC, user_id;

NULL va agregatlar

COUNT(*) hamma qatorni sanaydi, COUNT(ustun) esa faqat NULL bo'lmaganlarni:

SELECT NULL = NULL       AS teng,
       NULL IS NULL      AS is_null,
       COUNT(promo_code) AS promo_bor,
       COUNT(*)          AS hammasi
FROM orders;

NULL = NULL natijasi true emas, yana NULL! "Noma'lum noma'lumga tengmi?" — "noma'lum". Shuning uchun WHERE promo_code = NULL hech qachon qator qaytarmaydi.

SQLite "kechirimli", PostgreSQL qat'iy

SELECT user_id, status, COUNT(*) AS soni
FROM orders
GROUP BY user_id;

SQLite bunga javob beradi va status ustuniga guruhdan ixtiyoriy bitta qiymatni qo'yadi (masalan, 1-foydalanuvchi uchun DELIVERED). PostgreSQL esa to'g'ri ish qiladi va xato beradi: column "orders.status" must appear in the GROUP BY clause or be used in an aggregate function. Qoida: SELECT'dagi har bir ustun yo GROUP BY'da bo'lsin, yo agregat funksiya ichida.

🎯 Intervyuda: "WHERE va HAVING farqi?" — WHERE alohida qatorlarni guruhlashdan oldin filtrlaydi va agregat ishlata olmaydi; HAVING guruhlangan natijani filtrlaydi va COUNT(), SUM() kabi agregatlar bilan ishlaydi.

6. JOIN'lar: jadvallarni bog'lash {#joinlar}

Ma'lumot bir necha jadvalga bo'lingan: buyurtma orders'da, xaridor ismi users'da. Ularni birlashtirish — JOIN.

SQL JOIN turlari: INNER, LEFT, RIGHT, FULL va QA'ning sevimli anti-join'i

INNER JOIN — faqat moslar

SELECT o.id AS order_id, u.full_name, o.status, o.total
FROM orders AS o
INNER JOIN users AS u ON u.id = o.user_id
WHERE o.status IN ('PAID', 'DELIVERED')
ORDER BY o.id;

o va u — jadval alias'lari. Ular so'rovni qisqa va o'qiladigan qiladi.

LEFT JOIN — chap jadval to'liq

Chap jadvaldagi har bir qator chiqadi, o'ng tomonda mos qator bo'lmasa — NULL. Har bir foydalanuvchining buyurtmalar soni, buyurtmasi yo'qlar ham:

SELECT u.id, u.full_name, COUNT(o.id) AS buyurtmalar
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id
ORDER BY u.id;

E'tibor bering: COUNT(*) emas, COUNT(o.id). Buyurtmasi yo'q foydalanuvchida o.id = NULL, shuning uchun 0 chiqadi. COUNT(*) ishlatsangiz, ular 1 bo'lib qolardi.

Anti-join — "mosi yo'qlar" (QA'ning sevimli so'rovi)

LEFT JOIN + WHERE o'ng.id IS NULL = chapda bor, o'ngda yo'q qatorlar. Hech qachon buyurtma bermagan xaridorlar:

SELECT u.id, u.full_name, u.is_premium
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE u.role = 'buyer' AND o.id IS NULL;

Xuddi shu usul bilan yetim (orphan) yozuvlarni topamiz — mavjud bo'lmagan buyurtmaga tegishli mahsulot qatorlari:

SELECT oi.id AS item_id, oi.order_id, oi.product_id
FROM order_items AS oi
LEFT JOIN orders AS o ON o.id = oi.order_id
WHERE o.id IS NULL;

Bu — haqiqiy bug: 999-buyurtma bazada yo'q, lekin uning mahsuloti bor. Qanday paydo bo'lgani 11-bo'limda.

RIGHT va FULL OUTER JOIN

SQLite RIGHT va FULL OUTER JOIN'ni 3.39.0 (2022) versiyadan beri qo'llaydi; PostgreSQL esa azaldan. RIGHT JOIN — LEFT'ning ko'zgudagi aksi:

SELECT oi.id AS item_id, oi.order_id, o.id AS orders_id
FROM orders AS o
RIGHT JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.id IS NULL;

Natija yuqoridagi bilan bir xil: item_id = 17, order_id = 999, orders_id = NULL. Amalda ko'pchilik LEFT JOIN'ni ishlatadi — jadvallar tartibini almashtirsa bo'ldi.

FULL OUTER JOIN ikki tomonning mos kelmaganlarini birdaniga ko'rsatadi. To'lovsiz buyurtmalar va buyurtmasiz to'lovlar:

SELECT o.id AS order_id, o.status, o.payment_method,
       p.id AS payment_id, p.status AS pay_status
FROM orders AS o
FULL OUTER JOIN payments AS p ON p.order_id = o.id
WHERE o.id IS NULL OR p.id IS NULL
ORDER BY o.id;

Uchta qator — lekin uchala holat ham bug emas! 6-buyurtma hali to'lov kutyapti, 8-buyurtma naqd (to'lov yetkazilganda bo'ladi). Haqiqiy bug faqat 5-buyurtma: PAID, lekin to'lov yozuvi yo'q. SQL natijasi — dalil, xulosa esa biznes qoidalarini bilgan QA'dan chiqadi.

Uch jadval va self-join

SELECT o.id AS order_id, p.name AS mahsulot, oi.quantity, oi.unit_price,
       oi.quantity * oi.unit_price AS qator_summa
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p     ON p.id = oi.product_id
WHERE o.id = 4;

Self-join — jadvalni o'zi bilan bog'lash. Telefon raqami probellarni olib tashlagandan keyin bir xil bo'lib qoladigan foydalanuvchi juftliklari:

SELECT a.id AS id_1, b.id AS id_2, a.full_name, a.phone AS phone_1, b.phone AS phone_2
FROM users AS a
JOIN users AS b
  ON REPLACE(a.phone, ' ', '') = REPLACE(b.phone, ' ', '')
 AND a.id < b.id;

UNIQUE cheklovi bu dublikatni to'xtata olmadi: satrlar har xil, raqam esa bir xil. Bu ro'yxatdan o'tishda raqam normalizatsiya qilinmaganini bildiradi — formadagi bug.

Tuzoq: JOIN qatorlarni ko'paytiradi

3-buyurtmada 2 xil mahsulot va 1 ta muvaffaqiyatli to'lov bor. Mahsulotlar va to'lovlarni bir so'rovda birlashtirsak:

SELECT o.id, COUNT(*) AS qatorlar, SUM(p.amount) AS tolangan_xato
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
JOIN payments AS p     ON p.order_id = o.id AND p.status = 'SUCCESS'
WHERE o.id = 3
GROUP BY o.id;

To'lov 262 250 edi, lekin har mahsulot qatori uchun takrorlanib, summa ikki barobar chiqdi. To'g'risi — har bir "bir-ko'p" bog'lanishni alohida agregatlash:

SELECT o.id, o.total,
       (SELECT SUM(amount) FROM payments WHERE order_id = o.id AND status = 'SUCCESS') AS tolangan
FROM orders AS o
WHERE o.id = 3;

⚠️ Diqqat: Hisobotdagi summa "shubhali katta" bo'lsa — birinchi gumon JOIN ko'payishi. Bu dasturchilarning hisobot so'rovlarida ham uchraydigan klassik bug.

7. Subquery, EXISTS, CASE va window funksiyalar {#subquery}

Subquery — so'rov ichida so'rov

Yuqoridagi (SELECT SUM(amount) …) — skalyar subquery: har bir qator uchun bitta qiymat qaytaradi. Subquery WHERE ... IN (...) ichida ham ishlatiladi.

NOT IN va NULL tuzog'i

SELECT 'topildi' AS natija WHERE 5 NOT IN (1, 2, 3);      -- 1 qator: topildi
SELECT 'topildi' AS natija WHERE 5 NOT IN (1, 2, NULL);   -- 0 qator!

Ro'yxatda bitta NULL bo'lsa, NOT IN hech qachon true bo'lmaydi ("5 noma'lum qiymatga teng emasmi?" — "noma'lum"). Subquery NULL qaytarishi mumkin bo'lsa, NOT IN o'rniga NOT EXISTS yozing.

EXISTS / NOT EXISTS

"Shunday qator mavjudmi?" degan savolga eng aniq javob. To'langan (yoki undan keyingi bosqichdagi), naqd bo'lmagan, lekin muvaffaqiyatli to'lovi yo'q buyurtmalar:

SELECT o.id, o.status, o.payment_method, o.total
FROM orders AS o
WHERE o.status IN ('PAID', 'PACKING', 'SHIPPED', 'DELIVERED')
  AND o.payment_method <> 'CASH'
  AND NOT EXISTS (
        SELECT 1 FROM payments AS p
        WHERE p.order_id = o.id AND p.status = 'SUCCESS'
      );

Bu yerda biznes qoidasi so'rovning o'zida: naqd buyurtma va to'lov kutayotgan buyurtma avtomatik chiqarib tashlandi — FULL JOIN'dagi "yolg'on signal"lar yo'q.

CASE — SQL ichidagi if/else

SELECT id, total,
       CASE WHEN total >= 1000000 THEN 'katta'
            WHEN total >= 300000  THEN 'o''rta'
            ELSE 'kichik'
       END AS segment
FROM orders
ORDER BY total DESC
LIMIT 4;

QA uchun CASE'ning eng kuchli qo'llanishi — kutilgan qiymatni hisoblab, haqiqiy qiymat bilan solishtirish (9-bo'limda).

CTE (WITH) va window funksiyalar

Window funksiya qatorlarni yig'masdan, har bir qatorga "guruh ichidagi o'rni" kabi qiymat qo'shadi (SQLite 3.25+, PostgreSQL). Har bir buyurtma foydalanuvchining nechanchi buyurtmasi:

SELECT id, user_id, created_at,
       CASE WHEN ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) = 1
            THEN 'ha' ELSE 'yo''q' END AS birinchi
FROM orders
WHERE user_id IN (1, 9)
ORDER BY user_id, created_at;

Bu "Yangi mijoz — birinchi buyurtmaga 10%" qoidasini tekshirish uchun kalit. WITH (CTE) esa murakkab so'rovni nomlangan bosqichlarga bo'ladi — 9-bo'limda ikkalasini birga ishlatamiz.

8. Sana va vaqt: UTC, Toshkent va BETWEEN tuzog'i {#sana-vaqt}

DemoBozor vaqtni UTC'da saqlaydi, foydalanuvchiga esa Toshkent vaqti (UTC+5, yozgi vaqtga o'tilmaydi) ko'rsatiladi. QA uchun bu doimiy bug manbai: "buyurtma 5-oktabrda berilgan" — qaysi vaqt zonasida?

SELECT id, created_at AS utc, datetime(created_at, '+5 hours') AS toshkent
FROM orders
WHERE id IN (1, 3, 11);

Oy bo'yicha guruhlash va oxirgi 7 kun (testlar takrorlanuvchan bo'lishi uchun 'now' o'rniga aniq sana):

SELECT strftime('%Y-%m', created_at) AS oy, COUNT(*) AS soni, SUM(total) AS summa
FROM orders
GROUP BY oy
ORDER BY oy;

SELECT id, created_at
FROM orders
WHERE created_at >= datetime('2026-10-09 00:00:00', '-7 days')
ORDER BY created_at;

Ikkinchi so'rov 4–11-buyurtmalarni (8 qator) qaytaradi.

BETWEEN tuzog'i — SQL'dagi chegaraviy qiymat

Vazifa: "1-oktabrdan 5-oktabrgacha (5-oktabr ham kiradi) berilgan buyurtmalar":

SELECT id, created_at
FROM orders
WHERE created_at BETWEEN '2026-10-01' AND '2026-10-05'
ORDER BY created_at;

Natija: 3, 4, 5, 6, 7 — beshta. 8-buyurtma (2026-10-05 07:00:00) yo'qoldi! Sabab: matn sifatida '2026-10-05 07:00:00' > '2026-10-05'. Chegaraga vaqt qo'shildi va u oraliqdan chiqib ketdi. To'g'ri yozuv — yarim ochiq oraliq:

SELECT id, created_at
FROM orders
WHERE created_at >= '2026-10-01' AND created_at < '2026-10-06'
ORDER BY created_at;

Endi 3, 4, 5, 6, 7, 8 — oltita. Bu QA 5 dagi Boundary Value Analysis'ning SQL'dagi ko'rinishi: hisobot va filtr'larni test qilganda chegaradagi kunni albatta tekshiring.

🎯 Intervyuda: "Kunlik hisobot 23:59 dan keyingi buyurtmalarni ko'rsatmayapti, sababi nima bo'lishi mumkin?" — Sana oralig'i BETWEEN ... AND '2026-10-05' kabi vaqtsiz yozilgan yoki vaqt zonasi (UTC vs mahalliy) hisobga olinmagan.

9. QA so'rovlari: ma'lumotlardagi bug'larni ovlash {#qa-sorovlari}

Endi atayin yashirilgan xatolarni ovlaymiz. Har bir tekshiruvning mantig'i bir xil: biznes qoidasini SQL shartiga aylantir → qoidani buzgan qatorlarni chiqar. Bo'sh natija — yaxshi xabar.

1) Summa arifmetikasi: total = subtotal - discount + delivery_fee

SELECT id, subtotal, discount, delivery_fee, total,
       subtotal - discount + delivery_fee AS hisoblangan
FROM orders
WHERE subtotal - discount + delivery_fee <> total;

Natija: 0 qator. Arifmetika hamma joyda to'g'ri. Lekin bu "hammasi joyida" degani emas — keyingi tekshiruvlarni ko'ring.

2) subtotal mahsulotlar summasiga tengmi

SELECT o.id AS order_id, o.subtotal, SUM(oi.quantity * oi.unit_price) AS items_summa
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
GROUP BY o.id, o.subtotal
HAVING SUM(oi.quantity * oi.unit_price) <> o.subtotal;

Bug: 6-buyurtmada mahsulotlar 165 000 so'm, lekin subtotal 150 000. Ehtimol mahsulot savatchaga summa hisoblangandan keyin qo'shilgan. 1-tekshiruv buni ushlay olmadi — chunki noto'g'ri subtotal ustiga hisoblangan total o'zaro "izchil".

3) Yetkazish narxi qoidasi

Qoida: mahsulotlar summasi ≥ 300 000 so'm yoki premium a'zo → bepul, aks holda 20 000 so'm.

SELECT o.id, o.subtotal, u.is_premium, o.delivery_fee,
       CASE WHEN o.subtotal >= 300000 OR u.is_premium = 1 THEN 0 ELSE 20000 END AS kutilgan_fee
FROM orders AS o
JOIN users AS u ON u.id = o.user_id
WHERE o.delivery_fee <> CASE WHEN o.subtotal >= 300000 OR u.is_premium = 1 THEN 0 ELSE 20000 END;

Ikkita bug: 4-buyurtma aynan 300 000 so'm — kodda >= o'rniga > yozilgan (klassik chegaraviy xato). 10-buyurtma — premium mijozdan yetkazish haqi olingan.

4) Chegirmalar qo'shilib ketmaganmi

Qoida: yangi mijoz (birinchi buyurtma) — 10%, promo-kod — 15%, qo'shilmaydi, kattasi qo'llanadi. Avval oddiy "sanity" tekshiruv — chegirma 15% dan oshmasin:

SELECT id, promo_code, subtotal, discount,
       ROUND(100.0 * discount / subtotal, 1) AS foiz
FROM orders
WHERE discount > subtotal * 0.15;

Endi aniq tekshiruv: har bir buyurtma uchun kutilgan chegirmani CTE + window funksiya bilan hisoblaymiz (pul uchun butun son arifmetikasi — * foiz / 100):

WITH numbered AS (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS nth
  FROM orders AS o
)
SELECT id, user_id, nth, promo_code, subtotal, discount,
       subtotal * MAX(CASE WHEN nth = 1 THEN 10 ELSE 0 END,
                      CASE WHEN promo_code IS NOT NULL THEN 15 ELSE 0 END) / 100 AS kutilgan
FROM numbered
WHERE discount <> subtotal * MAX(CASE WHEN nth = 1 THEN 10 ELSE 0 END,
                                 CASE WHEN promo_code IS NOT NULL THEN 15 ELSE 0 END) / 100;

Bug: Otabek birinchi buyurtmasida promo-kod ishlatgan va unga 10% + 15% = 25% berilgan. Kutilgan natija — faqat 15% (59 850 so'm). Do'kon har shunday buyurtmada pul yo'qotadi.

💡 Maslahat: SQLite'da MAX(a, b) ikki argument bilan — skalyar "kattasi". PostgreSQL'da buning uchun GREATEST(a, b) ishlatiladi.

5) To'lov holati va summasi

To'lovsiz PAID buyurtma — 7-bo'limdagi NOT EXISTS so'rovi: 5-buyurtma (1 250 000 so'm, to'lov yozuvi yo'q). To'langan summa buyurtma summasiga mosmi:

SELECT o.id, o.total, SUM(p.amount) AS tolangan
FROM orders AS o
JOIN payments AS p ON p.order_id = o.id AND p.status = 'SUCCESS'
GROUP BY o.id, o.total
HAVING SUM(p.amount) <> o.total;

Bug: birinchi urinish FAILED bo'lgan, qayta urinishda 5 000 so'm kam yechilgan.

6) Mahsulot qoldig'i, telefon formati, dublikatlar

SELECT id, name, stock FROM products WHERE stock < 0;

SELECT id, full_name, phone
FROM users
WHERE phone NOT GLOB '+998[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';

SELECT REPLACE(phone, ' ', '') AS normal_phone, COUNT(*) AS soni,
       GROUP_CONCAT(id ORDER BY id) AS idlar
FROM users
GROUP BY normal_phone
HAVING COUNT(*) > 1;

Uchta bug: manfiy qoldiq (omborda yo'q mahsulot sotilgan — stock ustunida CHECK (stock >= 0) yo'q), +998 dan keyin 8 ta raqamli telefon va probelli formatda saqlangan dublikat foydalanuvchi. GLOB — SQLite'ning katta-kichik harfni farqlaydigan shabloni ([0-9] — bitta raqam); PostgreSQL'da buning o'rniga phone !~ '^\+998[0-9]{9}$' (regex) yoziladi. GROUP_CONCAT(id ORDER BY id) — agregat ichidagi tartib, SQLite 3.44+ da ishlaydi.

7) Hammasi bitta "sog'liq hisoboti"da

Bu tekshiruvlarni bitta so'rovga yig'sangiz — har kecha CI'da ishga tushadigan data quality smoke tayyor (QA 21):

SELECT 'orphan order_items' AS tekshiruv, COUNT(*) AS xatolar
FROM order_items AS oi LEFT JOIN orders AS o ON o.id = oi.order_id
WHERE o.id IS NULL
UNION ALL
SELECT 'subtotal <> items summasi', COUNT(*)
FROM (SELECT o.id FROM orders AS o JOIN order_items AS oi ON oi.order_id = o.id
      GROUP BY o.id, o.subtotal
      HAVING SUM(oi.quantity * oi.unit_price) <> o.subtotal)
UNION ALL
SELECT 'to''lovsiz PAID buyurtma', COUNT(*)
FROM orders AS o
WHERE o.status IN ('PAID', 'PACKING', 'SHIPPED', 'DELIVERED') AND o.payment_method <> 'CASH'
  AND NOT EXISTS (SELECT 1 FROM payments AS p WHERE p.order_id = o.id AND p.status = 'SUCCESS')
UNION ALL
SELECT 'manfiy qoldiq (stock)', COUNT(*) FROM products WHERE stock < 0
UNION ALL
SELECT 'telefon formati xato', COUNT(*)
FROM users WHERE phone NOT GLOB '+998[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';

API javobi vs baza: aniq buyurtmani tekshirish

Tasavvur qiling, Dilnoza hisobidan xayoliy DemoBozor API orqali buyurtma berdingiz va javob keldi:

{
  "id": 4,
  "status": "PAID",
  "subtotal": 300000,
  "deliveryFee": 20000,
  "total": 290000
}

API "muvaffaqiyatli" dedi. Endi bazaga qaraymiz:

SELECT o.id, o.status, o.subtotal, o.discount, o.delivery_fee, o.total,
       (SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = o.id) AS items_sum,
       (SELECT COUNT(*) FROM payments WHERE order_id = o.id AND status = 'SUCCESS') AS success_payments
FROM orders AS o
WHERE o.id = 4;

API va baza bir-biriga mos — demak bug "ma'lumot saqlashda" emas, hisoblash qoidasida. Bug report sarlavhasi (QA 8 formulasi bo'yicha): "Checkout: aynan 300 000 so'mlik buyurtmaga 20 000 so'm yetkazish haqi qo'shiladi (bepul bo'lishi kerak)". Dalil sifatida so'rov va natijani reportga qo'shing.

🧠 Esda tuting: Bitta "mos" natija ham ma'lumot: u bug'ni qaysi qatlamda qidirish kerakligini (UI, API, hisoblash, saqlash) toraytiradi.

10. Test ma'lumot: INSERT, UPDATE, DELETE va tranzaksiyalar {#dml}

⚠️ Diqqat: Bu bo'limdagi buyruqlar ma'lumotni o'zgartiradi. Ularni faqat lokal yoki maxsus test bazasida ishlating. Umumiy staging'da — jamoa kelishuvi bilan, production'da — hech qachon.

INSERT — yangi qator

INSERT INTO users (phone, full_name) VALUES ('+998935550011', 'QA Test User');

SELECT id, phone, full_name, role, is_premium, created_at
FROM users WHERE phone = '+998935550011';

role va is_premium DEFAULT qiymatlarini oldi, created_at esa NULL qoldi — ilova uni to'ldirishi kutilgan. "Ilova orqali yaratilgan foydalanuvchida created_at bo'shmi?" — bu ham yaxshi test g'oyasi.

UPDATE va DELETE — doim WHERE bilan

UPDATE products SET stock = stock - 1 WHERE id = 3 RETURNING id, name, stock;
DELETE FROM users WHERE phone = '+998935550011' RETURNING id;

RETURNING (SQLite 3.35+, PostgreSQL) o'zgargan qatorni darhol ko'rsatadi: birinchi buyruq 3 | Simsiz quloqchin | 7 qaytaradi.

⚠️ Diqqat: UPDATE products SET stock = 0; yoki DELETE FROM orders; — WHERE'siz bo'lsa butun jadval o'zgaradi yoki o'chadi. Odat qiling: avval xuddi shu shart bilan SELECT yozing, natijani ko'ring, keyin SELECT'ni UPDATE/DELETE'ga almashtiring.

Tranzaksiya — xavfsiz eksperiment

Tranzaksiya bir nechta o'zgarishni "hammasi yoki hech biri" qilib bog'laydi. QA uchun eng foydali tomoni — ROLLBACK bilan hamma narsani ortga qaytarish:

BEGIN;
UPDATE orders SET status = 'CANCELLED' WHERE user_id = 1;
SELECT id, status FROM orders WHERE user_id = 1;   -- 1, 3, 9 → hammasi CANCELLED
ROLLBACK;
SELECT id, status FROM orders WHERE user_id = 1;   -- 1 DELIVERED, 3 PAID, 9 CANCELLED

ROLLBACK'dan keyin baza eksperimentdan oldingi holatiga qaytdi. Agar natija to'g'ri bo'lsa — ROLLBACK o'rniga COMMIT.

Test ma'lumot gigiyenasi

11. Cheklovlar, indekslar va EXPLAIN {#cheklovlar}

Cheklovlar — bazaning "himoya chizig'i". QA ularni ataylab buzishga urinib, ishlayotganini tekshiradi:

INSERT INTO users (phone, full_name) VALUES ('+998901234567', 'Aziza Karimova (dublikat)');
-- ❌ UNIQUE constraint failed: users.phone

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (3, 4, 0, 50000);
-- ❌ CHECK constraint failed: quantity BETWEEN 1 AND 99

INSERT INTO users (phone) VALUES ('+998935550011');
-- ❌ NOT NULL constraint failed: users.full_name

sqlite3 CLI bu matnlar oldiga Runtime error near line 3: kabi prefiks va oxiriga xato kodi (19) qo'shadi — asosiysi o'rtadagi qism.

Bu xatolar yaxshi: baza noto'g'ri ma'lumotni qabul qilmadi. QA'ning keyingi savoli — ilova bu xatoni foydalanuvchiga qanday ko'rsatadi? Xom UNIQUE constraint failed matni ekranda chiqsa — bu alohida bug (texnik ma'lumot sizib chiqyapti).

Orphan qanday paydo bo'ldi: SQLite'da FK default o'chiq

PRAGMA foreign_keys;                -- 0 — tekshiruv o'chiq!
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (12345, 1, 1, 289000);       -- qabul qilindi, garchi 12345-buyurtma yo'q bo'lsa ham

PRAGMA foreign_keys = ON;
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (54321, 1, 1, 289000);
-- ❌ FOREIGN KEY constraint failed

SQLite eski versiyalar bilan moslik uchun foreign key tekshiruvini har bir ulanishda alohida yoqishni talab qiladi. PostgreSQL'da FK doim ishlaydi. Real loyihalarda yetim yozuvlar FK'siz jadvallar, qo'lda qilingan tuzatishlar, migratsiyalar yoki "soft delete" mantiqi tufayli paydo bo'ladi — shuning uchun orphan tekshiruvi har doim foydali.

SQLite turlar bo'yicha "moslashuvchan"

INSERT INTO products (name, category, price, stock) VALUES ('Test mahsulot', 'Test', '12abc', 5);
SELECT id, name, price, typeof(price) AS turi FROM products WHERE name = 'Test mahsulot';

INTEGER ustunga matn yozildi! SQLite type affinity bilan ishlaydi: qiymatni songa aylantirishga urinadi, bo'lmasa shundayligicha saqlaydi. Qat'iy turlar uchun 3.37+ versiyada STRICT jadvallar bor — u holda xato: cannot store TEXT value in INTEGER column. PostgreSQL esa '12abc''ni INTEGER ustunga hech qachon qabul qilmaydi. Xulosa: SQLite'da validatsiya ilova tomonida ekanini, uni esa test qilish kerakligini unutmang.

Indeks va EXPLAIN QUERY PLAN

Indeks — kitob oxiridagi "ko'rsatkich": butun jadvalni varaqlamasdan kerakli qatorni topadi. So'rov qanday bajarilishini EXPLAIN QUERY PLAN ko'rsatadi:

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 2;
-- `--SCAN orders                       ← butun jadval ko'rib chiqiladi

CREATE INDEX idx_orders_user_id ON orders(user_id);

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 2;
-- `--SEARCH orders USING INDEX idx_orders_user_id (user_id=?)

11 qatorli jadvalda farq sezilmaydi, millionlab qatorda esa sekundlar va millisekundlar farqi. QA uchun amaliy foyda: "sahifa sekin ochilyapti" bug'ini tekshirganda dasturchiga EXPLAIN natijasini ilova qilish. PostgreSQL'da buyruq — EXPLAIN / EXPLAIN ANALYZE. Yuklama testlari — QA 23.

12. PostgreSQL farqlari, SQL injection va MongoDB {#postgresql-mongodb}

DemoBozor production'da PostgreSQL ishlatadi. Siz o'rgangan SQL u yerda ham ishlaydi, faqat quyidagi farqlarni yodda tuting:

SQL injection — QA nimani bilishi kerak

Agar backend so'rovni satrlarni yopishtirib yasasa:

// ❌ XAVFLI: foydalanuvchi kiritgan qiymat to'g'ridan-to'g'ri SQL'ga qo'shiladi
const sql = "SELECT * FROM users WHERE full_name = '" + name + "'";

"O'g'iloy" ismi kiritilishi bilan so'rov buziladi — 500 xato. Yovuz niyatli odam esa apostrof orqali so'rov mantiqini o'zgartirishi mumkin. To'g'ri yechim — parametrlangan so'rov, unda qiymat SQL matnidan alohida uzatiladi:

// ✅ XAVFSIZ: qiymat alohida parametr sifatida yuboriladi (node-postgres uslubi)
const result = await db.query("SELECT * FROM users WHERE full_name = $1", [
  name,
]);

QA darajasidagi tekshiruv (faqat o'zingizning test muhitingizda): ism, qidiruv va promo-kod maydonlariga O'g'iloy, ' OR '1'='1, "; -- kabi qiymatlar kiriting. Kutilgan natija: oddiy matn sifatida saqlanadi yoki chiroyli validatsiya xabari chiqadi; 500 xato, SQL xatosi matni yoki "ortiqcha" ma'lumot chiqishi — bug. Batafsil — QA 24.

MongoDB — qisqa tanishuv

Ba'zi loyihalar hujjat (document) bazalarini ishlatadi. MongoDB'da jadval o'rniga kolleksiya, qator o'rniga JSON-ga o'xshash hujjat. mongosh sintaksisi:

// status = "PAID" bo'lgan buyurtmalar
db.orders.find({ status: "PAID" });

// total >= 300000; faqat id va total maydonlari
db.orders.find({ total: { $gte: 300000 } }, { _id: 0, id: 1, total: 1 });

// sanash
db.orders.countDocuments({ promo_code: { $ne: null } });

// GROUP BY analogi
db.orders.aggregate([
  { $group: { _id: "$status", soni: { $sum: 1 }, summa: { $sum: "$total" } } },
]);

Fikrlash usuli bir xil: "qaysi hujjatlar → qaysi maydonlar → qanday guruhlash". SQL'ni yaxshi bilgan QA MongoDB so'rovlarini bir-ikki kunda o'rganib oladi.

13. Amaliy mashqlar {#mashqlar}

Barcha mashqlar 3-bo'limdagi demobozor.sql bazasida bajariladi. Javoblar sqlite3'da tekshirilgan.

Mashq 1: Arzon mahsulotlar 🟢

100 000 so'mdan arzon mahsulotlarning nomi va narxini arzonidan qimmatiga qarab chiqaring.

<details> <summary>💡 Javobni ko'rish</summary>

SELECT name, price FROM products WHERE price < 100000 ORDER BY price;

</details>

Mashq 2: To'lov usullari statistikasi 🟢

Har bir to'lov usuli bo'yicha buyurtmalar sonini chiqaring (ko'pidan kamiga).

<details> <summary>💡 Javobni ko'rish</summary>

SELECT payment_method, COUNT(*) AS soni
FROM orders
GROUP BY payment_method
ORDER BY soni DESC, payment_method;

</details>

Mashq 3: Premium mijozlar 🟡

Har bir premium mijozning ismi, buyurtmalar soni va jami summasini chiqaring. Buyurtmasi yo'q premium mijoz ham ro'yxatda 0 bilan ko'rinsin.

<details> <summary>💡 Javobni ko'rish</summary>

SELECT u.full_name, COUNT(o.id) AS buyurtmalar, COALESCE(SUM(o.total), 0) AS jami
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE u.is_premium = 1
GROUP BY u.id
ORDER BY jami DESC;

LEFT JOIN Nodirani saqlab qoladi, COALESCE esa uning NULL summasini 0 ga aylantiradi. INNER JOIN yozsangiz Nodira yo'qolib qoladi.

</details>

Mashq 4: Sotuvchilar tushumi 🟡

Har bir sotuvchi bo'yicha sotilgan mahsulotlar summasini (quantity * unit_price) hisoblang. Bekor qilingan va qaytarilgan buyurtmalar hisobga olinmasin.

<details> <summary>💡 Javobni ko'rish</summary>

SELECT u.full_name AS sotuvchi, SUM(oi.quantity * oi.unit_price) AS tushum
FROM order_items AS oi
JOIN orders AS o   ON o.id = oi.order_id
JOIN products AS p ON p.id = oi.product_id
JOIN users AS u    ON u.id = p.seller_id
WHERE o.status NOT IN ('CANCELLED', 'RETURNED')
GROUP BY u.id
ORDER BY tushum DESC;

E'tibor bering: JOIN orders yetim 17-qatorni avtomatik tashlab yubordi (999-buyurtma yo'q). Bu hisobot uchun to'g'ri, lekin ma'lumot sifati tekshiruvi uchun yetim qatorni alohida qidirish kerak.

</details>

Mashq 5: To'lov urinishlari tarixi 🟡

Bir nechta to'lov yozuvi bor buyurtmalar uchun statuslar ketma-ketligini (vaqt bo'yicha) bitta satrda chiqaring.

<details> <summary>💡 Javobni ko'rish</summary>

SELECT order_id, GROUP_CONCAT(status, ' -> ') AS tarix
FROM (SELECT order_id, status FROM payments ORDER BY order_id, created_at)
GROUP BY order_id
HAVING COUNT(*) > 1;

9-buyurtma bekor qilingan va pul qaytarilgan — to'g'ri oqim. 11-buyurtmada esa FAILED → SUCCESS, lekin 9-bo'limda ko'rganimizdek, ikkinchi urinish summasi noto'g'ri.

</details>

Mashq 6: Bekor qilinganlarda pul qaytdimi 🟡

Har bir CANCELLED buyurtma uchun muvaffaqiyatli to'langan va qaytarilgan summalarni yonma-yon ko'rsating.

<details> <summary>💡 Javobni ko'rish</summary>

SELECT o.id, o.status,
       SUM(CASE WHEN p.status = 'SUCCESS'  THEN p.amount ELSE 0 END) AS tolangan,
       SUM(CASE WHEN p.status = 'REFUNDED' THEN p.amount ELSE 0 END) AS qaytarilgan
FROM orders AS o
JOIN payments AS p ON p.order_id = o.id
WHERE o.status = 'CANCELLED'
GROUP BY o.id, o.status;

Hammasi joyida. SUM(CASE …) — bitta so'rovda bir nechta shartli yig'indini olishning klassik usuli ("pivot").

</details>

Mashq 7: Narxi o'zgargan qatorlar — bug'mi? 🔴

order_items.unit_price mahsulotning hozirgi narxidan (products.price) farq qiladigan qatorlarni toping. Topilgan narsa bug'mi?

<details> <summary>💡 Javobni ko'rish</summary>

SELECT oi.order_id, p.name, oi.unit_price, p.price AS hozirgi_narx
FROM order_items AS oi
JOIN products AS p ON p.id = oi.product_id
WHERE oi.unit_price <> p.price;

Bug emas. unit_price — buyurtma paytidagi narx, products.price — hozirgi narx. 2-buyurtma 10-sentabrda berilgan, keyin narx 50 000 ga ko'tarilgan. Aksincha, agar eski buyurtmalar narx o'zgarganda "qayta hisoblanib" ketsa — o'sha bug bo'lardi. Xulosa: SQL natijasini talqin qilish uchun ma'lumot modelini bilish shart. Shubha bo'lsa — dasturchi yoki analitikdan so'rang.

</details>

Mashq 8: Xavfsiz eksperiment 🔴

Tranzaksiya ichida Bekzodning (id = 2) premium statusini olib tashlang va 9-bo'limdagi "yetkazish narxi qoidasi" so'rovini qayta ishga tushiring. Natija qanday o'zgardi va nega? Oxirida bazani asl holiga qaytaring.

<details>