سوال پیدا کردن کارمندانی که بالاترین حقوق در دپارتمان خود را دارند

این سوال از مهمترین تمرین های SQL است . که اون رو به روش های مختلف (Subquery, JOIN + GROUP BY, CTE, Window Function) مینویسیم و کاملا تریس میکنیم .

📌 داده فرضی برای تریس

جدول Employees:

EmployeeID	Name	DepartmentID	Salary
1	Ali	10	5000
2	Sara	10	7000
3	Reza	20	6000
4	Mina	20	6000
5	Amir	30	4000

روش 1: Subquery

SELECT Name, DepartmentID, Salary
FROM Employees E1
WHERE Salary = (
    SELECT MAX(Salary)
    FROM Employees E2
    WHERE E2.DepartmentID = E1.DepartmentID
);

تحلیل

• برای هر ردیف از E1 (کارمند بیرونی)، یک Subquery اجرا می‌شود.
• Subquery بالاترین حقوق در همان DepartmentID را پیدا می‌کند.
• اگر حقوق کارمند برابر با اون بود → برگردانده میشه.

خروجی

Name	DepartmentID	Salary
Sara	10	7000
Reza	20	6000
Mina	20	6000

🔹 مزیت: ساده و واضح.
🔹 عیب: ممکنه روی داده‌های خیلی بزرگ کند باشه چون برای هر کارمند، Subquery اجرا میشه.

🔹 روش 2: JOIN + GROUP BY

SELECT E.Name, E.DepartmentID, E.Salary
FROM Employees E
JOIN (
    SELECT DepartmentID, MAX(Salary) AS MaxSalary
    FROM Employees
    GROUP BY DepartmentID
) M ON E.DepartmentID = M.DepartmentID AND E.Salary = M.MaxSalary;

تحلیل

1. در Subquery M → برای هر DepartmentID بیشترین حقوق محاسبه میشه.
   خروجی M:

DepartmentID	MaxSalary
10	7000
20	6000
30	4000

1. بعد جدول اصلی Employees با M JOIN میشه:
   شرط JOIN میگه: DepartmentID برابر باشه + حقوق برابر با MaxSalary باشه.

خروجی (همون قبلی):

Name	DepartmentID	Salary
Sara	10	7000
Reza	20	6000
Mina	20	6000
Amir	30	4000

🔹 مزیت: خیلی سریع‌تر از روش Subquery correlated روی دیتاست بزرگ.
🔹 عیب: یک JOIN اضافه باید نوشته بشه.

---

🔹 روش 3: CTE (خوانایی بیشتر)

WITH MaxSalaries AS (
    SELECT DepartmentID, MAX(Salary) AS MaxSalary
    FROM Employees
    GROUP BY DepartmentID
)
SELECT E.Name, E.DepartmentID, E.Salary
FROM Employees E
JOIN MaxSalaries M
  ON E.DepartmentID = M.DepartmentID AND E.Salary = M.MaxSalary;

تحلیل

• CTE درست مثل همون Subquery در روش JOIN هست، فقط به جای اینکه وسط کوئری بنویسی، بیرون تعریف می‌کنی → خواناتر و قابل استفاده مجدد میشه.

خروجی

Name	DepartmentID	Salary
Sara	10	7000
Reza	20	6000
Mina	20	6000
Amir	30	4000

---

🔹 روش 4: Window Function (ROW_NUMBER یا RANK)

SELECT Name, DepartmentID, Salary
FROM (
    SELECT E.*,
           RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS rk
    FROM Employees E
) X
WHERE rk = 1;

تحلیل

1. Window Function → داده‌ها رو پارتیشن‌بندی (گروه‌بندی) می‌کنه بر اساس DepartmentID.
2. در هر دپارتمان، بر اساس Salary به ترتیب نزولی رتبه می‌ده:
   • Sara → رتبه 1 در دپارتمان 10
   • Ali → رتبه 2 در دپارتمان 10
   • Reza → رتبه 1 در دپارتمان 20
   • Mina → رتبه 1 در دپارتمان 20
   • Amir → رتبه 1 در دپارتمان 30
3. در نهایت فقط کسانی که rk = 1 هستند انتخاب میشن.

خروجی

Name	DepartmentID	Salary
Sara	10	7000
Reza	20	6000
Mina	20	6000
Amir	30	4000

