خانه/ پروژه‌های واقعی/ مدرسه

پروژه ۱: سامانه مدرسه — ۵ جدول، ۷ چالش واقعی

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

در ۱۱ فصل قبل، تک‌تک ابزارهای SQL را یاد گرفتی. حالا وقتش رسیده همه را کنار هم، روی یک پایگاه‌داده‌ی واقعی و کامل به کار بگیری. این پروژه یک سامانه‌ی مدیریت مدرسه است: معلم‌ها درس تدریس می‌کنند، دانش‌آموزها در درس‌ها ثبت‌نام می‌کنند، و برای هر ثبت‌نام یک نمره ثبت می‌شود.

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

teachers
  • idINTEGER PK
  • full_nameTEXT
  • subjectTEXT
  • hire_dateTEXT
students
  • idINTEGER PK
  • full_nameTEXT
  • cityTEXT
  • gradeINTEGER
  • enrollment_dateTEXT
courses
  • idINTEGER PK
  • titleTEXT
  • teacher_idFK→ teachers
  • credit_hoursINTEGER
enrollments
  • idINTEGER PK
  • student_idFK→ students
  • course_idFK→ courses
  • enrollment_dateTEXT
grades
  • idINTEGER PK
  • enrollment_idFK→ enrollments
  • scoreREAL
  • exam_dateTEXT

نمره در جدول جداگانه grades نگه داشته می‌شود نه داخل enrollments — چون هر ثبت‌نام می‌تواند چند امتحان/نمره داشته باشد؛ همان اصل نرمال‌سازی که در فصل ۷ یاد گرفتیم. داده‌ی کامل: ۵ معلم، ۱۲ دانش‌آموز، ۶ درس، ۲۱ ثبت‌نام و ۲۱ نمره — با چند نکته‌ی عمدی داخلش (یک دانش‌آموز بدون هیچ ثبت‌نامی، و یک درس بدون هیچ دانش‌آموزی) تا چالش‌های زیر معنادار باشند.

sql
CREATE TABLE teachers (
  id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL,
  subject TEXT NOT NULL,
  hire_date TEXT NOT NULL
);

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL,
  city TEXT,
  grade INTEGER NOT NULL,
  enrollment_date TEXT NOT NULL
);

CREATE TABLE courses (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  teacher_id INTEGER NOT NULL REFERENCES teachers(id),
  credit_hours INTEGER NOT NULL
);

CREATE TABLE enrollments (
  id INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL REFERENCES students(id),
  course_id INTEGER NOT NULL REFERENCES courses(id),
  enrollment_date TEXT NOT NULL
);

CREATE TABLE grades (
  id INTEGER PRIMARY KEY,
  enrollment_id INTEGER NOT NULL REFERENCES enrollments(id),
  score REAL NOT NULL,
  exam_date TEXT NOT NULL
);

-- به‌همراه ۵ معلم، ۱۲ دانش‌آموز، ۶ درس، ۲۱ ثبت‌نام و ۲۱ نمره
-- (متن کامل INSERTها داخل فایل قابل‌دانلود پایین همین صفحه است)

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

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

فایل .db را می‌توانی مستقیم با نرم‌افزار رایگان DB Browser for SQLite باز کنی — بدون هیچ نصب یا تنظیمات اضافه. فایل .sql برای اجرا در SQL Server Management Studio، DBeaver، یا هر کلاینت دیگری مناسب است (نیاز به تغییر جزئی نحو REFERENCES ندارد چون از سینتکس استاندارد استفاده شده).

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

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

۱ دانش‌آموزان یک درس خاص

نام همه‌ی دانش‌آموزانی را پیدا کن که در درس «ریاضی ۱» ثبت‌نام کرده‌اند.

sql
SELECT s.full_name
FROM students s
JOIN enrollments e ON e.student_id = s.id
JOIN courses c ON c.id = e.course_id
WHERE c.title = 'ریاضی ۱';

۵ ردیف برمی‌گردد: علی محمدی، زهرا احمدی، حسین حسینی، سارا نوری، نگار جعفری.

۲ میانگین نمره هر دانش‌آموز

میانگین نمره‌ی هر دانش‌آموز (در همه‌ی درس‌هایش) را حساب کن و از بیشترین به کمترین مرتب کن.

sql
SELECT s.full_name, ROUND(AVG(g.score), 2) AS avg_score
FROM students s
JOIN enrollments e ON e.student_id = s.id
JOIN grades g ON g.enrollment_id = e.id
GROUP BY s.id
ORDER BY avg_score DESC;

