8. تمارين SQL البسيطة

باستخدام الجدول nyc_census_blocks، أجب عن الأسئلة التالية (ولا تختلس النظر إلى الإجابات!).

إليك بعض المعلومات المفيدة للبدء. تذكّر تعريف جدولنا nyc_census_blocks من قسم حول بياناتنا.

blkid	رمز من 15 رقمًا يعرّف كل كتلة إحصائية (block) تعريفًا فريدًا. ("360050001009000")
popn_total	إجمالي عدد السكان في الكتلة الإحصائية
popn_white	عدد الأشخاص الذين يعرّفون أنفسهم بأنهم "بيض" في الكتلة
popn_black	عدد الأشخاص الذين يعرّفون أنفسهم بأنهم "سود" في الكتلة
popn_nativ	عدد الأشخاص الذين يعرّفون أنفسهم بأنهم "أمريكيون أصليون" في الكتلة
popn_asian	عدد الأشخاص الذين يعرّفون أنفسهم بأنهم "آسيويون" في الكتلة
popn_other	عدد الأشخاص الذين يعرّفون أنفسهم ضمن فئات أخرى في الكتلة
hous_total	عدد الوحدات السكنية في الكتلة
hous_own	عدد الوحدات السكنية التي يشغلها مالكوها في الكتلة
hous_rent	عدد الوحدات السكنية التي يشغلها مستأجرون في الكتلة
boroname	اسم مقاطعة نيويورك: Manhattan، The Bronx، Brooklyn، Staten Island، Queens
geom	حدود الكتلة على هيئة مضلع

وإليك بعض دوال التجميع الشائعة في SQL التي قد تجدها مفيدة:

• avg() - المتوسط (الوسط الحسابي) للقيم في مجموعة من السجلات

• sum() - مجموع القيم في مجموعة من السجلات

• count() - عدد السجلات في مجموعة من السجلات

والآن الأسئلة:

• كم عدد السجلات في الجدول nyc_streets؟

  SELECT Count(*)
  FROM nyc_streets;
  

  19091
  

• كم عدد الشوارع في مدينة نيويورك التي تبدأ بالحرف ‘B’؟

  SELECT Count(*)
    FROM nyc_streets
    WHERE name LIKE 'B%';
  

  1282
  

• ما عدد سكان مدينة نيويورك؟

  SELECT Sum(popn_total) AS population
    FROM nyc_census_blocks;
  

  8175032
  

  ملاحظة

  ما هذا الـ AS؟ يمكنك إعطاء جدول أو عمود اسمًا آخر باستخدام اسم مستعار (alias). والأسماء المستعارة تجعل الاستعلامات أسهل كتابةً وقراءةً. فبدلًا من أن يكون اسم العمود الناتج sum نكتبه AS الاسم الأوضح population.

• ما عدد سكان Bronx؟

  SELECT Sum(popn_total) AS population
    FROM nyc_census_blocks
    WHERE boroname = 'The Bronx';
  

  1385108
  

• كم عدد "الأحياء" في كل مقاطعة؟

  SELECT boroname, count(*)
    FROM nyc_neighborhoods
    GROUP BY boroname;
  

     boroname    | count
  ---------------+-------
   Queens        |    30
   Brooklyn      |    23
   Staten Island |    24
   The Bronx     |    24
   Manhattan     |    28
  

• ما نسبة السكان البيض في كل مقاطعة؟

  SELECT
    boroname,
    100.0 * Sum(popn_white)/Sum(popn_total) AS white_pct
  FROM nyc_census_blocks
  GROUP BY boroname;
  

     boroname    |    white_pct
  ---------------+------------------
   Brooklyn      | 42.8011737932687
   Manhattan     | 57.4493039480463
   The Bronx     | 27.9037446899448
   Queens        |  39.722077394591
   Staten Island | 72.8942034860154
  

8.1. قائمة الدوال

avg(expression): دالة تجميعية في PostgreSQL تعيد القيمة المتوسطة لعمود رقمي.

count(expression): دالة تجميعية في PostgreSQL تعيد عدد السجلات في مجموعة من السجلات.

sum(expression): دالة تجميعية في PostgreSQL تعيد مجموع السجلات في مجموعة من السجلات.