🔹 مزیت: خیلی پرقدرت و مناسب مسائل پیچیده‌تر (مثلاً اگر بخوایم Top 3 در هر دپارتمان).
🔹 عیب: برای دیتابیس‌هایی که Window Function ساپورت نمی‌کنن، قابل استفاده نیست.

---

✅ جمع‌بندی:

• Subquery → ساده و مستقیم.
• JOIN + GROUP BY → بهینه‌تر روی داده‌های بزرگ.
• CTE → همون JOIN ولی خواناتر.
• Window Function → پیشرفته‌تر و انعطاف‌پذیرتر (برای Top N).

روش 5: EXISTS :

سوال :

“مشتریانی که مجموع سفارششان بالاتر از میانگین مجموع سفارش همه مشتری‌هاست.”

این همون Aggregation + Subquery کلاسیکه. من همه روش‌ها (Subquery, JOIN+GROUP BY, CTE, Window Function) رو می‌زنم و کامل تریس می‌کنم.

📌 داده فرضی برای تریس

جدول Customers:

CustomerID	Name
1	Ali
2	Sara
3	Reza

جدول Orders:

OrderID	CustomerID	Amount
101	1	200
102	1	300
103	2	400
104	2	600
105	3	150

---

1. Subquery (ساده‌ترین حالت)

SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY CustomerID
HAVING SUM(Amount) > (
    SELECT AVG(Total) 
    FROM (
        SELECT SUM(Amount) AS Total
        FROM Orders
        GROUP BY CustomerID
    ) AS T
);

تحلیل

• زیرکوئری داخلی: مجموع سفارش هر مشتری

CustomerID	Total
1	500
2	1000
3	150

• زیرکوئری خارجی: میانگین این مجموع‌ها → (500 + 1000 + 150) ÷ 3 = 550
• شرط HAVING: فقط کسانی که SUM(Amount) > 550 → مشتری 2 (Sara).

خروجی

CustomerID	TotalAmount
2	1000

---

2. JOIN + GROUP BY

راه حل مشابه ولی با JOIN بین Customers و Orders برای وضوح:

SELECT C.CustomerID, C.Name, SUM(O.Amount) AS TotalAmount
FROM Customers C
LEFT JOIN Orders O ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerID, C.Name
HAVING SUM(O.Amount) > (
    SELECT AVG(Total) 
    FROM (
        SELECT SUM(Amount) AS Total
        FROM Orders
        GROUP BY CustomerID
    ) AS T
);

تحلیل

• همون کار روش 1 رو می‌کنه، فقط Customers رو هم join کردیم که اسم مشتری هم بیاد.

خروجی

CustomerID	Name	TotalAmount
2	Sara	1000

---

3. CTE (خوانایی بیشتر)

WITH CustomerTotals AS (
    SELECT CustomerID, SUM(Amount) AS TotalAmount
    FROM Orders
    GROUP BY CustomerID
),
AvgTotal AS (
    SELECT AVG(TotalAmount) AS AvgTotal
    FROM CustomerTotals
)
SELECT C.CustomerID, C.Name, CT.TotalAmount
FROM Customers C
JOIN CustomerTotals CT ON C.CustomerID = CT.CustomerID
CROSS JOIN AvgTotal A
WHERE CT.TotalAmount > A.AvgTotal;

تحلیل

1. CustomerTotals → مجموع سفارش هر مشتری (مثل جدول T در روش اول).
2. AvgTotal → میانگین اون مجموع‌ها.
3. CROSS JOIN باعث میشه مقدار AvgTotal به هر ردیف وصل بشه.
4. شرط WHERE → فقط کسانی که بیشتر از میانگین دارند.

خروجی

CustomerID	Name	TotalAmount
2	Sara	1000

---

4. Window Function

SELECT CustomerID, Name, TotalAmount
FROM (
    SELECT C.CustomerID, C.Name, SUM(O.Amount) AS TotalAmount,
           AVG(SUM(O.Amount)) OVER () AS AvgTotal
    FROM Customers C
    JOIN Orders O ON C.CustomerID = O.CustomerID
    GROUP BY C.CustomerID, C.Name
) X
WHERE TotalAmount > AvgTotal;

تحلیل

1. اول SUM(O.Amount) → مجموع سفارش هر مشتری.
2. AVG(SUM(...)) OVER () → میانگین مجموع‌ها در کل جدول (بدون نیاز به Subquery دوم).
   • Customer 1 → 500, Avg = 550
   • Customer 2 → 1000, Avg = 550
   • Customer 3 → 150, Avg = 550