فقط ۱۱ دانش‌آموز نتیجه می‌دهند نه ۱۲ — چون یک نفر (بهزاد یوسفی) هیچ ثبت‌نامی ندارد و JOIN او را حذف می‌کند. نگار جعفری با ۱۹.۱۳ بالاترین میانگین را دارد.

۳ دانش‌آموزان بدون ثبت‌نام

کدام دانش‌آموز، در هیچ درسی ثبت‌نام نکرده است؟

sql
SELECT s.full_name
FROM students s
WHERE NOT EXISTS (
  SELECT 1 FROM enrollments e WHERE e.student_id = s.id
);

جواب: بهزاد یوسفی. یادت باشد از فصل ۵: NOT EXISTS امن‌تر از NOT IN است، مخصوصاً وقتی زیرپرس‌وجو ممکن است NULL داشته باشد.

۴ پرمشغله‌ترین معلم

کدام معلم (با جمع دانش‌آموزان همه‌ی درس‌هایش) بیشترین تعداد دانش‌آموز را دارد؟

sql
SELECT t.full_name, COUNT(*) AS total_students
FROM teachers t
JOIN courses c ON c.teacher_id = t.id
JOIN enrollments e ON e.course_id = c.id
GROUP BY t.id
ORDER BY total_students DESC;

نکته‌ی جالب: نتیجه یک تساوی واقعی نشان می‌دهد — «خانم احمدی» و «دکتر رضایی» هر دو دقیقاً ۵ دانش‌آموز دارند. داده‌های واقعی همیشه تمیز و یک‌طرفه نیستند؛ گزارش درست باید هر دو را نشان دهد، نه فقط ردیف اول.

۵ دانش‌آموزان ممتاز

دانش‌آموزانی را پیدا کن که میانگین نمره‌شان بالای ۱۸ است.

sql
SELECT s.full_name, ROUND(AVG(g.score), 2) AS avg_score
FROM students s
JOIN enrollments e ON e.student_id = s.id
JOIN grades g ON g.enrollment_id = e.id
GROUP BY s.id
HAVING AVG(g.score) > 18;

یادآوری فصل ۳: چرا HAVING و نه WHERE؟ چون شرط روی نتیجه‌ی AVG() است — و WHERE قبل از تجمیع اجرا می‌شود، درحالی‌که HAVING بعد از آن. دو دانش‌آموز واجد شرایط‌اند: حسین حسینی (۱۹) و نگار جعفری (۱۹.۱۳).

۶ درس‌های بدون دانش‌آموز

آیا درسی هست که هیچ دانش‌آموزی در آن ثبت‌نام نکرده باشد؟

sql
SELECT c.title
FROM courses c
WHERE NOT EXISTS (
  SELECT 1 FROM enrollments e WHERE e.course_id = c.id
);

جواب: «ریاضی ۲ (پیشرفته)» — یک درس واقعی می‌تواند بدون هیچ دانش‌آموزی وجود داشته باشد، و گزارش‌های خوب باید بتوانند این را نشان دهند.

۷ رتبه‌بندی برترین‌ها

با استفاده از یک تابع پنجره‌ای، ۵ دانش‌آموز برتر را بر اساس میانگین نمره رتبه‌بندی کن.

sql
SELECT s.full_name, ROUND(AVG(g.score), 2) AS avg_score,
       RANK() OVER (ORDER BY AVG(g.score) DESC) AS rnk
FROM students s
JOIN enrollments e ON e.student_id = s.id
JOIN grades g ON g.enrollment_id = e.id
GROUP BY s.id
ORDER BY rnk
LIMIT 5;

دقت کن: لیلا هاشمی و سارا نوری هر دو دقیقاً ۱۶.۲۵ میانگین دارند و RANK() به هر دو رتبه‌ی ۴ می‌دهد (نه ۴ و ۵) — دقیقاً رفتاری که در فصل ۵ درباره‌ی توابع پنجره‌ای یاد گرفتیم.

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

  • یک پایگاه‌داده‌ی ۵جدولی با روابط واقعی (یک‌به‌چند و زنجیره‌ای) ساختیم: معلم → درس → ثبت‌نام → نمره.
  • هر ۷ چالش، ترکیبی از JOIN، GROUP BY، HAVING، NOT EXISTS و توابع پنجره‌ای بود — دقیقاً چیزهایی که در فصل‌های ۲ تا ۵ یاد گرفتی.
  • می‌توانی همین پایگاه‌داده را دانلود کنی و در SSMS یا DB Browser for SQLite، پرسش‌های خودت را هم رویش امتحان کنی.

در پروژه‌ی بعدی سراغ یک فروشگاه اینترنتی می‌رویم — با مشتری، محصول، سفارش و جزئیات سفارش؛ جایی که گزارش‌های فروش و درآمد ماهانه را می‌سازیم.

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