Subqueries و Views — SQL الأساسيات

الاستعلامات الفرعية و Views

استعلام فرعي في WHERE

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total REAL NOT NULL CHECK (total >= 0),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'cancelled')),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO users (id, name, email, city) VALUES
  (1, 'سارة', 'sara@example.com', 'بغداد'),
  (2, 'عمر', 'omar@example.com', 'البصرة'),
  (3, 'ليلى', 'layla@example.com', 'بغداد'),
  (4, 'زيد', 'zaid@example.com', NULL);

INSERT INTO orders (user_id, total, status) VALUES
  (1, 120.0, 'paid'), (1, 45.5, 'paid'), (2, 300.0, 'pending'),
  (3, 80.0, 'paid'), (3, 15.0, 'cancelled'), (1, 60.0, 'pending');

-- الطلبات الأكبر من متوسط كل الطلبات
SELECT id, total FROM orders WHERE total > (SELECT AVG(total) FROM orders);

الاستعلام الداخلي يعيد قيمة واحدة تُستخدم في المقارنة.

IN و EXISTS

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total REAL NOT NULL CHECK (total >= 0),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'cancelled')),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO users (id, name, email, city) VALUES
  (1, 'سارة', 'sara@example.com', 'بغداد'),
  (2, 'عمر', 'omar@example.com', 'البصرة'),
  (3, 'ليلى', 'layla@example.com', 'بغداد'),
  (4, 'زيد', 'zaid@example.com', NULL);

INSERT INTO orders (user_id, total, status) VALUES
  (1, 120.0, 'paid'), (1, 45.5, 'paid'), (2, 300.0, 'pending'),
  (3, 80.0, 'paid'), (3, 15.0, 'cancelled'), (1, 60.0, 'pending');

SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 'paid');

SELECT name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 100);

EXISTS يتوقف عند أول تطابق. ⚠️ NOT IN مع استعلام فرعي قد يعيد NULL يعطي نتيجة فارغة غير متوقعة؛ فضّل NOT EXISTS.

استعلام فرعي في FROM (جدول مؤقت)

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total REAL NOT NULL CHECK (total >= 0),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'cancelled')),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO users (id, name, email, city) VALUES
  (1, 'سارة', 'sara@example.com', 'بغداد'),
  (2, 'عمر', 'omar@example.com', 'البصرة'),
  (3, 'ليلى', 'layla@example.com', 'بغداد'),
  (4, 'زيد', 'zaid@example.com', NULL);

INSERT INTO orders (user_id, total, status) VALUES
  (1, 120.0, 'paid'), (1, 45.5, 'paid'), (2, 300.0, 'pending'),
  (3, 80.0, 'paid'), (3, 15.0, 'cancelled'), (1, 60.0, 'pending');

SELECT AVG(spent) AS avg_customer_spend
FROM (SELECT user_id, SUM(total) AS spent FROM orders WHERE status = 'paid' GROUP BY user_id) AS per_user;

WITH (CTE): نفس الشيء بقراءة أوضح

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total REAL NOT NULL CHECK (total >= 0),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'cancelled')),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO users (id, name, email, city) VALUES
  (1, 'سارة', 'sara@example.com', 'بغداد'),
  (2, 'عمر', 'omar@example.com', 'البصرة'),
  (3, 'ليلى', 'layla@example.com', 'بغداد'),
  (4, 'زيد', 'zaid@example.com', NULL);

INSERT INTO orders (user_id, total, status) VALUES
  (1, 120.0, 'paid'), (1, 45.5, 'paid'), (2, 300.0, 'pending'),
  (3, 80.0, 'paid'), (3, 15.0, 'cancelled'), (1, 60.0, 'pending');

WITH per_user AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders WHERE status = 'paid'
  GROUP BY user_id
),
ranked AS (
  SELECT u.name, p.spent FROM per_user p JOIN users u ON u.id = p.user_id
)
SELECT * FROM ranked WHERE spent > 50 ORDER BY spent DESC;

CTE تسمّي خطوات الاستعلام وتقرأ من الأعلى للأسفل كخطوات منطقية. (WITH RECURSIVE تعالج البيانات الشجرية مثل التعليقات المتداخلة.)

Views: استعلام محفوظ باسم

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total REAL NOT NULL CHECK (total >= 0),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'cancelled')),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO users (id, name, email, city) VALUES
  (1, 'سارة', 'sara@example.com', 'بغداد'),
  (2, 'عمر', 'omar@example.com', 'البصرة'),
  (3, 'ليلى', 'layla@example.com', 'بغداد'),
  (4, 'زيد', 'zaid@example.com', NULL);

INSERT INTO orders (user_id, total, status) VALUES
  (1, 120.0, 'paid'), (1, 45.5, 'paid'), (2, 300.0, 'pending'),
  (3, 80.0, 'paid'), (3, 15.0, 'cancelled'), (1, 60.0, 'pending');

CREATE VIEW customer_summary AS
SELECT u.id, u.name, COUNT(o.id) AS orders, COALESCE(SUM(CASE WHEN o.status = 'paid' THEN o.total END), 0) AS paid_total
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

SELECT * FROM customer_summary WHERE orders > 0;

- تُستخدم كجدول في الاستعلامات.

- لا تخزن بيانات (إلا "Materialized Views" في PostgreSQL التي تُحدَّث يدوياً).

- مفيدة لإخفاء التعقيد، ولتقديم أعمدة محددة فقط (لا تُظهر الحقول الحساسة) — لكن الصلاحيات الفعلية تُضبط على مستوى القاعدة.

تمرين

بـ CTE: احسب لكل مدينة متوسط إنفاق العميل، ثم اعرض فقط المدن التي يتجاوز متوسطها المتوسط العام لكل العملاء.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total REAL NOT NULL CHECK (total >= 0),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'cancelled')),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO users (id, name, email, city) VALUES
  (1, 'سارة', 'sara@example.com', 'بغداد'),
  (2, 'عمر', 'omar@example.com', 'البصرة'),
  (3, 'ليلى', 'layla@example.com', 'بغداد'),
  (4, 'زيد', 'zaid@example.com', NULL);

INSERT INTO orders (user_id, total, status) VALUES
  (1, 120.0, 'paid'), (1, 45.5, 'paid'), (2, 300.0, 'pending'),
  (3, 80.0, 'paid'), (3, 15.0, 'cancelled'), (1, 60.0, 'pending');

-- استعلام فرعي في WHERE
SELECT id, total FROM orders WHERE total > (SELECT AVG(total) FROM orders);

-- NOT EXISTS أأمن من NOT IN
SELECT name FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- CTE متعددة الخطوات
WITH paid AS (
  SELECT user_id, SUM(total) AS spent FROM orders WHERE status = 'paid' GROUP BY user_id
)
SELECT u.name, p.spent FROM paid p JOIN users u ON u.id = p.user_id ORDER BY p.spent DESC;

-- View
CREATE VIEW customer_summary AS
SELECT u.id, u.name, COUNT(o.id) AS orders
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

SELECT * FROM customer_summary ORDER BY orders DESC;