-- ربط الاسم
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

-- MySQL يدعم أيضًا || للربط (يتطلب تفعيل PIPES_AS_CONCAT)
-- SELECT first_name || ' ' || last_name FROM employees;

-- استخراج جزء اسم المستخدم من البريد الإلكتروني (قبل @)
SELECT email, SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username
FROM employees
WHERE email IS NOT NULL;

-- استخراج بسيط: ابدأ من الموضع 1، خذ 3 أحرف
SELECT SUBSTRING('Hello World', 1, 3);  -- النتيجة: Hel

SELECT UPPER('hello') AS uppercase, LOWER('WORLD') AS lowercase;
-- النتيجة: HELLO, world

-- استخدام عملي: بحث غير حساس لحالة الأحرف
SELECT * FROM employees WHERE LOWER(email) = 'zhangsan@company.com';

-- إزالة المسافات من البداية والنهاية
SELECT TRIM('  hello  ') AS trimmed;  -- النتيجة: hello

-- إزالة أحرف محددة
SELECT TRIM(LEADING '0' FROM '0012300');  -- النتيجة: 12300

-- إخفاء الأرقام الأربعة الوسطى من رقم الهاتف بـ ****
SELECT phone, REPLACE(phone, SUBSTRING(phone, 4, 4), '****') AS masked
FROM employees;

SELECT first_name, LENGTH(first_name) AS name_len
FROM employees;

SELECT ROUND(3.14159, 2);   -- النتيجة: 3.14
SELECT ROUND(3.14159, 0);   -- النتيجة: 3
SELECT ROUND(1234.5, -2);   -- النتيجة: 1200 (تقريب للمئات)

SELECT CEIL(3.1);    -- النتيجة: 4 (تقريب للأعلى)
SELECT CEIL(3.0);    -- النتيجة: 3
SELECT FLOOR(3.9);   -- النتيجة: 3 (تقريب للأسفل)
SELECT FLOOR(3.0);   -- النتيجة: 3

SELECT ABS(-100);    -- النتيجة: 100
SELECT ABS(50);      -- النتيجة: 50

SELECT MOD(10, 3);   -- النتيجة: 1
SELECT MOD(100, 7);  -- النتيجة: 2

-- SQL Server لا يدعم دالة MOD، استخدم % بدلاً من ذلك
-- SELECT 10 % 3;    -- النتيجة: 1

SELECT NOW();        -- النتيجة: 2026-06-28 14:30:00 (الوقت الحالي)
SELECT CURDATE();    -- النتيجة: 2026-06-28 (التاريخ فقط)
SELECT CURTIME();    -- النتيجة: 14:30:00 (الوقت فقط)

SELECT DATE(NOW());  -- النتيجة: 2026-06-28

-- استخراج السنة والشهر واليوم
SELECT EXTRACT(YEAR FROM order_date) AS year,
       EXTRACT(MONTH FROM order_date) AS month,
       EXTRACT(DAY FROM order_date) AS day
FROM orders;

-- حساب عدد الأيام بين تاريخين
SELECT DATEDIFF('2026-12-31', '2026-01-01');  -- النتيجة: 364

-- حساب مدة خدمة الموظف بالأيام
SELECT first_name, hire_date,
       DATEDIFF(CURDATE(), hire_date) AS tenure_days
FROM employees;

-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date
FROM orders;

-- SQL Server
-- SELECT FORMAT(order_date, 'yyyy-MM-dd') FROM orders;

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

-- نص إلى عدد صحيح
SELECT CAST('123' AS SIGNED INTEGER);  -- MySQL
SELECT CAST('123' AS INT);             -- SQL Server

-- عدد إلى نص
SELECT CAST(12345 AS CHAR);

-- تاريخ إلى نص
SELECT CAST(order_date AS CHAR) FROM orders;

-- نص إلى تاريخ
SELECT CAST('2026-06-28' AS DATE);

-- MySQL
SELECT CONVERT('123', SIGNED INTEGER);
SELECT CONVERT(123, CHAR);

