استعلامات SQL بسيطة

تتناول هذه الفئة أساسيات SQL. وتغطي عبارتي select وwhere، وتعبيرات case، وعمليات union، وبعض الأمور الأخرى المتنوعة. وإن كنت قد تعلمت SQL من قبل، فستجد هذه التمارين سهلة على الأرجح. وإن لم تكن قد تعلمتها، فستجد فيها نقطة انطلاق جيدة نحو الفئات الأكثر صعوبة المقبلة!

إن واجهت صعوبة في هذه الأسئلة، فأنصح بشدة بكتاب Learning SQL لـ Alan Beaulieu، فهو كتاب موجز وجيد الكتابة في الموضوع. وإن كنت مهتمًا بأساسيات نظم قواعد البيانات (لا بمجرد كيفية استخدامها)، فابحث أيضًا في كتاب An Introduction to Database Systems لـ C.J. Date.

1. استرجاع كل شيء من جدول

السؤال

كيف يمكنك استرجاع كل المعلومات من جدول cd.facilities؟

النتائج المتوقعة

facid	name	membercost	guestcost	initialoutlay	monthlymaintenance
0	Tennis Court 1	5	25	10000	200
1	Tennis Court 2	5	25	8000	200
2	Badminton Court	0	15.5	4000	50
3	Table Tennis	0	5	320	10
4	Massage Room 1	35	80	4000	3000
5	Massage Room 2	35	80	4000	3000
6	Squash Court	3.5	17.5	5000	80
7	Snooker Table	0	5	450	15
8	Pool Table	0	5	400	15

الإجابة

select * from cd.facilities;

عبارة SELECT هي الكتلة الأساسية التي تبدأ بها الاستعلامات (queries) التي تقرأ المعلومات من قاعدة البيانات. وتتكوّن عبارة select البسيطة عمومًا من select [مجموعة من الأعمدة (columns)] from [جدول (table) أو مجموعة جداول]. وفي هذه الحالة، نريد كل المعلومات من جدول facilities. قسم from سهل — كل ما نحتاج إليه هو تحديد جدول cd.facilities. و'cd' هو مخطط (schema) الجدول — وهو مصطلح يُستخدم لتجميع منطقي لمعلومات مترابطة في قاعدة البيانات. بعد ذلك، نحتاج إلى تحديد أننا نريد كل الأعمدة. ومن الملائم أن هناك اختصارًا يعني "كل الأعمدة" هو *. ويمكننا استخدامه بدلًا من تعداد كل أسماء الأعمدة بمشقة.

تلميح: يمكن استخدام select * لاسترجاع كل الأعمدة من جدول.

2. استرجاع أعمدة محددة من جدول

السؤال

تريد طباعة قائمة بكل المرافق وتكلفتها للأعضاء. فكيف تسترجع قائمة تضم أسماء المرافق والتكاليف فقط؟

النتائج المتوقعة

name	membercost
Tennis Court 1	5
Tennis Court 2	5
Badminton Court	0
Table Tennis	0
Massage Room 1	35
Massage Room 2	35
Squash Court	3.5
Snooker Table	0
Pool Table	0

الإجابة

select name, membercost from cd.facilities;

في هذا السؤال، نحتاج إلى تحديد الأعمدة التي نريدها. ويمكننا فعل ذلك بقائمة بسيطة من أسماء الأعمدة مفصولة بفواصل نحددها لعبارة select. وكل ما تفعله قاعدة البيانات هو النظر في الأعمدة المتاحة في عبارة FROM، وإعادة الأعمدة التي طلبناها، كما هو موضح أدناه

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

تلميح: تتيح لك عبارة select تحديد أسماء الأعمدة التي تريد استرجاعها.

3. التحكم في الصفوف المسترجعة

السؤال

كيف يمكنك إنتاج قائمة بالمرافق التي تتقاضى رسمًا من الأعضاء؟

النتائج المتوقعة

facid	name	membercost	guestcost	initialoutlay	monthlymaintenance
0	Tennis Court 1	5	25	10000	200
1	Tennis Court 2	5	25	8000	200
4	Massage Room 1	35	80	4000	3000
5	Massage Room 2	35	80	4000	3000
6	Squash Court	3.5	17.5	5000	80

الإجابة

select * from cd.facilities where membercost > 0;

