PostgreSQL: معاملات وتعابير PostgreSQL

SELECT
  order_id,
  total_amount,
  total_amount * 0.08 AS tax,
  total_amount * 1.08 AS total_with_tax
FROM orders
WHERE order_status = 'completed';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  7 / 2 AS int_div,
  7.0 / 2 AS numeric_div,
  7 / 2.0 AS numeric_div2,
  CAST(7 AS NUMERIC) / 2 AS cast_div;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  2 + 3 * 4 AS no_parens,
  (2 + 3) * 4 AS with_parens;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  order_id,
  total_amount::NUMERIC(12,2) AS amount,
  (total_amount::NUMERIC(12,2) * 0.08)::NUMERIC(12,2) AS tax,
  (total_amount::NUMERIC(12,2) * 1.08)::NUMERIC(12,2) AS total_with_tax
FROM orders;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT product_name, unit_price
FROM products
WHERE unit_price >= 100
  AND unit_price < 500
  AND category = 'Electronics';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT order_id, total_amount, order_date
FROM orders
WHERE total_amount BETWEEN 100 AND 1000
  AND order_date BETWEEN '2025-01-01' AND '2025-12-31';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT order_id, old_status, new_status
FROM order_status_log
WHERE old_status IS DISTINCT FROM new_status;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  TRUE AND NULL AS and_result,
  TRUE OR NULL AS or_result,
  FALSE AND NULL AS and_false,
  FALSE OR NULL AS or_null,
  NOT NULL AS not_result;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- هذا يستبعد الصفوف حيث discount_rate هو NULL
SELECT product_name, discount_rate
FROM products
WHERE discount_rate > 0 OR discount_rate = 0;
-- صفوف NULL غير مضمنة لأن NULL > 0 هو NULL، NULL = 0 هو NULL

-- إصلاح: معالجة NULL صراحة
SELECT product_name, discount_rate
FROM products
WHERE discount_rate > 0 OR discount_rate = 0 OR discount_rate IS NULL;

 count 
-------
     5
(1 row)

SELECT
  customer_id,
  first_name || ' ' || last_name AS full_name,
  'Order #' || order_id || ': $' || total_amount::TEXT AS order_summary
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

  result  
----------
   42.50
(1 row)

SELECT
  product_name || COALESCE(' - ' || description, '') AS product_info
FROM products;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  order_id,
  order_date::DATE AS date_only,
  '2025-06-15'::DATE AS literal_date,
  CAST('2025-06-15 14:30:00' AS TIMESTAMP) AS full_timestamp
FROM orders
WHERE order_date::DATE >= '2025-01-01'::DATE;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  total_amount::NUMERIC(12,2) AS rounded,
  total_amount::INTEGER AS truncated,
  total_amount::TEXT AS as_text
FROM orders;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT order_id, total_amount, order_date
FROM orders
WHERE order_date >= '2025-01-01'::DATE
  AND order_date < '2025-07-01'::DATE
  AND total_amount::NUMERIC(12,2) > 500;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT order_id, order_status, total_amount
FROM orders
WHERE ROW(order_status, total_amount) = ROW('completed', 500);

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT order_id, customer_id, order_status
FROM orders
WHERE (order_status, customer_id) IN (
  ('completed', 1001),
  ('completed', 1002),
  ('pending', 1003)
);

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT product_name, unit_price, category
FROM products
WHERE unit_price > ALL (
  SELECT AVG(unit_price) FROM products GROUP BY category
);

  result  
----------
   42.50
(1 row)

SELECT daterange('2025-01-01'::DATE, '2025-06-30'::DATE) AS h1_range;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT o1.order_id, o2.order_id
FROM orders o1, orders o2
WHERE o1.customer_id = o2.customer_id
  AND o1.order_id < o2.order_id
  AND daterange(o1.order_date, o1.delivery_date) &&
      daterange(o2.order_date, o2.delivery_date);

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT product_name, unit_price
FROM products
WHERE numrange(100, 500) @> unit_price::NUMERIC;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- الخطوة 1: حساب الضريبة مع التحكم في الدقة
SELECT
  order_id,
  total_amount::NUMERIC(12,2) AS amount,
  (total_amount::NUMERIC(12,2) * 0.08)::NUMERIC(12,2) AS tax,
  (total_amount::NUMERIC(12,2) * 1.08)::NUMERIC(12,2) AS total_with_tax
FROM orders
WHERE order_status = 'completed'
  AND order_date >= '2025-01-01'::DATE;

-- الخطوة 2: فحص نطاق التاريخ بـ daterange
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE daterange('2025-01-01'::DATE, '2025-07-01'::DATE) @> order_date::DATE
  AND order_status <> 'cancelled';

-- الخطوة 3: تحويل سلسلة إلى تاريخ وتنسيق
SELECT
  order_id,
  'Order #' || order_id || ' - ' ||
  COALESCE(customer_name, 'Unknown') ||
  ' ($' || total_amount::TEXT || ')' AS order_label
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date::DATE >= '2025-01-01'::DATE
ORDER BY o.total_amount DESC;

-- الخطوة 4: منشئ صف وتداخل نطاق
SELECT
  p1.product_name AS product_a,
  p2.product_name AS product_b,
  numrange(p1.unit_price, p1.unit_price * 1.2) AS price_range_a,
  numrange(p2.unit_price, p2.unit_price * 1.2) AS price_range_b
FROM products p1
JOIN products p2 ON p1.product_id < p2.product_id
WHERE p1.category = p2.category
  AND numrange(p1.unit_price, p1.unit_price * 1.2) &&
      numrange(p2.unit_price, p2.unit_price * 1.2)
  AND p1.is_active = true AND p2.is_active = true;