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

البرمجة، كما يتضح، صعبة. فالقواعد الأساسية عادةً بسيطة وواضحة. لكن البرامج المبنية فوق هذه القواعد تميل إلى أن تصبح معقدة بما يكفي لتُدخل قواعدها وتعقيدها الخاص. أنت تبني متاهتك الخاصة، بمعنى ما، وقد تضيع فيها. —Marijn Haverbeke

مخطط students_campus

ألقِ نظرة سريعة على مخطط الكيانات والعلاقات (ERD) في الشكل. ويمكنك إيجاد المخطط "students_campus" في قاعدة البيانات "df" عبر اتصال التجميع (أو اتصال 5...). ونفّذ SELECT * على الجدولين للحصول على فكرة عن المعلومات الواردة في المخطط.

وبشكل محدد، يتعلق الأمر بقائمة بجميع حرم UCLL الثمانية، وجدول فيه 1000 طالب مع بعض المعلومات الشخصية لكل طالب، وأي حرم يرتاده (لا يمكن للطالب الانتماء إلا إلى حرم واحد)، وكم يبعد سكنه عن هذا الحرم. والحقل الأخير غير إلزامي. وبالمناسبة، أنشأنا هذا المخطط كمثال للتوليد التلقائي للبيانات في DBSchema أو باستخدام موقع مثل https://mockaroo.com.

من يسكن أبعد عن الحرم من متوسط المسافة؟

هناك عدة أنواع من استعلامات SQL. فنوع يسأل عن الإجابة فقط. وقد تكون الإجابة عددًا أو اسمًا أو أكثر أو تاريخًا... ولهذا النوع من الاستعلامات، لا يلزمك تقديم الاستعلام أو الاستعلامات المستخدمة. ويكفي الجواب وحده. ومن أمثلة هذا النوع من الأسئلة التمرين التالي. أنجزه وتحقق من إجابتك.

كم طالبًا يسكن أبعد عن حرمه من متوسط المسافة؟ طُلبت الإجابة فقط، لا الاستعلامات التي استخدمتها. وهل يمكنك إيجاد الإجابة الصحيحة: 401 طالب؟

الحل

هذا سؤال يمكنك عادةً حله بسرعة باستعلامين بسيطين. يبحث الاستعلام الأول عن متوسط جميع المسافات:

SELECT AVG(distance_to_campus)
FROM student;

ونتيجة هذا الاستعلام 52.01658. ولاحظ أن القيم الفارغة لا تُحتسب عند حساب المتوسط، كما تذكر التوثيق بإيجاز عن الدالة 'avg'. ويمكنك الآن استخدام هذا العدد في استعلام لاحق:

SELECT count(*) -- or simply use * and read the number of lines in the query tool
FROM student
WHERE distance_to_campus > 52.01658;

إذن، لهذا النوع من استعلامات SQL، أجب فقط بـ '401'. حسنًا، نلت نقطة!

لكن بالنسبة إلى نوع آخر من أسئلة الامتحان، يلزمك تقديم الاستعلام. وإضافة إلى ذلك يجوز لك تقديم استعلام واحد فقط، يجب أن يستطيع محاضرك نسخه وتنفيذه في pgAdmin. ولا يُقبل حل باستعلامين منفصلين، حيث عليك بعد ذلك نسخ إجابة الاستعلام الأول إلى الثاني.

وللتمرين أعلاه حل أنيق: استخدم استعلامًا فرعيًا (ويُسمّى أيضًا استعلامًا متداخلًا). ويقدّم الاستعلام التالي حل التمرين (401) في استعلام واحد:

SELECT COUNT(*)
FROM student
WHERE distance_to_campus > (
  SELECT AVG(distance_to_campus)
  FROM student
);

في جملة WHERE يبدأ SELECT جديد. ونسمي هذا الاستعلام الاستعلام الداخلي. أما الاستعلام المحيط به فيُسمّى الاستعلام الخارجي. ويمكنك تعريف مستويات مختلفة من التداخل. ويمكن للاستعلام الفرعي أن يعيد صفًا واحدًا أو أكثر إلى الاستعلام الذي يستدعيه. وما سبق مثال على استعلام فرعي قياسي (scalar): لأن الاستعلام الفرعي يعيد عددًا واحدًا فقط. ويمكنك تداخل استعلام فرعي في جملة SELECT أو FROM أو WHERE أو HAVING.

متوسط المسافة لطلاب Proximus

نسختان للتمرين نفسه

حلّ الآن التمرين التالي باستعلام فرعي:

أعطِ متوسط المسافة إلى الحرم لجميع الطلاب الذين يرتادون حرم Proximus. واستخدم استعلامًا واحدًا مع استعلام فرعي. والجواب: 49.0319

الحل

