لغز جريمة SQL: شرح تفصيلي

وقعت جريمة قتل في مدينة SQL! صُمّم لغز جريمة SQL ليكون في الوقت نفسه درسًا ذاتيًا لتعلّم مفاهيم SQL وأوامرها، ولعبة ممتعة لمستخدمي SQL المخضرمين لحلّ جريمة مثيرة.

شرح تفصيلي للمبتدئين في SQL

إن كنت مرتاحًا مع SQL، فيمكنك تخطّي هذه الشروح واختبار مهاراتك! نقدّم فيما يلي بعض مفاهيم SQL الأساسية، وقدرًا من التفاصيل يكفي فقط لحلّ جريمة القتل. وإن كنت تريد مقدمة أكمل عن SQL، فجرّب Select Star SQL.

وقعت جريمة، والمحقق يحتاج إلى مساعدتك. أعطاك المحقق تقرير مسرح الجريمة، لكنك فقدته بطريقة ما. وتتذكر بضبابية أن الجريمة كانت جريمة قتل (murder) وقعت في وقت ما في 15 يناير 2018 وأنها حدثت في مدينة SQL. ابدأ باسترجاع تقرير مسرح الجريمة المقابل من قاعدة بيانات قسم الشرطة.

كل أدلة هذا اللغز مدفونة في قاعدة بيانات ضخمة، وتحتاج إلى استخدام SQL للتنقل عبر هذه الشبكة الواسعة من المعلومات. وخطوتك الأولى لحلّ اللغز هي استرجاع تقرير مسرح الجريمة المقابل من قاعدة بيانات قسم الشرطة. وسنشرح فيما يلي، على مستوى عالٍ، الأوامر التي تحتاج إلى معرفتها؛ ومتى كنت مستعدًا، يمكنك البدء بتكييف الأمثلة لإنشاء أوامر SQL خاصة بك بحثًا عن الأدلة -- ويمكنك تشغيل أي SQL في أي من صناديق الشيفرة، أيًا كان ما كان في الصندوق عند بدئك.

بعض التعريفات

ما هي SQL؟

SQL، وهي اختصار Structured Query Language (لغة الاستعلامات البنيوية)، طريقة للتفاعل مع قواعد البيانات العلائقية والجداول بما يتيح لنا نحن البشر استخلاص معلومات محددة ذات معنى.

مهلاً، ما هي قاعدة البيانات العلائقية؟

لا يوجد تعريف واحد لكلمة قاعدة بيانات. وبوجه عام، قواعد البيانات أنظمة لإدارة المعلومات. ويمكن أن تُفرض على البيانات درجات متفاوتة من البنية. وعندما تكون البيانات أكثر بنية، يساعد ذلك الناس والحواسيب على العمل بها بكفاءة أكبر.

قواعد البيانات العلائقية هي على الأرجح النوع الأشهر من قواعد البيانات. وهي تتكوّن في جوهرها من جداول تشبه إلى حد كبير جداول البيانات. ولكل عمود (column) في الجدول اسم ونوع بيانات (نص، رقم، إلخ)، وكل صف (row) في الجدول حالة محددة لما "يدور حوله" الجدول. أما الجزء "العلائقي" فيأتي بقواعد محددة لكيفية ربط البيانات بين الجداول المختلفة.

ما هو ERD؟

ERD، وهي اختصار Entity Relationship Diagram (مخطط الكيانات والعلاقات)، تمثيل بصري للعلاقات بين كل الجداول ذات الصلة داخل قاعدة البيانات. وستجد أدناه مخطط ERD لقاعدة بيانات لغز جريمة SQL. ويوضح المخطط أن لكل جدول اسمًا (في أعلى المربع، بخط عريض)، وقائمة بأسماء الأعمدة (على اليسار) وأنواع بياناتها المقابلة (على اليمين، بأحرف كبيرة كلها). وهناك أيضًا بعض رموز المفاتيح الذهبية، ورموز الأسهم الزرقاء، والأسهم الرمادية في مخطط ERD. ويشير المفتاح الذهبي إلى أن العمود هو المفتاح الأساسي (primary key) للجدول المقابل، ويشير السهم الأزرق إلى أن العمود هو المفتاح الأجنبي (foreign key) للجدول المقابل. المفتاح الأساسي: معرّف فريد لكل صف في الجدول. المفتاح الأجنبي: يُستخدم للإشارة إلى بيانات في جدول من جدول آخر. وإذا كان جدولان مترابطين، فإن العمودين المتطابقين، أي المعرّفين المشتركين في الجدولين، يتصلان بسهم رمادي في المخطط.

وهذا هو مخطط ERD لقاعدة بياناتنا:

ما هو الاستعلام (query)؟

