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

پروژه ۴: مدیریت کارکنان — زنجیره مدیریتی و تحلیل حقوق

پروژه عملی · پیشرفته ۲۵ دقیقه

آخرین و پیشرفته‌ترین پروژه‌ی این دوره. فقط ۲ جدول دارد، اما یکی از آن‌ها به خودش ارجاع می‌دهد (Self-Reference) — دقیقاً همان الگویی که در فصل ۴ (Self Join) و فصل ۵ (CTE بازگشتی) دیدیم. این‌بار آن را روی یک سناریوی واقعی به کار می‌گیریم: زنجیره‌ی مدیریتی یک سازمان.

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

departments
  • idINTEGER PK
  • nameTEXT
  • budgetINTEGER
employees
  • idINTEGER PK
  • full_nameTEXT
  • department_idFK→ departments
  • manager_idFK→ employees (خودش!)
  • hire_dateTEXT
  • salaryINTEGER
manager_id به همان جدول employees ارجاع می‌دهد — هر کارمند، مدیرش هم یک کارمند دیگر (یا NULL برای بالاترین رده) است. این‌جا نیازی به جدول جداگانه‌ی «مدیران» نیست.

داده‌ی کامل: ۴ بخش (مدیریت، فنی، فروش، منابع انسانی) و ۱۲ کارمند با زنجیره‌ی مدیریتی تا ۳ سطح عمق.

sql
CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  budget INTEGER NOT NULL
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL,
  department_id INTEGER NOT NULL REFERENCES departments(id),
  manager_id INTEGER REFERENCES employees(id),
  hire_date TEXT NOT NULL,
  salary INTEGER NOT NULL
);

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

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

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

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

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

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

۱ تعداد کارکنان هر بخش

هر بخش چند کارمند دارد؟ از بیشترین به کمترین مرتب کن.

sql
SELECT d.name, COUNT(*) AS emp_count
FROM employees e
JOIN departments d ON d.id = e.department_id
GROUP BY d.id
ORDER BY emp_count DESC;

فنی(۵)، فروش(۴)، منابع انسانی(۲)، مدیریت(۱).

۲ میانگین حقوق هر بخش

میانگین حقوق کارکنان هر بخش را حساب کن.

sql
SELECT d.name, ROUND(AVG(e.salary), 0) AS avg_salary
FROM employees e
JOIN departments d ON d.id = e.department_id
GROUP BY d.id
ORDER BY avg_salary DESC;

مدیریت ۱۸۰,۰۰۰,۰۰۰ (فقط یک نفر، مدیرعامل)، فنی ۹۴,۰۰۰,۰۰۰، فروش ۸۷,۰۰۰,۰۰۰، منابع انسانی ۸۵,۰۰۰,۰۰۰.

۳ کارکنان بالاتر از میانگین بخش خودشان

کدام کارکنان، حقوقشان از میانگین حقوق همان بخش خودشان بیشتر است؟

sql
SELECT e.full_name, e.salary, d.name AS dept
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE e.salary > (
  SELECT AVG(e2.salary) FROM employees e2
  WHERE e2.department_id = e.department_id
);

این یک زیرپرس‌وجوی همبسته (Correlated Subquery) است (فصل ۵): برخلاف زیرپرس‌وجوی معمولی که یک‌بار محاسبه می‌شود، این یکی برای هر ردیف کارمند دوباره اجرا می‌شود، چون به e.department_id از پرس‌وجوی بیرونی وابسته است. ۴ کارمند شرایط را دارند.

۴ بالاترین رده‌ی سازمان

کدام کارمند، هیچ مدیر مستقیمی ندارد؟

sql
SELECT full_name FROM employees WHERE manager_id IS NULL;

جواب: دکتر رضایی — تنها کسی که manager_idش NULL است، یعنی بالاترین رده‌ی سازمان.

۵ بخش‌های با کسری بودجه

کدام بخش، مجموع حقوق کارکنانش از بودجه‌ی تعیین‌شده‌اش بیشتر شده؟

sql
SELECT d.name, d.budget, SUM(e.salary) AS total_salary,
       d.budget - SUM(e.salary) AS remaining
FROM departments d
JOIN employees e ON e.department_id = d.id
GROUP BY d.id
HAVING remaining < 0;

جواب: «منابع انسانی» — بودجه‌اش ۱۵۰,۰۰۰,۰۰۰ است اما مجموع حقوق‌ها ۱۷۰,۰۰۰,۰۰۰ شده، یعنی ۲۰,۰۰۰,۰۰۰ کسری. دقیقاً همان کاربرد HAVING برای فیلترکردن روی نتیجه‌ی تجمیع‌شده.

۶ زنجیره‌ی کامل مدیریتی

برای کارمند «محمد رستمی» (id=7)، کل زنجیره‌ی مدیریتی‌اش را تا بالاترین رده نشان بده.

sql
WITH RECURSIVE chain AS (
  SELECT id, full_name, manager_id, 0 AS lvl
  FROM employees WHERE id = 7
  UNION ALL
  SELECT e.id, e.full_name, e.manager_id, chain.lvl + 1
  FROM employees e JOIN chain ON e.id = chain.manager_id
)
SELECT full_name, lvl FROM chain ORDER BY lvl;

دقیقاً همان WITH RECURSIVE فصل ۵: محمد رستمی(رده ۰) ← علی محمدی(۱) ← خانم احمدی(۲) ← دکتر رضایی(۳). هر تکرار، یک پله بالاتر از زنجیره‌ی مدیریتی می‌رود تا به کسی برسد که manager_idش NULL است.

۷ رتبه‌بندی حقوق داخل هر بخش

داخل هر بخش (جدا از بخش‌های دیگر)، کارکنان را بر اساس حقوق رتبه‌بندی کن.

sql
SELECT d.name, e.full_name, e.salary,
       RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rnk
FROM employees e
JOIN departments d ON d.id = e.department_id
ORDER BY d.name, rnk;

PARTITION BY (فصل ۵) رتبه‌بندی را در هر بخش از نو شروع می‌کند — رتبه‌ی ۱ در «فروش» و رتبه‌ی ۱ در «فنی» کاملاً مستقل از هم محاسبه می‌شوند، برخلاف RANK() ساده که کل جدول را یک‌جا رتبه‌بندی می‌کرد.

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

  • یک جدول با ارجاع به خودش (self-reference)، می‌تواند سلسله‌مراتب‌های نامحدود‌عمق را با CTE بازگشتی مدل کند.
  • زیرپرس‌وجوی همبسته، برای مقایسه‌ی «هر ردیف در برابر گروه خودش» ابزار استاندارد است.
  • توابع پنجره‌ای با PARTITION BY، رتبه‌بندی مستقل داخل هر گروه را بدون نوشتن پرس‌وجوی جداگانه برای هر گروه ممکن می‌کنند.

با این ۴ پروژه، هرچه در ۱۲ فصل این دوره یاد گرفتی — از SELECT ساده تا تراکنش، ایندکس‌گذاری، امنیت و اکنون طراحی و تحلیل واقعی — را یک‌جا به کار گرفتی. تبریک می‌گوییم؛ حالا آماده‌ای با اعتمادبه‌نفس کامل، سراغ پایگاه‌داده‌های واقعی خودت بروی.

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