Saralash va ifodalar

ORDER BY

ORDER BY natijani saralaydi: ASC — o‘sish (standart), DESC — kamayish.

SELECT name, price FROM products ORDER BY price DESC;
SELECT name, grade FROM students ORDER BY grade DESC, name ASC;   -- teng bo‘lsa — ism bo‘yicha

Maslahatlar

• Masalada «teng bo‘lsa…» degan qoida bo‘lsa, uni ikkinchi kalit sifatida qo‘shing — aks holda tartib tasodifiy bo‘lib, javob noto‘g‘ri chiqishi mumkin.
• Hisoblangan ustun yoki uning AS nomi bo‘yicha ham saralash mumkin.

LIMIT

LIMIT natijadan faqat dastlabki n qatorni qoldiradi. Deyarli doim ORDER BY bilan birga ishlatiladi.

SELECT name, price FROM products ORDER BY price DESC LIMIT 3;          -- eng qimmat 3 ta
SELECT name FROM students ORDER BY id LIMIT 10 OFFSET 20;              -- 21–30-qatorlar

Diqqat

«Eng katta» chegarada teng qiymatlar bo‘lsa, LIMIT ulardan birini tasodifiy kesib tashlaydi. Masala tenglarni ham so‘rasa, ichki so‘rov ishlating: WHERE price = (SELECT MAX(price) FROM products).

CASE

CASE — SQL ichidagi if/elif/else. Yangi hisoblangan ustun yasash uchun.

SELECT name,
       CASE
           WHEN grade >= 90 THEN 'A'
           WHEN grade >= 75 THEN 'B'
           WHEN grade >= 60 THEN 'C'
           ELSE 'F'
       END AS letter
FROM students;

Shartli sanash

SELECT faculty,
       SUM(CASE WHEN grade >= 4 THEN 1 ELSE 0 END) AS good,
       COUNT(*) AS total
FROM students GROUP BY faculty;

Shartlar yuqoridan pastga tekshiriladi — birinchi mos kelgani olinadi.

Satr funksiyalari

Satrlar bilan ishlash (SQLite):

SELECT LENGTH(name) FROM students;                  -- uzunlik
SELECT UPPER(name), LOWER(name) FROM students;      -- katta / kichik harf
SELECT SUBSTR(name, 1, 3) FROM students;            -- 1-belgidan 3 ta
SELECT first_name || ' ' || last_name AS full_name FROM students;   -- birlashtirish
SELECT TRIM(name) FROM students;                    -- chetdagi bo‘sh joylar
SELECT REPLACE(phone, '-', '') FROM customers;
SELECT INSTR(email, '@') FROM customers;            -- belgining o‘rni

SUBSTR da sanash 1 dan boshlanadi. Domen: SUBSTR(email, INSTR(email, '@') + 1).