1 / 15

SQLite3 - To'liq darsligi

Boshlang'ich darajadan professional darajagacha

🗄️

Oson o'rnatish

Server talab qilmaydi

⚡

Tez ishlaydi

Kichik va samarali

📱

Ko'p platformali

Windows, Mac, Linux

🔒

Xavfsiz

ACID xususiyatlari

2 / 15

🔧 SQLite3 ni o'rnatish

Windows

https://sqlite.org/download.html dan yuklab oling
sqlite3.exe ni PATH ga qo'shing
Terminalda tekshiring: sqlite3 --version
            

macOS

# Homebrew orqali
brew install sqlite3

# MacPorts orqali  
sudo port install sqlite3
            

Ubuntu/Debian

sudo apt update
sudo apt install sqlite3 libsqlite3-dev
            

💡 Maslahat: Ko'pchilik tizmlarda SQLite3 allaqachon o'rnatilgan bo'ladi. Tekshirish uchun: sqlite3 --version

3 / 15

⚡ Asosiy buyruqlar

SQLite3 ga kirish

-- Yangi baza yaratish yoki mavjudini ochish
sqlite3 mening_bazam.db

-- Xotira bazasi (test uchun)
sqlite3 :memory:
            

Asosiy meta-buyruqlar

Buyruq	Tavsif	Misol
.tables	Barcha jadvallar	.tables
.schema	Jadval strukturasi	.schema users
.databases	Ulangan bazalar	.databases
.quit	Chiqish	.quit
.help	Yordam	.help
.mode	Chiqish formatini o'zgartirish	.mode column
.headers on	Sarlavhalarni ko'rsatish	.headers on

🎯 Amaliyot: Terminalda sqlite3 test.db buyrug'ini ishga tushirib, .help buyrug'ini sinab ko'ring.

4 / 15

📊 SQLite3 da ma'lumot turlari

Muhim: SQLite3 dinamik tip tizimiga ega - ustun turi faqat tavsiya, har qator har xil turdagi ma'lumot saqlashi mumkin.

Asosiy turlar

Tur	Tavsif	Misollar
INTEGER	Butun sonlar	1, -5, 1000
REAL	Haqiqiy sonlar	3.14, -2.5, 1.0
TEXT	Matn	'Salom', "Dunyo"
BLOB	Binary ma'lumot	Rasm, fayl
NULL	Bo'sh qiymat	NULL

Keng tarqalgan sinonimlar

-- Bu turlar ham INTEGER sifatida saqlanadi
TINYINT, SMALLINT, MEDIUMINT, BIGINT, INT, INT2, INT8

-- Bu turlar ham TEXT sifatida saqlanadi  
VARCHAR(255), CHAR(10), TEXT, CLOB

-- Bu turlar ham REAL sifatida saqlanadi
DOUBLE, FLOAT, DECIMAL(10,2), NUMERIC

-- Boolean qiymatlar INTEGER sifatida saqlanadi
BOOLEAN  -- 0 = false, 1 = true
            

⚠️ Diqqat: SQLite3 da VARCHAR(100) degan chegara yo'q - TEXT sifatida saqlanadi va istalgancha uzun bo'lishi mumkin.

5 / 15

📅 Sana va vaqt bilan ishlash

Muhim: SQLite3 da DATETIME turi yo'q! Sana-vaqt TEXT, REAL yoki INTEGER sifatida saqlanadi.

Sana formatlar

-- TEXT format (ISO 8601)
'2024-12-25'
'2024-12-25 15:30:00'
'2024-12-25T15:30:00.000Z'

-- INTEGER format (Unix timestamp)
1703516400  -- 2023-12-25 12:00:00 UTC

-- REAL format (Julian day)
2460310.5   -- 2024-12-25 12:00:00
            

Sana-vaqt funksiyalari

-- Hozirgi sana-vaqt
SELECT datetime('now');              -- 2024-12-25 10:30:45
SELECT date('now');                  -- 2024-12-25  
SELECT time('now');                  -- 10:30:45

