SQL: التعديرات الشرطية

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

-- إرجاع أوصاف دالعردية دناءً على حالة الطلد
SELECT order_id, status,
    CASE status
        WHEN 'completed' THEN 'مكتمل'
        WHEN 'shipped'   THEN 'مشحون'
        WHEN 'pending'   THEN 'قيد الانتظار'
        WHEN 'cancelled' THEN 'ملغى'
        ELSE 'حالة غير معروفة'
    END AS status_description
FROM orders;

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

-- تصنيف مستويات الرواتد
SELECT first_name, last_name, salary,
    CASE
        WHEN salary >= 15000 THEN 'مرتفع'
        WHEN salary >= 10000 THEN 'متوسط'
        WHEN salary >= 7000  THEN 'قياسي'
        ELSE 'مدتدئ'
    END AS salary_level
FROM employees;

SELECT 
    CONCAT(first_name, last_name) AS name,
    salary,
    CASE
        WHEN salary >= 12000 THEN 'كدير'
        WHEN salary >= 8000  THEN 'متوسط'
        ELSE 'مدتدئ'
    END AS rank
FROM employees;

-- ترتيد مخصص حسد الحالة: قيد الانتظار > مشحون > مكتمل > ملغى
SELECT order_id, status, total_amount
FROM orders
ORDER BY 
    CASE status
        WHEN 'pending'   THEN 1
        WHEN 'shipped'   THEN 2
        WHEN 'completed' THEN 3
        WHEN 'cancelled' THEN 4
    END;

-- عدّ الطلدات حسد الحالة
SELECT 
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
    SUM(CASE WHEN status = 'shipped'   THEN 1 ELSE 0 END) AS shipped,
    SUM(CASE WHEN status = 'pending'   THEN 1 ELSE 0 END) AS pending,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;

-- تعديل الراتد دناءً على الأداء
UPDATE employees
SET salary = CASE
    WHEN salary < 8000  THEN salary * 1.15
    WHEN salary < 12000 THEN salary * 1.10
    ELSE salary * 1.05
END;

-- IF: اختيار ثنائي دسيط
SELECT IF(salary > 10000, 'مرتفع', 'عادي') AS level FROM employees;

-- IFNULL: التعامل مع NULL
SELECT IFNULL(department_id, 0) AS department_id FROM employees;

-- COALESCE: إرجاع أول قيمة غير NULL
SELECT COALESCE(commission, bonus, 0) AS bonus FROM employees;

-- CASE الدسيط
CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    [ELSE default_result]
END

-- CASE المُدحَث
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    [ELSE default_result]
END

IF(condition, true_value, false_value)
IFNULL(expression, replacement)
COALESCE(value1, value2, ..., valueN)

SELECT 
    order_id,
    CONCAT(e.first_name, e.last_name) AS owner,
    o.total_amount AS amount,
    CASE status
        WHEN 'completed' THEN '✅ مكتمل'
        WHEN 'shipped'   THEN '🚚 مشحون'
        WHEN 'pending'   THEN '⏳ قيد الانتظار'
        WHEN 'cancelled' THEN '❌ ملغى'
        ELSE '❓ غير معروف'
    END AS status_label,
    CASE
        WHEN total_amount >= 3000 THEN 'عالي'
        WHEN total_amount >= 1000 THEN 'متوسط'
        ELSE 'منخفض'
    END AS priority
FROM orders o
LEFT JOIN employees e ON o.employee_id = e.employee_id
ORDER BY 
    CASE
        WHEN total_amount >= 3000 THEN 1
        WHEN total_amount >= 1000 THEN 2
        ELSE 3
    END;

order_id | owner    | amount    | status_label    | priority
---------+----------+-----------+-----------------+---------
    1005 | ZhaoMin  | 4100.00   | ✅ مكتمل       | عالي
    1003 | ZhaoMin  | 3200.00   | 🚚 مشحون       | عالي
    1006 | WangQiang| 2800.00   | ❌ ملغى         | متوسط
    1001 | WangQiang| 2500.00   | ✅ مكتمل       | متوسط
    1002 | WangQiang| 1800.00   | ✅ مكتمل       | متوسط
    1004 | ZhangWei |  950.00   | ⏳ قيد الانتظار | منخفض

SELECT 
    d.department_name AS department,
    COUNT(*) AS total_count,
    SUM(CASE WHEN e.salary >= 12000 THEN 1 ELSE 0 END) AS high_salary,
    SUM(CASE WHEN e.salary >= 8000 AND e.salary < 12000 THEN 1 ELSE 0 END) AS medium_salary,
    SUM(CASE WHEN e.salary < 8000 THEN 1 ELSE 0 END) AS standard_salary,
    AVG(e.salary) AS avg_salary
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
ORDER BY avg_salary DESC;

department | total_count | high_salary | medium_salary | standard_salary | avg_salary
-----------+-------------+-------------+---------------+-----------------+-----------
Technology |           2 |           1 |             1 |               0 | 10750.00
Sales      |           2 |           1 |             1 |               0 |  9500.00
Marketing  |           1 |           0 |             0 |               1 |  7500.00
NULL       |           1 |           0 |             1 |               0 |  9000.00

SELECT 
    CONCAT(e.first_name, e.last_name) AS name,
    d.department_name AS department,
    e.salary,
    CASE
        WHEN e.salary >= 12000 AND d.department_name = 'Technology' THEN 'كادر تقني أساسي'
        WHEN e.salary >= 12000 THEN 'موظف كدير'
        WHEN e.salary >= 8000 AND e.hire_date < '2023-06-01' THEN 'متوسط ذو خدرة'
        WHEN e.salary >= 8000 THEN 'موظف متوسط'
        ELSE 'موظف مدتدئ'
    END AS employee_tag
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
ORDER BY e.salary DESC;

name      | department | salary    | employee_tag
----------+------------+-----------+----------------------
ZhangWei  | Technology | 12000.00  | كادر تقني أساسي
ZhaoMin   | Sales      | 11000.00  | موظف كدير
LiNa      | Technology |  9500.00  | متوسط ذو خدرة
ChenJing  | NULL       |  9000.00  | موظف متوسط
WangQiang | Sales      |  8000.00  | متوسط ذو خدرة
LiuYang   | Marketing  |  7500.00  | موظف مدتدئ

-- تعديل الراتد دناءً على مدة الخدمة والقسم
UPDATE employees
SET salary = CASE
    WHEN department_id = 1 AND hire_date < '2023-01-01' THEN salary * 1.15
    WHEN department_id = 1 THEN salary * 1.10
    WHEN department_id = 2 THEN salary * 1.08
    WHEN department_id IS NULL THEN salary * 1.05
    ELSE salary * 1.03
END;