استعلامات 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 other_college,
       Count(*) AS students
FROM   epcms_uni_equalization
WHERE  status NOT IN ( 0, 5 )
GROUP  BY other_college
ORDER  BY students DESC,
          other_college ASC;

الساعات الغير متكافئة بين المقررات المحلية والبعيدة في طلبات المعادلة

 SELECT     epcms_uni_equalization_entry.entryid,
           epcms_uni_equalization.equalizationid,
           epcms_uni_equalization.NAME,
           epcms_uni_equalization_entry.foreigner_course,
           epcms_uni_equalization_entry.foreigner_course_name,
           epcms_uni_equalization_entry.foreigner_course_ch,
           epcms_uni_course.code,
           epcms_uni_course.title_en,
           epcms_uni_course.credit_hours
FROM       epcms_uni_equalization
INNER JOIN epcms_uni_equalization_entry
ON         epcms_uni_equalization.equalizationid = epcms_uni_equalization_entry.equalizationid
LEFT JOIN  epcms_uni_course
ON         epcms_uni_equalization_entry.local_course = epcms_uni_course.code
WHERE      epcms_uni_course.credit_hours > epcms_uni_equalization_entry.foreigner_course_ch && epcms_uni_equalization.year = 2017 && epcms_uni_equalization.semester = 1 ;

مع مراعاة تغيير السنة والفصل

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

 SELECT epcms_uni_equalization.equalizationid,
       epcms_uni_student.collegeid,
       epcms_uni_student.name_ar,
       epcms_uni_equalization.collegeid AS "CID"
FROM   epcms_uni_student
       INNER JOIN epcms_uni_equalization
               ON epcms_uni_equalization.mobile_number =
                  epcms_uni_student.mobile_number
WHERE  epcms_uni_equalization.status IN ( 3, 4 )
ORDER  BY epcms_uni_equalization.equalizationid ASC;

ملاحظة: يجب أن يتم ربط كل طلب معادلة بملف الطالب بعد أن يقوم بالتسجيل

أسباب رفض معادلة مواد المعادلات

 SELECT rejection,
       Count(*) AS total
FROM   epcms_uni_equalization_entry
WHERE  rejection != ''
GROUP  BY rejection
ORDER  BY total DESC,
          rejection ASC;

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

 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

قائمة الطلاب الذين تم اصدار إفادة لهم بعد تاريخ معين

select LPAD(epcms_uni_student.collegeid, 6, 0) as "Student ID", epcms_uni_student.name_ar, epcms_uni_student.specialization_ar, 
if(study_nature = 5 && accept_nature = 4, 'طالب بعثة داخلية', '') as "Notes" from epcms_uni_archive inner join epcms_uni_student on epcms_uni_archive.collegeid =  epcms_uni_student.collegeid where 
epcms_uni_archive.archive_time >= 1519728404  -- Change the time here
&& epcms_uni_archive.messageid in  (7, 58) 
&& epcms_uni_student.collegeid not in 
( 131129,120366,160637,110212,150308,120571) -- here to exclude students who already been added to the list
 group by epcms_uni_student.collegeid order by epcms_uni_student.specialization_ar ASC, epcms_uni_student.name_ar ASC;