-- Sana-vaqt bilan amallar
SELECT datetime('now', '+1 day');     -- Ertaga
SELECT datetime('now', '-1 month');   -- Bir oy avval
SELECT datetime('now', '+3 hours');   -- 3 soat keyin
SELECT datetime('now', 'start of year'); -- Yil boshi

-- Formatni o'zgartirish
SELECT strftime('%d/%m/%Y', 'now');   -- 25/12/2024
SELECT strftime('%Y yil %m oy', 'now'); -- 2024 yil 12 oy
            

Amaliy misollar

CREATE TABLE voqealar (
    id INTEGER PRIMARY KEY,
    nom TEXT,
    sana TEXT DEFAULT (datetime('now')),
    yaratilgan INTEGER DEFAULT (strftime('%s', 'now'))
);

-- Ma'lumot qo'shish
INSERT INTO voqealar (nom, sana) VALUES 
('Yig'ilish', '2024-12-25 14:00:00'),
('Prezentatsiya', datetime('now', '+2 days'));

-- Oxirgi 7 kun ichidagi voqealar
SELECT * FROM voqealar 
WHERE date(sana) >= date('now', '-7 days');
            

6 / 15

🏗️ Jadval yaratish

Oddiy jadval

CREATE TABLE talabalar (
    id INTEGER PRIMARY KEY,
    ism TEXT NOT NULL,
    familiya TEXT NOT NULL,
    yosh INTEGER,
    fakultet TEXT,
    kurs INTEGER,
    baho REAL
);
            

Qo'shimcha shartlar bilan

CREATE TABLE mahsulotlar (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nomi TEXT UNIQUE NOT NULL,
    kategoriya TEXT DEFAULT 'Umumiy',
    narx REAL CHECK(narx > 0),
    soni INTEGER DEFAULT 0,
    yaratilgan TEXT DEFAULT (datetime('now')),
    yangilangan TEXT DEFAULT (datetime('now'))
);
            

Chet el kalitlari (Foreign Keys)

-- Fakultetlar jadvali
CREATE TABLE fakultetlar (
    id INTEGER PRIMARY KEY,
    nomi TEXT UNIQUE NOT NULL,
    dekan TEXT
);

-- Talabalar jadvali fakultet bilan bog'langan
CREATE TABLE talabalar_yangi (
    id INTEGER PRIMARY KEY,
    ism TEXT NOT NULL,
    fakultet_id INTEGER,
    kurs INTEGER,
    FOREIGN KEY (fakultet_id) REFERENCES fakultetlar(id)
);

-- Foreign key larni yoqish
PRAGMA foreign_keys = ON;
            

💡 Maslahat: AUTOINCREMENT faqat kerak bo'lganda ishlating - oddiy INTEGER PRIMARY KEY ham avtomatik o'sadi.

7 / 15

📝 CRUD amallar

CREATE - Ma'lumot qo'shish (INSERT)

-- Bitta qator qo'shish
INSERT INTO talabalar (ism, familiya, yosh, fakultet, kurs, baho) 
VALUES ('Ali', 'Valiyev', 20, 'IT', 2, 4.5);

-- Bir nechta qator
INSERT INTO talabalar VALUES 
(1, 'Oybek', 'Karimov', 21, 'IT', 3, 4.2),
(2, 'Nilufar', 'Toshmatova', 19, 'Iqtisod', 1, 4.8),
(3, 'Sardor', 'Rahimov', 22, 'Tibbiyot', 4, 3.9),
(4, 'Malika', 'Norova', 20, 'IT', 2, 4.6);

-- Faqat kerakli ustunlar
INSERT INTO talabalar (ism, familiya, fakultet) 
VALUES ('Jasur', 'Komilov', 'Fizika');
            

READ - Ma'lumot o'qish (SELECT)

-- Barcha ma'lumotlar
SELECT * FROM talabalar;

-- Muayyan ustunlar
SELECT ism, familiya, baho FROM talabalar;

-- Shartli tanlov
SELECT * FROM talabalar WHERE fakultet = 'IT';
SELECT ism, baho FROM talabalar WHERE baho >= 4.0;

-- Hisoblangan ustunlar
SELECT ism, familiya, (baho * 20) AS foiz FROM talabalar;
            

Natija: id | ism | familiya | yosh | fakultet | kurs | baho ---|---------|------------|------|----------|------|----- 1 | Oybek | Karimov | 21 | IT | 3 | 4.2 2 | Nilufar | Toshmatova | 19 | Iqtisod | 1 | 4.8

8 / 15

✏️ UPDATE va DELETE

UPDATE - Ma'lumotni yangilash

-- Bitta talabaning bahosini yangilash
UPDATE talabalar SET baho = 4.7 WHERE id = 1;

-- Bir nechta ustunni yangilash  
UPDATE talabalar 
SET kurs = kurs + 1, yangilangan = datetime('now')
WHERE fakultet = 'IT';

-- Shartli yangilash
UPDATE talabalar 
SET baho = baho + 0.1 
WHERE baho < 4.0 AND kurs >= 3;

-- Barcha qatorlarni yangilash (ehtiyot bo'ling!)
UPDATE talabalar SET yangilangan = datetime('now');
            

DELETE - Ma'lumotni o'chirish

-- Muayyan talabani o'chirish
DELETE FROM talabalar WHERE id = 5;

-- Shartli o'chirish
DELETE FROM talabalar WHERE baho < 3.0;
DELETE FROM talabalar WHERE fakultet = 'Fizika' AND kurs = 1;

-- Barcha ma'lumotlarni o'chirish (ehtiyot bo'ling!)
DELETE FROM talabalar;

-- Jadval tuzilmasini saqlab, ma'lumotlarni o'chirish
DELETE FROM talabalar WHERE 1=1;
            

⚠️ Muhim: DELETE va UPDATE amallarini bajarishdan oleh WHERE shartini tekshiring! Aks holda barcha ma'lumotlar o'zgartirilishi mumkin.

💡 Xavfsizlik: Muhim amallardan oldin BACKUP oling yoki transaction ishlatingl

9 / 15

🔍 WHERE va Filterlash

Taqqoslash operatorlari

-- Tenglik/tengsizlik
SELECT * FROM talabalar WHERE yosh = 20;
SELECT * FROM talabalar WHERE baho != 4.0;
SELECT * FROM talabalar WHERE kurs <> 1;

-- Katta/kichik
SELECT * FROM talabalar WHERE baho > 4.5;
SELECT * FROM talabalar WHERE yosh >= 21;
SELECT * FROM talabalar WHERE kurs < 3;

-- Oraliq (BETWEEN)
SELECT * FROM talabalar WHERE yosh BETWEEN 19 AND 21;
SELECT * FROM talabalar WHERE baho BETWEEN 4.0 AND 4.5;

-- Ro'yxatda bor-yo'qligini tekshirish
SELECT * FROM talabalar WHERE fakultet IN ('IT', 'Iqtisod');
SELECT * FROM talabalar WHERE kurs NOT IN (1, 2);
            

LIKE operatori (pattern matching)

-- % - istalgancha belgi
SELECT * FROM talabalar WHERE ism LIKE 'A%';      -- A bilan boshlanadi
SELECT * FROM talabalar WHERE ism LIKE '%ov';     -- ov bilan tugaydi  
SELECT * FROM talabalar WHERE ism LIKE '%li%';    -- li o'rtasida bor

-- _ - bitta belgi
SELECT * FROM talabalar WHERE ism LIKE 'A_i';     -- A?i (3 ta belgi)
SELECT * FROM talabalar WHERE ism LIKE '_a%';     -- ikkinchi harfi 'a'

-- GLOB operatori (* va ? ishlatadi)
SELECT * FROM talabalar WHERE ism GLOB 'A*';      -- A bilan boshlanadi
SELECT * FROM talabalar WHERE ism GLOB '???';     -- aynan 3 ta belgi
            

NULL qiymatlar

-- NULL qiymatlarni topish
SELECT * FROM talabalar WHERE baho IS NULL;
SELECT * FROM talabalar WHERE telefon IS NULL;

-- NULL bo'lmagan qiymatlar
SELECT * FROM talabalar WHERE baho IS NOT NULL;

-- NULL qiymatlarni almаshtirish
SELECT ism, COALESCE(baho, 0) AS baho FROM talabalar;
SELECT ism, IFNULL(telefon, 'Noma\'lum') AS telefon FROM talabalar;
            

10 / 15

🧠 Mantiqiy operatorlar va murakkab so'rovlar

AND, OR, NOT operatorlari

-- AND - barcha shartlar bajarilishi kerak
SELECT * FROM talabalar 
WHERE fakultet = 'IT' AND kurs >= 2 AND baho > 4.0;

-- OR - kamida bitta shart bajarilishi kerak  
SELECT * FROM talabalar 
WHERE fakultet = 'IT' OR fakultet = 'Iqtisod';

-- NOT - shartni inkor qilish
SELECT * FROM talabalar 
WHERE NOT (fakultet = 'IT' OR kurs = 1);

-- Murakkab shartlar (qavs ishlatish)
SELECT * FROM talabalar 
WHERE (fakultet = 'IT' OR fakultet = 'Fizika') 
  AND (baho >= 4.0 AND kurs >= 2);
            

CASE operatori

-- Shartli qiymat qaytarish
SELECT ism, familiya, baho,
    CASE 
        WHEN baho >= 4.5 THEN 'A\'lo'
        WHEN baho >= 4.0 THEN 'Yaxshi' 
        WHEN baho >= 3.5 THEN 'Qoniqarli'
        ELSE 'Qoniqarsiz'
    END AS daraja
FROM talabalar;

-- Qisqa CASE
SELECT ism, 
    CASE fakultet 
        WHEN 'IT' THEN 'Informatika'
        WHEN 'Iqtisod' THEN 'Iqtisodiyot'
        ELSE fakultet 
    END AS to_liq_nom
FROM talabalar;
            

EXISTS va subquery

-- Subquery mavjudligini tekshirish
SELECT * FROM talabalar t1
WHERE EXISTS (
    SELECT 1 FROM talabalar t2 
    WHERE t2.fakultet = t1.fakultet 
    AND t2.id != t1.id 
    AND t2.baho > t1.baho
);

-- IN bilan subquery
SELECT * FROM talabalar 
WHERE fakultet IN (
    SELECT fakultet FROM talabalar 
    GROUP BY fakultet 
    HAVING COUNT(*) > 2
);
            

11 / 15

📊 Tartiblash va cheklash

ORDER BY - tartiblash

-- O'sish tartibida (ASC - default)
SELECT * FROM talabalar ORDER BY baho;
SELECT * FROM talabalar ORDER BY baho ASC;

-- Kamayish tartibida
SELECT * FROM talabalar ORDER BY baho DESC;

-- Bir nechta ustun bo'yicha
SELECT * FROM talabalar 
ORDER BY fakultet ASC, baho DESC, ism ASC;

-- RANDOM() bilan tasodifiy tartiblash
SELECT * FROM talabalar ORDER BY RANDOM() LIMIT 3;

-- NULL qiymatlarning joylashuvi
SELECT * FROM talabalar ORDER BY baho NULLS LAST;
            

LIMIT va OFFSET - cheklash

-- Faqat birinchi 5 ta
SELECT * FROM talabalar ORDER BY baho DESC LIMIT 5;

-- 3-qatordan boshlab 5 ta (pagination)
SELECT * FROM talabalar ORDER BY id LIMIT 5 OFFSET 2;

-- Qisqa yozuv (LIMIT offset, count)
SELECT * FROM talabalar ORDER BY id LIMIT 2, 5;

-- Eng yaxshi 3 ta talaba
SELECT * FROM talabalar ORDER BY baho DESC LIMIT 3;

-- 2-sahifa (har sahifada 10 ta)
SELECT * FROM talabalar ORDER BY id LIMIT 10 OFFSET 10;
            

DISTINCT - takrorlanmaslar

-- Noyob fakultetlar
SELECT DISTINCT fakultet FROM talabalar;

-- Noyob fakultet va kurs kombinatsiyalari
SELECT DISTINCT fakultet, kurs FROM talabalar ORDER BY fakultet;

-- COUNT bilan birgalikda
SELECT COUNT(DISTINCT fakultet) AS fakultet_soni FROM talabalar;

-- GROUP BY bilan farqi
SELECT DISTINCT fakultet, COUNT(*) AS soni 
FROM talabalar GROUP BY fakultet;
            

Natija (eng yaxshi 3 ta talaba): ism | familiya | fakultet | baho --------|------------|----------|----- Nilufar | Toshmatova | Iqtisod | 4.8 Malika | Norova | IT | 4.6 Ali | Valiyev | IT | 4.5

12 / 15

🔢 Agregat funksiyalar

Asosiy agregat funksiyalar

-- Soni
SELECT COUNT(*) AS jami_talabalar FROM talabalar;
SELECT COUNT(baho) AS baholangan_talabalar FROM talabalar;
SELECT COUNT(DISTINCT fakultet) AS fakultet_soni FROM talabalar;

-- Yig'indi  
SELECT SUM(yosh) AS jami_yosh FROM talabalar;
SELECT SUM(baho) AS jami_baho FROM talabalar WHERE fakultet = 'IT';

-- O'rtacha
SELECT AVG(baho) AS ortacha_baho FROM talabalar;
SELECT ROUND(AVG(yosh), 2) AS ortacha_yosh FROM talabalar;

-- Eng katta/kichik
SELECT MAX(baho) AS eng_yaxshi_baho FROM talabalar;
SELECT MIN(yosh) AS eng_yosh_talaba FROM talabalar;

-- Bir nechta funksiya
SELECT 
    COUNT(*) AS soni,
    AVG(baho) AS ortacha,
    MAX(baho) AS eng_yaxshi,
    MIN(baho) AS eng_yomon
FROM talabalar;
            

GROUP BY - guruh bo'yicha

-- Fakultet bo'yicha statistika
SELECT 
    fakultet,
    COUNT(*) AS talaba_soni,
    ROUND(AVG(baho), 2) AS ortacha_baho,
    MAX(baho) AS eng_yaxshi_baho
FROM talabalar 
GROUP BY fakultet;

-- Kurs bo'yicha
SELECT 
    kurs,
    COUNT(*) AS soni,
    ROUND(AVG(yosh), 1) AS ortacha_yosh
FROM talabalar 
GROUP BY kurs
ORDER BY kurs;

-- Fakultet va kurs bo'yicha
SELECT 
    fakultet, 
    kurs,
    COUNT(*) AS soni,
    AVG(baho) AS ortacha_baho
FROM talabalar 
GROUP BY fakultet, kurs
HAVING COUNT(*) > 1;  -- faqat 1 dan ortiq talaba bo'lgan guruhlar
            

HAVING - guruh shartlari

-- O'rtacha baho 4.0 dan yuqori bo'lgan fakultetlar
SELECT fakultet, AVG(baho) AS ortacha_baho
FROM talabalar 
GROUP BY fakultet
HAVING AVG(baho) > 4.0;

-- 2 dan ortiq talaba bo'lgan fakultetlar
SELECT fakultet, COUNT(*) AS talaba_soni
FROM talabalar 
GROUP BY fakultet
HAVING COUNT(*) > 2
ORDER BY talaba_soni DESC;

-- WHERE va HAVING birgalikda
SELECT fakultet, AVG(baho) AS ortacha_baho
FROM talabalar 
WHERE kurs >= 2           -- faqat 2+ kurs talabalari
GROUP BY fakultet
HAVING AVG(baho) > 4.0    -- o'rtacha baho 4.0+
ORDER BY ortacha_baho DESC;
            

13 / 15

🔗 JOIN - jadvallarni birlashtirish

Avval test ma'lumotlari yaratamiz:

-- Fakultetlar jadvali
CREATE TABLE fakultetlar (
    id INTEGER PRIMARY KEY,
    nomi TEXT NOT NULL,
    dekan TEXT,
    tashkil_etilgan INTEGER
);

INSERT INTO fakultetlar VALUES
(1, 'Informatika', 'Prof. Karimov', 1998),
(2, 'Iqtisodiyot', 'Prof. Toshmatova', 1995),
(3, 'Tibbiyot', 'Prof. Rahimov', 1990),
(4, 'Fizika', 'Prof. Norova', 2000);

-- Talabalar jadvalini yangilaymiz
CREATE TABLE talabalar_yangi (
    id INTEGER PRIMARY KEY,
    ism TEXT NOT NULL,
    familiya TEXT NOT NULL,
    fakultet_id INTEGER,
    kurs INTEGER,
    baho REAL,
    FOREIGN KEY (fakultet_id) REFERENCES fakultetlar(id)
);

INSERT INTO talabalar_yangi VALUES
(1, 'Ali', 'Valiyev', 1, 2, 4.5),
(2, 'Nilufar', 'Toshmatova', 2, 3, 4.8),
(3, 'Sardor', 'Rahimov', 1, 4, 4.2),
(4, 'Malika', 'Norova', 3, 1, 3.9),
(5, 'Jasur', 'Komilov', NULL, 2, 4.0);  -- fakultetsiz talaba
            

INNER JOIN

-- Faqat fakulteti mavjud bo'lgan talabalar
SELECT 
    t.ism,
    t.familiya,
    f.nomi AS fakultet,
    t.kurs,
    t.baho
FROM talabalar_yangi t
INNER JOIN fakultetlar f ON t.fakultet_id = f.id;

-- Qisqa yozuv (alias)
SELECT t.ism, t.familiya, f.nomi AS fakultet
FROM talabalar_yangi t
JOIN fakultetlar f ON t.fakultet_id = f.id
WHERE t.baho >= 4.0;
            

LEFT JOIN

-- Barcha talabalar, fakulteti bo'lmasa ham
SELECT 
    t.ism,
    t.familiya,
    COALESCE(f.nomi, 'Fakultetsiz') AS fakultet,
    t.baho
FROM talabalar_yangi t
LEFT JOIN fakultetlar f ON t.fakultet_id = f.id;
            

RIGHT JOIN va FULL JOIN

-- SQLite3 da RIGHT JOIN yo'q, lekin LEFT JOIN bilan qilish mumkin
-- Barcha fakultetlar, talabasi bo'lmasa ham  
SELECT 
    f.nomi AS fakultet,
    f.dekan,
    COUNT(t.id) AS talaba_soni
FROM fakultetlar f
LEFT JOIN talabalar_yangi t ON f.id = t.fakultet_id
GROUP BY f.id, f.nomi, f.dekan;

-- FULL JOIN ham yo'q, lekin UNION bilan
SELECT t.ism, f.nomi FROM talabalar_yangi t LEFT JOIN fakultetlar f ON t.fakultet_id = f.id
UNION
SELECT t.ism, f.nomi FROM talabalar_yangi t RIGHT JOIN fakultetlar f ON t.fakultet_id = f.id;
            

14 / 15

⚡ Foydali funksiyalar

Matn funksiyalari

-- Uzunlik
SELECT ism, LENGTH(ism) AS uzunlik FROM talabalar;

-- Katta/kichik harflar
SELECT UPPER(ism) AS katta_harf FROM talabalar;
SELECT LOWER(fakultet) AS kichik_harf FROM talabalar;

-- Qirqish (substring)  
SELECT SUBSTR(ism, 1, 3) AS qisqa_ism FROM talabalar;
SELECT ism, SUBSTR(ism, -2) AS oxirgi_2_harf FROM talabalar;

-- Birlashtirish
SELECT ism || ' ' || familiya AS to_liq_ism FROM talabalar;
SELECT CONCAT(ism, ' - ', fakultet) AS malumot FROM talabalar;  -- ba'zi versiyalarda

-- Almashtirish
SELECT REPLACE(fakultet, 'IT', 'Informatika') AS yangi_nom FROM talabalar;

-- Bo'sh joylarni olib tashlash
SELECT TRIM('  ' || ism || '  ') AS tozalangan FROM talabalar;
            

Raqam funksiyalari

-- Yaxlitlash
SELECT baho, ROUND(baho) AS yaxlitlangan FROM talabalar;
SELECT baho, ROUND(baho, 1) AS bir_kasr FROM talabalar;

-- Yuqoriga/pastga yaxlitlash  
SELECT baho, CEIL(baho) AS yuqori FROM talabalar;   -- ba'zi versiyalarda
SELECT baho, FLOOR(baho) AS past FROM talabalar;

-- Absolyut qiymat
SELECT ABS(baho - 4.0) AS farq FROM talabalar;

-- Min/Max (2 qiymat orasidan)
SELECT baho, MIN(baho, 4.0) AS cheklangan FROM talabalar;
            

Sana-vaqt funksiyalari (batafsil)

-- Formatlar
SELECT strftime('%d.%m.%Y', 'now') AS bugun;           -- 25.12.2024
SELECT strftime('%A, %d %B %Y', 'now') AS to_liq_sana;  -- Monday, 25 December 2024
SELECT strftime('%H:%M', 'now') AS vaqt;               -- 14:30

-- Hisoblashlar
SELECT 
    ism,
    tug_sana,
    ROUND((julianday('now') - julianday(tug_sana)) / 365.25) AS yosh_hisobli
FROM talabalar 
WHERE tug_sana IS NOT NULL;

-- Sana qo'shish/ayirish
SELECT datetime('2024-12-25', '+1 year', '+3 months', '-5 days');

-- Hafta kunlari
SELECT 
    strftime('%w', 'now') AS hafta_raqam,  -- 0=Sunday, 1=Monday
    CASE strftime('%w', 'now')
        WHEN '0' THEN 'Yakshanba'
        WHEN '1' THEN 'Dushanba'  
        WHEN '2' THEN 'Seshanba'
        WHEN '3' THEN 'Chorshanba'
        WHEN '4' THEN 'Payshanba'
        WHEN '5' THEN 'Juma'
        WHEN '6' THEN 'Shanba'
    END AS hafta_kuni;
            

15 / 15

🎯 Amaliy loyiha: Universitet tizimi

To'liq tizim yaratamiz

-- 1. Jadvallar yaratish
CREATE TABLE fakultetlar (
    id INTEGER PRIMARY KEY,
    nomi TEXT UNIQUE NOT NULL,
    dekan TEXT,
    telefon TEXT,
    tashkil_etilgan INTEGER,
    yaratilgan TEXT DEFAULT (datetime('now'))
);

CREATE TABLE talabalar (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ism TEXT NOT NULL,
    familiya TEXT NOT NULL,
    tug_sana TEXT,
    jinsi TEXT CHECK(jinsi IN ('Erkak', 'Ayol')),
    telefon TEXT,
    email TEXT UNIQUE,
    fakultet_id INTEGER,
    kurs INTEGER CHECK(kurs BETWEEN 1 AND 5),
    baho REAL CHECK(baho BETWEEN 0 AND 5),
    manzil TEXT,
    qabul_yili INTEGER,
    yaratilgan TEXT DEFAULT (datetime('now')),
    yangilangan TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (fakultet_id) REFERENCES fakultetlar(id)
);

CREATE TABLE fanlar (
    id INTEGER PRIMARY KEY,
    nomi TEXT NOT NULL,
    kod TEXT UNIQUE,
    kredit_soat INTEGER,
    fakultet_id INTEGER,
    FOREIGN KEY (fakultet_id) REFERENCES fakultetlar(id)
);

CREATE TABLE baholar (
    id INTEGER PRIMARY KEY,
    talaba_id INTEGER,
    fan_id INTEGER,
    baho REAL CHECK(baho BETWEEN 0 AND 5),
    sana TEXT DEFAULT (date('now')),
    FOREIGN KEY (talaba_id) REFERENCES talabalar(id),
    FOREIGN KEY (fan_id) REFERENCES fanlar(id)
);
            

Ma'lumotlar kiritish

-- Fakultetlar INSERT INTO fakultetlar (nomi, dekan, telefon, tashkil_etilgan) VALUES ('Informatika va Texnologiya', 'Prof. A.Karimov', '+998901234567', 1995), ('Iqtisodiyot', 'Prof. N.Toshmatova', '+998901234568', 1992), ('Tibbiyot', 'Prof. S.Rahimov', '+998901234569', 1988); -- Talabalar INSERT INTO talabalar (ism, familiya, tug_sana, jinsi, telefon, email, fakultet_id, kurs, qabul_yili) VALUES ('Ali', 'Valiyev', '2003-05-15', 'Erkak', '+998911111111', '[email protected]', 1, 2, 2022), ('Nilufar', 'Toshmatova', '2004-03-22', 'Ayol', '+998922222222', '[email protected]', 2, 1, 2023), ('Sardor', 'Rahimov', '2001-11-08', 'Erkak', '+998933333333', '[email protected]', 1, 4, 2020), ('Malika', 'Norova', '2002-07-12', 'Ayol', '+998944444444', '[email protected]', 3, 3, 2021); -- Fanlar INSERT INTO fanlar (nomi, kod, kredit_soat, fakultet_id) VALUES ('Dasturlash asoslari', 'IT101', 4, 1), ('Ma\'lumotlar bazasi', 'IT201', 3, 1), ('Mikroiqtisod', 'EC101', 3, 2), ('Anatomiya', 'MD101', 5, 3);

Murakkab so'rovlar

-- Har fakultetdagi talabalar statistikasi
SELECT 
    f.nomi AS fakultet,
    f.dekan,
    COUNT(t.id) AS talaba_soni,
    ROUND(AVG(CAST((strftime('%Y', 'now') - strftime('%Y', t.tug_sana)) AS INTEGER)), 1) AS ortacha_yosh,
    MIN(t.qabul_yili) AS eng_eski_talaba,
    MAX(t.qabul_yili) AS eng_yangi_talaba
FROM fakultetlar f
LEFT JOIN talabalar t ON f.id = t.fakultet_id
GROUP BY f.id, f.nomi, f.dekan
ORDER BY talaba_soni DESC;

-- Yosh bo'yicha talabalar taqsimoti
SELECT 
    CASE 
        WHEN CAST((strftime('%Y', 'now') - strftime('%Y', tug_sana)) AS INTEGER) <= 20 THEN '18-20 yosh'
        WHEN CAST((strftime('%Y', 'now') - strftime('%Y', tug_sana)) AS INTEGER) <= 23 THEN '21-23 yosh'
        ELSE '24+ yosh'
    END AS yosh_guruhi,
    COUNT(*) AS soni,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM talabalar), 1) AS foiz
FROM talabalar
GROUP BY yosh_guruhi
ORDER BY soni DESC;

-- Eng faol fakultet (eng ko'p talabaga ega)
SELECT 
    f.nomi,
    COUNT(t.id) AS talaba_soni,
    COUNT(fan.id) AS fan_soni
FROM fakultetlar f
LEFT JOIN talabalar t ON f.id = t.fakultet_id
LEFT JOIN fanlar fan ON f.id = fan.fakultet_id
GROUP BY f.id, f.nomi
ORDER BY talaba_soni DESC
LIMIT 1;
            

🎉 Tabriklaymiz! Siz SQLite3 ning asosiy va ilg'or xususiyatlarini o'rgandingiz. Endi o'z loyihalaringizda foydalanishingiz mumkin!

📚 Keyingi qadamlar:

Indekslar yaratish (CREATE INDEX)
View lar (CREATE VIEW)
Triggerlar (CREATE TRIGGER)
Python/PHP bilan integratsiya