استعلامات MySQL

يمكن الرجوع لصفحة قاعدة بيانات خدمات قسم القبول والتسجيل لمعرفة المزيد عن قاعدة البيانات المتعلقة بالاستعلامات أدناه.

احصائيات

عدد المسجلين الطلاب في سنة معينة

SELECT COUNT(*) AS total
FROM   (SELECT epcms_uni_student.collegeid
        FROM   epcms_uni_student
               LEFT JOIN epcms_uni_student_grade
                      ON epcms_uni_student.collegeid =
                         epcms_uni_student_grade.collegeid
        WHERE  epcms_uni_student_grade .YEAR = 2016
        GROUP  BY epcms_uni_student_grade.collegeid) tbl;

مع مراعاة تغيير السنة

عدد الطلاب العمانيين في سنة معينة

 SELECT Count(*) AS total
FROM   (
                 SELECT    epcms_uni_student.collegeid
                 FROM      epcms_uni_student
                 LEFT JOIN epcms_uni_student_grade
                 ON        epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid
                 WHERE     epcms_uni_student_grade.year = 2016 && epcms_uni_student.nationality_code = 10
                 GROUP BY  epcms_uni_student_grade.collegeid) tbl;

مع مراعاة تغيير السنة

عدد الطلاب الغير عمانيين في سنة معينة

SELECT Count(*) AS total
FROM   (
                 SELECT    epcms_uni_student.collegeid
                 FROM      epcms_uni_student
                 LEFT JOIN epcms_uni_student_grade
                 ON        epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid
                 WHERE     epcms_uni_student_grade.year = 2016 && epcms_uni_student.nationality_code != 10
                 GROUP BY  epcms_uni_student_grade.collegeid) tbl;

مع مراعاة تغيير السنة

جنسيات الطلاب حسب السنة (دون العمانيين)

SELECT Group_concat(' ', tbl.nationality_ar)
FROM   (
                 SELECT    epcms_uni_student.nationality_ar
                 FROM      epcms_uni_student
                 LEFT JOIN epcms_uni_student_grade
                 ON        epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid
                 WHERE     epcms_uni_student_grade.year = 2016 && epcms_uni_student.nationality_code != 10
                 GROUP BY  epcms_uni_student.nationality_code) tbl;

مع مراعاة تغيير السنة

عدد مرات طلبات استعادة كلمة المرور الخاصة بـSIS

SELECT epcms_uni_password_reset.*,
       epcms_uni_student.name_en,
       Count(*) AS resets
FROM   epcms_uni_password_reset
       INNER JOIN epcms_uni_student
               ON epcms_uni_password_reset.collegeid =
                  epcms_uni_student.collegeid
GROUP  BY epcms_uni_password_reset.collegeid
ORDER  BY resets DESC;

المعادلات المعلقة لكل عضو في لجنة المعادلات

 SELECT     userid,
           Concat_ws(' ', firstname, lastname) AS NAME,
           Count(*)                            AS "pending equalization"
FROM       epcms_uni_equalization_pc
INNER JOIN epcms_uni_equalization
ON         epcms_uni_equalization.equalizationid = epcms_uni_equalization_pc.equalizationid
INNER JOIN epcms_user
ON         epcms_user.userid = epcms_uni_equalization_pc.to_userid
WHERE      signed = 0 && pc_status = 1 && epcms_uni_equalization.status NOT IN (0,5)
GROUP BY   to_userid
ORDER BY   Count(*) DESC;

الطلاب المنقطعين بالفصول:

 SELECT std.collegeid,
       civil_id,
       name_ar,
       specialization_ar,
       beginning_year,
       beginning_semester,
       last_year,
       last_semester,
       mobile_number,
       left_credit,
       courses,
       gpa,
       study_nature,
       study_status,IF(is_sponsored = 1, "Yes", "No") as "is sponsored?",
(
       SELECT count(*) AS total
       FROM   epcms_uni_student_status stt
       WHERE  semester != 3 && year > 2000 && std_study_status IN (2) && stt.collegeid = std.collegeid ) AS discontinued, sts.discontinued_semesters FROM epcms_uni_student std INNER JOIN
(
         SELECT   collegeid,
                  group_concat(concat_ws('|', year, semester), ' ') AS discontinued_semesters
         FROM     epcms_uni_student_status
         WHERE    semester != 3 && year > 2000 && std_study_status IN (2)
         GROUP BY collegeid) sts ON sts.collegeid = std.collegeid WHERE std.study_status NOT IN (3,
                                                                                                 4,
                                                                                                 5,
                                                                                                 6,
                                                                                                 8,
                                                                                                 9,
                                                                                                 10,
                                                                                                 11,
                                                                                                 12,
                                                                                                 13,
                                                                                                 14,
                                                                                                 15,
                                                                                                 16,
                                                                                                 17,
                                                                                                 18) && is_graduated = 0 && study_nature = 5 ORDER BY name_ar ASC;

مع مراعاة تغيير طبيعة الدراسة عند الحاجة لذلك - study_nature