-- SQL Server
SELECT CONVERT(INT, '123');
SELECT CONVERT(VARCHAR, 12345);

-- دوال النصوص
SELECT CONCAT(string1, string2) AS concatenated,
       SUBSTRING(string, start_position, length) AS extracted,
       UPPER(string) AS uppercase,
       LOWER(string) AS lowercase,
       TRIM(string) AS trimmed,
       REPLACE(string, old_value, new_value) AS replaced,
       LENGTH(string) AS length;

-- دوال الأرقام
SELECT ROUND(number, decimal_places) AS rounded,
       CEIL(number) AS rounded_up,
       FLOOR(number) AS rounded_down,
       ABS(number) AS absolute,
       MOD(number1, number2) AS remainder;

-- دوال التواريخ
SELECT NOW() AS current_time,
       CURDATE() AS current_date,
       DATEDIFF(date1, date2) AS day_difference,
       EXTRACT(YEAR FROM date) AS year;

-- تحويل الأنواع
SELECT CAST(value AS target_type) AS converted;

SELECT 
    CONCAT(UPPER(last_name), ', ', first_name) AS name,
    LOWER(email) AS email,
    SUBSTRING(phone, 1, 3) AS area_code,
    LENGTH(first_name) AS first_name_length,
    REPLACE(department_id, NULL, 'Unassigned') AS department
FROM employees
ORDER BY last_name;

name          email                   area_code  first_name_length  department
-----------  ----------------------  ---------  -----------------  ----------
LI, Si       lisi@company.com        138        2                  2
WANG, Wu     wangwu@company.com      139        2                  2
ZHANG, San   zhangsan@company.com    137        2                  1

SELECT 
    order_id,
    total_amount AS original_amount,
    ROUND(total_amount * 0.9, 2) AS discounted_amount,
    CEIL(total_amount / 1000) AS thousands_ceil,
    FLOOR(total_amount / 1000) AS thousands_floor,
    MOD(total_amount, 100) AS remainder
FROM orders
WHERE status = 'completed';

order_id  original_amount  discounted_amount  thousands_ceil  thousands_floor  remainder
--------  --------------  -----------------  --------------  ---------------  ---------
1         15000.00        13500.00           15              15               0.00
2         8500.00         7650.00            9               8                0.00
3         3200.00         2880.00            4               3                0.00

SELECT 
    first_name AS name,
    hire_date AS hire_date,
    DATEDIFF(CURDATE(), hire_date) AS tenure_days,
    EXTRACT(YEAR FROM hire_date) AS hire_year,
    CONCAT(CAST(salary AS CHAR), ' yuan') AS salary_display,
    DATE_FORMAT(hire_date, '%Y-%m') AS hire_month
FROM employees
ORDER BY hire_date;

name     hire_date   tenure_days  hire_year  salary_display  hire_month
------   ----------  -----------  ---------  --------------  ----------
Zhang    2023-03-15  1199         2023       15000.00 yuan   2023-03
Li       2023-07-01  1089         2023       18000.00 yuan   2023-07
Wang     2024-01-10  899          2024       12000.00 yuan   2024-01

SELECT 
    employee_id,
    CONCAT(UPPER(TRIM(last_name)), ', ', TRIM(first_name)) AS standard_name,
    LOWER(TRIM(email)) AS standard_email,
    REPLACE(REPLACE(phone, '-', ''), ' ', '') AS standard_phone,
    CAST(salary AS DECIMAL(10,2)) AS standard_salary
FROM employees
WHERE email IS NOT NULL;

SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    CEIL(EXTRACT(MONTH FROM order_date) / 3.0) AS quarter,
    COUNT(*) AS order_count,
    ROUND(SUM(total_amount), 2) AS total_amount,
    ROUND(AVG(total_amount), 2) AS avg_order_amount,
    DATEDIFF(MAX(order_date), MIN(order_date)) AS span_days
FROM orders
WHERE status != 'cancelled'
GROUP BY EXTRACT(YEAR FROM order_date), CEIL(EXTRACT(MONTH FROM order_date) / 3.0)
ORDER BY year, quarter;