SQL: الربط والعلاقات

users (id, username, email)
  |
  +-- orders (id, user_id, total_amount, created_at)
        |
        +-- order_items (id, order_id, product_id, quantity, price)
              |
              +-- products (id, name, category_id, price)
                    |
                    +-- categories (id, name)

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

SELECT
    users.username,
    users.email,
    orders.id AS order_id,
    orders.total_amount,
    orders.created_at
FROM users
INNER JOIN orders ON users.id = orders.user_id;

SELECT
    u.username,
    o.id AS order_id,
    o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total_amount > 100
ORDER BY o.created_at DESC;

SELECT
    u.username,
    u.email,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email
ORDER BY total_spent DESC;

-- العثور على المستخدمين الذين لم يقدموا طلباً أبداً
SELECT
    u.username,
    u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- جميع الطلبات مع معلومات المستخدم (حتى الطلبات اليتيمة)
SELECT
    o.id AS order_id,
    o.total_amount,
    u.username
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

SELECT
    u.username,
    o.id AS order_id,
    o.created_at,
    p.name AS product_name,
    oi.quantity,
    oi.price AS item_price,
    (oi.quantity * oi.price) AS item_total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE u.id = 10
ORDER BY o.created_at DESC;

SELECT
    p.name AS product_name,
    p.price,
    c.name AS category_name
FROM products p
INNER JOIN categories c ON p.category_id = c.id
WHERE p.price > 50
ORDER BY c.name, p.name;

-- الجدول: employees (id, name, manager_id)

SELECT
    e.name AS employee_name,
    m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- البحث عن المنتجات في نفس الفئة
SELECT
    p1.name AS product,
    p2.name AS related_product,
    p1.category_id
FROM products p1
INNER JOIN products p2
    ON p1.category_id = p2.category_id
    AND p1.id != p2.id  -- لا تطابق المنتج مع نفسه
WHERE p1.id = 5
LIMIT 5;

-- كل مجموعة من الحجم واللون
SELECT
    sizes.name AS size,
    colors.name AS color
FROM sizes
CROSS JOIN colors;

-- الجداول:
-- products (id, name)
-- tags (id, name)
-- product_tags (product_id, tag_id)

-- الحصول على جميع الوسوم لمنتج
SELECT
    p.name AS product_name,
    t.name AS tag_name
FROM products p
INNER JOIN product_tags pt ON p.id = pt.product_id
INNER JOIN tags t ON pt.tag_id = t.id
WHERE p.id = 15;

-- الحصول على جميع الدورات لطالب
SELECT
    s.name AS student_name,
    c.name AS course_name,
    sc.grade
FROM students s
INNER JOIN student_courses sc ON s.id = sc.student_id
INNER JOIN courses c ON sc.course_id = c.id
WHERE s.id = 25;

SELECT
    u.username,
    o.id AS order_id,
    o.status
FROM users u
INNER JOIN orders o
    ON u.id = o.user_id
    AND o.status = 'completed'
    AND o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- الربط مع جدول مشتق
SELECT
    u.username,
    order_stats.order_count,
    order_stats.total_spent
FROM users u
INNER JOIN (
    SELECT
        user_id,
        COUNT(*) AS order_count,
        SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY user_id
) AS order_stats ON u.id = order_stats.user_id
WHERE order_stats.order_count > 5;

SELECT
    o.id AS order_id,
    o.created_at AS order_date,
    u.username,
    u.email,
    p.name AS product_name,
    oi.quantity,
    oi.price AS unit_price,
    (oi.quantity * oi.price) AS line_total,
    o.total_amount AS order_total
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.id = 100;

SELECT
    p.name AS product_name,
    c.name AS category,
    COUNT(oi.id) AS times_ordered,
    SUM(oi.quantity) AS total_quantity_sold,
    SUM(oi.quantity * oi.price) AS total_revenue
FROM products p
INNER JOIN categories c ON p.category_id = c.id
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name, c.name
ORDER BY total_revenue DESC
LIMIT 20;

SELECT
    u.username,
    u.email,
    u.created_at AS member_since,
    COUNT(DISTINCT o.id) AS total_orders,
    COUNT(DISTINCT oi.product_id) AS unique_products_bought,
    COALESCE(SUM(o.total_amount), 0) AS lifetime_value,
    MAX(o.created_at) AS last_order_date,
    DATEDIFF(NOW(), MAX(o.created_at)) AS days_since_last_order
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN order_items oi ON o.id = oi.order_id
GROUP BY u.id, u.username, u.email, u.created_at
HAVING total_orders > 0
ORDER BY lifetime_value DESC;

SELECT
    c.name AS category,
    COUNT(DISTINCT p.id) AS product_count,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(oi.quantity) AS items_sold,
    SUM(oi.quantity * oi.price) AS total_revenue,
    AVG(oi.price) AS avg_item_price
FROM categories c
INNER JOIN products p ON c.id = p.category_id
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.id
GROUP BY c.id, c.name
ORDER BY total_revenue DESC;

-- عد الطلبات لكل مستخدم
SELECT
    u.username,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- الحصول على أحدث طلب لكل مستخدم
SELECT
    u.username,
    o.id AS last_order_id,
    o.created_at AS last_order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.created_at = (
    SELECT MAX(o2.created_at)
    FROM orders o2
    WHERE o2.user_id = u.id
);

-- إجمالي الإنفاق لكل فئة من قبل كل مستخدم
SELECT
    u.username,
    c.name AS category,
    SUM(oi.quantity * oi.price) AS category_spending
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
INNER JOIN categories c ON p.category_id = c.id
GROUP BY u.id, u.username, c.id, c.name
ORDER BY u.username, category_spending DESC;