SQL & Database Tutorial پروژه عملی پایان دوره پروژه عملی سطح: متوسط

پروژه پایانی ۱ — دیتابیس فروشگاه کوچک از طراحی تا گزارش

پروژه پایانی اول: از صفر یک دیتابیس فروشگاه طراحی و پیاده می‌کنید: جدول‌های کاربر، محصول و سفارش با روابط درست، داده نمونه و پنج گزارش مدیریتی — تمرین کامل چرخه DDL و DML.

راه‌حل قدم‌به‌قدم پروژه

  1. ۱

    هدف پروژه

    طراحی سه‌جدوله فروشگاه با FK، درج داده نمونه و اجرای گزارش‌های فروش بر اساس آن.

  2. ۲

    الزامات و موارد موردنیاز

    SQLite برای سادگی (sqlite3 app.db) یا MySQL؛ ترمینال دیتابیس؛ آشنایی با درس‌های SELECT و JOIN.

  3. ۳

    قدم ۱ — اسکیما را بنویسید

    سه جدول: products(id, name, price DECIMAL, stock INT) و customers(id, name, email UNIQUE) و orders(id, customer_id FK, product_id FK, qty INT, total DECIMAL, created_at). قبل از نوشتن، روی کاغذ روابط را بکشید: هر سفارش به یک مشتری و یک محصول.

  4. ۴

    قدم ۲ — داده نمونه واقعی

    ۵ محصول و ۴ مشتری و ۸ سفارش درج کنید؛ حداقل یک مشتری بدون سفارش بگذارید تا تفاوت INNER و LEFT را با چشم ببینید. تاریخ‌ها را پخش کنید تا گزارش ماهانه معنا بگیرد.

  5. ۵

    قدم ۳ — گزارش‌های پایه

    گزارش ۱: پرفروش‌ترین محصولات با SUM(qty). گزارش ۲: خرید هر مشتری با JOIN و SUM(total). گزارش ۳: مشتریان بدون سفارش با LEFT JOIN + IS NULL. هر گزارش را با انتظار ذهنی‌تان مقایسه کنید.

  6. ۶

    قدم ۴ — گزارش ماهانه

    گزارش فروش هر ماه: strftime('%Y-%m', created_at) در SQLite یا DATE_FORMAT در MySQL؛ با GROUP BY ماه و SUM(total). شماره ماه‌ها باید مرتب باشند.

  7. ۷

    قدم ۵ — امنیت و ایمنی

    یک تراکنش تمرین کنید: افزایش qty سفارش و کاهش stock محصول باید با هم موفق یا با هم لغو شوند. بعد از آن، VIEW بسازید: CREATE VIEW customer_totals AS SELECT … تا گزارش‌ها قابل استفاده مجدد شوند.

نکات مهم

  • ◆ قبل از خواندن راه‌حل، خودتان حداقل ۳۰ دقیقه تلاش کنید؛ یادگیری واقعی در همین تلاش اتفاق می‌افتد.
  • ◆ راه‌حل را بعد از دیدن، عمداً تغییر دهید و دوباره اجرا کنید تا با هر خط آن آشنا شوید.
  • ◆ اگر جایی گیر کردید، فقط بخش مشکل را جستجو کنید، نه کل پروژه را — دامنه جستجوی کوچک یادگیری را عمیق‌تر می‌کند.

کد کامل راه‌حل پروژه

-- ۱) اسکیما
CREATE TABLE products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  price REAL NOT NULL CHECK (price >= 0),
  stock INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE customers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  qty INTEGER NOT NULL CHECK (qty > 0),
  total REAL NOT NULL,
  created_at TEXT DEFAULT (datetime('now'))
);

-- ۲) داده نمونه
INSERT INTO products (name, price, stock) VALUES
  ('هندزفری', 250000, 40), ('کیف لپ‌تاپ', 320000, 25),
  ('موس بی‌سیم', 180000, 60), ('ماوس‌پد', 90000, 100),
  ('شارژر فست', 210000, 55);
INSERT INTO customers (name, email) VALUES
  ('علی', 'ali@x.com'), ('سارا', 'sara@x.com'),
  ('مریم', 'm@x.com'), ('رضا', 'reza@x.com');
INSERT INTO orders (customer_id, product_id, qty, total) VALUES
  (1, 1, 2, 500000), (1, 3, 1, 180000), (2, 2, 1, 320000),
  (2, 5, 2, 420000), (3, 4, 3, 270000), (3, 1, 1, 250000);

-- ۳) گزارش‌ها
SELECT p.name, SUM(o.qty) AS sold
FROM orders o JOIN products p ON p.id = o.product_id
GROUP BY p.id ORDER BY sold DESC;

SELECT c.name, COALESCE(SUM(o.total), 0) AS spent
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id ORDER BY spent DESC;

SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

SELECT strftime('%Y-%m', created_at) AS month, SUM(total) AS revenue
FROM orders GROUP BY month ORDER BY month;

خروجی مورد انتظار

خروجی‌های مورد انتظار:
  پرفروش‌ترین‌ها: هندزفری (۳)، شارژر فست (۲)، ماوس‌پد (۳)، موس بی‌سیم (۱)، کیف لپ‌تاپ (۱)
  خرید مشتریان: علی ۶۸۰٫۰۰۰؛ سارا ۷۴۰٫۰۰۰؛ مریم ۵۲۰٫۰۰۰؛ رضا ۰
  مشتری بدون سفارش: رضا
  (اعداد بسته به داده شما کمی متفاوت می‌شود)

چالش اضافه (اختیاری)

دو ستون updated_at و soft delete به orders اضافه کنید و گزارش «سفارش‌های لغوشده» بسازید.

پرسش و پاسخ این درس (۰)

هنوز دیدگاهی ثبت نشده؛ اولین نفر باشید!

دیدگاه خود را بنویسید

کپچا

دیدگاه شما پس از تأیید مدیر نمایش داده می‌شود.