# 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](#nega-sql) 2. [Relyatsion baza asoslari](#asoslar) 3. [DemoBozor o'quv bazasini o'rnatish](#ornatish) 4. [SELECT asoslari: WHERE, ORDER BY, LIMIT, DISTINCT](#select) 5. [Agregatlar: COUNT, SUM, GROUP BY, HAVING](#agregatlar) 6. [JOIN'lar: jadvallarni bog'lash](#joinlar) 7. [Subquery, EXISTS, CASE va window funksiyalar](#subquery) 8. [Sana va vaqt: UTC, Toshkent va BETWEEN tuzog'i](#sana-vaqt) 9. [QA so'rovlari: ma'lumotlardagi bug'larni ovlash](#qa-sorovlari) 10. [Test ma'lumot: INSERT, UPDATE, DELETE va tranzaksiyalar](#dml) 11. [Cheklovlar, indekslar va EXPLAIN](#cheklovlar) 12. [PostgreSQL farqlari, SQL injection va MongoDB](#postgresql-mongodb) 13. [Amaliy mashqlar](#mashqlar) 14. [Xulosa](#xulosa) 15. [Manbalar](#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. | QA vazifasi | SQL nima beradi | DemoBozor misoli | | ------------------------------ | ------------------------------------------------------ | --------------------------------------------------------------- | | Side effect'ni tekshirish | UI/API amalidan keyin bazada nima o'zgarganini ko'rish | Buyurtmadan keyin `orders`, `order_items`, `payments` qatorlari | | Test ma'lumot tayyorlash | Kerakli holatni tez yaratish | Premium mijoz, 99 dona mahsulotli savatcha | | Bug'ni tekshirish | Muammoning ildizini ma'lumotdan topish | "Nega 300 000 so'mlik buyurtmaga yetkazish haqi olindi?" | | Ta'sir doirasini baholash | Bug nechta foydalanuvchiga tegishli ekanini sanash | "Shu xato bilan nechta buyurtma bor?" | | Ma'lumot sifati (data quality) | Butunlik qoidalari buzilganini topish | Yetim (orphan) yozuvlar, dublikatlar | > **🧠 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. | Tushuncha | Ma'nosi | DemoBozor misoli | | -------------------- | ---------------------------------------------------- | -------------------------------------------- | | Jadval (table) | Bir turdagi obyektlar to'plami | `orders` β€” buyurtmalar | | Qator (row) | Bitta obyekt | 4-buyurtma | | Ustun (column) | Obyekt xususiyati, o'z turi bor | `total INTEGER` | | Primary key (PK) | Qatorni yagona aniqlovchi | `orders.id` | | Foreign key (FK) | Boshqa jadval PK'siga havola | `orders.user_id β†’ users.id` | | Constraint (cheklov) | Ma'lumotga qo'yilgan qoida | `UNIQUE`, `NOT NULL`, `CHECK`, `DEFAULT` | | `NULL` | "Qiymat yo'q / noma'lum". 0 ham, bo'sh satr ham emas | Promo-kod ishlatilmasa `promo_code` = `NULL` | DemoBozor bazasining tuzilishi: ```text 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: | Oila | Buyruqlar | QA qanchalik ishlatadi | | ------------------ | ----------------------------- | ---------------------------------- | | DQL β€” so'rov | `SELECT` | Har kuni | | DML β€” o'zgartirish | `INSERT`, `UPDATE`, `DELETE` | Test muhitida, test ma'lumot uchun | | DDL β€” tuzilma | `CREATE`, `ALTER`, `DROP` | Kam (lokal baza yaratishda) | | TCL β€” tranzaksiya | `BEGIN`, `COMMIT`, `ROLLBACK` | Xavfsiz eksperimentlar uchun | | DCL β€” huquqlar | `GRANT`, `REVOKE` | Deyarli yo'q (DBA ishi) | --- ## 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. | Variant | Qanday | Kimga | | ----------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------- | | `sqlite3` CLI | macOS'da tayyor turadi; Linux'da paket menejeri orqali; Windows uchun [sqlite.org/download](https://sqlite.org/download.html) | Terminalni o'rganayotganlar | | [DB Browser for SQLite](https://sqlitebrowser.org/) | Bepul GUI dastur | Jadvallarni ko'z bilan ko'rishni xohlovchilar | | [SQLite Online](https://sqliteonline.com/), [DB Fiddle](https://www.db-fiddle.com/) | Brauzerda, o'rnatishsiz | Tez sinab ko'rish uchun | Quyidagi skriptni `demobozor.sql` nomi bilan saqlang. Jadval va ustunlar kursning boshqa darslarida ham aynan shunday ishlatiladi. ```sql -- 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: ```bash 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: ```text 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": ```sql SELECT id, full_name, phone FROM users WHERE role = 'buyer'; ``` | id | full_name | phone | | --- | ---------------- | ----------------- | | 1 | Aziza Karimova | +998901234567 | | 2 | Bekzod Rahimov | +998911112233 | | 3 | Dilnoza Yusupova | +998933334455 | | 7 | Sardor Aliyev | +99890123456 | | 8 | Nodira Saidova | +998951234567 | | 9 | Otabek Nazarov | +998881239876 | | 10 | Aziza Karimova | +998 90 123 45 67 | QA ko'zi allaqachon ishlay boshladi: 7- va 10-qatordagi telefonlar boshqalariga o'xshamaydi. Eslab qoling β€” 9-bo'limda qaytamiz. ### Taqqoslash operatorlari | Operator | Ma'nosi | Misol | | ------------------------ | --------------------------------------------------- | ----------------------------------- | | `=`, `<>` (`!=`) | teng / teng emas | `status <> 'CANCELLED'` | | `<`, `>`, `<=`, `>=` | katta-kichik | `total >= 300000` | | `BETWEEN a AND b` | oraliq, **ikkala chegara ham kiradi** | `price BETWEEN 100000 AND 300000` | | `IN (...)` | ro'yxatdan biri | `status IN ('PAID', 'SHIPPED')` | | `LIKE` | shablon: `%` β€” istalgan belgilar, `_` β€” bitta belgi | `name LIKE '%choynak%'` | | `IS NULL`, `IS NOT NULL` | qiymat yo'qligini tekshirish | `promo_code IS NULL` | | `AND`, `OR`, `NOT` | mantiqiy bog'lovchilar | `role = 'buyer' AND is_premium = 1` | ```sql SELECT name, price FROM products WHERE price BETWEEN 100000 AND 300000 ORDER BY price; ``` | name | price | | ------------------- | ------ | | Paxta ko'ylak | 125000 | | Atlas ro'mol | 180000 | | Elektr choynak 1.7L | 289000 | ### 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: ```sql SELECT id, name FROM products WHERE name LIKE '%ko''ylak%' OR category IN ('Kitoblar', 'Aksessuar'); ``` | id | name | | --- | ------------------------------- | | 2 | Paxta ko'ylak | | 4 | Telefon g'ilofi | | 6 | Kitob: Ingliz tili grammatikasi | > **πŸ‡ΊπŸ‡Ώ 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](/ios-lessons/qa-security) da. ### NULL bilan ishlash `NULL` β€” "noma'lum". Unga `=` ishlamaydi, faqat `IS NULL` / `IS NOT NULL`: ```sql SELECT id, user_id, status FROM orders WHERE promo_code IS NOT NULL; ``` | id | user_id | status | | --- | ------- | --------- | | 3 | 1 | PAID | | 7 | 9 | DELIVERED | ### ORDER BY, LIMIT, DISTINCT ```sql 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 ``` | name | price | | ------------------- | ------- | | Gilam 2x3 m | 1250000 | | Simsiz quloqchin | 399000 | | Elektr choynak 1.7L | 289000 | `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: ```sql SELECT id, status, total FROM orders WHERE status = 'PAID' OR status = 'DELIVERED' AND total > 300000; ``` | id | status | total | | --- | --------- | ------- | | 3 | PAID | 262250 | | 4 | PAID | 290000 | | 5 | PAID | 1250000 | | 10 | DELIVERED | 309000 | | 11 | PAID | 135000 | `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: ```sql SELECT id, status, total FROM orders WHERE (status = 'PAID' OR status = 'DELIVERED') AND total > 300000; ``` | id | status | total | | --- | --------- | ------- | | 5 | PAID | 1250000 | | 10 | DELIVERED | 309000 | > **πŸ’‘ 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 ```sql 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: ```sql 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'; ``` | buyurtmalar | jami_summa | ortacha | eng_kichik | eng_katta | | ----------- | ---------- | -------- | ---------- | --------- | | 10 | 3552900 | 355290.0 | 125000 | 1250000 | ### GROUP BY β€” guruhlab sanash ```sql SELECT status, COUNT(*) AS soni, SUM(total) AS summa FROM orders GROUP BY status ORDER BY soni DESC, status; ``` | status | soni | summa | | --------------- | ---- | ------- | | PAID | 4 | 1937250 | | DELIVERED | 3 | 888350 | | CANCELLED | 1 | 200000 | | PACKING | 1 | 125000 | | PENDING_PAYMENT | 1 | 155000 | | SHIPPED | 1 | 447300 | ### HAVING β€” guruhlarni filtrlash `WHERE` qatorlarni guruhlashdan **oldin**, `HAVING` guruhlarni guruhlashdan **keyin** filtrlaydi: ```sql SELECT user_id, COUNT(*) AS buyurtmalar FROM orders GROUP BY user_id HAVING COUNT(*) >= 2 ORDER BY buyurtmalar DESC, user_id; ``` | user_id | buyurtmalar | | ------- | ----------- | | 1 | 3 | | 2 | 3 | | 3 | 2 | | 9 | 2 | ### NULL va agregatlar `COUNT(*)` hamma qatorni sanaydi, `COUNT(ustun)` esa faqat `NULL` bo'lmaganlarni: ```sql SELECT NULL = NULL AS teng, NULL IS NULL AS is_null, COUNT(promo_code) AS promo_bor, COUNT(*) AS hammasi FROM orders; ``` | teng | is_null | promo_bor | hammasi | | ---- | ------- | --------- | ------- | | NULL | 1 | 2 | 11 | `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 ```sql 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](/lessons/images/qa/qa_12_sql_joins.svg) ### INNER JOIN β€” faqat moslar ```sql 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; ``` | order_id | full_name | status | total | | -------- | ---------------- | --------- | ------- | | 1 | Aziza Karimova | DELIVERED | 280100 | | 3 | Aziza Karimova | PAID | 262250 | | 4 | Dilnoza Yusupova | PAID | 290000 | | 5 | Bekzod Rahimov | PAID | 1250000 | | 7 | Otabek Nazarov | DELIVERED | 299250 | | 10 | Bekzod Rahimov | DELIVERED | 309000 | | 11 | Otabek Nazarov | PAID | 135000 | `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: ```sql 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; ``` | id | full_name | buyurtmalar | | --- | ---------------- | ----------- | | 1 | Aziza Karimova | 3 | | 2 | Bekzod Rahimov | 3 | | 3 | Dilnoza Yusupova | 2 | | 4 | Jasur Toshmatov | 0 | | 5 | Malika Ergasheva | 0 | | 6 | DemoBozor Admin | 0 | | 7 | Sardor Aliyev | 1 | | 8 | Nodira Saidova | 0 | | 9 | Otabek Nazarov | 2 | | 10 | Aziza Karimova | 0 | 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: ```sql 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; ``` | id | full_name | is_premium | | --- | -------------- | ---------- | | 8 | Nodira Saidova | 1 | | 10 | Aziza Karimova | 0 | Xuddi shu usul bilan **yetim (orphan) yozuvlarni** topamiz β€” mavjud bo'lmagan buyurtmaga tegishli mahsulot qatorlari: ```sql 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; ``` | item_id | order_id | product_id | | ------- | -------- | ---------- | | 17 | 999 | 4 | 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: ```sql 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: ```sql 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; ``` | order_id | status | payment_method | payment_id | pay_status | | -------- | --------------- | -------------- | ---------- | ---------- | | 5 | PAID | UZCARD | NULL | NULL | | 6 | PENDING_PAYMENT | HUMO | NULL | NULL | | 8 | PACKING | CASH | NULL | NULL | 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 ```sql 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; ``` | order_id | mahsulot | quantity | unit_price | qator_summa | | -------- | --------------- | -------- | ---------- | ----------- | | 4 | Paxta ko'ylak | 2 | 125000 | 250000 | | 4 | Telefon g'ilofi | 1 | 50000 | 50000 | **Self-join** β€” jadvalni o'zi bilan bog'lash. Telefon raqami probellarni olib tashlagandan keyin bir xil bo'lib qoladigan foydalanuvchi juftliklari: ```sql 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; ``` | id_1 | id_2 | full_name | phone_1 | phone_2 | | ---- | ---- | -------------- | ------------- | ----------------- | | 1 | 10 | Aziza Karimova | +998901234567 | +998 90 123 45 67 | `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: ```sql 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; ``` | id | qatorlar | tolangan_xato | | --- | -------- | ------------- | | 3 | 2 | 524500 | 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: ```sql 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; ``` | id | total | tolangan | | --- | ------ | -------- | | 3 | 262250 | 262250 | > **⚠️ 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 ```sql 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: ```sql 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' ); ``` | id | status | payment_method | total | | --- | ------ | -------------- | ------- | | 5 | PAID | UZCARD | 1250000 | 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 ```sql 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; ``` | id | total | segment | | --- | ------- | ------- | | 5 | 1250000 | katta | | 2 | 447300 | o'rta | | 10 | 309000 | o'rta | | 7 | 299250 | kichik | 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: ```sql 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; ``` | id | user_id | created_at | birinchi | | --- | ------- | ------------------- | -------- | | 1 | 1 | 2026-09-02 05:15:00 | ha | | 3 | 1 | 2026-10-01 04:00:00 | yo'q | | 9 | 1 | 2026-10-06 09:45:00 | yo'q | | 7 | 9 | 2026-10-04 11:30:00 | ha | | 11 | 9 | 2026-10-08 16:25:00 | yo'q | 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? ```sql SELECT id, created_at AS utc, datetime(created_at, '+5 hours') AS toshkent FROM orders WHERE id IN (1, 3, 11); ``` | id | utc | toshkent | | --- | ------------------- | ------------------- | | 1 | 2026-09-02 05:15:00 | 2026-09-02 10:15:00 | | 3 | 2026-10-01 04:00:00 | 2026-10-01 09:00:00 | | 11 | 2026-10-08 16:25:00 | 2026-10-08 21:25:00 | Oy bo'yicha guruhlash va oxirgi 7 kun (testlar takrorlanuvchan bo'lishi uchun `'now'` o'rniga aniq sana): ```sql 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; ``` | oy | soni | summa | | ------- | ---- | ------- | | 2026-09 | 2 | 727400 | | 2026-10 | 9 | 3025500 | 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": ```sql 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: ```sql 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](/ios-lessons/qa-test-design-1) 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` ```sql 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 ```sql 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; ``` | order_id | subtotal | items_summa | | -------- | -------- | ----------- | | 6 | 150000 | 165000 | **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. ```sql 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; ``` | id | subtotal | is_premium | delivery_fee | kutilgan_fee | | --- | -------- | ---------- | ------------ | ------------ | | 4 | 300000 | 0 | 20000 | 0 | | 10 | 289000 | 1 | 20000 | 0 | **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: ```sql SELECT id, promo_code, subtotal, discount, ROUND(100.0 * discount / subtotal, 1) AS foiz FROM orders WHERE discount > subtotal * 0.15; ``` | id | promo_code | subtotal | discount | foiz | | --- | ---------- | -------- | -------- | ---- | | 7 | KUZ2026 | 399000 | 99750 | 25.0 | Endi aniq tekshiruv: har bir buyurtma uchun kutilgan chegirmani CTE + window funksiya bilan hisoblaymiz (pul uchun butun son arifmetikasi β€” `* foiz / 100`): ```sql 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; ``` | id | user_id | nth | promo_code | subtotal | discount | kutilgan | | --- | ------- | --- | ---------- | -------- | -------- | -------- | | 7 | 9 | 1 | KUZ2026 | 399000 | 99750 | 59850 | **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: ```sql 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; ``` | id | total | tolangan | | --- | ------ | -------- | | 11 | 135000 | 130000 | **Bug:** birinchi urinish `FAILED` bo'lgan, qayta urinishda 5 000 so'm kam yechilgan. ### 6) Mahsulot qoldig'i, telefon formati, dublikatlar ```sql 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; ``` | id | name | stock | | --- | ------------ | ----- | | 7 | Atlas ro'mol | -2 | | id | full_name | phone | | --- | -------------- | ----------------- | | 7 | Sardor Aliyev | +99890123456 | | 10 | Aziza Karimova | +998 90 123 45 67 | | normal_phone | soni | idlar | | ------------- | ---- | ----- | | +998901234567 | 2 | 1,10 | **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](/ios-lessons/qa-ci-cd)): ```sql 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]'; ``` | tekshiruv | xatolar | | ------------------------- | ------- | | orphan order_items | 1 | | subtotal <> items summasi | 1 | | to'lovsiz PAID buyurtma | 1 | | manfiy qoldiq (stock) | 1 | | telefon formati xato | 2 | ### API javobi vs baza: aniq buyurtmani tekshirish Tasavvur qiling, Dilnoza hisobidan xayoliy DemoBozor API orqali buyurtma berdingiz va javob keldi: ```json { "id": 4, "status": "PAID", "subtotal": 300000, "deliveryFee": 20000, "total": 290000 } ``` API "muvaffaqiyatli" dedi. Endi bazaga qaraymiz: ```sql 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; ``` | id | status | subtotal | discount | delivery_fee | total | items_sum | success_payments | | --- | ------ | -------- | -------- | ------------ | ------ | --------- | ---------------- | | 4 | PAID | 300000 | 30000 | 20000 | 290000 | 300000 | 1 | API va baza bir-biriga mos β€” demak bug "ma'lumot saqlashda" emas, **hisoblash qoidasida**. Bug report sarlavhasi ([QA 8](/ios-lessons/qa-bug-reports) 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 ```sql 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'; ``` | id | phone | full_name | role | is_premium | created_at | | --- | ------------- | ------------ | ----- | ---------- | ---------- | | 11 | +998935550011 | QA Test User | buyer | 0 | NULL | `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 ```sql 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: ```sql 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 | Qoida | Nega | | ------------------------------------------------------------------------- | ------------------------------------------------ | | Test yozuvlarini belgilang (`QA Test ...` ism, alohida telefon diapazoni) | Keyin topish va tozalash oson | | Har bir test o'z ma'lumotini yaratsin va tozalasin | Testlar bir-biriga bog'liq bo'lmaydi | | Haqiqiy shaxsiy ma'lumot (PII) ishlatmang | Maxfiylik va qonun talablari | | Production nusxasidan foydalansangiz β€” anonimlashtiring | Telefon, ism, karta ma'lumotlari sizib chiqmasin | | Katta o'zgarishdan oldin zaxira nusxa oling | `ROLLBACK` har doim ham yordam bermaydi | --- ## 11. Cheklovlar, indekslar va EXPLAIN {#cheklovlar} Cheklovlar β€” bazaning "himoya chizig'i". QA ularni ataylab buzishga urinib, ishlayotganini tekshiradi: ```sql 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 ```sql 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" ```sql 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'; ``` | id | name | price | turi | | --- | ------------- | ----- | ---- | | 9 | Test mahsulot | 12abc | text | `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: ```sql 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](/ios-lessons/qa-performance-k6). --- ## 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: | Mavzu | SQLite | PostgreSQL | | ------------------- | ------------------------------------- | ----------------------------------------------------- | | Avtomatik ID | `INTEGER PRIMARY KEY` | `GENERATED ALWAYS AS IDENTITY` yoki `SERIAL` | | Sana/vaqt turi | `TEXT` (ISO-8601) | `TIMESTAMP` / `TIMESTAMPTZ` | | Hozirgi vaqt | `datetime('now')` | `NOW()` | | Oraliq | `datetime(x, '-7 days')` | `x - INTERVAL '7 days'` | | Vaqt zonasi | `datetime(x, '+5 hours')` | `x AT TIME ZONE 'Asia/Tashkent'` | | Oy bo'yicha | `strftime('%Y-%m', x)` | `date_trunc('month', x)` yoki `to_char(x, 'YYYY-MM')` | | Mantiqiy tur | `INTEGER` 0/1 | `BOOLEAN` true/false | | `LIKE` registri | Lotin harflarda registrni farqlamaydi | Farqlaydi; farqlamaslik uchun `ILIKE` | | Ikki qiymat kattasi | `MAX(a, b)` | `GREATEST(a, b)` | | Satrlarni yig'ish | `GROUP_CONCAT(id ORDER BY id)` | `string_agg(id::text, ',' ORDER BY id)` | | Regex | `GLOB` (cheklangan shablon) | `~` operatori (to'liq regex) | | Foreign key | `PRAGMA foreign_keys = ON` kerak | Doim yoqiq | | Turlar | Moslashuvchan (`STRICT`'siz) | Qat'iy | | `GROUP BY` | Guruhlanmagan ustunga ruxsat beradi | Xato beradi | ### SQL injection β€” QA nimani bilishi kerak Agar backend so'rovni satrlarni yopishtirib yasasa: ```javascript // ❌ 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: ```javascript // βœ… 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](/ios-lessons/qa-security). ### 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: ```javascript // 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.
πŸ’‘ Javobni ko'rish ```sql SELECT name, price FROM products WHERE price < 100000 ORDER BY price; ``` | name | price | | ------------------------------- | ----- | | Non savati | 35000 | | Telefon g'ilofi | 50000 | | Kitob: Ingliz tili grammatikasi | 65000 |
### Mashq 2: To'lov usullari statistikasi 🟒 Har bir to'lov usuli bo'yicha buyurtmalar sonini chiqaring (ko'pidan kamiga).
πŸ’‘ Javobni ko'rish ```sql SELECT payment_method, COUNT(*) AS soni FROM orders GROUP BY payment_method ORDER BY soni DESC, payment_method; ``` | payment_method | soni | | -------------- | ---- | | UZCARD | 4 | | HUMO | 3 | | VISA | 2 | | CASH | 1 | | MASTERCARD | 1 |
### 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.
πŸ’‘ Javobni ko'rish ```sql 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; ``` | full_name | buyurtmalar | jami | | -------------- | ----------- | ------- | | Bekzod Rahimov | 3 | 2006300 | | Nodira Saidova | 0 | 0 | `LEFT JOIN` Nodirani saqlab qoladi, `COALESCE` esa uning `NULL` summasini 0 ga aylantiradi. `INNER JOIN` yozsangiz Nodira yo'qolib qoladi.
### Mashq 4: Sotuvchilar tushumi 🟑 Har bir sotuvchi bo'yicha sotilgan mahsulotlar summasini (`quantity * unit_price`) hisoblang. Bekor qilingan va qaytarilgan buyurtmalar hisobga olinmasin.
πŸ’‘ Javobni ko'rish ```sql 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; ``` | sotuvchi | tushum | | ---------------- | ------- | | Malika Ergasheva | 2120000 | | Jasur Toshmatov | 1574000 | 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.
### Mashq 5: To'lov urinishlari tarixi 🟑 Bir nechta to'lov yozuvi bor buyurtmalar uchun statuslar ketma-ketligini (vaqt bo'yicha) bitta satrda chiqaring.
πŸ’‘ Javobni ko'rish ```sql 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; ``` | order_id | tarix | | -------- | ------------------- | | 9 | SUCCESS -> REFUNDED | | 11 | FAILED -> SUCCESS | 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.
### Mashq 6: Bekor qilinganlarda pul qaytdimi 🟑 Har bir `CANCELLED` buyurtma uchun muvaffaqiyatli to'langan va qaytarilgan summalarni yonma-yon ko'rsating.
πŸ’‘ Javobni ko'rish ```sql 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; ``` | id | status | tolangan | qaytarilgan | | --- | --------- | -------- | ----------- | | 9 | CANCELLED | 200000 | 200000 | Hammasi joyida. `SUM(CASE …)` β€” bitta so'rovda bir nechta shartli yig'indini olishning klassik usuli ("pivot").
### 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?
πŸ’‘ Javobni ko'rish ```sql 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; ``` | order_id | name | unit_price | hozirgi_narx | | -------- | --------------- | ---------- | ------------ | | 2 | Telefon g'ilofi | 49000 | 50000 | **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.
### 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.
πŸ’‘ Javobni ko'rish ```sql BEGIN; UPDATE users SET is_premium = 0 WHERE id = 2; 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; ROLLBACK; SELECT id, full_name, is_premium FROM users WHERE id = 2; -- is_premium yana 1 ``` Tranzaksiya ichidagi natija: | id | subtotal | is_premium | delivery_fee | kutilgan_fee | | --- | -------- | ---------- | ------------ | ------------ | | 4 | 300000 | 0 | 20000 | 0 | 10-buyurtma ro'yxatdan "yo'qoldi" β€” endi u bug bo'lib ko'rinmaydi. Bu muhim saboq: `is_premium` β€” foydalanuvchining **hozirgi** holati, buyurtma paytidagi holat esa saqlanmagan. Agar mijoz premium'ni keyinroq bekor qilsa, bunday tekshiruv yolg'on xulosa beradi. Yaxshi model `orders` jadvalida buyurtma paytidagi holatni (masalan, `was_premium`) ham saqlaydi β€” bu dasturchilarga beriladigan qimmatli taklif.
### Mashq 9: NOT IN tuzog'ini tushuntiring πŸ”΄ Hamkasbingiz "hech qachon sotilmagan mahsulotlar"ni `WHERE id NOT IN (SELECT product_id FROM order_items)` bilan qidiryapti. Bir kuni `order_items.product_id` ustuniga `NULL` tushib qolsa nima bo'ladi va qanday yozish to'g'ri?
πŸ’‘ Javobni ko'rish Subquery natijasida bitta `NULL` paydo bo'lishi bilan `NOT IN` **hech qanday qator qaytarmaydi** β€” so'rov "hamma mahsulot sotilgan" degan yolg'on javob beradi (`5 NOT IN (1, 2, NULL)` = `NULL`). Xavfsiz variant: ```sql SELECT p.id, p.name FROM products AS p WHERE NOT EXISTS (SELECT 1 FROM order_items AS oi WHERE oi.product_id = p.id); ``` Bizning bazada natija bo'sh β€” barcha 8 mahsulot kamida bir marta sotilgan. Lekin endi bu javobga ishonsa bo'ladi.
--- ## O'zingizni tekshiring {#checklist} - [ ] `demobozor.sql`'ni yuklab, `.tables` bilan 5 ta jadvalni ko'rdim - [ ] `WHERE`'da `AND`/`OR` aralashganda qavs qo'yaman - [ ] `NULL` uchun `=` emas, `IS NULL` ishlataman; `COUNT(*)` va `COUNT(ustun)` farqini bilaman - [ ] `LEFT JOIN … WHERE o'ng.id IS NULL` bilan yetim yozuvlarni topa olaman - [ ] JOIN ko'payishi summani buzishini bilaman va uni subquery bilan chetlab o'taman - [ ] Sana oralig'ini `>= ... AND < ...` ko'rinishida yozaman - [ ] Biznes qoidasini `CASE` bilan "kutilgan qiymat"ga aylantirib, haqiqiy qiymat bilan solishtira olaman - [ ] `UPDATE`/`DELETE`'dan oldin o'sha shart bilan `SELECT` qilaman va eksperimentni tranzaksiyada bajaraman --- ## Xulosa {#xulosa} - UI "muvaffaqiyatli" desa ham, haqiqat bazada. SQL β€” QA'ning eng ishonchli "test oracle"laridan biri. - Asosiy to'plam: `SELECT/WHERE/ORDER BY`, agregatlar bilan `GROUP BY/HAVING`, `JOIN`'lar, `EXISTS`, `CASE`, window funksiyalar. - QA tekshiruvining formulasi: **biznes qoidasi β†’ SQL sharti β†’ qoidani buzgan qatorlar**. Bo'sh natija β€” yaxshi xabar. - Klassik tuzoqlar: `NULL = NULL`, `NOT IN` + `NULL`, qavssiz `AND/OR`, JOIN ko'payishi, vaqtli `BETWEEN`, vaqt zonasi. - SQL natijasi β€” dalil; uni bug yoki "normal holat" deb talqin qilish biznes qoidalari va ma'lumot modelini bilishni talab qiladi. - O'zgartiruvchi buyruqlar β€” faqat test bazasida, `WHERE` bilan va iloji bo'lsa tranzaksiya ichida. - SQLite va PostgreSQL asosan bir xil, lekin FK, turlar, `LIKE`, sana funksiyalari va `GROUP BY` qat'iyligida farq qiladi. - **Keyingi dars:** [QA 13: Terminal, Git, Docker va Loglar β€” QA'ning muhandislik asboblari](/ios-lessons/qa-terminal-git-logs) ## Manbalar {#manbalar} 1. [SQLite β€” SELECT sintaksisi](https://sqlite.org/lang_select.html) 2. [SQLite β€” Foreign Key Support (default o'chiqligi)](https://sqlite.org/foreignkeys.html) 3. [SQLite β€” Datatypes (type affinity va STRICT)](https://sqlite.org/datatype3.html) 4. [SQLite β€” Window Functions](https://sqlite.org/windowfunctions.html) 5. [SQLite β€” RETURNING clause](https://sqlite.org/lang_returning.html) 6. [SQLite β€” EXPLAIN QUERY PLAN](https://sqlite.org/eqp.html) 7. [SQLite β€” Aggregate funksiyalar](https://sqlite.org/lang_aggfunc.html) 8. [SQLite 3.39.0 reliz eslatmalari (RIGHT va FULL OUTER JOIN)](https://sqlite.org/releaselog/3_39_0.html) 9. [SQLite β€” Command Line Shell (`sqlite3`)](https://sqlite.org/cli.html) 10. [PostgreSQL β€” SELECT](https://www.postgresql.org/docs/current/sql-select.html) 11. [PostgreSQL β€” Conditional Expressions (CASE, COALESCE, GREATEST)](https://www.postgresql.org/docs/current/functions-conditional.html) 12. [PostgreSQL β€” Date/Time Types](https://www.postgresql.org/docs/current/datatype-datetime.html) 13. [OWASP β€” SQL Injection Prevention Cheat Sheet](https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html) 14. [MongoDB β€” db.collection.find()](https://www.mongodb.com/docs/manual/reference/method/db.collection.find/) 15. [DB Browser for SQLite](https://sqlitebrowser.org/)