3. شرط WHERE → فقط Customer 2

خروجی

CustomerID	Name	TotalAmount
2	Sara	1000

---

✅ جمع‌بندی

• Subquery → مستقیم و پایه‌ای.
• JOIN → همون منطق ولی با اسم مشتری.
• CTE → خواناتر و مرحله‌ای.
• Window Function → حرفه‌ای و خیلی سریع روی دیتای زیاد.

---

۱. کِی از WHERE در Subquery استفاده می‌کنیم؟

وقتی می‌خوای هر ردیف از جدول رو با یک مقدار محاسبه‌شده مقایسه کنی.

مثال: کارمندان با بالاترین حقوق در دپارتمان خود

SELECT Name, DepartmentID, Salary
FROM Employees E1
WHERE Salary = (
    SELECT MAX(Salary)
    FROM Employees E2
    WHERE E2.DepartmentID = E1.DepartmentID
);

اینجا:

• هر کارمند (یک ردیف) باید با حداکثر حقوق در دپارتمان خودش مقایسه بشه.
• بنابراین Subquery به ازای هر ردیف اجرا میشه.
• چون سطح داده «ردیف-به-ردیف» هست → نیاز به GROUP BY در بیرون نداریم.

---

🔹 ۲. کِی از GROUP BY استفاده می‌کنیم؟

وقتی می‌خوای چیزی رو در سطح گروه/مشتری/دپارتمان محاسبه کنی (aggregation).

مثال: مشتریانی که مجموع سفارششان بالاتر از میانگین است

SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY CustomerID
HAVING SUM(Amount) > (
    SELECT AVG(Total)
    FROM (
        SELECT SUM(Amount) AS Total
        FROM Orders
        GROUP BY CustomerID
    ) T
);

اینجا:

• اول باید مجموع سفارش هر مشتری محاسبه بشه (aggregation).
• پس نیاز به GROUP BY داریم.
• بعد این مجموع‌ها با میانگین کل مقایسه میشن.

---

🔹 تفاوت اصلی

• مسائل ردیف-به-ردیف → از Subquery در WHERE استفاده می‌کنیم (مثل کارمند و حداکثر حقوق).
• مسائل جمعی (aggregation) → از GROUP BY + HAVING استفاده می‌کنیم (مثل مشتری و مجموع سفارش).

---

🔹 راهنمای عملی (تحلیل مسئله)

وقتی یک سؤال SQL داری، قبل از نوشتن کوئری از خودت بپرس:

1. ❓ «آیا باید روی هر ردیف جداگانه بررسی کنم یا روی یک گروه از ردیف‌ها؟»
   • اگر روی هر ردیف → Subquery در WHERE یا EXISTS.
   • اگر روی گروه → GROUP BY + HAVING.
2. ❓ «خروجی من در چه سطحیه؟»
   • اگر خروجی در سطح کارمند، سفارش، محصول باشه → بیشتر WHERE + Subquery.
   • اگر خروجی در سطح مشتری، دپارتمان یا دسته‌بندی باشه → بیشتر GROUP BY.

---

✅ به همین خاطر:

• برای کارمند و بالاترین حقوق → مقایسه‌ی ردیف-به-ردیف → WHERE Subquery.
• برای مشتری و مجموع سفارش‌ها → نیاز به جمع‌زدن و مقایسه گروهی → GROUP BY.

🧾 جدول تشخیص Subquery در WHERE vs GROUP BY

نوع سؤال / مسئله	سطح داده (ردیف یا گروه)	ابزار مناسب	مثال
مقایسه هر ردیف با یک مقدار محاسبه‌شده	ردیف-به-ردیف	Subquery در WHERE	«کارمندانی که حقوقشان = بیشترین حقوق در دپارتمان خودشان»
بررسی وجود یا شرط خاص روی یک ردیف مرتبط	ردیف-به-ردیف	EXISTS یا Subquery در WHERE	«مشتریانی که سفارش دارند»
نیاز به محاسبه جمع/میانگین/حداکثر برای هر گروه (مشتری، دپارتمان، محصول)	گروهی	GROUP BY + Aggregation	«مشتریانی که مجموع سفارششان بالاتر از 1000 است»
مقایسه مقدار گروهی با یک معیار کلی (میانگین کل، بیشترین کل)	گروهی	GROUP BY + HAVING + Subquery	«مشتریانی که مجموع سفارششان بالاتر از میانگین همه مشتری‌هاست»
انتخاب n-امین رکورد (مثلاً بالاترین حقوق)	ردیف در گروه	Subquery در WHERE یا JOIN با MAX()	«کارمندی که بیشترین حقوق در هر دپارتمان دارد»
انتخاب چند رکورد برتر (Top N)	گروهی ولی پیشرفته	Window Function (ROW_NUMBER / RANK)	«۳ مشتری برتر در هر شهر»

