SQL: تدريب - استعلامات أساسية شاملة

-- إنشاء جدول الأقسام
CREATE TABLE departments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    location TEXT
);

-- إنشاء جدول الموظفين
CREATE TABLE employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    department_id INTEGER,
    salary DECIMAL(10,2),
    hire_date DATE,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- إنشاء جدول المنتجات
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    category TEXT,
    price DECIMAL(10,2),
    stock INTEGER DEFAULT 0
);

-- إنشاء جدول الطلبات
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_name TEXT NOT NULL,
    product_id INTEGER,
    quantity INTEGER,
    order_date DATE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- إدراج بيانات الأقسام
INSERT INTO departments (name, location) VALUES
    ('Engineering', 'New York'),
    ('Marketing', 'Los Angeles'),
    ('Finance', 'New York'),
    ('HR', 'Chicago');

-- إدراج بيانات الموظفين
INSERT INTO employees (name, department_id, salary, hire_date) VALUES
    ('John', 1, 15000.00, '2023-01-15'),
    ('Jane', 1, 18000.00, '2022-06-01'),
    ('Bob', 2, 12000.00, '2023-03-20'),
    ('Alice', 3, 13000.00, '2021-11-10'),
    ('Charlie', 1, 20000.00, '2020-08-05'),
    ('Diana', 2, 11000.00, '2024-01-10'),
    ('Eve', NULL, 9000.00, '2024-05-01'),
    ('Frank', 3, 14000.00, '2022-09-15');

-- إدراج بيانات المنتجات
INSERT INTO products (name, category, price, stock) VALUES
    ('iPhone 15', 'Phone', 5999.00, 100),
    ('MacBook Pro', 'Computer', 12999.00, 50),
    ('AirPods Pro', 'Accessory', 1899.00, 200),
    ('iPad Air', 'Tablet', 4799.00, 80),
    ('Apple Watch', 'Watch', 2999.00, 150),
    ('Magic Keyboard', 'Accessory', 999.00, 300),
    ('Mac Mini', 'Computer', 4499.00, 0),
    ('iPhone 14', 'Phone', 4999.00, 20);

-- إراج بيانات الطلبات
INSERT INTO orders (customer_name, product_id, quantity, order_date) VALUES
    ('Tom', 1, 1, '2024-06-01'),
    ('Lucy', 3, 2, '2024-06-02'),
    ('Mike', 2, 1, '2024-06-03'),
    ('Tom', 5, 1, '2024-06-05'),
    ('Sarah', 4, 3, '2024-06-10'),
    ('Lucy', 6, 5, '2024-06-15'),
    ('David', 1, 2, '2024-06-20'),
    ('Mike', 8, 1, '2024-06-25');

-- س1: استعلام جميع موظفي قسم الهندسة، مرتبة حسب الراتب من الأعلى إلى الأقل
SELECT name, salary, hire_date
FROM employees
WHERE department_id = 1
ORDER BY salary DESC;

name     salary    hire_date
-------  --------  ----------
Charlie  20000.00  2020-08-05
Jane     18000.00  2022-06-01
John     15000.00  2023-01-15

-- س2: استعلام أعلى 3 موظفين بالراتب (ترقيم صفحات: صفحة 1، 3 لكل صفحة)
SELECT name, salary, department_id
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 0;

name     salary    department_id
-------  --------  -------------
Charlie  20000.00  1
Jane     18000.00  1
John     15000.00  1

-- س3: استعلام الصفحة 2 (3 سجلات لكل صفحة)
SELECT name, salary, department_id
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 3;

name   salary    department_id
-----  --------  -------------
Frank  14000.00  3
Alice  13000.00  3
Bob    12000.00  2

-- س4: استعلام الموظفين without قسم معيّن
SELECT name, salary
FROM employees
WHERE department_id IS NULL;

name  salary
----  --------
Eve   9000.00

-- س5: استعلام المنتجات whose سعرها بين 1000 و 5000، مرتبة حسب السعر تصاعديًا
SELECT name, category, price, stock
FROM products
WHERE price BETWEEN 1000 AND 5000
ORDER BY price ASC;

