-- =====================================================================
-- magazin.sql — LearnCode SQL darslari uchun namuna baza
-- =====================================================================
-- Bu bitta kichik "do'kon" bazasi. Barcha SQL darslari shu uch jadval
-- ustida ishlaydi, shuning uchun uni bir marta yuklab, ishga tushirib
-- qo'ying — keyin har darsdagi so'rovlarni shu bazada sinaysiz.
--
-- Qanday ishga tushirish (DB Browser for SQLite):
--   1. Yangi baza yarating (New Database) yoki mavjudini oching.
--   2. "Execute SQL" bo'limiga o'ting.
--   3. Shu faylning butun matnini nusxalab, o'sha yerga qo'ying.
--   4. ▶ (Run) tugmasini bosing va "Write Changes" bilan saqlang.
--
-- Onlayn variant (o'rnatishsiz): sqliteonline.com — shu matnni qo'yib,
-- Run bosing.
--
-- Dialekt: SQLite. So'rovlarning ko'pi MySQL/PostgreSQL'da ham ishlaydi.
-- =====================================================================

-- Eski jadvallarni tozalash (qayta ishga tushirsa xato bermasin)

-- ---------------------------------------------------------------------
-- 1-jadval: mahsulotlar — do'kondagi tovarlar
-- ---------------------------------------------------------------------
CREATE TABLE mahsulotlar (
  id    INTEGER PRIMARY KEY,   -- har mahsulotning noyob raqami
  nom   TEXT    NOT NULL,      -- mahsulot nomi
  narx  INTEGER NOT NULL,      -- narxi (so'm)
  soni  INTEGER NOT NULL,      -- omborda nechta bor
  turi  TEXT                   -- toifasi (guruhlash uchun)
);

INSERT INTO mahsulotlar (id, nom, narx, soni, turi) VALUES
  (1, 'Olma',   8000,  50,  'meva'),
  (2, 'Non',    3000,  120, 'non'),
  (3, 'Sut',    12000, 30,  'sut mahsuloti'),
  (4, 'Choy',   15000, 40,  'ichimlik'),
  (5, 'Banan',  14000, 25,  'meva'),
  (6, 'Qatiq',  9000,  0,   'sut mahsuloti'),   -- soni 0: tugagan tovar
  (7, 'Suv',    4000,  200, 'ichimlik'),
  (8, 'Guruch', 18000, 60,  'don');

-- ---------------------------------------------------------------------
-- 2-jadval: mijozlar — xaridorlar
-- ---------------------------------------------------------------------
CREATE TABLE mijozlar (
  id     INTEGER PRIMARY KEY,
  ism    TEXT    NOT NULL,
  shahar TEXT,
  yosh   INTEGER               -- ba'zida noma'lum (NULL) bo'lishi mumkin
);

INSERT INTO mijozlar (id, ism, shahar, yosh) VALUES
  (1, 'Aziz',    'Toshkent',  28),
  (2, 'Dilnoza', 'Samarqand', 34),
  (3, 'Bobur',   'Toshkent',  22),
  (4, 'Malika',  'Buxoro',    NULL);   -- yoshi kiritilmagan: NULL darsi uchun

-- ---------------------------------------------------------------------
-- 3-jadval: buyurtmalar — kim qaysi mahsulotni sotib olgani
-- (mijozlar va mahsulotlarni bog'laydi — JOIN darslari uchun)
-- ---------------------------------------------------------------------
CREATE TABLE buyurtmalar (
  id           INTEGER PRIMARY KEY,
  mijoz_id     INTEGER,        -- mijozlar.id ga ishora
  mahsulot_id  INTEGER,        -- mahsulotlar.id ga ishora
  soni         INTEGER NOT NULL,
  sana         TEXT            -- 'YYYY-MM-DD' ko'rinishida
);

INSERT INTO buyurtmalar (id, mijoz_id, mahsulot_id, soni, sana) VALUES
  (1, 1, 2, 3,  '2026-07-01'),
  (2, 1, 4, 1,  '2026-07-02'),
  (3, 2, 1, 10, '2026-07-02'),
  (4, 3, 3, 2,  '2026-07-05'),
  (5, 2, 8, 5,  '2026-07-06'),
  (6, 1, 7, 4,  '2026-07-08');

-- =====================================================================
-- Tekshirish: quyidagi so'rov 8 ta mahsulotni ko'rsatishi kerak.
--   SELECT * FROM mahsulotlar;
-- =====================================================================