لو نظرت إلى البيانات في قاعدة البيانات هذه، لرأيت أن الجداول ضخمة! وهناك نقاط بيانات كثيرة جدًا؛ فليس من الممكن ببساطة المرور على الجداول صفًا صفًا للعثور على المعلومات التي نحتاجها. فماذا عسانا نفعل؟

هنا يأتي دور الاستعلامات. فالاستعلامات عبارات ننشئها للحصول على بيانات من قاعدة البيانات. والاستعلامات تُقرأ مثل الإنجليزية الطبيعية (في معظمها). لنجرّب بضعة استعلامات على قاعدة بياناتنا. وفي كل صندوق من الصناديق أدناه، انقر "run" لـ"تنفيذ" الاستعلام الموجود في الصندوق. ويمكنك تعديل الاستعلامات هنا في الصفحة نفسها لاستكشاف المزيد. (لاحظ أن أوامر SQL ليست حساسة لحالة الأحرف، لكن من المألوف كتابتها بأحرف كبيرة لتحسين القراءة. ويمكنك أيضًا استخدام أسطر جديدة ومسافات بيضاء كما تشاء لتنسيق الأمر للقراءة. وتطلب معظم أنظمة قواعد البيانات إنهاء الاستعلام بفاصلة منقوطة (';')، وإن كان النظام المستخدم لتشغيلها في صفحة الويب هذه أكثر تسامحًا.)

ما العناصر التي يتكوّن منها استعلام SQL؟

قد يحتوي استعلام SQL على: كلمات SQL المفتاحية (مثل SELECT وFROM أعلاه) أسماء الأعمدة (مثل العمود name أعلاه) أسماء الجداول (مثل الجدول person أعلاه) محارف بديلة (مثل %) دوال معايير تصفية محددة إلخ

كلمات SQL المفتاحية

تُستخدم كلمات SQL المفتاحية لتحديد الإجراءات في استعلاماتك. وهي ليست حساسة لحالة الأحرف، لكننا نقترح كتابتها كلها بأحرف كبيرة ليسهل تمييزها عن بقية الاستعلام. ومن الكلمات المفتاحية الشائعة:

SELECT

يتيح لنا SELECT جلب بيانات أعمدة محددة من قاعدة البيانات:

• النجمة * (asterisk): تُستخدم بعد SELECT لجلب كل الأعمدة من الجدول؛
• column_name(s): لتحديد أعمدة معيّنة، ضع أسماء الأعمدة بعد SELECT وافصل بينها بفواصل.

FROM

يتيح لنا FROM تحديد الجدول أو الجداول التي تهمنا؛ ولاختيار عدة جداول، اذكر أسماء الجداول وافصل بينها بفواصل. (لكن قبل أن تتعلم الكلمة المفتاحية JOIN، قد يفاجئك ما سيحدث. وسيأتي ذلك لاحقًا.)

WHERE

تُستخدم عبارة WHERE في الاستعلام لتصفية النتائج وفق معايير محددة.

لنجرّب بعض هذه الأشياء.

تُستخدم الكلمة المفتاحية AND لربط عدة معايير تصفية معًا بحيث تحقق النتائج المصفّاة كل معيار من المعايير. (وهناك أيضًا الكلمة المفتاحية OR، التي تُعيد الصفوف التي تطابق أيًّا من المعايير.)