SELECT avg(distance_to_campus)
FROM student
WHERE campus_id = (
  SELECT id
  FROM campus
  WHERE name = 'Campus Proximus'
);

ربما فكرت عند قراءة العبارة أعلاه: 'أستطيع فعل ذلك بـ JOIN بالقدر نفسه؟' وهذا صحيح! حلّ الآن التمرين نفسه دون استعلام فرعي باستخدام JOIN فقط:

أعطِ متوسط المسافة إلى الحرم لجميع الطلاب الذين يرتادون حرم Proximus باستخدام JOIN.

الحل

SELECT avg(distance_to_campus)
FROM student S INNER JOIN campus C on S.campus_id = C.id
WHERE C.name = 'Campus Proximus';

الاختيار بين JOIN والاستعلام الفرعي

أحيانًا لا يكون لديك خيار وعليك استخدام استعلام فرعي، كما في التمرين الأول. فلا يمكنك حل ذلك بـ JOIN. وبالعكس، تحتاج بعض الاستعلامات إلى JOIN. لكن أحيانًا يكون لديك خيار بين استعلام فرعي وJOIN. ويصعب تحديد الأفضل بشكل قاطع. وبالنسبة إلى الاستعلامات المعقدة، كثيرًا ما يكون JOIN أكثر كفاءة. أما الاستعلام الفرعي فقراءته أسهل عادةً.

وفي كل الأحوال، ينبغي أن تكون قادرًا على استخدام نوعي الاستعلام. ومن الممكن أنه في استعلام معين في الامتحان سيتعيّن عليك استخدام أحدهما صراحةً.

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

نظرنا في المعامل IN بالفعل في قسم جملة WHERE. ويمكن استخدام نتيجة استعلام فرعي بعد IN. جرّب التمرين التالي.

اعرض أقدم خمسة طلاب يرتادون حرمًا في Heverlee. واعمل باستعلام فرعي.

الحل

SELECT first_name || ' ' || last_name AS name, birth_date
FROM student
WHERE campus_id IN (
  SELECT id
  FROM campus
  WHERE location = 'Heverlee'
)
ORDER BY birth_date
LIMIT 5;

ويمكن حل هذا التمرين حلًا ممتازًا بـ JOIN أيضًا. جرّب التمرين مرة أخرى بـ JOIN.

الحل

SELECT first_name || ' ' || last_name AS name, birth_date
FROM student S INNER JOIN campus C ON S.campus_id = C.id
WHERE location = 'Heverlee'
ORDER BY birth_date
LIMIT 5;

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

يقارن المعامل ALL قيمةً بـكل قيمة في جدول النتيجة. ونوضّح ذلك بالمثال التالي.

اسرد جميع الطلاب الأصغر من جميع الطلاب الذين يبدأ اسمهم الأول بـ 'Al'. ومرة أخرى، يمكنك حل ذلك باستعلامين. أولًا، استعلم عن تواريخ ميلاد جميع الطلاب بالاسم الأول المطلوب:

SELECT birth_date
FROM student
WHERE (first_name like 'Al%')
ORDER BY birth_date DESC;

وتبيّن أن أصغر هؤلاء الطلاب وُلد في 27 فبراير 2004. ويمكنك الآن الاستعلام في استعلام ثانٍ عن جميع الطلاب الذين تاريخ ميلادهم أكبر من (لأنهم يجب أن يكونوا أصغر!) هذا التاريخ:

SELECT *
FROM student
WHERE birth_date > '2004-02-27'
ORDER BY birth_date DESC;

وستجد في النهاية قائمة بـ 22 طالبًا.

ويمكنك دمج هذين الاستعلامين المتتاليين في استعلام واحد باستخدام استعلام فرعي. وستجد الطلاب الـ 22 أنفسهم:

SELECT *
FROM student
WHERE birth_date > ALL (
  SELECT birth_date
  FROM student
  WHERE first_name LIKE 'Al%'
)
ORDER BY birth_date DESC;

لا يبدو التمرين التالي صعبًا إلى هذا الحد من النظرة الأولى، لكن هناك مشكلة غير متوقعة قد تعاني منها. وتجد النصائح والحل في المكان المعتاد، لكن جرّب هذا التمرين بنفسك أولًا!

اسرد جميع الطلاب الذين يسكنون أقرب إلى حرمهم من جميع الطلاب الذين يرتادون حرمًا في Diest ويحتوي اسمهم الأخير على حرف 'a' مرتين (ويُسمح بالحروف الكبيرة أيضًا). ويجوز لك استخدام استعلام واحد فقط (قد يحتوي بالطبع على استعلامات فرعية). ورتّب حسب campus_id المتزايد والمسافة المتناقصة.

الحل

من الجيد حل تمرين كهذا على أجزاء. السؤال الفرعي 1: ما campus_id الخاص بالحرم (أو الحرمات) في Diest؟

