خانه/ پروژه‌های واقعی/ فروشگاه اینترنتی

پروژه ۲: فروشگاه اینترنتی — گزارش‌های فروش واقعی

پروژه عملی ۲۵ دقیقه

هر فروشگاه اینترنتی واقعی، پشت صحنه‌اش دقیقاً همین ۴ جدول است: مشتری‌ها، محصول‌ها، سفارش‌ها، و اقلام هر سفارش. سوال‌هایی مثل «پرفروش‌ترین محصول چیست؟» یا «درآمد ماه گذشته چقدر بود؟» دقیقاً همان چیزی است که تیم‌های فروش هر روز از پایگاه‌داده می‌پرسند.

نقشه‌ی جدول‌ها

customers
  • idINTEGER PK
  • full_nameTEXT
  • cityTEXT
  • join_dateTEXT
products
  • idINTEGER PK
  • nameTEXT
  • categoryTEXT
  • priceINTEGER
  • stockINTEGER
orders
  • idINTEGER PK
  • customer_idFK→ customers
  • order_dateTEXT
  • statusTEXT + CHECK
order_items
  • idINTEGER PK
  • order_idFK→ orders
  • product_idFK→ products
  • quantityINTEGER
  • unit_priceINTEGER

unit_price در خودِ order_items دوباره ذخیره می‌شود (نه فقط از products.price خوانده شود) — یک تصمیم طراحی عمدی: قیمت یک محصول ممکن است فردا تغییر کند، اما قیمتی که مشتری در گذشته واقعاً پرداخته، باید برای همیشه ثابت بماند. جدول orders هم ستون status با قید CHECK دارد تا فقط مقادیر معتبر («در حال پردازش»، «ارسال‌شده»، «تحویل‌شده»، «لغوشده») در آن ثبت شوند — یادآوری فصل ۷. داده‌ی کامل: ۸ مشتری، ۹ محصول، ۱۰ سفارش و ۱۴ قلم سفارش.

sql
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL,
  city TEXT,
  join_date TEXT NOT NULL
);

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  price INTEGER NOT NULL,
  stock INTEGER NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  order_date TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('در حال پردازش','ارسال‌شده','تحویل‌شده','لغوشده'))
);

CREATE TABLE order_items (
  id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL,
  unit_price INTEGER NOT NULL
);

-- به‌همراه ۸ مشتری، ۹ محصول، ۱۰ سفارش و ۱۴ قلم سفارش
-- (متن کامل INSERTها داخل فایل قابل‌دانلود پایین همین صفحه است)

این پایگاه‌داده را دانلود کن و روی سیستم خودت کار کن

هر دو فایل، همین ۴ جدول و همین داده را دارند — کافی‌ست یکی را انتخاب کنی:

فایل .db را می‌توانی مستقیم با نرم‌افزار رایگان DB Browser for SQLite باز کنی. فایل .sql برای اجرا در SQL Server Management Studio، DBeaver، یا هر کلاینت دیگری مناسب است.

چالش‌ها: خودت را امتحان کن

اول خودت سعی کن، بعد پاسخ پیشنهادی را باز کن. همه‌ی جعبه‌ها از همین پایگاه‌داده به‌طور کامل و مستقل استفاده می‌کنند.

۱ پرفروش‌ترین محصولات

محصولات را بر اساس مجموع تعداد فروخته‌شده (فقط سفارش‌های لغونشده) از بیشترین به کمترین مرتب کن.

sql
SELECT p.name, SUM(oi.quantity) AS total_qty
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN orders o ON o.id = oi.order_id
WHERE o.status != 'لغوشده'
GROUP BY p.id
ORDER BY total_qty DESC;

«خودکار آبی» با ۱۳ عدد فروش، پرفروش‌ترین است. دقت کن که WHERE o.status != 'لغوشده' لازم است — وگرنه سفارش لغوشده هم در آمار فروش حساب می‌شود که واقعی نیست.

۲ مشتریان پرخرج

مشتریانی که بیشترین مبلغ را خرج کرده‌اند (فقط سفارش‌های لغونشده) پیدا کن.

sql
SELECT c.full_name, SUM(oi.quantity * oi.unit_price) AS total_spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status != 'لغوشده'
GROUP BY c.id
ORDER BY total_spent DESC;

