7.5-dars: SQL asoslari

7-QISM — MA'LUMOT YIG'ISH · 5-dars

1. Kirish va motivatsiya

Ko'p kompaniyalarda ma'lumot ma'lumotlar bazasida (database) saqlanadi — CSV faylda emas. Bazaga kirish uchun SQL (Structured Query Language) kerak — ma'lumotlar bazasi tili. SQL Data Scientist uchun eng muhim ko'nikmalardan biri: ish e'lonlarida deyarli har doim talab qilinadi, chunki ma'lumot ko'pincha bazada. Bu darsda SQL asoslari — SQLite 7.4-bob yordamida mahalliy: SELECT (ustun tanlash), WHERE (filtr), ORDER BY (saralash), LIMIT (cheklash), GROUP BY (guruhlash), va agregatsiya (COUNT, SUM, AVG). Bu pandas amallarining "boshqa tildagi" ekvivalenti — ammo ma'lumot bazada bo'lganda to'g'ridan-to'g'ri bazada bajariladi (tez, katta ma'lumot). Nega muhim? (1) Baza — ma'lumot ko'pincha bazada; (2) Talab — ish uchun shart (Data Scientist); (3) Tez — bazada bajariladi (katta ma'lumot). Bu dars SQL asoslarini o'rgatadi — bazadan ma'lumot olish.

SQL asoslari (Structured Query Language) — bazadan ma'lumot: SELECT (ustun tanlash — SELECT nom, narx), FROM (jadval), WHERE (filtr — WHERE narx > 100), ORDER BY (saralash — ORDER BY narx DESC), LIMIT (cheklash — LIMIT 10), GROUP BY (guruhlash), agregatsiya (COUNT, SUM, AVG, MIN, MAX). Foydalanish: bazadan ma'lumot olish, so'rov (query). Bu 7.4 (SQLite), 3.5 (filtrlash), 3.8 (groupby), 7.6 (pandas+SQL) bilan bog'liq. SQL asoslari — bazadan olish (SELECT/WHERE/GROUP BY). SQL. So'rov.

Real vaziyat. Data Scientist kompaniya bazasidan tahlil qilmoqchi (savdo jadvali — 10M qator; CSV emas — bazada). Muammo: butun jadvalni olish — 10M qator (xotira, sekin); kerak — faqat "2024 Toshkent, narx > 100, yuqori 10". Hal — SQL so'rov: SELECT shahar, narx FROM savdo WHERE shahar = 'Toshkent' AND narx > 100 ORDER BY narx DESC LIMIT 10 — bazada bajariladi (faqat kerak qator/ustun qaytadi; 10 qator; tez). Yoki agregatsiya: SELECT shahar, AVG(narx) FROM savdo GROUP BY shahar — har shahar o'rtacha narx (bazada hisob). Data Scientist SQL bilan bazadan aynan kerakni oldi (filtr, saralash, agregatsiya — bazada). SQL asoslari — bazadan olish.

Bu darsda SQL asoslarini o'rganamiz.

Bu darsda:

