SELECT, WHERE, ORDER BY

2-bob 13 kod bloki 3702 so'z

Analyst SQL da qiladigan hamma ish bitta ifodaning turli ko'rinishlaridan iborat. Hatto oltita JOIN va uchta window function bor 200 qatorli hisobot query si ham tuzilishiga ko'ra baribir SELECT ... FROM ... WHERE ... ORDER BY .... Shuning uchun ustiga biror narsa qo'shishdan oldin bu to'rtta clause ni juda yaxshi bilib olish foydali.

Quyidagi har bir query 1-bobdagi school.db ustida ishlaydi.

Column larni tanlash

SELECT "qaysi column larni ko'rmoqchisiz?" degan savolga javob beradi — va column lar table dagi tartibda emas, siz nomlagan tartibda chiqadi.

-- barcha column lar (faqat o'rganish uchun)
SELECT * FROM students;

-- nomlangan column lar, SIZ tanlagan tartibda
SELECT last_name, first_name, city FROM students;

-- bitta column bir necha marta kelishi mumkin, konstanta ham mumkin
SELECT first_name, first_name, 'student' FROM students;

1-natija

… 300 qatordan birinchi 15 tasi

300 qator

2-natija

… 300 qatordan birinchi 15 tasi

300 qator

3-natija

… 300 qatordan birinchi 15 tasi

300 qator

2 ms

Oxirgisi ma'nosizdek ko'rinadi, lekin u muhim g'oyani o'rgatadi: SELECT yangi natija table ini quradi. U sizga table ni "ko'rsatmaydi" — siz yozgan ifodalardan column-ma-column yangi table yasaydi.

Hisoblanadigan column lar (expressions)

Hisoblab bo'ladigan har qanday narsani SELECT ichiga yozish mumkin. Natija faqat chiqishda mavjud bo'lgan yangi column ga aylanadi — table ga hech narsa yozilmaydi.

SELECT
    title,
    price,
    price * 0.9              AS discounted_price,
    price / credits          AS price_per_credit,
    credits * 30             AS total_hours
FROM courses;

15 qator

0 ms

:::gotcha ⚠️ Data Visualization ga qarang: 316666.67 emas, 316666 950000 / 3 natijasi 316666 chiqadi, chunki SQL da butun son ÷ butun son = butun son — kasr qismi yaxlitlanmaydi, shunchaki tashlab yuboriladi. Bu o'rtacha qiymatlar va foizlarni indamay buzadi. 1.0 ga ko'paytirib kasrga majburlang: price * 1.0 / credits, yoki cast qiling: CAST(price AS REAL) / credits. 4-bobda bunga qaytamiz — u yerda bu foiz hisobini butunlay buzadi. :::

Matnlarni birlashtirish

SQLite va PostgreSQL da || string larni yopishtiradi:

SELECT
    first_name || ' ' || last_name        AS full_name,
    last_name  || ', ' || first_name      AS sorted_name,
    UPPER(city)                           AS city_upper,
    LENGTH(first_name)                    AS name_length
FROM students
LIMIT 4;

4 qator

0 ms

:::gotcha ⚠️ NULL butun string ni yutib yuboradi Sevara da city NULL, shuning uchun UPPER(city) ham NULL — va agar city || ', Uzbekistan' deb yozsangiz, natija ', Uzbekistan' emas, butunlay NULL bo'ladi. O'rang: COALESCE(city, 'unknown') || ', Uzbekistan'. COALESCE ni 3-bobda batafsil ko'ramiz. :::

Alias lar (AS)

Alias — chiqishda column (yoki table) ga yangi nom berish. Alias siz hisoblangan column ning sarlavhasi xom ifodaning o'zi bo'lib qoladi — hisobotda o'qib bo'lmaydi.

-- sarlavha: price * 0.9        ← xunuk
SELECT title, price * 0.9 FROM courses;

-- sarlavha: discounted_price   ← o'qish oson
SELECT title, price * 0.9 AS discounted_price FROM courses;

-- AS ixtiyoriy, lekin baribir yozing — o'qish ancha oson bo'ladi
SELECT title, price * 0.9 discounted_price FROM courses;

-- bo'sh joyi bor nom uchun ikkitali qo'shtirnoq kerak (identifier quoting)
SELECT title AS "Course Title", price AS "Price (UZS)" FROM courses;

1-natija

15 qator

2-natija

15 qator

3-natija

15 qator

4-natija

15 qator

1 ms

Table alias lari Table larga ham alias beriladi. 5-bobdan boshlab ular zarur bo'lib qoladi — ikkita table da ham student_id column i bo'lganda qaysi birini nazarda tutayotganingizni aytishingiz kerak.

SELECT s.first_name, s.city
FROM students AS s
WHERE s.city = 'Tashkent';

… 82 qatordan birinchi 15 tasi

82 qator

0 ms

:::tip 💡 Alias ni nomlash Qisqa, lekin ma'noli: students uchun s, enrollments uchun e, courses uchun c. a, b, t1, t2 dan qoching — olti oydan keyin t2 nima ekanini hech kim bilmaydi. :::

WHERE va solishtirish operatorlari

WHERE row ni faqat shart TRUE bo'lgandagina qoldiradi. FALSE row lar tashlanadi — UNKNOWN row lar ham tashlanadi, bu esa 1-bobdagi NULL muammosi.

-- "Qaysi enrollment lar 60 dan past ball oldi?"
SELECT enrollment_id, student_id, course_id, grade, status
FROM enrollments
WHERE grade < 60;

… 192 qatordan birinchi 15 tasi

192 qator

1 ms

AND, OR, NOT — va ustunlik tartibi

-- ikkala shart ham bajarilishi kerak
SELECT first_name, city, gender FROM students
WHERE city = 'Tashkent' AND gender = 'M';        -- Jasur, Otabek

-- shartlardan bittasi yetarli
SELECT first_name, city FROM students
WHERE city = 'Bukhara' OR city = 'Fergana';      -- Dilnoza, Ulugbek

-- inkor
SELECT first_name, status FROM enrollments
WHERE NOT status = 'completed';                  -- ehtiyot: NULL status lar yo'qolardi

:::gotcha ⚠️ AND, OR dan kuchliroq bog'laydi Xuddi arifmetikada × + dan kuchliroq bog'langani kabi. Bu ikkita query bir xil emas:

-- shunday o'qiladi: Tashkent, YOKI (Samarkand VA ayol)
-- natija: 3 ta Toshkentlik (ikkala jins) + Zarina = 4 ta row
WHERE city = 'Tashkent' OR city = 'Samarkand' AND gender = 'F';

-- aslida nazarda tutilgani: (Tashkent YOKI Samarkand) VA ayol
-- natija: Aziza + Zarina = 2 ta row
WHERE (city = 'Tashkent' OR city = 'Samarkand') AND gender = 'F';

Talabalar uchun qoida: shart ichida AND va OR aralashgan zahoti qavs qo'ying. Qavs ortiqcha bo'lgan joyda ham u hech narsa turmaydi va hamma shubhani yo'q qiladi. :::

ORDER BY

Table dagi row larning tartibi yo'q, shuning uchun chiqish tartibi muhim bo'lsa, uni so'rashingiz kerak. ASC (o'sish bo'yicha) — standart; DESC teskarisiga saralaydi.

-- bitta column
SELECT first_name, enrolled_date FROM students
ORDER BY enrolled_date DESC;

-- bir nechta column: avval city, keyin har bir city ichida ism
SELECT city, first_name FROM students
ORDER BY city ASC, first_name DESC;

-- ifoda bo'yicha yoki alias bo'yicha saralash mumkin
SELECT title, price / credits AS per_credit FROM courses
ORDER BY per_credit DESC;

-- hatto SELECT qilmagan column bo'yicha ham saralash mumkin
SELECT first_name FROM students
ORDER BY birth_date;

1-natija

… 300 qatordan birinchi 15 tasi

300 qator

2-natija

… 300 qatordan birinchi 15 tasi

300 qator

3-natija

15 qator

4-natija

… 300 qatordan birinchi 15 tasi

300 qator

1 ms

:::gotcha ⚠️ NULL lar qayerga saralanadi? Bizning student lar ustida ORDER BY city SQLite va MySQL da Sevara (NULL) ni birinchi qo'yadi, chunki ular NULL ni hamma narsadan kichik deb hisoblaydi. PostgreSQL va Oracle esa standart holatda NULL larni oxiriga qo'yadi. O'rin muhim bo'lsa, aniq yozing — PostgreSQL da ORDER BY city NULLS LAST bor; hamma joyda ishlaydigan universal usul esa:

ORDER BY (city IS NULL), city;   -- 0 dan keyin 1, ya'ni NULL lar oxirida

:::

LIMIT va OFFSET

LIMIT nechta row qaytishini cheklaydi. U eng oxirgi qadam, shuning uchun saralashdan keyin qo'llanadi.

-- eng yuqori 3 ta baho
SELECT enrollment_id, student_id, grade
FROM enrollments
WHERE grade IS NOT NULL
ORDER BY grade DESC
LIMIT 3;

3 qator

0 ms

enrollment_idstudent_idgrade

136100 6395 1192

OFFSET avval bir necha row ni tashlab ketadi — pagination shunday ishlaydi:

SELECT first_name FROM students ORDER BY student_id LIMIT 3;             -- 1-sahifa
SELECT first_name FROM students ORDER BY student_id LIMIT 3 OFFSET 3;   -- 2-sahifa
SELECT first_name FROM students ORDER BY student_id LIMIT 3 OFFSET 6;   -- 3-sahifa

1-natija

3 qator

2-natija

3 qator

3-natija

3 qator

1 ms

:::gotcha ⚠️ ORDER BY siz LIMIT ma'nosiz Yolg'iz LIMIT 10 "database qaysi 10 ta row ni birinchi bersa, o'shani" degani — va bu to'plam har safar o'zgarishi mumkin. LIMIT faqat ORDER BY bilan birga ma'noga ega. Pagination ga ham shu tegishli: barqaror saralash bo'lmasa, 2-sahifada 1-sahifadagi row lar takrorlanishi mumkin. :::

DISTINCT

DISTINCT natijadagi takrorlanuvchi row larni olib tashlaydi. "Bu column da qanday qiymatlar uchraydi?" degan savolga javob berishning eng tez usuli.

SELECT DISTINCT city FROM students ORDER BY city;

city

NULL Andijan Bukhara Fergana Namangan Samarkand Tashkent

10 ta student dan 7 ta row. E'tibor bering: DISTINCT NULL ni ham qiymatlardan biri sifatida saqlaydi — NULL = NULL UNKNOWN bo'lsa ham, u barcha NULL larni bir-birining takrori deb hisoblaydi. Bu SQL ning o'zidagi nomuvofiqlik; talabalarga buni maxsus holat deb ayting.

Mashqlar to'plami

Talabalarga shu tartibda bering; har biri aynan bitta yangi g'oya qo'shadi.

-- 1. Ayol student lar, familiya bo'yicha alifbo tartibida
SELECT first_name, last_name FROM students
WHERE gender = 'F'
ORDER BY last_name;

-- 2. 1 300 000 dan qimmat course lar, eng qimmati birinchi
SELECT title, price FROM courses
WHERE price > 1300000
ORDER BY price DESC;

-- 3. To'liq ism + city, noma'lum city 'unknown' bo'lib chiqsin
SELECT first_name || ' ' || last_name AS full_name,
       COALESCE(city, 'unknown')      AS city
FROM students;

-- 4. Haqiqatan qo'yilgan eng past 3 ta baho
SELECT enrollment_id, grade FROM enrollments
WHERE grade IS NOT NULL
ORDER BY grade ASC
LIMIT 3;

-- 5. enrollments da qanday status lar bor?
SELECT DISTINCT status FROM enrollments;

-- 6. Data kategoriyasidagi, 4 va undan ko'p credit li course lar
SELECT title, credits FROM courses
WHERE category = 'Data' AND credits >= 4
ORDER BY credits DESC, title;

-- 7. Toshkent yoki Samarqanddan bo'lgan ayol student lar (qavs!)
SELECT first_name, city FROM students
WHERE (city = 'Tashkent' OR city = 'Samarkand') AND gender = 'F';

-- 8. Har bir credit uchun narx, to'g'ri kasr ko'rinishida
SELECT title, ROUND(price * 1.0 / credits, 2) AS per_credit
FROM courses
ORDER BY per_credit DESC;

1-natija

… 154 qatordan birinchi 15 tasi

154 qator

2-natija

4 qator

3-natija

… 300 qatordan birinchi 15 tasi

300 qator

4-natija

3 qator

5-natija

4 qator

6-natija

2 qator

7-natija

… 67 qatordan birinchi 15 tasi

67 qator

8-natija

15 qator

2 ms

Intervyu savollari

S: WHERE ichida column alias ini ishlatsa bo'ladimi?

Yo'q. WHERE 2-qadamda ishlaydi, alias yaratiladigan SELECT esa 5-qadamda. Ifodani WHERE ichida takrorlang yoki query ni CTE/subquery ga o'rang. ORDER BY (7-qadam) esa alias ni ishlata oladi.

S: DISTINCT va GROUP BY orasidagi farq nima?

Faqat unique qiymatlarni sanab chiqish uchun ular bir xil javob beradi, va ko'p engine lar ularni bir xil bajaradi. GROUP BY kuchliroq: u har bir guruh uchun aggregate hisoblay oladi (COUNT, AVG) va HAVING bilan filtrlanadi. Faqat unique row lar kerak bo'lsa DISTINCT, guruh bo'yicha biror narsa hisoblash kerak bo'lsa GROUP BY ishlating.

S: DISTINCT bitta column ga qo'llanadimi yoki hammasiga?

Tanlangan barcha column larga birgalikda. SELECT DISTINCT a, b (a, b) ning unique juftliklarini qaytaradi.

S: WHERE city != 'Tashkent' — city si NULL bo'lgan row lar qaytadimi?

Yo'q, va bu klassik tuzoq savol. NULL != 'Tashkent' — UNKNOWN, WHERE esa faqat TRUE ni qoldiradi. WHERE city != 'Tashkent' OR city IS NULL deb yozing.

S: ORDER BY siz LIMIT 10 nima qaytaradi?

Tasodifiy 10 ta row — engine qaysinisini birinchi hosil qilgan bo'lsa, o'shani. Har safar bir xil bo'lishi kafolatlanmaydi, shuning uchun u faqat data ni ko'z bilan ko'rish uchun xavfsiz, hisobot uchun hech qachon emas.

S: Nega SELECT 7/2 3 qaytaradi?

Butun son ÷ butun son = butun son; kasr qismi yaxlitlanmaydi, kesib tashlanadi. 7 * 1.0 / 2 yoki CAST(7 AS REAL) / 2 ishlating.

S: ORDER BY name va ORDER BY 2 farqi nima?

Ikkinchisi SELECT ro'yxatidagi ikkinchi column bo'yicha, ya'ni o'rni bo'yicha saralaydi. Ishlaydi, lekin mo'rt: SELECT ro'yxatini tahrirlash saralashni indamay o'zgartiradi.

Xulosa

• SELECT yangi table quradi: column lar siz nomlagan tartibda chiqadi va ular ifoda, konstanta yoki birlashtirilgan matn bo'lishi mumkin.
• Butun bo'lish kasrni kesib tashlaydi — price / credits kasrni yo'qotadi. 1.0 ga ko'paytiring yoki CAST qiling.
• Alias lar (AS) chiqish column lariga nom beradi; ular ORDER BY da ishlaydi, WHERE da hech qachon ishlamaydi.
• WHERE faqat TRUE bo'lgan row larni qoldiradi. NULL row lar UNKNOWN, shuning uchun <> ularni indamay tashlaydi — kerak bo'lsa OR col IS NULL qo'shing.
• AND OR dan kuchliroq bog'laydi. Ularni qavssiz aralashtirish — kutib turgan xato.
• ORDER BY — row tartibini kafolatlashning yagona yo'li; NULL ning o'rni database ga qarab farq qiladi.
• ORDER BY siz LIMIT ma'nosiz; LIMIT ... OFFSET ... — bu pagination.
• DISTINCT butun tanlangan row ga qo'llanadi va NULL ni bitta qiymat sifatida saqlaydi.

Keyingi: 3-bob filtrlashga chuqurroq kiradi — IN, BETWEEN, LIKE pattern lari va real hisobotlarni indamay buzadigan NULL tuzoqlari.