إن لم تكن قد وجدت تقرير مسرح الجريمة الصحيح بعد، فانقر "show solution" أعلاه، واستبدل محتوى الصندوق بالأمر الظاهر وحده. (واترك /* في البداية.) وإن كنت قد اكتشفت الاستعلام الذي يعرض تقرير مسرح الجريمة الواحد بدلًا من بضعة تقارير للمدينة والنوع نفسهما، فتهانينا وتجاهل كلمة "incorrect". وستعرف إن كنت قد أصبت!

المحارف البديلة (Wildcards) ودوال أخرى للمطابقات الجزئية

أحيانًا لا تعرف إلا جزءًا من المعلومات التي تحتاجها. وتستطيع SQL التعامل مع ذلك. فالرموز الخاصة التي تمثّل محارف مجهولة تسمى "محارف بديلة" (wildcards)، وتدعم SQL اثنين منها. وأكثرها شيوعًا المحرف البديل %.

عندما تضع محرفًا بديلًا % في نص استعلام، سيُعيد نظام SQL نتائج تطابق بقية النص مطابقة تامة، ويكون فيها أي شيء (أو لا شيء) في موضع المحرف البديل. على سبيل المثال، 'Ca%a' تطابق Canada وCalifornia.

والمحرف البديل الآخر، الأقل استخدامًا، هو _. وهو يعني "طابِق بقية النص، بشرط وجود محرف واحد بالضبط في موضع _ نفسه، أيًّا كان. لذا فإن 'B_b' ستطابق 'Bob' و'Bub' لكن ليس 'Babe' ولا 'Bb'.

مهم: عند استخدام المحارف البديلة، لا تستخدم الرمز =؛ بل تستخدم LIKE.

تدعم SQL أيضًا المقارنات العددية مثل < (أصغر من) و> (أكبر من). ويمكنك أيضًا استخدام الكلمتين المفتاحيتين BETWEEN وAND -- وكلها تعمل مع الكلمات كما تعمل مع الأرقام.

ذكرنا أن أوامر SQL ليست حساسة لحالة الأحرف، لكن قيم الاستعلام في WHERE مع = وLIKE حساسة لها. وأحيانًا لا تعرف كيف خُزّن النص في قاعدة البيانات. وتوفّر SQL دالتين يمكنهما تذليل ذلك لك. وهما UPPER() وLOWER()، والأرجح أنك ستدرك ما تفعلانه، خصوصًا إن استكشفت في الصندوق أدناه.

التعمّق أكثر

دوال SQL التجميعية (Aggregate Functions)

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

ما عمر أكبر شخص يحمل رخصة قيادة؟ مع قدر صغير من البيانات، قد تستطيع الحكم بالنظر، لكن في جدول drivers_license آلاف السجلات. (جرّب COUNT إن أردت معرفة عددها بالضبط!) ولا يمكنك مجرد إمرار النظر على تلك القائمة لإيجاد الجواب.

وهذه بعض الدوال التجميعية المفيدة التي توفّرها SQL: MAX يجد القيمة العظمى؛ وMIN يجد القيمة الصغرى؛ وSUM يحسب مجموع قيم العمود المحددة؛ وAVG يحسب متوسط قيم العمود المحددة؛ وCOUNT يعُدّ عدد قيم العمود المحددة.

وهناك طريقة أخرى لإيجاد القيم الصغرى والعظمى، مع رؤية مزيد من البيانات في الوقت نفسه. يمكنك التحكم في ترتيب النتائج التي تحصل عليها. والأمر بديهي فعلًا: استخدم فقط ORDER BY متبوعة باسم عمود. وقد يكون ذلك صعبًا عندما تكون البيانات كثيرة! (حين يأخذ الناس العمل بـ SQL على محمل الجدية، يستخدمون أدوات أفضل من هذا النظام القائم على الويب.) ويسير ORDER BY افتراضيًا بترتيب "تصاعدي" (من الأصغر إلى الأكبر، أو من A إلى Z)، لكن يمكنك التحديد بـ ASC للترتيب التصاعدي، أو عكسه بـ DESC.

والآن تعرف من SQL ما يكفي لتحديد الشاهدين. جرّب ذلك!

إنشاء الروابط

ربط الجداول

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

يستخدم المخضرمون في SQL بضعة أنواع مختلفة من JOIN -- وقد تسمع عن الربط INNER وOUTER وLEFT وRIGHT. وهنا سنتحدث فقط عن أكثر أنواع JOIN شيوعًا، وهو INNER JOIN. ولأنه شائع، يمكنك حذف INNER في أوامر SQL.

وأكثر طرق ربط الجداول شيوعًا استخدام أعمدة المفتاح الأساسي والمفتاح الأجنبي. عُد إلى مخطط الكيانات والعلاقات (ERD) أعلاه إن لم تتذكر ما هما، أو لترى علاقات المفاتيح بين الجداول في قاعدة بياناتنا. ويمكنك إجراء عمليات الربط على أي أعمدة، لكن أعمدة المفاتيح مُحسّنة للحصول على نتائج سريعة. والأرجح أن عرض كيفية عمل الربط في نظام SQL التفاعلي أسهل من كتابته.

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

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

هيّا إلى العمل!

الآن تعرف من SQL ما يكفي لحلّ اللغز. وستحتاج إلى قراءة مخطط ERD ووضع بعض الافتراضات المعقولة، لكنك لا تحتاج إلى أي صياغة أخرى!

تحقّق من حلّك

الإسناد

أنشأ لغز جريمة SQL كل من Joon Park وCathy He عندما كانا زميلين في Knight Lab. راجع مستودع GitHub لمزيد من المعلومات.

تكفّل Joe Germuska بتكييفه وإنتاجه للويب.

استُلهم هذا اللغز من جريمة في مدينة Terminal المجاورة.

يعمل SQL في المتصفح بفضل SQL.js

أنشأ مكوّنات الويب المخصّصة لاستعلامات SQL وأتاحها للملكية العامة Zi Chong Kao، مبتكر Select Star SQL.

رسم المحقق مقدَّم من Vectors by Vecteezy

الشيفرة الأصلية لهذا المشروع منشورة تحت رخصة MIT

النص الأصلي والمحتوى الآخر لهذا المشروع منشوران تحت Creative Commons CC BY-SA 4.0