تُستخدم عبارة FROM لبناء مجموعة من الصفوف (rows) المرشّحة التي نقرأ منها النتائج. وفي أمثلتنا حتى الآن، كانت هذه المجموعة من الصفوف مجرد محتوى جدول. وسنستكشف لاحقًا الربط، الذي يتيح لنا إنشاء مرشّحين أكثر إثارة للاهتمام. وبعد أن نبني مجموعة الصفوف المرشّحة، تتيح لنا عبارة WHERE تصفية الصفوف التي تهمنا — في هذه الحالة، الصفوف التي تكون فيها قيمة membercost أكبر من صفر. وكما سترى في تمارين لاحقة، يمكن أن تحتوي عبارات WHERE على مكونات متعددة تُدمج بالمنطق البولياني (boolean) — فيمكن، مثلًا، البحث عن المرافق التي تكون تكلفتها أكبر من 0 وأصغر من 10. ويوضح ما يلي فعل التصفية الذي تجريه عبارة WHERE على جدول facilities:

تلميح: تتيح لك عبارة WHERE تصفية الصفوف التي تريد استرجاعها.

4. التحكم في الصفوف المسترجعة — الجزء 2

السؤال

كيف يمكنك إنتاج قائمة بالمرافق التي تتقاضى رسمًا من الأعضاء، ويكون هذا الرسم أقل من 1/50 من تكلفة الصيانة الشهرية؟ أرجع facid واسم المرفق وتكلفة الأعضاء والصيانة الشهرية للمرافق المعنية.

النتائج المتوقعة

facid	name	membercost	monthlymaintenance
4	Massage Room 1	35	3000
5	Massage Room 2	35	3000

الإجابة

select facid, name, membercost, monthlymaintenance 
	from cd.facilities 
	where 
		membercost > 0 and 
		(membercost < monthlymaintenance/50.0);

تتيح لنا عبارة WHERE تصفية الصفوف التي تهمنا — في هذه الحالة، الصفوف التي تكون فيها قيمة membercost أكبر من صفر وأقل من 1/50 من تكلفة الصيانة الشهرية. وكما ترى، فإن تشغيل غرف التدليك مكلف جدًا بسبب تكاليف الموظفين!

وعندما نريد اختبار شرطين أو أكثر، نستخدم AND لدمجها. ويمكننا، كما قد تتوقع، استخدام OR لاختبار ما إذا كان أحد الشرطين صحيحًا. وقد لاحظت أن هذا أول استعلام لدينا يجمع بين عبارة WHERE واختيار أعمدة محددة. ويمكنك أن ترى في الصورة أدناه أثر ذلك: تقاطع الأعمدة المحددة والصفوف المحددة يعطينا البيانات التي سنعيدها. وقد لا يبدو هذا مثيرًا للاهتمام الآن، لكنك سترى أناقته البسيطة حين نضيف عمليات أكثر تعقيدًا مثل الربط لاحقًا.

تلميح: تتيح لك عبارة WHERE تصفية الصفوف التي تريد استرجاعها.

5. عمليات بحث نصية أساسية

السؤال

كيف يمكنك إنتاج قائمة بكل المرافق التي تحتوي أسماؤها على كلمة 'Tennis'؟

النتائج المتوقعة

facid	name	membercost	guestcost	initialoutlay	monthlymaintenance
0	Tennis Court 1	5	25	10000	200
1	Tennis Court 2	5	25	8000	200
3	Table Tennis	0	5	320	10

الإجابة

select *
	from cd.facilities 
	where 
		name like '%Tennis%';

توفّر عبارة LIKE في SQL مطابقة أنماط (pattern matching) بسيطة على النصوص. وهي مُنفَّذة على نحو شبه شامل، وهي بسيطة وجميلة الاستخدام — فهي تأخذ نصًا يكون فيه المحرف % مطابقًا لأي نص، و_ مطابقًا لأي محرف واحد. وفي هذه الحالة، نبحث عن أسماء تحتوي كلمة 'Tennis'، لذا فإن وضع % على الجانبين يفي بالغرض.

وهناك طرق أخرى لإنجاز هذه المهمة: فيدعم Postgres التعبيرات النمطية (regular expressions) بالمُعامل ~، على سبيل المثال. استخدم ما يريحك، لكن اعلم أن مُعامل LIKE أكثر قابلية للنقل بين الأنظمة بكثير.

تلميح: جرّب البحث عن مُعامل LIKE في SQL.

6. المطابقة مع قيم محتملة متعددة

السؤال

كيف يمكنك استرجاع تفاصيل المرفقين ذوي المعرّفين 1 و5؟ حاول فعل ذلك دون استخدام المُعامل OR.

النتائج المتوقعة

facid	name	membercost	guestcost	initialoutlay	monthlymaintenance
1	Tennis Court 2	5	25	8000	200
5	Massage Room 2	35	80	4000	3000

الإجابة

select *
	from cd.facilities 
	where 
		facid in (1,5);