name            category  price    stock
--------------  --------  -------  -----
AirPods Pro     Accessory 1899.00  200
Apple Watch     Watch     2999.00  150
Mac Mini        Computer  4499.00  0
iPad Air        Tablet    4799.00  80

-- س6: بحث غامض عن منتجات تحتوي على "iPhone" في الاسم
SELECT name, price, stock
FROM products
WHERE name LIKE '%iPhone%';

name        price    stock
----------  -------  -----
iPhone 15   5999.00  100
iPhone 14   4999.00  20

-- س7: استعلام المنتجات المتوفرة (مخزون > 0) whose سعرها أقل من 3000
SELECT name, price, stock
FROM products
WHERE stock > 0 AND price < 3000
ORDER BY price DESC;

name            price    stock
--------------  -------  -----
AirPods Pro     1899.00  200
Magic Keyboard  999.00   300

-- س8: استعلام آخر 5 طلبات، معرضًا اسم المنتج واسم العميل
SELECT o.customer_name, p.name AS product_name,
       o.quantity, o.order_date
FROM orders o
JOIN products p ON o.product_id = p.id
ORDER BY o.order_date DESC
LIMIT 5;

customer_name  product_name   quantity  order_date
-------------  -------------  --------  ----------
Mike           iPhone 14      1         2024-06-25
David          iPhone 15      2         2024-06-20
Lucy           Magic Keyboard 5         2024-06-15
Sarah          iPad Air       3         2024-06-10
Tom            Apple Watch    1         2024-06-05

-- س9: استعلام مبلغ كل طلب (سعر الوحدة × الكمية)، مرتبة حسب المبلغ تنازليًا
SELECT o.customer_name, p.name AS product_name,
       p.price, o.quantity,
       (p.price * o.quantity) AS total_amount
FROM orders o
JOIN products p ON o.product_id = p.id
ORDER BY total_amount DESC;

customer_name  product_name   price     quantity  total_amount
-------------  -------------  --------  --------  ------------
Sarah          iPad Air       4799.00   3         14397.00
Mike           MacBook Pro    12999.00  1         12999.00
David          iPhone 15      5999.00   2         11998.00
Tom            iPhone 15      5999.00   1         5999.00
Lucy           Magic Keyboard 999.00    5         4995.00
Mike           iPhone 14      4999.00   1         4999.00
Tom            Apple Watch    2999.00   1         2999.00
Lucy           AirPods Pro    1899.00   2         3798.00

-- س10: زيادة سعر جميع منتجات فئة "Accessory" بنسبة 10%
-- أكد أولاً نطاق التأثير
SELECT name, price FROM products WHERE category = 'Accessory';

name            price
--------------  -------
AirPods Pro     1899.00
Magic Keyboard  999.00

-- نفّذ التحديث
UPDATE products
SET price = ROUND(price * 1.1, 2)
WHERE category = 'Accessory';

-- تحقق من النتائج
SELECT name, price FROM products WHERE category = 'Accessory';

name            price
--------------  -------
AirPods Pro     2088.90
Magic Keyboard  1098.90

-- س11: تحديد المنتجات نفدت المخزون (مخزون = 0) كمُوقَفة
-- هنا نحذف المنتجات نفدت المخزون كمثال
-- أكد أولاً
SELECT id, name, stock FROM products WHERE stock = 0;

id  name      stock
--  --------  -----
7   Mac Mini  0

-- حذف المنتجات نفدت المخزون
DELETE FROM products WHERE stock = 0;

-- س12: إدراج منتج جديد
INSERT INTO products (name, category, price, stock)
VALUES ('AirTag', 'Accessory', 229.00, 500);

-- عرض جميع المنتجات أخيرًا
SELECT id, name, category, price, stock
FROM products
ORDER BY id;

id  name            category  price     stock
--  --------------  --------  --------  -----
1   iPhone 15       Phone     5999.00   100
2   MacBook Pro     Computer  12999.00  50
3   AirPods Pro     Accessory 2088.90   200
4   iPad Air        Tablet    4799.00   80
5   Apple Watch     Watch     2999.00   150
6   Magic Keyboard  Accessory 1098.90   300
8   iPhone 14       Phone     4999.00   20
9   AirTag          Accessory 229.00    500

SELECT category, COUNT(*) AS total
FROM products
GROUP BY category
HAVING total > 5;