الاستعلامات المتقدمة في SQL: JOIN والاستعلامات الفرعية والتوابع الإحصائية — الثالثة ثانوي (بكالوريا) إعلام آلي — شعبة تقني رياضي — المنهاج الجزائري

📝 مثال: جدولان (Étudiants و Notes)

-- جدول الطلاب
CREATE TABLE Etudiants (
  NumEt INTEGER PRIMARY KEY,
  Nom VARCHAR(30),
  Classe VARCHAR(20)
);

-- جدول العلامات
CREATE TABLE Notes (
  NumNote INTEGER PRIMARY KEY,
  NumEt INTEGER,
  Matiere VARCHAR(30),
  Note REAL,
  FOREIGN KEY (NumEt) REFERENCES Etudiants(NumEt)
);

-- الاستعلام: اسم الطالب وعلامته في الرياضيات
SELECT E.Nom, N.Note
FROM Etudiants E
INNER JOIN Notes N ON E.NumEt = N.NumEt
WHERE N.Matiere = 'Mathématiques';

شرح: استخدمنا INNER JOIN لربط الجدولين عبر NumEt. الاختصار E و N هما أسماء مستعارة (Alias) للجدولين لتسهيل الكتابة. الشرط ON E.NumEt = N.NumEt يحدد كيفية الربط.

📝 مثال: عرض كل الطلاب وعلاماتهم (حتى من لا علامة له)

SELECT E.Nom, N.Matiere, N.Note
FROM Etudiants E
LEFT JOIN Notes N ON E.NumEt = N.NumEt;

إذا كان هناك طالب لم يسجل له أي علامة بعد، سيظهر اسمه مع NULL في الخانتين الأخريين.

📝 مثال: ثلاثة جداول — الطلاب، المواد، العلامات

CREATE TABLE Matieres (
  CodeMat VARCHAR(10) PRIMARY KEY,
  Libelle VARCHAR(50)
);

-- إضافة مفتاح أجنبي للمادة في جدول Notes
-- (يفترض أن Notes.Matiere أصبح CodeMat)

SELECT E.Nom, M.Libelle, N.Note
FROM Etudiants E
INNER JOIN Notes N ON E.NumEt = N.NumEt
INNER JOIN Matieres M ON N.Matiere = M.CodeMat
ORDER BY E.Nom, M.Libelle;

📝 مثال: الطلاب الذين معدلهم أكبر من المعدل العام

SELECT NumEt, AVG(Note) AS Moyenne
FROM Notes
GROUP BY NumEt
HAVING AVG(Note) > (
  SELECT AVG(Note) FROM Notes
);

الاستعلام الداخلي SELECT AVG(Note) FROM Notes يحسب المعدل العام لجميع العلامات. الاستعلام الخارجي يقارن معدل كل طالب به.

📝 مثال: الطلاب المسجلين في مادة “الرياضيات”

SELECT Nom, Classe
FROM Etudiants
WHERE NumEt IN (
  SELECT NumEt
  FROM Notes
  WHERE Matiere = 'Mathématiques'
);

IN يتحقق مما إذا كانت قيمة NumEt موجودة في قائمة النتائج من الاستعلام الداخلي.

📝 مثال: الطلاب الذين لهم على الأقل علامة واحدة

SELECT Nom, Classe
FROM Etudiants E
WHERE EXISTS (
  SELECT 1
  FROM Notes N
  WHERE N.NumEt = E.NumEt
);

EXISTS يرجع TRUE إذا كان الاستعلام الداخلي يعيد صفاً واحداً على الأقل. SELECT 1 تقليد لتحسين الأداء (لا يهم أي قيمة نختار).

📝 مثال: معدل كل طالب

SELECT NumEt, AVG(Note) AS Moyenne, COUNT(*) AS NbNotes
FROM Notes
GROUP BY NumEt;

GROUP BY NumEt يجمع الصفوف حسب رقم الطالب، ثم تطبق AVG على كل مجموعة. الحقول في SELECT يجب أن تكون إما دالة إحصائية أو موجودة في GROUP BY.

📝 مثال: الطلاب الذين معدلهم أكبر من 12

SELECT NumEt, AVG(Note) AS Moyenne
FROM Notes
GROUP BY NumEt
HAVING AVG(Note) >= 12;

HAVING يشبه WHERE لكنه يُطبق بعد التجميع (GROUP BY). WHERE يُطبق قبل التجميع على الصفوف الفردية.

📝 مثال: اسم الطالب، عدد المواد التي اجتازها، ومعدله العام

SELECT E.Nom,
       COUNT(N.Matiere) AS NbMatieres,
       ROUND(AVG(N.Note), 2) AS MoyenneGenerale
FROM Etudiants E
INNER JOIN Notes N ON E.NumEt = N.NumEt
GROUP BY E.NumEt, E.Nom
HAVING AVG(N.Note) >= 10
ORDER BY MoyenneGenerale DESC;

شرح: INNER JOIN يربط الجدولين، GROUP BY يجمع حسب الطالب، COUNT يحسب عدد المواد، AVG يحسب المعدل، HAVING يكتفي بمن معدله 10 فأكثر، ORDER BY ... DESC يرتب تنازلياً.