الجواب البديهي لهذا السؤال هو استخدام عبارة WHERE على صورة where facid = 1 or facid = 5. والبديل الأسهل عند وجود أعداد كبيرة من المطابقات المحتملة هو مُعامل IN. يأخذ مُعامل IN قائمة بالقيم المحتملة، ويطابقها مع (في هذه الحالة) facid. وإن تطابقت إحدى القيم، كانت عبارة where صحيحة لذلك الصف، وأُعيد الصف. ومُعامل IN برهان مبكّر جيد على أناقة النموذج العلائقي. فالمعامل الذي يأخذه ليس مجرد قائمة قيم — بل هو في الحقيقة جدول بعمود واحد. ولأن الاستعلامات تُعيد جداول أيضًا، فإنك إن أنشأت استعلامًا يُعيد عمودًا واحدًا، أمكنك تمرير نتائجه إلى مُعامل IN. ولإعطاء مثال مبسّط:

select * 
	from cd.facilities
	where
		facid in (
			select facid from cd.facilities
			);

هذا المثال مكافئ وظيفيًا لاختيار كل المرافق فقط، لكنه يوضح لك كيفية تمرير نتائج استعلام إلى آخر. ويسمى الاستعلام الداخلي استعلامًا فرعيًا (subquery).

تلميح: جرّب البحث عن مُعامل IN في SQL.

7. تصنيف النتائج في فئات

السؤال

كيف يمكنك إنتاج قائمة بالمرافق، بحيث يُوصَف كل مرفق بأنه 'cheap' أو 'expensive' بحسب ما إذا كانت تكلفة صيانته الشهرية تزيد على 100 دولار؟ أرجع اسم المرفق وصيانته الشهرية للمرافق المعنية.

النتائج المتوقعة

name	cost
Tennis Court 1	expensive
Tennis Court 2	expensive
Badminton Court	cheap
Table Tennis	cheap
Massage Room 1	expensive
Massage Room 2	expensive
Squash Court	cheap
Snooker Table	cheap
Pool Table	cheap

الإجابة

select name, 
	case when (monthlymaintenance > 100) then
		'expensive'
	else
		'cheap'
	end as cost
	from cd.facilities;

يحتوي هذا التمرين على بضعة مفاهيم جديدة. الأول هو أننا نجري حسابًا في المنطقة الواقعة بين SELECT وFROM في الاستعلام. وقد استخدمناها سابقًا فقط لاختيار الأعمدة التي نريد إعادتها، لكن يمكنك وضع أي شيء فيها ينتج نتيجة واحدة لكل صف مُعاد — بما في ذلك الاستعلامات الفرعية. والمفهوم الجديد الثاني هو عبارة CASE نفسها. وCASE تشبه فعليًا عبارات if/switch في لغات أخرى، بصورة كما هي مبينة في الاستعلام. ولإضافة خيار "متوسط"، نُدخل ببساطة قسم when...then آخر. وأخيرًا، هناك مُعامل AS. وهو يُستخدم ببساطة لوسم الأعمدة أو التعبيرات باسم مستعار (alias)، لجعل عرضها أجمل أو لتسهيل الإشارة إليها عند استخدامها كجزء من استعلام فرعي.

تلميح: جرّب البحث عن عبارة CASE في SQL.

8. التعامل مع التواريخ

السؤال

كيف يمكنك إنتاج قائمة بالأعضاء الذين انضموا بعد بداية سبتمبر 2012؟ أرجع memid وsurname وfirstname وjoindate للأعضاء المعنيين.

النتائج المتوقعة

memid	surname	firstname	joindate
24	Sarwin	Ramnaresh	2012-09-01 08:44:42
26	Jones	Douglas	2012-09-02 18:43:05
27	Rumney	Henrietta	2012-09-05 08:42:35
28	Farrell	David	2012-09-15 08:22:05
29	Worthington-Smyth	Henry	2012-09-17 12:27:15
30	Purview	Millicent	2012-09-18 19:04:01
33	Tupperware	Hyacinth	2012-09-18 19:32:05
35	Hunt	John	2012-09-19 11:32:45
36	Crumpet	Erica	2012-09-22 08:36:38
37	Smith	Darren	2012-09-26 18:08:45

الإجابة

select memid, surname, firstname, joindate 
	from cd.members
	where joindate >= '2012-09-01';

هذه أول إطلالة لنا على الطوابع الزمنية (timestamps) في SQL. وهي منسّقة بترتيب تنازلي للمقدار: YYYY-MM-DD HH:MM:SS.nnnnnn. ويمكننا مقارنتها كما نقارن طابعًا زمنيًا يونيكسيًا، وإن كان الحصول على الفروق بين التواريخ أكثر تعقيدًا قليلًا (وأقوى!). وفي هذه الحالة، حدّدنا جزء التاريخ من الطابع الزمني فقط. فيحوّله postgres تلقائيًا إلى الطابع الزمني الكامل 2012-09-01 00:00:00.

