SELECT والتصفية وقيم NULL

-- Fictional Dar Al Souq data, for practice only
CREATE TABLE customers (
  id      integer PRIMARY KEY,
  name    text NOT NULL,
  wilayat text            -- may be NULL
);
CREATE TABLE orders (
  id           integer PRIMARY KEY,
  customer_id  integer NOT NULL REFERENCES customers(id),
  status       text NOT NULL,          -- 'paid', 'cancelled', ...
  total        numeric(10,3) NOT NULL, -- OMR
  delivered_at timestamptz             -- NULL until delivered
);
CREATE TABLE order_items (
  order_id   integer NOT NULL REFERENCES orders(id),
  product_id text NOT NULL,
  qty        integer NOT NULL,
  unit_price numeric(10,3) NOT NULL
);

INSERT INTO customers VALUES
  (1, 'Test Customer A', 'Muscat'),
  (2, 'Test Customer B', NULL),
  (3, 'Test Customer C', 'Sohar');
INSERT INTO orders VALUES
  (101, 1, 'paid',      16.500, '2026-10-01 10:00+04'),
  (102, 1, 'paid',      23.000, NULL),
  (103, 2, 'cancelled',  6.500, NULL),
  (104, 2, 'paid',      12.000, '2026-10-03 15:30+04');
INSERT INTO order_items VALUES
  (101, 'coffee-cups',     2,  8.250),
  (102, 'serving-bowl',    1, 12.000),
  (102, 'woven-tote',      1,  9.500),
  (103, 'incense-burner',  1,  6.500),
  (104, 'serving-bowl',    1, 12.000);

SELECT id, status, total
FROM orders
WHERE status = 'paid' AND total >= 15
ORDER BY total DESC
LIMIT 10;
-- 102 | paid | 23.000
-- 101 | paid | 16.500

SELECT count(*)            AS orders,
       count(delivered_at) AS delivered
FROM orders;
-- orders = 4, delivered = 2

SELECT id, status, delivered_at
FROM orders
WHERE id = 103;
-- 103 | cancelled | NULL