جملة SELECT المتقدمة (Advanced SELECT)

+----+-----------+-----+--------+
| id | name      | age | city   |
+----+-----------+-----+--------+
| 1  | أحمد      | 20  | الرياض |
| 2  | فاطمة     | 19  | جدة    |
| 3  | محمد      | 21  | الرياض |
| 4  | سارة      | 20  | مكة    |
| 5  | علي       | 22  | جدة    |
| 6  | نورة      | 19  | الرياض |
+----+-----------+-----+--------+

SELECT city FROM students;

+--------+
| city   |
+--------+
| الرياض |  ← مكررة
| جدة    |
| الرياض |  ← مكررة
| مكة    |
| جدة    |  ← مكررة
| الرياض |  ← مكررة
+--------+
6 rows in set

SELECT DISTINCT city FROM students;

+--------+
| city   |
+--------+
| الرياض |
| جدة    |
| مكة    |
+--------+
3 rows in set

SELECT DISTINCT age, city FROM students;

+-----+--------+
| age | city   |
+-----+--------+
| 20  | الرياض |
| 19  | جدة    |
| 21  | الرياض |
| 20  | مكة    |
| 22  | جدة    |
| 19  | الرياض |
+-----+--------+

SELECT column_name AS alias_name
FROM table_name;

+----+------------------+--------+
| id | name             | price  |
+----+------------------+--------+
| 1  | لابتوب Dell      | 3500   |
| 2  | ماوس لاسلكي      | 50     |
| 3  | لوحة مفاتيح      | 120    |
+----+------------------+--------+

SELECT 
    name AS product_name,
    price AS product_price
FROM products;

+------------------+---------------+
| product_name     | product_price |
+------------------+---------------+
| لابتوب Dell      | 3500          |
| ماوس لاسلكي      | 50            |
| لوحة مفاتيح      | 120           |
+------------------+---------------+

SELECT 
    name product_name,
    price product_price
FROM products;

SELECT 
    name AS "اسم المنتج",
    price AS "السعر بالريال"
FROM products;

+------------------+---------------+
| اسم المنتج       | السعر بالريال |
+------------------+---------------+
| لابتوب Dell      | 3500          |
| ماوس لاسلكي      | 50            |
| لوحة مفاتيح      | 120           |
+------------------+---------------+

SELECT 
    name,
    price,
    price * 1.15 AS price_with_tax
FROM products;

+------------------+-------+-----------------+
| name             | price | price_with_tax  |
+------------------+-------+-----------------+
| لابتوب Dell      | 3500  | 4025.00         |
| ماوس لاسلكي      | 50    | 57.50           |
| لوحة مفاتيح      | 120   | 138.00          |
+------------------+-------+-----------------+

SELECT 
    name,
    price,
    price * 0.20 AS discount_amount,
    price - (price * 0.20) AS final_price
FROM products;

+------------------+-------+-----------------+-------------+
| name             | price | discount_amount | final_price |
+------------------+-------+-----------------+-------------+
| لابتوب Dell      | 3500  | 700.00          | 2800.00     |
| ماوس لاسلكي      | 50    | 10.00           | 40.00       |
| لوحة مفاتيح      | 120   | 24.00           | 96.00       |
+------------------+-------+-----------------+-------------+

+----+------------------+-------+----------+
| id | product          | price | quantity |
+----+------------------+-------+----------+
| 1  | لابتوب           | 3500  | 2        |
| 2  | ماوس             | 50    | 10       |
| 3  | لوحة مفاتيح      | 120   | 5        |
+----+------------------+-------+----------+

SELECT 
    product,
    price,
    quantity,
    price * quantity AS total
FROM orders;

+------------------+-------+----------+-------+
| product          | price | quantity | total |
+------------------+-------+----------+-------+
| لابتوب           | 3500  | 2        | 7000  |
| ماوس             | 50    | 10       | 500   |
| لوحة مفاتيح      | 120   | 5        | 600   |
+------------------+-------+----------+-------+

+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 1  | أحمد       | محمد      |
| 2  | فاطمة      | علي       |
| 3  | محمد       | حسن       |
+----+------------+-----------+

SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

+-------------+
| full_name   |
+-------------+
| أحمد محمد   |
| فاطمة علي   |
| محمد حسن    |
+-------------+

SELECT 
    first_name || ' ' || last_name AS full_name
FROM employees;

SELECT 
    CONCAT(first_name, ' ', last_name, ' - القسم: ', department) AS employee_info
FROM employees;

+-----------------------------+
| employee_info               |
+-----------------------------+
| أحمد محمد - القسم: IT       |
| فاطمة علي - القسم: HR       |
| محمد حسن - القسم: Sales     |
+-----------------------------+

SELECT 
    product AS "المنتج",
    price AS "السعر",
    quantity AS "الكمية",
    price * quantity AS "الإجمالي",
    (price * quantity) * 0.15 AS "الضريبة",
    (price * quantity) * 1.15 AS "الإجمالي مع الضريبة"
FROM orders;

SELECT DISTINCT 
    city AS "المدن المتوفرة"
FROM students
ORDER BY city;

SELECT 
    CONCAT(first_name, ' ', last_name) AS "الاسم الكامل",
    salary AS "الراتب الشهري",
    salary * 12 AS "الراتب السنوي",
    (salary * 12) / 365 AS "الراتب اليومي"
FROM employees;

-- سيء: غير واضح
SELECT price * 1.15 FROM products;

-- جيد: واضح ومفهوم
SELECT price * 1.15 AS price_with_tax FROM products;

-- قد يكون غامضاً
SELECT price + tax * quantity FROM orders;

-- واضح ومحدد
SELECT (price + tax) * quantity FROM orders;

-- سيء: غير واضح
SELECT price * 1.15 AS p1 FROM products;

-- جيد: واضح ومفهوم
SELECT price * 1.15 AS price_with_vat FROM products;