تلميح: ابحث عن صيغة الطابع الزمني (timestamp) في SQL، وتذكّر أنك تستطيع مقارنة التواريخ كثيرًا كما تقارن قيمًا صحيحة.

9. إزالة التكرار وترتيب النتائج

السؤال

كيف يمكنك إنتاج قائمة مرتّبة بأول 10 أسماء عائلية في جدول members؟ ويجب ألا تحتوي القائمة على تكرارات.

النتائج المتوقعة

surname
Bader
Baker
Boothe
Butters
Coplin
Crumpet
Dare
Farrell
GUEST
Genting

الإجابة

select distinct surname 
	from cd.members
order by surname
limit 10;

هناك ثلاثة مفاهيم جديدة هنا، لكنها كلها بسيطة إلى حد كبير.

تلميح: ابحث عن كلمات SQL المفتاحية DISTINCT وORDER BY وLIMIT.

10. دمج نتائج استعلامات متعددة

السؤال

تريد، لسبب ما، قائمة مدموجة تضم كل الأسماء العائلية وكل أسماء المرافق. نعم، هذا مثال متكلّف :-). أنتج تلك القائمة!

النتائج المتوقعة

surname
Tennis Court 2
Worthington-Smyth
Badminton Court
Pinker
Dare
Bader
Mackenzie
Crumpet
Massage Room 1
Squash Court
Tracy
Hunt
Tupperware
Smith
Butters
Rownam
Baker
Genting
Purview
Coplin
Massage Room 2
Joplette
Stibbons
Rumney
Pool Table
Sarwin
Boothe
Farrell
Tennis Court 1
Snooker Table
Owen
Table Tennis
GUEST
Jones

الإجابة

select surname 
	from cd.members
union
select name
	from cd.facilities;

يفعل مُعامل UNION ما قد تتوقعه: فهو يدمج نتائج استعلامَي SQL في جدول واحد. والتحفظ هو أنه يجب أن يكون لنتيجتي الاستعلامين العدد نفسه من الأعمدة وأنواع بيانات متوافقة. ويزيل UNION الصفوف المكررة، بخلاف UNION ALL. استخدم UNION ALL افتراضيًا، إلا إذا كانت النتائج المكررة تهمك.

تلميح: ابحث عن كلمة SQL المفتاحية UNION

11. تجميع بسيط

السؤال

تريد الحصول على تاريخ تسجيل آخر عضو لديك. فكيف تسترجع هذه المعلومة؟

النتائج المتوقعة

latest
2012-09-26 18:08:45

الإجابة

select max(joindate) as latest
	from cd.members;

هذه أول جولة لنا في الدوال التجميعية (aggregate functions) في SQL. وهي تُستخدم لاستخراج معلومات عن مجموعات كاملة من الصفوف، وتتيح لنا طرح أسئلة مثل:

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

تلميح: ابحث عن الدالة التجميعية MAX في SQL

12. مزيد من التجميع

السؤال

تريد الحصول على الاسم الأول والاسم العائلي لآخر عضو (أو أعضاء) سجّلوا — لا على التاريخ فقط. فكيف تفعل ذلك؟

النتائج المتوقعة

firstname	surname	joindate
Darren	Smith	2012-09-26 18:08:45

الإجابة

select firstname, surname, joindate
	from cd.members
	where joindate = 
		(select max(joindate) 
			from cd.members);

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

select firstname, surname, max(joindate)
        from cd.members

للأسف، هذا لا يعمل. فدالة MAX لا تقيّد الصفوف كما تفعل عبارة WHERE — إنها ببساطة تستقبل مجموعة من القيم وتُعيد أكبرها. فتبقى قاعدة البيانات حائرة في كيفية إقران قائمة طويلة من الأسماء بتاريخ الانضمام الواحد الذي خرج من دالة max، وتفشل. وبدلًا من ذلك، يبقى عليك القول 'أرني الصف أو الصفوف التي يكون تاريخ انضمامها مماثلًا لتاريخ الانضمام الأقصى'.

وكما ذُكر في التلميح، هناك طرق أخرى لإنجاز هذه المهمة — ومنها المثال التالي. في هذه المقاربة، بدلًا من إيجاد تاريخ آخر انضمام صراحةً، نرتّب جدول members تنازليًا بحسب تاريخ الانضمام، ونلتقط الأول. ولاحظ أن هذه المقاربة لا تغطي الاحتمال النادر جدًا بأن ينضم شخصان في الوقت نفسه بالضبط :-).

select firstname, surname, joindate
	from cd.members
order by joindate desc
limit 1;

تلميح: قد تجد أنك تحتاج إلى استعلام فرعي لإنجاز ذلك — وإن كانت هناك طرق أخرى!