• SELECT, FROM, WHERE (tanlash, filtr)
• ORDER BY, LIMIT (saralash, cheklash)
• GROUP BY va agregatsiya
• SQL vs pandas (o'xshashlik)
• JOIN (jadvallarni birlashtirish)
• SQL amaliyoti
• SQL tuzoqlari
• Amaliy: SQL so'rovlar

ℹ Misollar real sqlite3/pandas bilan (Python 3.14).

2. Nazariya — chuqur tushuntirish

2.1. SELECT, FROM, WHERE (tanlash, filtr)

Asosiy so'rov:

-- SELECT — qaysi ustunlar; FROM — qaysi jadval
SELECT shahar, narx FROM savdo;

-- * — barcha ustun
SELECT * FROM savdo;

-- WHERE — filtr (shart)
SELECT * FROM savdo WHERE narx > 100;
SELECT * FROM savdo WHERE shahar = 'Toshkent' AND narx > 100;
SELECT * FROM savdo WHERE shahar IN ('Toshkent', 'Samarqand');

SELECT, FROM, WHERE (tanlash, filtr) — asosiy: SELECT ustun1, ustun2 (qaysi ustunlar; * — barcha), FROM jadval (qaysi jadval), WHERE shart (filtr — narx > 100; AND/OR — birlashtirish; = teng, IN (...) ro'yxat, LIKE naqsh, BETWEEN). Sabab: so'rov strukturasi (SELECT nima — ustun; FROM qayerdan — jadval; WHERE qaysi — filtr); faqat kerak (barcha emas — ustun/qator; tez, kam xotira); matn qiymat '...' (bitta tirnoq). SELECT (ustun), FROM (jadval), WHERE (filtr), AND/OR/IN (shart). SELECT/FROM/WHERE — tanlash/jadval/filtr. SELECT. WHERE.

2.2. ORDER BY, LIMIT (saralash, cheklash)

Saralash va cheklash:

-- ORDER BY — saralash (ASC o'sish standart; DESC kamayish)
SELECT * FROM savdo ORDER BY narx DESC;
SELECT * FROM savdo ORDER BY shahar ASC, narx DESC;

-- LIMIT — nechta qator (yuqori N)
SELECT * FROM savdo ORDER BY narx DESC LIMIT 10;

-- birga
SELECT shahar, narx FROM savdo
WHERE narx > 50 ORDER BY narx DESC LIMIT 5;

ORDER BY, LIMIT (saralash, cheklash) — tartib: ORDER BY ustun (saralash — ASC o'sish (standart), DESC kamayish; ko'p ustun — ORDER BY a ASC, b DESC), LIMIT N (nechta qator — yuqori N; LIMIT 10 birinchi 10). Sabab: tartib (ORDER BY — saralash; eng qimmat/yangi); cheklash (LIMIT — top N; katta jadval — faqat 10; xotira/tezlik); "eng qimmat 10 uy" (ORDER BY narx DESC LIMIT 10). ORDER BY (saralash — ASC/DESC), LIMIT (nechta — top N), so'rov tartibi (SELECT→FROM→WHERE→ORDER BY→LIMIT). ORDER BY/LIMIT — saralash/cheklash. ORDER BY. LIMIT.

2.3. GROUP BY va agregatsiya

GROUP BY va agregatsiya — guruhlash: GROUP BY ustun (guruhlash — 3.8 pandas groupby; har guruh bir qator) + agregatsiya (COUNT(*) soni, SUM(narx) yig'indi, AVG(narx) o'rtacha, MIN/MAX); HAVING (guruh filtri — WHERE guruhdan keyin). Sabab: guruh statistikasi (SELECT shahar, AVG(narx) FROM savdo GROUP BY shahar — har shahar o'rtacha; 3.8); GROUP BY (guruh — kategoriya), agregatsiya (guruhga bir son), HAVING (guruh filtri — HAVING COUNT(*) > 5; WHERE qatorga, HAVING guruhga). GROUP BY (guruh), COUNT/SUM/AVG (agregatsiya), HAVING (guruh filtri). GROUP BY — guruhlash + agregatsiya. GROUP BY. AVG.

2.4. SQL vs pandas (o'xshashlik)

SQL vs pandas (o'xshashlik) — bir mantiq: SQL va pandas bir amal (turli til): SELECT ↔ df[["a", "b"]] (ustun), WHERE ↔ df[df["x"] > 100] (filtr 3.5), ORDER BY ↔ df.sort_values (saralash 3.10), GROUP BY ↔ df.groupby (guruh 3.8), AVG ↔ .mean(), JOIN ↔ pd.merge 3.9-bob, LIMIT ↔ .head(). Sabab: bir mantiq (SQL bilgan — pandas oson, aksincha; ikkalasi — ma'lumot tanlash/filtr/guruh); qachon qaysi (ma'lumot bazada — SQL (bazada bajariladi; katta, tez); pandasda — pandas). SELECT↔ustun, WHERE↔filtr, GROUP BY↔groupby (o'xshash); bazada — SQL, xotirada — pandas. SQL vs pandas — bir mantiq (turli til). O'xshash. Mantiq.

2.5. JOIN (jadvallarni birlashtirish)

JOIN (jadvallarni birlashtirish) — munosabat: JOIN — ikki jadvalni umumiy ustun (kalit) bilan birlashtirish (3.9 merge); INNER JOIN (ikkalasida bor — kesishma), LEFT JOIN (chap jadval to'liq + o'ng mos); ON (bog'lanish sharti — ON a.id = b.id). Sabab: bazada ma'lumot bo'lingan (normalizatsiya — mijoz jadvali, buyurtma jadvali; takror yo'q); JOIN — birlashtirish (buyurtma + mijoz nomi); ON (kalit — mijoz_id). JOIN ... ON (birlashtirish — kalit), INNER (kesishma), LEFT (chap to'liq), kalit (id). JOIN — jadval birlashtirish (munosabat; ON kalit). JOIN. Kalit.

2.6. SQL amaliyoti

SQL amaliyoti: SELECT ustun (kerak ustun — * emas; kam ma'lumot); WHERE (filtr — bazada; kerak qator); ORDER BY (saralash — DESC katta); LIMIT (top N — katta jadval); GROUP BY + agregatsiya (guruh statistika — AVG/COUNT); JOIN (jadvallar — kalit bilan); HAVING (guruh filtri); pandas'ga (pd.read_sql(sorov, conn) — natija DataFrame; 7.6). Tuzoqlar: SELECT * (barcha — ko'p ma'lumot), matn tirnoq ("..." emas — '...'), NULL (= NULL xato — IS NULL), GROUP BY xato (SELECT ustun guruhda yoki agregat), JOIN kalit (noto'g'ri — ko'p qator). Amaliyot — SELECT, WHERE, GROUP BY, JOIN. SQL. So'rov.

2.7. SQL tuzoqlari

SQL asosiy tuzoqlari: SELECT * (barcha ustun — ko'p ma'lumot (kerak emas ustun; xotira/tezlik; katta jadval); faqat kerak ustun SELECT a, b); matn tirnoq (SQL matn — bitta tirnoq 'Toshkent'; ikki tirnoq "Toshkent" — ustun nomi deb (xato); '...' matn); NULL taqqoslash (WHERE x = NULL — xato (NULL — noma'lum; = ishlamaydi); IS NULL/IS NOT NULL); GROUP BY xato (SELECT shahar, narx ... GROUP BY shahar — narx guruhda emas, agregat emas → xato/noaniq; SELECT ustun — GROUP BY'da yoki agregat (AVG(narx))); JOIN kalit (noto'g'ri ON (kalit) — ko'p qator (dekart ko'paytma) yoki kam; to'g'ri kalit; INNER vs LEFT farq); WHERE vs HAVING (WHERE — qator filtri (guruhdan oldin); HAVING — guruh filtri (agregat — HAVING COUNT(*) > 5); aralashtirma); so'rov tartibi (SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT — tartib qat'iy); katta so'rov (murakkab — bosqichma-bosqich (subquery, CTE WITH); o'qiluvchan); SQL in'yeksiya (foydalanuvchi kirishi so'rovga — xavf (parametr ? — 7.6; string birlashtirma)); case sezgirlik (SQL kalit so'z — katta harf odat (SELECT); jadval/ustun — bazaga bog'liq). Sabab: SQL sintaksis/mantiq nozik (*, tirnoq, NULL, GROUP BY — xato yoki ko'p ma'lumot). Yechim: kerak ustun, '...' matn, IS NULL, GROUP BY to'g'ri, JOIN kalit. Tuzoqlar — SELECT *, tirnoq, NULL, GROUP BY.

2.8. SQL asoslari — bazadan ma'lumot olish

SQL asoslari asosiy g'oyasi — bazadan ma'lumot olish: ma'lumot ko'pincha bazada (CSV emas — kompaniya); SQL (baza tili) — Data Scientist uchun eng muhim ko'nikmalardan (ish talabi). SELECT (ustun — * emas, kerak), FROM (jadval), WHERE (filtr — narx > 100; AND/OR/IN/LIKE), ORDER BY (saralash — ASC/DESC), LIMIT (top N), GROUP BY (guruhlash — 3.8) + agregatsiya (COUNT/SUM/AVG/MIN/MAX; HAVING guruh filtri), JOIN (jadvallar — kalit ON; INNER/LEFT; munosabat 3.9). SQL vs pandas (bir mantiq — SELECT↔ustun, WHERE↔filtr, GROUP BY↔groupby; bazada — SQL, xotirada — pandas). Foydalanish: bazadan ma'lumot olish (so'rov — faqat kerak; bazada bajariladi — tez, katta), so'rov (filtr/saralash/guruh/birlashtirish). Tuzoqlar: SELECT * (ko'p ma'lumot), matn tirnoq ('...'), NULL (IS NULL), GROUP BY xato (guruh/agregat), JOIN kalit (ko'p qator), WHERE vs HAVING (qator/guruh). Bu 7.4 (SQLite), 3.5 (filtr), 3.8 (groupby), 3.9 (merge), 3.10 (saralash), 7.6 (pandas+SQL) bilan. SQL asoslari — bazadan olish (SELECT/WHERE/GROUP BY/JOIN). SQL. So'rov. Baza.

3. Tez ma'lumotnoma

-- SO'ROV TUZILISHI (tartib qat'iy):
SELECT ustun1, ustun2         -- qaysi ustun (* barcha — kerak emas)
FROM jadval                    -- qaysi jadval
WHERE shart                    -- qator filtri (narx > 100; = 'matn')
GROUP BY ustun                 -- guruhlash
HAVING agregat_shart           -- guruh filtri (COUNT(*) > 5)
ORDER BY ustun DESC            -- saralash (ASC/DESC)
LIMIT 10;                      -- top N

-- FILTR: narx > 100 · shahar = 'Toshkent' · x IN (..) · x IS NULL · x LIKE 'A%'
-- AGREGAT: COUNT(*) · SUM(x) · AVG(x) · MIN(x) · MAX(x)

-- JOIN (jadvallar — kalit):
SELECT b.id, m.ism FROM buyurtma b
JOIN mijoz m ON b.mijoz_id = m.id;   -- INNER (kesishma) / LEFT (chap to'liq)

# PANDAS'GA 7.6-bob:
import sqlite3, pandas as pd
conn = sqlite3.connect("loyiha.db")
df = pd.read_sql("SELECT * FROM savdo WHERE narx > 100", conn)

SQL asoslari xulosasi

SQL asoslari — bazadan ma'lumot olish (so'rov)
SELECT/FROM/WHERE — ustun/jadval/filtr (narx > 100; 'matn')
ORDER BY/LIMIT — saralash (DESC) / top N
GROUP BY + agregat — guruh statistika (COUNT/SUM/AVG; HAVING)
SQL vs pandas — bir mantiq (SELECT↔ustun, WHERE↔filtr, GROUP BY↔groupby)

4. Batafsil misollar

Misollar real sqlite3/pandas bilan (Python 3.14).

Misol 1 — SELECT, WHERE

"""SELECT, WHERE (real sqlite3/pandas)."""

import sqlite3
import pandas as pd


def main() -> None:
    conn = sqlite3.connect(":memory:")
    df = pd.DataFrame({
        "shahar": ["Toshkent", "Samarqand", "Buxoro", "Toshkent"],
        "narx": [120, 80, 70, 150],
    })
    df.to_sql("savdo", conn, index=False)

    print("=== 1. SELECT ustun ===")
    r1 = pd.read_sql("SELECT shahar, narx FROM savdo", conn)
    print(f"  ustunlar: {list(r1.columns)}")

    print("\n=== 2. WHERE (filtr) ===")
    r2 = pd.read_sql("SELECT * FROM savdo WHERE narx > 100", conn)
    print(f"  narx > 100: {r2['narx'].tolist()}")

    print("\n=== 3. WHERE AND ===")
    r3 = pd.read_sql("SELECT * FROM savdo WHERE shahar = 'Toshkent' AND narx > 100", conn)
    print(f"  Toshkent va narx>100: {r3['narx'].tolist()}")

    print("\n=== 4. IN (ro'yxat) ===")
    r4 = pd.read_sql("SELECT * FROM savdo WHERE shahar IN ('Buxoro', 'Samarqand')", conn)
    print(f"  Buxoro/Samarqand: {r4['shahar'].tolist()}")
    conn.close()
    print("  ⭐ SELECT/WHERE — tanlash/filtr")


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

=== 1. SELECT ustun ===
  ustunlar: ['shahar', 'narx']

=== 2. WHERE (filtr) ===
  narx > 100: [120, 150]

=== 3. WHERE AND ===
  Toshkent va narx>100: [120, 150]

=== 4. IN (ro'yxat) ===
  Buxoro/Samarqand: ['Samarqand', 'Buxoro']
  ⭐ SELECT/WHERE — tanlash/filtr

Nima ko'rsatdi: 2.1-bo'lim.

Misol 2 — ORDER BY, LIMIT

"""ORDER BY, LIMIT (real sqlite3/pandas)."""

import sqlite3
import pandas as pd


def main() -> None:
    conn = sqlite3.connect(":memory:")
    pd.DataFrame({
        "shahar": ["Toshkent", "Samarqand", "Buxoro", "Xiva", "Navoiy"],
        "narx": [120, 80, 70, 150, 95],
    }).to_sql("savdo", conn, index=False)

    print("=== 1. ORDER BY DESC (kamayish) ===")
    r1 = pd.read_sql("SELECT shahar, narx FROM savdo ORDER BY narx DESC", conn)
    print(f"  narxlar: {r1['narx'].tolist()}")

    print("\n=== 2. LIMIT (top 3) ===")
    r2 = pd.read_sql("SELECT * FROM savdo ORDER BY narx DESC LIMIT 3", conn)
    print(f"  top 3 narx: {r2['narx'].tolist()}")

    print("\n=== 3. WHERE + ORDER + LIMIT ===")
    r3 = pd.read_sql(
        "SELECT shahar, narx FROM savdo WHERE narx > 80 ORDER BY narx DESC LIMIT 2", conn)
    print(f"  {r3['shahar'].tolist()}")

    print("\n=== 4. Eng qimmat shahar ===")
    print(f"  {r1.iloc[0]['shahar']} ({r1.iloc[0]['narx']})")
    conn.close()
    print("  ⭐ ORDER BY/LIMIT — saralash/top N")


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

=== 1. ORDER BY DESC (kamayish) ===
  narxlar: [150, 120, 95, 80, 70]

=== 2. LIMIT (top 3) ===
  top 3 narx: [150, 120, 95]

=== 3. WHERE + ORDER + LIMIT ===
  ['Xiva', 'Toshkent']

=== 4. Eng qimmat shahar ===
  Xiva (150)
  ⭐ ORDER BY/LIMIT — saralash/top N

Nima ko'rsatdi: 2.2-bo'lim.

Misol 3 — GROUP BY, agregatsiya

"""GROUP BY, agregatsiya (real sqlite3/pandas)."""

import sqlite3
import pandas as pd


def main() -> None:
    conn = sqlite3.connect(":memory:")
    pd.DataFrame({
        "shahar": ["Toshkent", "Toshkent", "Samarqand", "Buxoro", "Samarqand"],
        "narx": [120, 150, 80, 70, 90],
    }).to_sql("savdo", conn, index=False)

    print("=== 1. GROUP BY + AVG ===")
    r1 = pd.read_sql(
        "SELECT shahar, AVG(narx) AS ortacha FROM savdo GROUP BY shahar", conn)
    print(r1.to_string(index=False))

    print("\n=== 2. COUNT (har guruh soni) ===")
    r2 = pd.read_sql(
        "SELECT shahar, COUNT(*) AS soni FROM savdo GROUP BY shahar", conn)
    print(f"  {dict(zip(r2['shahar'], r2['soni']))}")

    print("\n=== 3. HAVING (guruh filtri) ===")
    r3 = pd.read_sql(
        "SELECT shahar, COUNT(*) AS soni FROM savdo GROUP BY shahar HAVING COUNT(*) > 1", conn)
    print(f"  1 dan ko'p: {r3['shahar'].tolist()}")

    print("\n=== 4. SUM, MAX ===")
    r4 = pd.read_sql("SELECT SUM(narx) AS jami, MAX(narx) AS eng FROM savdo", conn)
    print(f"  jami: {r4['jami'][0]}, eng: {r4['eng'][0]}")
    conn.close()
    print("  ⭐ GROUP BY — guruh statistika")


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

=== 1. GROUP BY + AVG ===
   shahar  ortacha
   Buxoro     70.0
Samarqand     85.0
 Toshkent    135.0

=== 2. COUNT (har guruh soni) ===
  {'Buxoro': 1, 'Samarqand': 2, 'Toshkent': 2}

=== 3. HAVING (guruh filtri) ===
  1 dan ko'p: ['Samarqand', 'Toshkent']

=== 4. SUM, MAX ===
  jami: 510, eng: 150
  ⭐ GROUP BY — guruh statistika

Nima ko'rsatdi: 2.3-bo'lim.

Misol 4 — JOIN

"""JOIN (real sqlite3/pandas)."""

import sqlite3
import pandas as pd


def main() -> None:
    conn = sqlite3.connect(":memory:")
    pd.DataFrame({"id": [1, 2, 3], "ism": ["Ali", "Vali", "Guli"]}).to_sql(
        "mijoz", conn, index=False)
    pd.DataFrame({"buyurtma_id": [10, 11, 12], "mijoz_id": [1, 2, 1], "narx": [100, 200, 150]}).to_sql(
        "buyurtma", conn, index=False)

    print("=== 1. Ikki jadval (bo'lingan) ===")
    print("  mijoz (id, ism); buyurtma (mijoz_id, narx)")

    print("\n=== 2. JOIN (kalit — mijoz_id) ===")
    r1 = pd.read_sql("""
        SELECT b.buyurtma_id, m.ism, b.narx
        FROM buyurtma b
        JOIN mijoz m ON b.mijoz_id = m.id
    """, conn)
    print(r1.to_string(index=False))

    print("\n=== 3. Kim nima buyurdi ===")
    print(f"  Ali buyurtmalar: {r1[r1['ism'] == 'Ali']['narx'].tolist()}")

    print("\n=== 4. Munosabat ===")
    print("  baza bo'lingan (normalizatsiya) — JOIN birlashtiradi")
    conn.close()
    print("  ⭐ JOIN — jadvallar (kalit)")


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

=== 1. Ikki jadval (bo'lingan) ===
  mijoz (id, ism); buyurtma (mijoz_id, narx)

=== 2. JOIN (kalit — mijoz_id) ===
 buyurtma_id  ism  narx
          10  Ali   100
          11 Vali   200
          12  Ali   150

=== 3. Kim nima buyurdi ===
  Ali buyurtmalar: [100, 150]

=== 4. Munosabat ===
  baza bo'lingan (normalizatsiya) — JOIN birlashtiradi
  ⭐ JOIN — jadvallar (kalit)

Nima ko'rsatdi: 2.5-bo'lim.

5. To'g'ri va noto'g'ri tushunishlar

6. Keng tarqalgan xatolar va yechimlari

**1. SELECT ***

SELECT * FROM savdo   -- barcha ustun (ko'p ma'lumot)             -- ⚠️
SELECT shahar, narx FROM savdo   -- kerak ustun                  -- ✅

2. Matn tirnoq

WHERE shahar = "Toshkent"   -- ustun nomi deb (xato)              -- ⚠️
WHERE shahar = 'Toshkent'   -- matn (bitta tirnoq)               -- ✅

3. NULL taqqoslash

WHERE narx = NULL   -- hech qachon rost                           -- ⚠️
WHERE narx IS NULL   -- to'g'ri                                   -- ✅

4. GROUP BY xato

SELECT shahar, narx FROM s GROUP BY shahar   -- narx noaniq       -- ⚠️
SELECT shahar, AVG(narx) FROM s GROUP BY shahar   -- agregat     -- ✅

5. WHERE vs HAVING

SELECT shahar, COUNT(*) FROM s WHERE COUNT(*) > 5   -- xato       -- ⚠️
... GROUP BY shahar HAVING COUNT(*) > 5   -- guruh filtri        -- ✅

6. JOIN kalit yo'q

SELECT * FROM a JOIN b   -- ON yo'q → dekart (ko'p qator)         -- ⚠️
SELECT * FROM a JOIN b ON a.id = b.a_id   -- kalit               -- ✅

7. SQL in'yeksiya (7.6)

f"SELECT * FROM t WHERE id = {kirish}"   # xavf (in'yeksiya)      # ⚠️
pd.read_sql("SELECT * FROM t WHERE id = ?", conn, params=[kirish])  # ✅

7. Integratsiya — bu bilim qayerda kerak bo'ladi

• 7.4-dars (o'tilgan): SQLite (baza)
• 3.5-dars (o'tilgan): Filtrlash (WHERE)
• 3.8-dars (o'tilgan): Groupby (GROUP BY)
• 3.9-dars (o'tilgan): Merge (JOIN)
• 7.6-dars: Pandas va SQL

8. Eng yaxshi amaliyotlar

1. SELECT — kerak ustun (* emas).

2. Matn — bitta tirnoq ('...').

3. NULL — IS NULL.

4. GROUP BY — guruh yoki agregat.

5. WHERE (qator) vs HAVING (guruh).

6. JOIN — kalit (ON).

7. So'rov tartibi — SELECT...LIMIT.

8. Parametr — ? (in'yeksiya — 7.6).

9. Amaliy topshiriq

Vazifa 1: Bashorat qiling

1.  # SQL nima?
2.  # SELECT nima?
3.  # WHERE nima?
4.  # ORDER BY?
5.  # LIMIT nima?
6.  # GROUP BY?
7.  # agregatsiya?
8.  # HAVING nima?
9.  # JOIN nima?
10. # SQL vs pandas?
11. # matn tirnoq?
12. # nega SQL?

Vazifa 2: Xatolarni tuzating

1.  SELECT * FROM savdo

2.  WHERE shahar = "Toshkent"

3.  WHERE narx = NULL

4.  SELECT shahar, narx ... GROUP BY shahar

5.  ... WHERE COUNT(*) > 5

1.  SELECT shahar, narx (kerak)

2.  = 'Toshkent' (bitta tirnoq)

3.  narx IS NULL

4.  AVG(narx) (agregat)

5.  HAVING COUNT(*) > 5

Vazifa 3: Asosiy so'rov

Modellang:

1. SELECT
2. FROM
3. WHERE
4. AND/IN

Vazifa 4: Saralash va guruh

Modellang:

1. ORDER BY
2. LIMIT
3. GROUP BY
4. AVG/COUNT

Vazifa 5: JOIN

Modellang:

1. JOIN
2. ON (kalit)
3. INNER
4. LEFT

Vazifa 6: Integratsiya

Modellang:

1. SQLite (7.4)
2. Filtr (3.5)
3. Groupby (3.8)
4. Pandas+SQL (7.6)

Vazifa 7: O'ylash

SQL va pandas bir xil amallarni (tanlash, filtr, guruh, birlashtirish) bajaradi — faqat turli tilda. Data Scientist ikkalasini ham bilishi kerak: ma'lumot bazada bo'lganda SQL (bazada bajariladi — tez, katta ma'lumot), xotirada bo'lganda pandas. Nima uchun "ma'lumotni u yerda qayerda bo'lsa, o'sha yerda qayta ishlash" (SQL — bazada) tamoyili katta ma'lumotda muhim, va bu ikki vosita qanday bir-birini to'ldiradi?

Nimani mustahkamlaydi: 2.4, 2.6-bo'limlar.

Xulosa

Bu darsda SQL asoslarini o'rgandik.

Eng muhim uch fikr:

1. SELECT/WHERE va ORDER BY/LIMIT. SELECT ustun FROM jadval WHERE shart (ustun tanlash — * emas kerak; filtr — narx > 100, shahar = 'Toshkent' bitta tirnoq, AND/OR/IN/LIKE/IS NULL). ORDER BY ustun DESC (saralash — ASC/DESC), LIMIT N (top N). So'rov tartibi qat'iy: SELECT...FROM...WHERE...GROUP BY...HAVING...ORDER BY...LIMIT.

2. GROUP BY va JOIN. GROUP BY ustun (guruhlash — 3.8) + agregatsiya (COUNT(*), SUM, AVG, MIN/MAX; HAVING guruh filtri — WHERE qator, HAVING guruh). JOIN ... ON kalit (jadvallar — 3.9 merge; INNER kesishma, LEFT chap to'liq; bo'lingan baza — normalizatsiya).

3. Bazadan olish. SQL vs pandas — bir mantiq (turli til; SELECT↔ustun, WHERE↔filtr, GROUP BY↔groupby); ma'lumotni qayerda bo'lsa o'sha yerda (katta — bazada SQL kamaytir, tez; kichik — pandas batafsil; ikki vosita to'ldiradi). Tuzoqlar: SELECT * (ko'p ma'lumot), matn tirnoq ('...'), NULL (IS NULL), GROUP BY xato (guruh/agregat), WHERE vs HAVING (qator/guruh), JOIN kalit (ON — ko'p qator), SQL in'yeksiya (parametr ? — 7.6).

Keyingi darsda pandas va SQLni birgalikda o'rganamiz: pd.read_sql (SQL natija → DataFrame), df.to_sql (DataFrame → baza), parametrli so'rov (? — in'yeksiya xavfsizligi), va SQL/pandas ish oqimi (bazada kamaytir → pandas tahlil).