---

🔹 چند قانون طلایی برای تشخیص

1. اگر می‌بینی توی صورت سؤال کلمه‌هایی مثل «بیشترین هر دپارتمان»، «کمترین برای هر گروه»، «مجموع هر مشتری» → احتمالاً باید GROUP BY بزنی.
2. اگر می‌بینی صورت سؤال می‌گه «هر کارمند را با … مقایسه کن» یا «هر ردیف بررسی شود» → معمولاً Subquery در WHERE یا EXISTS.
3. اگر می‌خوای «روی کل جدول» یک عدد محاسبه کنی (مثل میانگین کل، بیشترین کل) → Subquery سطح بالا.
4. اگر می‌خوای «روی هر گروه» عدد محاسبه کنی (مثل مجموع سفارش مشتری) → GROUP BY.

---

✅ مثال تطبیقی:

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

سوالات تمرینی

❓ سوال 1

کارمندانی را پیدا کنید که کمترین حقوق در دپارتمان خودشان را دارند.

---

❓ سوال 2

محصولاتی را بیابید که تعداد فروش‌شان بیشتر از میانگین تعداد فروش همه محصولات است.

---

❓ سوال 3

دانشجویانی را پیدا کنید که نمره آن‌ها در یک درس خاص برابر با بالاترین نمره همان درس است.

---

❓ سوال 4

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

---

❓ سوال 5

مشتریانی را پیدا کنید که حداقل یک سفارش داده‌اند.

---

---

✅ پاسخ‌ها + تحلیل

---

✔️ پاسخ 1: کارمند با کمترین حقوق در دپارتمان

SELECT Name, DepartmentID, Salary
FROM Employees E1
WHERE Salary = (
    SELECT MIN(Salary)
    FROM Employees E2
    WHERE E2.DepartmentID = E1.DepartmentID
);

• اینجا نیاز به مقایسه ردیف-به-ردیف داریم (هر کارمند با مینیمم دپارتمان خودش).
• پس WHERE + Subquery مناسبه.

---

✔️ پاسخ 2: محصولات با تعداد فروش بالاتر از میانگین

SELECT ProductID, COUNT(*) AS SaleCount
FROM Sales
GROUP BY ProductID
HAVING COUNT(*) > (
    SELECT AVG(ProductSaleCount)
    FROM (
        SELECT COUNT(*) AS ProductSaleCount
        FROM Sales
        GROUP BY ProductID
    ) X
);

• اینجا باید اول تعداد فروش هر محصول محاسبه بشه (GROUP BY).
• بعد با میانگین مقایسه بشه → پس نیاز به GROUP BY + HAVING.

---

✔️ پاسخ 3: دانشجویان با بالاترین نمره در هر درس

SELECT StudentID, CourseID, Grade
FROM Grades G1
WHERE Grade = (
    SELECT MAX(Grade)
    FROM Grades G2
    WHERE G2.CourseID = G1.CourseID
);

• اینجا هر دانشجو (ردیف) باید با بیشترین نمره همان درس مقایسه بشه.
• پس WHERE + Subquery.

---

✔️ پاسخ 4: فروشندگان با مجموع فروش بالای ۱ میلیون

SELECT SalesmanID, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY SalesmanID
HAVING SUM(Amount) > 1000000;

• اینجا جمع فروش هر فروشنده مد نظره (aggregation).
• پس GROUP BY بدون Subquery هم جواب میده.

---

✔️ پاسخ 5: مشتریانی که حداقل یک سفارش داده‌اند

SELECT C.CustomerID, C.Name
FROM Customers C
WHERE EXISTS (
    SELECT 1
    FROM Orders O
    WHERE O.CustomerID = C.CustomerID
);

• اینجا شرط «وجود» داریم (حداقل یک سفارش).
• پس مناسب‌ترین راه → EXISTS.

---

🔑 جمع‌بندی

• سوال 1 و 3 → ردیف به ردیف → Subquery در WHERE.
• سوال 2 و 4 → گروهی → GROUP BY + HAVING.
• سوال 5 → وجود → EXISTS.