الاستعلامات الفرعية Subqueries

-- الشكل العام
SELECT column
FROM table
WHERE column = (SELECT column FROM table WHERE condition);

-- الموظفون الذين راتبهم أعلى من المتوسط
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- الموظف الأعلى راتباً
SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

-- الموظفون في الأقسام الموجودة في الرياض
SELECT name, department_id
FROM employees
WHERE department_id IN (
    SELECT id
    FROM departments
    WHERE location = 'الرياض'
);

-- الموظفون الذين طلبوا منتجات
SELECT DISTINCT customer_name
FROM orders
WHERE product_id IN (
    SELECT id
    FROM products
    WHERE category = 'إلكترونيات'
);

-- الموظفون الذين ليسوا في قسم التقنية أو المبيعات
SELECT name
FROM employees
WHERE department_id NOT IN (
    SELECT id
    FROM departments
    WHERE name IN ('التقنية', 'المبيعات')
);

-- كل موظف مع متوسط راتب قسمه
SELECT name,
       salary,
       department_id,
       (SELECT AVG(salary)
        FROM employees e2
        WHERE e2.department_id = e1.department_id) AS dept_avg
FROM employees e1;

-- كل قسم مع عدد موظفيه
SELECT name,
       (SELECT COUNT(*)
        FROM employees
        WHERE department_id = d.id) AS employee_count
FROM departments d;

-- معالجة البيانات على مرحلتين
SELECT department, avg_salary
FROM (
    SELECT department_id AS department,
           AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_stats
WHERE avg_salary > 5000;

-- أعلى راتب في كل قسم
SELECT dept_salaries.department_id, dept_salaries.max_salary
FROM (
    SELECT department_id,
           MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
) AS dept_salaries;

-- الأقسام التي فيها موظفون
SELECT name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.id
);

-- الأقسام الفارغة (بدون موظفين)
SELECT name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.id
);

-- المنتجات التي لم تُطلب أبداً
SELECT name
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.product_id = p.id
);

-- الموظفون الذين راتبهم أعلى من كل موظفي المبيعات
SELECT name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department_id = 1
);
-- يجب أن يكون الراتب أعلى من الجميع

-- الموظفون الذين راتبهم أعلى من أي موظف في المبيعات
SELECT name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE department_id = 1
);
-- يكفي أن يكون أعلى من واحد فقط

-- الموظفون الذين راتبهم أعلى من متوسط قسمهم
SELECT name, salary, department_id
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

-- آخر طلب لكل عميل
SELECT *
FROM orders o1
WHERE order_date = (
    SELECT MAX(order_date)
    FROM orders o2
    WHERE o2.customer_name = o1.customer_name
);

SELECT name
FROM products
WHERE id = (
    SELECT product_id
    FROM orders
    GROUP BY product_id
    ORDER BY SUM(quantity) DESC
    LIMIT 1
);

SELECT name, salary
FROM employees
WHERE salary = (
    SELECT DISTINCT salary
    FROM employees
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1
);

SELECT name,
       salary,
       ROUND(salary * 100.0 / (SELECT SUM(salary) FROM employees), 2) AS percentage
FROM employees
ORDER BY percentage DESC;

SELECT d.name, COUNT(e.id) AS emp_count
FROM departments d
JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.name
HAVING COUNT(e.id) > (
    SELECT AVG(emp_count)
    FROM (
        SELECT COUNT(*) AS emp_count
        FROM employees
        GROUP BY department_id
    ) AS counts
);

-- نفس النتيجة بطريقتين:

-- باستخدام Subquery:
SELECT name FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE location = 'الرياض'
);

-- باستخدام JOIN:
SELECT e.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.location = 'الرياض';