محمد رضایی با ۸۹۰,۰۰۰ تومان اول است. فقط ۶ مشتری در نتیجه دیده می‌شوند — دو مشتری دیگر یا اصلاً سفارشی ثبت نکرده‌اند، یا تنها سفارششان لغو شده (مثل حسین حسینی).

۳ محصولات هرگز فروخته‌نشده

آیا محصولی هست که تا امروز حتی یک‌بار هم فروخته نشده باشد؟

sql
SELECT p.name FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM order_items oi
  JOIN orders o ON o.id = oi.order_id
  WHERE oi.product_id = p.id AND o.status != 'لغوشده'
);

جواب: «پرگار». محصولی که در انبار موجود است اما هنوز مشتری‌ای پیدا نکرده — دقیقاً همان چیزی که تیم بازاریابی باید بداند.

۴ میانگین ارزش سفارش

میانگین مبلغ کل هر سفارش (فقط سفارش‌های لغونشده) را حساب کن.

sql
SELECT ROUND(AVG(order_total), 0)
FROM (
  SELECT o.id, SUM(oi.quantity * oi.unit_price) AS order_total
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status != 'لغوشده'
  GROUP BY o.id
);

این یک زیرپرس‌وجوی Derived Table است (فصل ۵): اول جمع هر سفارش را جدا حساب می‌کنیم، بعد میانگین همان جمع‌ها را می‌گیریم — که با میانگین‌گرفتن مستقیم از قیمت تک‌تک اقلام کاملاً فرق دارد. نتیجه: حدود ۲۷۲,۵۵۶ تومان.

۵ سفارش‌های در حال پردازش

لیست سفارش‌هایی که هنوز وضعیت‌شان «در حال پردازش» است را با نام مشتری نشان بده.

sql
SELECT o.id, c.full_name, o.order_date
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'در حال پردازش';

۲ سفارش در انتظارند: سفارش فاطمه کریمی (۲۰۲۵-۰۱-۰۵) و سفارش علی محمدی (۲۰۲۵-۰۱-۲۰).

۶ درآمد ماهانه

درآمد (فقط سفارش‌های لغونشده) را به تفکیک ماه محاسبه کن.

sql
SELECT strftime('%Y-%m', o.order_date) AS ym,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o JOIN order_items oi ON oi.order_id = o.id
WHERE o.status != 'لغوشده'
GROUP BY ym ORDER BY ym;

strftime('%Y-%m', ...) تابع تاریخ SQLite است که فقط بخش سال-ماه را از یک تاریخ استخراج می‌کند — معادلش در SQL Server، FORMAT(order_date, 'yyyy-MM') یا DATEFROMPARTS است. نتیجه سه ماه را نشان می‌دهد: ۲۰۲۴-۱۱ (۲۱۴,۰۰۰)، ۲۰۲۴-۱۲ (۱,۳۴۰,۰۰۰) و ۲۰۲۵-۰۱ (۸۹۹,۰۰۰).

۷ مشتریانی که هرگز سفارش نداده‌اند

کدام مشتری عضو شده اما تا حالا حتی یک سفارش هم ثبت نکرده؟

sql
SELECT c.full_name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

جواب: امیر رستمی — نامزد خوبی برای یک ایمیل تشویقی «اولین خریدت را ثبت کن».

جمع‌بندی این پروژه

  • پایگاه‌داده‌ی ۴جدولی فروشگاه، الگوی استاندارد هر سیستم فروش/سفارش است.
  • نگه‌داشتن unit_price در خودِ order_items (نه فقط ارجاع به قیمت فعلی محصول) یک تصمیم طراحی مهم برای تاریخچه‌ی دقیق مالی است.
  • توابع تاریخ مثل strftime برای گزارش‌های دوره‌ای (ماهانه، سالانه) ضروری‌اند.

در پروژه‌ی بعدی سراغ یک کتابخانه می‌رویم — جایی که با امانت و بازگشت کتاب، تاریخ‌های سررسید و محاسبه‌ی مدت‌زمان امانت کار می‌کنیم.

درس قبلی
مدرسه
© ۱۴۰۴ SQLFarsi — سوالات متداول · ساخته شده توسط مصطفی موسوی