SQL: الاستعلامات المتقدمة

SELECT column1, aggregate_function(column2)
FROM table
GROUP BY column1;

SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;

+-------------+---------------+
| category    | product_count |
+-------------+---------------+
| Electronics | 25            |
| Books       | 48            |
| Furniture   | 12            |
+-------------+---------------+

-- الحصول على إحصائيات الفئة
SELECT
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price,
    MIN(price) AS min_price,
    MAX(price) AS max_price,
    SUM(stock) AS total_stock
FROM products
GROUP BY category;

-- عد المنتجات حسب الفئة والحالة
SELECT
    category,
    is_active,
    COUNT(*) AS count
FROM products
GROUP BY category, is_active;

+-------------+-----------+-------+
| category    | is_active | count |
+-------------+-----------+-------+
| Electronics | 1         | 22    |
| Electronics | 0         | 3     |
| Books       | 1         | 45    |
| Books       | 0         | 3     |
+-------------+-----------+-------+

SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

-- الفئات بمتوسط سعر أكثر من 100 دولار
SELECT
    category,
    COUNT(*) AS products,
    AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 100
ORDER BY avg_price DESC;

-- المنتجات النشطة، مجمعة حسب الفئة، بأكثر من 5 عناصر
SELECT
    category,
    COUNT(*) AS active_count,
    AVG(price) AS avg_price
FROM products
WHERE is_active = 1              -- تصفية الصفوف قبل التجميع
GROUP BY category
HAVING COUNT(*) > 5              -- تصفية المجموعات بعد التجميع
ORDER BY active_count DESC;

-- العثور على المنتجات أغلى من متوسط السعر
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- العثور على المستخدمين الذين قدموا طلبات
SELECT username, email
FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);

-- إظهار كل منتج مع متوسط سعر الفئة
SELECT
    name,
    price,
    category,
    (SELECT AVG(price)
     FROM products p2
     WHERE p2.category = products.category) AS category_avg_price
FROM products;

-- الحصول على الفئات بإحصائياتها، ثم التصفية
SELECT *
FROM (
    SELECT
        category,
        COUNT(*) AS product_count,
        AVG(price) AS avg_price
    FROM products
    GROUP BY category
) AS category_stats
WHERE avg_price > 50;

-- العثور على المستخدمين الذين قدموا طلباً واحداً على الأقل
SELECT username, email
FROM users
WHERE EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.user_id = users.id
);

-- العثور على المستخدمين الذين لم يقدموا طلباً أبداً
SELECT username, email
FROM users
WHERE NOT EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.user_id = users.id
);

-- دمج المنتجات النشطة والمميزة
SELECT id, name, 'active' AS type
FROM products
WHERE is_active = 1

UNION

SELECT id, name, 'featured' AS type
FROM products
WHERE is_featured = 1;

SELECT
    name,
    price,
    CASE
        WHEN price < 50 THEN 'رخيص'
        WHEN price < 200 THEN 'متوسط'
        WHEN price < 500 THEN 'غالي'
        ELSE 'فاخر'
    END AS price_category
FROM products;

-- عد المنتجات في كل نطاق سعري
SELECT
    CASE
        WHEN price < 50 THEN 'الميزانية'
        WHEN price < 200 THEN 'متوسط النطاق'
        ELSE 'متميز'
    END AS price_range,
    COUNT(*) AS product_count
FROM products
GROUP BY
    CASE
        WHEN price < 50 THEN 'الميزانية'
        WHEN price < 200 THEN 'متوسط النطاق'
        ELSE 'متميز'
    END;

SELECT NOW();              -- التاريخ والوقت الحالي: 2024-01-15 14:30:45
SELECT CURDATE();          -- التاريخ الحالي: 2024-01-15
SELECT CURTIME();          -- الوقت الحالي: 14:30:45

-- الطلبات من آخر 7 أيام
SELECT * FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

-- الطلبات من الشهر الماضي
SELECT * FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH);

-- تاريخ مستقبلي
SELECT DATE_ADD(NOW(), INTERVAL 30 DAY) AS delivery_date;

SELECT
    created_at,
    DATE_FORMAT(created_at, '%Y-%m-%d') AS date_only,
    DATE_FORMAT(created_at, '%M %d, %Y') AS formatted_date,
    DATE_FORMAT(created_at, '%W') AS day_of_week
FROM orders;

SELECT
    YEAR(created_at) AS year,
    MONTH(created_at) AS month,
    DAY(created_at) AS day,
    HOUR(created_at) AS hour
FROM orders;

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    UPPER(username) AS uppercase_name,
    LOWER(email) AS lowercase_email,
    LENGTH(description) AS desc_length,
    SUBSTRING(name, 1, 10) AS short_name,
    TRIM('  text  ') AS trimmed,
    REPLACE(description, 'old', 'new') AS updated_desc
FROM users;

SELECT
    price,
    ROUND(price) AS rounded,
    ROUND(price, 2) AS two_decimals,
    CEIL(price) AS rounded_up,
    FLOOR(price) AS rounded_down,
    ABS(-50) AS absolute_value,
    POWER(2, 3) AS power_result,      -- 2^3 = 8
    SQRT(16) AS square_root           -- 4
FROM products;

SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS monthly_revenue,
    AVG(total_amount) AS avg_order_value
FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month DESC;

SELECT
    users.username,
    users.email,
    COUNT(orders.id) AS order_count,
    SUM(orders.total_amount) AS total_spent
FROM users
INNER JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.username, users.email
ORDER BY total_spent DESC
LIMIT 5;

SELECT
    p1.name,
    p1.category,
    p1.price,
    (SELECT AVG(p2.price)
     FROM products p2
     WHERE p2.category = p1.category) AS category_avg
FROM products p1
WHERE p1.price < (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category = p1.category
)
ORDER BY p1.category, p1.price;

SELECT
    username,
    email,
    CASE
        WHEN total_orders = 0 THEN 'لا توجد طلبات'
        WHEN total_orders < 5 THEN 'عرضي'
        WHEN total_orders < 20 THEN 'منتظم'
        ELSE 'VIP'
    END AS customer_tier,
    total_orders,
    total_spent
FROM (
    SELECT
        u.username,
        u.email,
        COUNT(o.id) AS total_orders,
        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
) AS customer_stats
ORDER BY total_spent DESC;

SELECT
    name,
    stock,
    CASE
        WHEN stock = 0 THEN 'نفد المخزون'
        WHEN stock < 5 THEN 'حرج'
        WHEN stock < 20 THEN 'منخفض'
        ELSE 'جيد'
    END AS stock_status,
    price * stock AS inventory_value
FROM products
WHERE is_active = 1
HAVING stock_status IN ('نفد المخزون', 'حرج', 'منخفض')
ORDER BY
    CASE stock_status
        WHEN 'نفد المخزون' THEN 1
        WHEN 'حرج' THEN 2
        WHEN 'منخفض' THEN 3
    END;