SELECT id
FROM campus
WHERE location = 'Diest';

يبدو أنه لا يوجد سوى حرم واحد في Diest، وهو الحرم ذو id = 2. وبهذا يمكننا الآن الإجابة عن الجزء الثاني من السؤال: أعطِ جميع الطلاب من هذا الحرم الذين اسم عائلتهم فيه 'a' مرتان (لا تهم حالة الأحرف).

SELECT *
FROM student
WHERE lower(last_name) LIKE '%a%a%' AND campus_id = 2
ORDER BY distance_to_campus ASC;

تحقق من أن النتيجة لا تشمل فعلًا سوى الطلاب الذين يرتادون الحرم 2 ولقبهم فيه حرف 'a' مرتين على الأقل. وتبيّن أن أصغر مسافة 15 كم. وأخيرًا، يمكننا الإجابة عن الجزء الأخير من السؤال: جميع الطلاب من جدول الطلاب الذين يسكنون على مسافة أقل من 15 كم من حرمهم:

SELECT *
FROM student
WHERE distance_to_campus < 15
ORDER BY campus_id ASC, distance_to_campus DESC;

وتبيّن أن هذه قائمة بـ 106 طلاب. وتجميع كل شيء في استعلام واحد كبير فيه استعلامان فرعيان ليس صعبًا الآن:

SELECT *
from student
WHERE distance_to_campus < ALL (
  SELECT distance_to_campus
  FROM student
  WHERE lower(last_name) LIKE '%a%a%' AND campus_id IN (
    SELECT id
    FROM campus
    WHERE location = 'Diest'
  )
)
ORDER BY campus_id ASC, distance_to_campus DESC;

غير أن هذا الاستعلام لا يبدو أنه يعطي النتيجة الصحيحة. فالنتيجة لا تحتوي أي صفوف على الإطلاق! فما الذي يجري؟

المشكلة في السؤال الفرعي 2. فقائمة جميع الطلاب من الحرم 4 الذين في لقبهم 'a' مرتان تحتوي على عدد من القيم الفارغة في المسافة. وأي مقارنة بين عدد وقيمة فارغة تعطي دائمًا قيمة فارغة، كما يوضح هذا الاستعلام:

SELECT null > 999 -- result is not true or false but null
SELECT null <= 999 -- result is not true or false but null;

ونتيجة لذلك، سيفشل كل اختبار WHERE distance_to_campus < ALL ... دائمًا، فلا يبقى أي صف في النتيجة. ولحسن الحظ، الحل ليس بهذه الصعوبة: في السؤال الفرعي 2، تجنّب القيم الفارغة في النتيجة. لذا نضيف شرطًا واحدًا في الاستعلام الفرعي الأول:

SELECT *
from student
WHERE distance_to_campus < ALL (
  SELECT distance_to_campus
  FROM student
  WHERE lower(last_name) LIKE '%a%a%'
    AND distance_to_campus IS NOT null --Avoid null in the result
    AND campus_id IN (
      SELECT id
      FROM campus
      WHERE location = 'Diest'
    )
)
ORDER BY campus_id ASC, distance_to_campus DESC;

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

يقارن المعامل ANY قيمةً بأي قيمة في جدول، ويكون محققًا إذا انطبقت المقارنة على قيمة واحدة على الأقل في الجدول. ويجب أن يحتوي الجدول المقارن به على قيمة واحدة على الأقل. ويشرح المثال التالي المبدأ.

نريد قائمة بجميع الطلاب من الحرم 7 الأكبر من طالب واحد على الأقل من الحرم 4. وتعطي الشيفرة التالية قائمة بـ 135 طالبًا يحققون الشرط:

SELECT *
FROM student
WHERE campus_id = 7 AND birth_date < ANY(
  SELECT birth_date
  FROM student
  WHERE campus_id = 4
);

وكالعادة، من الجيد تحليل الاستعلام تحليلًا سليمًا لفهم كيفية عمل كل شيء. لنستعلم عن قائمة جميع الطلاب من الحرم 7:

SELECT *
FROM student
WHERE campus_id = 7;

وتحتوي تلك القائمة 136 طالبًا، أي واحدًا أكثر من الاستعلام الأول! ولنفحص بسرعة أين يكمن الفرق.

نبحث أولًا عن أصغر طالب من الحرم 4:

SELECT *
FROM student
WHERE campus_id = 4
ORDER BY 4 DESC;

وتبيّن أنه Lisa Vargas، المولودة في 30 سبتمبر 2004. غير أن في الحرم 7 طالبًا أصغر حتى من Lisa Vargas، وهو Kate Gonzales، المولودة في 12 أكتوبر 2004. وبالتالي فهي لا تحقق شرط ANY وبالتالي لا تظهر في النتيجة.