استعلامات MySQL
يمكن الرجوع لصفحة قاعدة بيانات خدمات قسم القبول والتسجيل لمعرفة المزيد عن قاعدة البيانات المتعلقة بالاستعلامات أدناه.
محتويات
- ١ احصائيات
- ١.١ عدد المسجلين الطلاب في سنة معينة
- ١.٢ عدد الطلاب العمانيين في سنة معينة
- ١.٣ عدد الطلاب الغير عمانيين في سنة معينة
- ١.٤ جنسيات الطلاب حسب السنة (دون العمانيين)
- ١.٥ الطلاب المسجلين حسب السنة والتخصص
- ١.٦ الطلاب المسجلين حسب السنة والتخصص بالإنجليزية
- ١.٧ عدد مرات طلبات استعادة كلمة المرور الخاصة بـSIS
- ١.٨ المعادلات المعلقة لكل عضو في لجنة المعادلات
- ١.٩ احصائية المعادلات حسب الجهة التعليمة المنتقل منها
- ١.١٠ الساعات الغير متكافئة بين المقررات المحلية والبعيدة في طلبات المعادلة
- ١.١١ مقدمي طلبات المعادلة المسجلين في الجامعة
- ١.١٢ أسباب رفض معادلة مواد المعادلات
- ٢ الطلاب المنقطعين بالفصول
- ٣ قائمة الطلاب الذين تم اصدار إفادة لهم بعد تاريخ معين
- ٤ مسودة الملف كاملة
احصائيات
عدد المسجلين الطلاب في سنة معينة
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;مع مراعاة تغيير السنة
الطلاب المسجلين حسب السنة والتخصص
select *, (select count(DISTINCT epcms_uni_student_grade.collegeid) from epcms_uni_student_grade inner join epcms_uni_student on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where year = tbl.year && semester = tbl.semester &&
(CASE WHEN specialization_ar LIKE 'إدارة الأعمال - إدارة الأعمال' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - إقتصاد' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - التسويق' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - محاسبة' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال -الانظمة' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'الدبلوم في اللغة الانجليزية وادابها ' THEN 'الدبلوم في اللغة الانجليزية وادابها'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وآدابها' THEN 'اللغة الإنجليزية وآدابها'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وادارة الأعمال' THEN 'اللغة الإنجليزية وآدابها'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الاتصالات' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الحوسبة' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / تقنية المعلومات والحوسبة' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الحوسبة وادارة الاعمال' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الشبكات وأمن البيانات' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'دبلوم في ادارة الاعمال' THEN 'دبلوم في ادارة الاعمال'
WHEN specialization_ar LIKE 'دبلوم في تقنية المعلومات والحوسبة' THEN 'دبلوم في تقنية المعلومات والحوسبة'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية - مسار الامتحان الشامل' THEN 'ماجستير التربية في القيادة التربوية'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية- مسار الأطروحة' THEN 'ماجستير التربية في القيادة التربوية'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم (مسار الأطروحة)' THEN 'ماجستير التربية في تكنولوجيا التعليم'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم(مسار الامتحان الشامل)' THEN 'ماجستير التربية في تكنولوجيا التعليم'
WHEN specialization_ar LIKE 'ماجستير في إدارة الأعمال' THEN 'ماجستير في إدارة الأعمال' END
) like tbl.sp) as registered_students from (select year, semester, (CASE WHEN specialization_ar LIKE 'إدارة الأعمال - إدارة الأعمال' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - إقتصاد' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - التسويق' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - محاسبة' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال -الانظمة' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'الدبلوم في اللغة الانجليزية وادابها ' THEN 'الدبلوم في اللغة الانجليزية وادابها'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وآدابها' THEN 'اللغة الإنجليزية وآدابها'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وادارة الأعمال' THEN 'اللغة الإنجليزية وآدابها'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الاتصالات' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الحوسبة' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / تقنية المعلومات والحوسبة' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الحوسبة وادارة الاعمال' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الشبكات وأمن البيانات' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'دبلوم في ادارة الاعمال' THEN 'دبلوم في ادارة الاعمال'
WHEN specialization_ar LIKE 'دبلوم في تقنية المعلومات والحوسبة' THEN 'دبلوم في تقنية المعلومات والحوسبة'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية - مسار الامتحان الشامل' THEN 'ماجستير التربية في القيادة التربوية'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية- مسار الأطروحة' THEN 'ماجستير التربية في القيادة التربوية'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم (مسار الأطروحة)' THEN 'ماجستير التربية في تكنولوجيا التعليم'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم(مسار الامتحان الشامل)' THEN 'ماجستير التربية في تكنولوجيا التعليم'
WHEN specialization_ar LIKE 'ماجستير في إدارة الأعمال' THEN 'ماجستير في إدارة الأعمال' END
) as sp
from epcms_uni_student_grade inner join epcms_uni_student on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid
where year >= 2012 && semester != 3 group by concat_ws('|', sp, year, semester) order by concat_ws('|', year, semester) ASC) tbl;الطلاب المسجلين حسب السنة والتخصص بالإنجليزية
select *, (select count(DISTINCT epcms_uni_student_grade.collegeid) from epcms_uni_student_grade inner join epcms_uni_student on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where year = tbl.year && semester = tbl.semester && epcms_uni_student.is_sponsored = 1 && (epcms_uni_student.study_nature != 5 || epcms_uni_student.study_nature = 5 && epcms_uni_student.accept_nature != 4) &&
(CASE WHEN specialization_ar LIKE 'إدارة الأعمال - إدارة الأعمال' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'إدارة الأعمال - إقتصاد' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'إدارة الأعمال - التسويق' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'إدارة الأعمال - محاسبة' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'إدارة الأعمال -الانظمة' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'الدبلوم في اللغة الانجليزية وادابها ' THEN 'Diploma in English Language Studies and Literature'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وآدابها' THEN 'English Language & Literature'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وادارة الأعمال' THEN 'English Language & Literature'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الاتصالات' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الحوسبة' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / تقنية المعلومات والحوسبة' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الحوسبة وادارة الاعمال' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الشبكات وأمن البيانات' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'دبلوم في ادارة الاعمال' THEN 'Diploma in Business Studies Study'
WHEN specialization_ar LIKE 'دبلوم في تقنية المعلومات والحوسبة' THEN 'Diploma Information Technology & Computing'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية - مسار الامتحان الشامل' THEN 'Master of Education in Leadership'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية- مسار الأطروحة' THEN 'Master of Education in Leadership'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم (مسار الأطروحة)' THEN 'Master of Education in Educational Technology'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم(مسار الامتحان الشامل)' THEN 'Master of Education in Educational Technology'
WHEN specialization_ar LIKE 'ماجستير في إدارة الأعمال' THEN 'Master of Business Administration' END
) like tbl.sp) as registered_students from (select year, semester, (CASE WHEN specialization_ar LIKE 'إدارة الأعمال - إدارة الأعمال' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'إدارة الأعمال - إقتصاد' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'إدارة الأعمال - التسويق' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'إدارة الأعمال - محاسبة' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'إدارة الأعمال -الانظمة' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'الدبلوم في اللغة الانجليزية وادابها ' THEN 'Diploma in English Language Studies and Literature'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وآدابها' THEN 'English Language & Literature'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وادارة الأعمال' THEN 'English Language & Literature'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الاتصالات' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الحوسبة' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / تقنية المعلومات والحوسبة' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الحوسبة وادارة الاعمال' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الشبكات وأمن البيانات' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'دبلوم في ادارة الاعمال' THEN 'Diploma in Business Studies Study'
WHEN specialization_ar LIKE 'دبلوم في تقنية المعلومات والحوسبة' THEN 'Diploma Information Technology & Computing'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية - مسار الامتحان الشامل' THEN 'Master of Education in Leadership'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية- مسار الأطروحة' THEN 'Master of Education in Leadership'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم (مسار الأطروحة)' THEN 'Master of Education in Educational Technology'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم(مسار الامتحان الشامل)' THEN 'Master of Education in Educational Technology'
WHEN specialization_ar LIKE 'ماجستير في إدارة الأعمال' THEN 'Master of Business Administration' END
) as sp from epcms_uni_student_grade inner join epcms_uni_student on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid
where year >= 2012 && semester != 3 group by concat_ws('|', sp, year, semester) order by concat_ws('|', year, semester) ASC) 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;
مسودة الملف كاملة
SELECT tbl1.* FROM epcms_uni_student_grade tbl1 INNER JOIN epcms_uni_student ON epcms_uni_student.collegeid =tbl1.collegeid WHERE CONCAT_WS('|', year,semester) = (
SELECT MAX(CONCAT_WS('|', year,semester)) FROM epcms_uni_student_grade tbl2 where
tbl2.grade like '%FI%' AND tbl2.collegeid = tbl1.collegeid AND tbl2.course_code = tbl1.course_code
) AND epcms_uni_student.study_status in (1,2,3,4) ORDER BY year ASC, semester ASC;
SELECT * FROM epcms_uni_student_grade where grade like 'FI';
SELECT collegeid, civil_id, name_ar, specialization_ar, if(mobile_number = '', home_tel, mobile_number) as phone FROM epcms_uni_student where study_nature = 1 AND collegeid >= 160000
order by specialization_ar ASC, name_ar ASC;
SELECT collegeid, name_ar,specialization_ar
FROM epcms_uni_student
WHERE collegeid
IN (110189,120538,140499,100256,140444,130193)
ORDER BY specialization_ar ASC, name_ar ASC;
SELECT CONCAT_WS(' | ', name_ar, collegeid) FROM epcms_uni_student WHERE study_nature = 5 AND study_status in (1,2,3,4) AND is_graduated = 0;
SELECT * FROM epcms_uni_student WHERE collegeid = 130135;
SELECT collegeid, name_ar,specialization_ar
FROM epcms_uni_student
WHERE civil_id
IN (
)
ORDER BY specialization_ar ASC, name_ar ASC;
SELECT collegeid, civil_id, name_ar, specialization_ar, study_status FROM epcms_uni_student WHERE study_nature = 5 AND study_status in (1,2,4) AND is_graduated = 0 AND collegeid >= 160000 order by specialization_ar ASC, name_ar ASC;
SELECT name_ar from epcms_uni_student where collegeid in (
160038,160111,160134,160056,160071,160099,160100,160084,160070,160062,160048,160078,160120,160105,160021,160015,160093,160137,160012,160066,160072,160103,160109,160089,160108,160054,160073,160104,160081,160127,160027,160075,160047,160010,160032,160086,160144,160091,160124,160085,160065,160009,160088,160139,160058,160033,160080,160133,160035,160126,160031,160121,160114,160067,160136,160034,160069,160042,160118,160122,160119,160217,140698,140903,160007,160008,160009,160010,160011,160012,160013,160014,160015,160017,160018,160020,160021,160022,160023,160024,160025,160026,160027,160028,160030,160031,160032,160033,160034,160035,160036,160037,160038,160039,160040,160042,160043,160044,160047,160048,160049,160050,160051,160052,160053,160054,160055,160056,160058,160059,160060,160062,160063,160064,160065,160066,160067,160068,160069,160070,160071,160072,160073,160074,160075,160076,160078,160079,160080,160081,160082,160083,160084,160085,160086,160087,160088,160089,160091,160093,160094,160095,160096,160097,160098,160099,160100,160101,160103,160104,160105,160106,160107,160108,160109,160110,160111,160113,160114,160115,160116,160117,160118,160119,160120,160121,160122,160123,160124,160125,160126,160127,160128,160131,160132,160133,160135,160136,160137,160138,160139,160140,160141,160142,160143,160144,160217,160391,160410
);
SELECT collegeid, name_ar, specialization_ar, gpa, credit_hours, IF(specialization_en = 'Master of Business Administration', '-', IF(locate('ED221', epcms_uni_std_first_semester_courses(collegeid)) > 0 OR
locate('ED423', epcms_uni_std_first_semester_courses(collegeid)) > 0 OR
locate('ED241', epcms_uni_std_first_semester_courses(collegeid) > 0), 'غير تربوي', 'تربوي')) AS "تربوي/غير تربوي",
epcms_uni_courses_credit(completed_courses) as cch
FROM epcms_uni_student WHERE /*gpa >= 2.67 AND*/
(specialization_en not like 'Master%' AND gpa >= 2 || specialization_en like 'Master%' AND gpa >= 3) AND
last_year = 2015 AND credit_hours <= 0 AND
((specialization_ar like '%التربية%' AND (
locate('ED221', epcms_uni_std_first_semester_courses(collegeid)) > 0 OR
locate('ED423', epcms_uni_std_first_semester_courses(collegeid)) > 0 OR
locate('ED241', epcms_uni_std_first_semester_courses(collegeid)) > 0) AND locate('ED698', completed_courses) > 0) OR specialization_ar not like '%التربية%')
ORDER BY gpa DESC, specialization_ar ASC, name_ar ASC;
SELECT * FROM epcms_uni_student WHERE collegeid = 080825;
SELECT * FROM epcms_uni_student GROUP BY nationality_code;
SELECT * FROM epcms_uni_student WHERE courses != '' order by gpa DESC;
SELECT collegeid, name_ar, nationality_ar, specialization_ar, name_en, nationality_en, specialization_en, if(gender = 1, "female", "male") as gender, CONCAT_WS('|', beginning_semester, beginning_year) as "First Semester", CONCAT_WS('|', last_semester, last_year) as "Last Semester", mobile_number, home_tel, current_credit, left_credit, courses as "Current Courses", gpa, study_nature FROM epcms_uni_student WHERE courses != '' && specialization_en like '%master%' order by gpa DESC LIMIT 10
UNION
SELECT collegeid, name_ar, nationality_ar, specialization_ar, name_en, nationality_en, specialization_en, if(gender = 1, "female", "male") as gender, CONCAT_WS('|', beginning_semester, beginning_year) as "First Semester", CONCAT_WS('|', last_semester, last_year) as "Last Semester", mobile_number, home_tel, current_credit, left_credit, courses as "Current Courses", gpa, study_nature FROM epcms_uni_student WHERE courses != '' && specialization_en not like '%master%' order by gpa DESC LIMIT 10;
SELECT * FROM (SELECT collegeid, name_ar, nationality_ar, specialization_ar, name_en, nationality_en, specialization_en, if(gender = 1, "female", "male") as gender, CONCAT_WS('|', beginning_semester, beginning_year) as "First Semester", CONCAT_WS('|', last_semester, last_year) as "Last Semester", mobile_number, home_tel, current_credit, left_credit, courses as "Current Courses", gpa, study_nature, is_sponsored FROM epcms_uni_student WHERE courses != '' && gpa > 3.67 && specialization_en like '%master%' order by gpa DESC /*LIMIT 10*/ ) std1 UNION ALL SELECT * FROM (SELECT collegeid, name_ar, nationality_ar, specialization_ar, name_en, nationality_en, specialization_en, if(gender = 1, "female", "male") as gender, CONCAT_WS('|', beginning_semester, beginning_year) as "First Semester", CONCAT_WS('|', last_semester, last_year) as "Last Semester", mobile_number, home_tel, current_credit, left_credit, courses as "Current Courses", gpa, study_nature, is_sponsored FROM epcms_uni_student WHERE courses != '' && gpa > 3.67 && specialization_en not like '%master%' order by gpa DESC /*LIMIT 10*/) std2 order by gpa DESC;
SELECT collegeid, name_ar, nationality_ar, specialization_ar, name_en, nationality_en, specialization_en, if(gender = 1, "female", "male") as gender, CONCAT_WS('|', beginning_semester, beginning_year) as "First Semester", CONCAT_WS('|', last_semester, last_year) as "Last Semester", mobile_number, home_tel, current_credit, left_credit, epcms_uni_courses_credit(completed_courses) as completed_credit, courses as "Current Courses", gpa, study_nature, study_status, is_sponsored FROM epcms_uni_student WHERE gpa >= 3.67 && is_graduated = 0 && study_status not in (6,9,12) && collegeid not in(
81392,100372,140275,130412,110109,90722,120225,110192,91481,90135,130868,130854,130853,130481,100702,140287
) order by gpa DESC;
SELECT * FROM epcms_uni_student_grade where collegeid = 70040 order by year ASC, semester ASC;
SELECT * FROM epcms_uni_student_grade where course_code like'M150B' AND year = 2016 AND semester = 1 AND section = 1 order by year ASC, semester ASC;
select MAX(CONCAT('', year, semester)) as yearsem from epcms_uni_student_grade;
select collegeid, civil_id from epcms_uni_student where collegeid in (140757,140842,140342,140446,140889,150399,131064,140880,130369,140542,140822,150280,150302,150484,150315,150324,150333,150257,150317,150434,150236,130603,140838,140883,140749,150435,150252,150270,150425,150426,140582,150323,150237,140805,140836,150339,150354,150493) order by field (collegeid, 140757,140842,140342,140446,140889,150399,131064,140880,130369,140542,140822,150280,150302,150484,150315,150324,150333,150257,150317,150434,150236,130603,140838,140883,140749,150435,150252,150270,150425,150426,140582,150323,150237,140805,140836,150339,150354,150493) ASC;
SELECT civil_id, name_ar, specialization_ar, '', collegeid, study_nature, study_status FROM epcms_uni_student WHERE civil_id in (
10273581,10301152,10322389,10417291,10419716,10481043,10531411,10578839,10599631,10609361,10657735,10692121,10819554,10884908,10896161,10916957,10951019,10952715,10984657,11048984,11066793,11074022,11145032,11226724,11231749,11239503,11277688,11284248,11380035,11414777,11457226,11514858,11542313,11574728,11642962,11666103,11677328,11696917,11743272,11748376,11781442,11810315,11818632,11823554,11844739,11898648,12015501,12049611,12079806,12130202,12170409,12181471,12191286,12197345,12277065,12357717,12441835,12465211,12571821,12647884,12695433,12707256,12736773,12799265,12833651,12870814,12919299,12923589,13015371,13095846,13140773,13233591,13414112,13472485,13535876,13797341,13833361,13897671,14192612,14646597,14711885,15342134,15391783,17940637,17977214,17981397,18111479,18112218,18281611,18460865,18619452,18943428,19053933,19377389,19395151,19435868,19501914,19767599,20037683,20180678,20270569,20313017,20841179,20865928,21267366,21369489,21932087,21936161,21946843,22009417,22028285,22038653,22658306,22738912,22765515,22782106,22929502,22992618,23022924,23026994,23046717,23065636,23076552,23627696,23656542,23708839,23714608,23726526,23831797,25122111,25149815,25880208,26491526,26693984,26768022,27092781,27461088,27507382,27629022,27643887,27649187,27663226,27733131,27932957,27947248,28331173,28875054
);
SELECT collegeid as "Student ID", civil_id, name_ar, specialization_ar,
CONCAT_WS('|', beginning_semester, beginning_year) as "First Semester", CONCAT_WS('|', last_semester, last_year) as "Last Semester",
mobile_number, home_tel, gpa, gender, birthday, nationality_ar, study_nature, study_status
FROM epcms_uni_student WHERE study_status in (1,2,4);
SELECT MIN(birthday) AS Oldest, MAX(birthday) as Youngest
FROM epcms_uni_student WHERE study_status in (1,2,4);
select collegeid, name_ar, specialization_ar, nationality_ar, mobile_number, home_tel as "mobile number 2", gpa from epcms_uni_student where is_graduated = 1 and specialization_en like '%diploma%' and gpa >= 2.33 order by specialization_ar ASC, name_ar ASC;
-- OUVS ID InstRef Forename(s) Family Name DOB Gender MODE_OF_STUDY ACADEMIC_LEVEL_OF_STUDY Title Address Line 1 Address Line 2 Address Line 3 Address Line 4 Address Line 5 Country Name Postcode Telephone No MOBILE_NO Email Address Location HAVE_AN_OLD_BRANCH_ID AwardName InstituteName STD_OU_HOURS
select ouvs_code, collegeid as 'InstRef', SUBSTRING_INDEX(name_en,' ',1) as 'Forename(s)', SUBSTRING_INDEX(name_en,' ', -1 * (length(name_en )-length(replace(name_en ,' ','')))) as 'Family Name', birthday as 'DOB', if(gender = 2, 'M', 'F') as 'Gender', '' as 'MODE_OF_STUDY', '' as 'ACADEMIC_LEVEL_OF_STUDY', '' as 'STD_OU_HOURS', city_en, '', '', address, '', '', '', home_tel, mobile_number, student_email, '', '', '', '', 'Oman' as "Location", '' as HAVE_AN_OLD_BRANCH_ID from epcms_uni_student where collegeid in (
130618,130633,130737,130757,130784,140056,140603,140625,140631,140659,140692,140913,150008,150083,150095,150126,150139,150145,150209,150250,150370,150374,150459,150462,150510,150613,150683,150708,150718,150745,150862,160068,160089,160142,160259,160272,160367,160469,160487,160488,160494,160498,160550,160551,160566,160567,160579,160590,160601,160610,160613,160635,160643,160653,160661,160677,160680,170007,170010,170011,170079,170081,170083,170084,170172,170176,170182,170199,170216,170223,170225,170227,170228,170241,170242,170244,170245,170253,170260,170270,170271,170273,170277,170283,170284,170286,170287,170290,170294,170296,170304,170308,170311,170313,170314,170340,170346,170348,170349,170351,170352,170359,170361,170363,170365,170370,170371,170384,170388,170389,170396,170400,170403,170405,170406,170410,170412,170414,170415
) order by field(collegeid, 130618,130633,130737,130757,130784,140056,140603,140625,140631,140659,140692,140913,150008,150083,150095,150126,150139,150145,150209,150250,150370,150374,150459,150462,150510,150613,150683,150708,150718,150745,150862,160068,160089,160142,160259,160272,160367,160469,160487,160488,160494,160498,160550,160551,160566,160567,160579,160590,160601,160610,160613,160635,160643,160653,160661,160677,160680,170007,170010,170011,170079,170081,170083,170084,170172,170176,170182,170199,170216,170223,170225,170227,170228,170241,170242,170244,170245,170253,170260,170270,170271,170273,170277,170283,170284,170286,170287,170290,170294,170296,170304,170308,170311,170313,170314,170340,170346,170348,170349,170351,170352,170359,170361,170363,170365,170370,170371,170384,170388,170389,170396,170400,170403,170405,170406,170410,170412,170414,170415) ASC;
SELECT * FROM epcms_uni_student WHERE study_status in (1,2,4) AND study_nature = 5 order by beginning_year ASC, beginning_semester ASC, name_ar ASC;
SELECT collegeid, specialization_en, specialization_ar, CONCAT_WS('|', beginning_semester, beginning_year) as beginning FROM epcms_uni_student WHERE beginning_year >= 2014 AND specialization_en NOT LIKE '%Diploma%' AND specialization_en NOT LIKE '%Master%' ORDER BY beginning ASC, specialization_en ASC, name_en ASC;
select GROUP_CONCAT(collegeid) from epcms_uni_student_extradata where is_attending = 1;
select collegeid from epcms_uni_student_extradata where is_attending = 1;
select collegeid, civil_id, name_ar, specialization_ar, mobile_number, home_tel, nationality_ar from epcms_uni_student where collegeid in (
120280 ,110615 ,110229 ,120218 ,100609 ,160256 ,110739 ,110736 ,120294 ,140898 ,120630 ,120129 ,110633 ,130442 ,120151,140753,120295,130385,120527,131028,120757,120235,140880,100659,140791,130963,140758,140323,130463,160249,130915,131064,140828,130290,110730,131140,100682,120635,110538,130531,110435,120511,110672,110578,120636,100418,120668,120109,120437,120046,120380,120027,120164,100392,120279,110715,140268,120034,120012,120007,110111,120051,130465,120419,140321,120096,120036,120064,120098,120017,120104,120071,120003,120068,120038,120091,110663,100546,130189,120048,110147,120298,160392,140366,120762,160240,120760,120566,100150,140715,110220,90014,140878,110014,140764,80473,81356,140375,120135,140545,100002,81380,110623,90377,81051,120111,131133,120055,120042,120170,120323,110530,140360,110485,91226,140918,110546,90908,131004,120052,140810,120033,140729,140579,100536,120078,120620,130199,120099,120073,100704,110279,90193,120045,120102,120353,120037,120182,150688,120083,120069,120016,120089,120015,120025,120075,70026,120094,140483,120057,120110,120049,120014,120044,120103,120030,120043,90418,131066,131108,110295,120307 ,110465,140848,100629,150541,120507 ,120314,120524,110051,130910,110557,130981,140358,80912,120354,130588,130955,140334,150804,100223,130407,130503,131036,130406,130316,130865,120525,130913,140400,130469,140904,130943,130411,150329,150084,120646,130367,160358,140357,120140,140777,140525,140452,90353,100403,140309,120147,140404,130241,130424,140728,120020,90383,130969,110568,150394,150730,140594,130608,131002,120187,150490,120530,110056,131006,80080,90793 ,120150,130167,130399,150757,130873,90110,130403 ,100409,130942 ,120305,110624,100512,120396,131005,140302,130979 ,130242 ,130266,150793 ,120018,90995,91210,100449,130313,160218,150231,150434,150398,150333,150514,150484,150285,150272,150540,150519,140787,150334,150317,150257,150289,140833,150280,150315,150324,150475,150271,140569,150438,150371,140822,150325,150482,140542,140836,140761,140804,140817,140854,140818,140759,140793,131131,140433,140805,140464,140748,140825,140790,150508,150486,150415,150445,140492,150151,150191,150503,150339,150535,150364,150500,150087,150354,150509,150461,140752,150316,150493,150356,150515,150288,150304,150007,150452,150237,140749,140872,150518,130857,150300,150267,140678,150426,140582,150323,140755,150252 ,150453,140732,140838,130603,150388,150425,140883,150270 ,140613,140864,150283,150338,150390,130599,130250,090715
) AND collegeid not in (select collegeid from epcms_uni_student_extradata where is_attending = 1 || (is_attending = 0 && attending_reason != '')) order by specialization_ar ASC, name_ar ASC;
SELECT collegeid, name_ar, specialization_ar FROM epcms_uni_student where collegeid in (
131058,130880,140494,140479,140566,130607,130882,130883,130905,140485,130919,140501,131097
) order by specialization_ar ASC, name_ar ASC;
select collegeid, civil_id, name_en, name_ar, specialization_en, specialization_ar, study_nature, study_status from epcms_uni_student where civil_id in (
10164517,10312869,10762419,12057769,13514859,14809244,18080789,20966192,21292527,24588075,25688382,27084876
) order by field(civil_id,
10164517,10312869,10762419,12057769,13514859,14809244,18080789,20966192,21292527,24588075,25688382,27084876
);
select collegeid, gpa, if((credit_hours + current_credit) > 0, (credit_hours + current_credit), 0) as credit from epcms_uni_student where collegeid in (
150821,130942,120366,150710,131064,140702,120449,131110,110614,90103,140891,150329,130946,130945,120762,140379,150732,140847,150653,150447,120571,150842,140579,130424,160211,140191
) order by field(collegeid,
150821,130942,120366,150710,131064,140702,120449,131110,110614,90103,140891,150329,130946,130945,120762,140379,150732,140847,150653,150447,120571,150842,140579,130424,160211,140191
);
select collegeid as "Student ID", name_en from epcms_uni_student where study_status in (5,6,9,14,16,24,28) and study_nature != 5 order by collegeid ASC;
select * from epcms_uni_student inner join epcms_uni_student_grade on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where (specialization_en not like '%diploma%' AND specialization_en not like '%master%') AND epcms_uni_student_grade.year = 2014 group by epcms_uni_student_grade.collegeid;
select collegeid, name_en, name_ar, nationality_code, nationality_en, nationality_ar from epcms_uni_student where nationality_code != 10 AND beginning_year = 2016 AND specialization_en NOT LIKE '%Master%' order by collegeid ASC;
select epcms_uni_student.collegeid, epcms_uni_student.civil_id, epcms_uni_student.mobile_number, epcms_uni_student.name_en, epcms_uni_student_balance.balance from epcms_uni_student inner join epcms_uni_student_balance on epcms_uni_student.collegeid = epcms_uni_student_balance.collegeid where epcms_uni_student.study_nature = 1 AND epcms_uni_student.last_year = 2016 AND epcms_uni_student_balance.balance >= '-10';
-- 70120
-- 0004237591
-- 97754130
select * from epcms_uni_student where collegeid = 70120 AND cast(civil_id as int) = 4237591 AND mobile_number = 97754130;
select DATEDIFF('2016-12-28', birthday) / 365.25 as age from epcms_uni_student where collegeid = 130857;
select specialization_ar from epcms_uni_student where collegeid in (130486,150236,150272,150325,150388,150398,150415,150475,150477,150484,150494,150526,150650,150651,150685,150686,150692,150722,150749,150754,150782,150801,150822);
select epcms_uni_student_authletter.collegeid, name_en from epcms_uni_student_authletter inner join epcms_uni_student on epcms_uni_student_authletter.collegeid = epcms_uni_student.collegeid group by epcms_uni_student_authletter.collegeid order by authid DESC;
select collegeid, name_ar,gpa, if(gpa >= 3.67, "ممتاز", if(gpa >= 3, "جيد جدًا", 'جيد')) from epcms_uni_student where collegeid in (
131029,140339,140502,130542,130595,130887,140596,140441,140597,140568,130257,131048,120206,140450,130883,110577,130868,130986,140895,130468,110513,110714,120169,120183,130517,120624,131105,131065,130262
) order by field(collegeid,
131029,140339,140502,130542,130595,130887,140596,140441,140597,140568,130257,131048,120206,140450,130883,110577,130868,130986,140895,130468,110513,110714,120169,120183,130517,120624,131105,131065,130262
);
SELECT collegeid as "student ID", name_ar, specialization_ar, beginning_year FROM epcms_uni_student WHERE nationality_ar LIKE '%بحرين%' ORDER BY name_ar ASC;
-- [statistics] --
-- Total number of students
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;
-- Locals
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;
-- Foreigners
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;
-- Other Nationalities
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;
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.gender = 1 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.gender = 2 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.study_nature = 1 group by epcms_uni_student.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.study_nature = 5 group by epcms_uni_student.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.is_sponsored = 1 group by epcms_uni_student.collegeid) tbl;
select specialization_ar, count(*) as total from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = 2016 group by collegeid) group by specialization_ar order by specialization_ar ASC;
select nationality_code, nationality_en, COUNT(*) AS TOTAL from epcms_uni_student GROUP BY nationality_en ORDER BY TOTAL DESC, nationality_en ASC;
select collegeid, civil_id, name_en, specialization_en, nationality_en from epcms_uni_student where nationality_code in (31,86,53,47,20,96,62,4);
select collegeid as "Student ID", name_ar, specialization_ar,epcms_uni_courses_credit(completed_courses) as finished_credit, gpa from epcms_uni_student where epcms_uni_courses_credit(completed_courses) >= 32 && study_status = 1 && gpa >= 3.67 order by gpa ASC, specialization_ar ASC, name_ar;
select "2013" as year,
(select count(*) as total from (select count(*) as total from epcms_uni_student_grade where year = 2016 && semester = 1 group by collegeid) s) as "Semester 1",
(select count(*) as total from (select count(*) as total from epcms_uni_student_grade where year = 2016 && semester = 2 group by collegeid) s2) as "Semester 2",
(select count(*) as total from (select count(*) as total from epcms_uni_student_grade where year = 2016 && semester = 3 group by collegeid) s3) as "Semester 3"
from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = 2016 group by collegeid) order by specialization_ar ASC limit 1 ;
select "2014" as year,
(select count(*) as total from (select count(*) as total from epcms_uni_student_grade where year = 2016 && semester = 1 group by collegeid) s) as "Semester 1",
(select count(*) as total from (select count(*) as total from epcms_uni_student_grade where year = 2016 && semester = 2 group by collegeid) s2) as "Semester 2",
(select count(*) as total from (select count(*) as total from epcms_uni_student_grade where year = 2016 && semester = 3 group by collegeid) s3) as "Semester 3"
from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = 2016 group by collegeid) order by specialization_ar ASC limit 1 ;
select "2015" as year,
(select count(*) as total from (select count(*) as total from epcms_uni_student_grade where year = 2016 && semester = 1 group by collegeid) s) as "Semester 1",
(select count(*) as total from (select count(*) as total from epcms_uni_student_grade where year = 2016 && semester = 2 group by collegeid) s2) as "Semester 2",
(select count(*) as total from (select count(*) as total from epcms_uni_student_grade where year = 2016 && semester = 3 group by collegeid) s3) as "Semester 3"
from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = 2016 group by collegeid) order by specialization_ar ASC limit 1 ;
select "2016" as year,
(select count(*) as total from (select count(*) as total from epcms_uni_student_grade where year = 2016 && semester = 1 group by collegeid) s) as "Semester 1",
(select count(*) as total from (select count(*) as total from epcms_uni_student_grade where year = 2016 && semester = 2 group by collegeid) s2) as "Semester 2",
(select count(*) as total from (select count(*) as total from epcms_uni_student_grade where year = 2016 && semester = 3 group by collegeid) s3) as "Semester 3"
from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = 2016 group by collegeid) order by specialization_ar ASC limit 1 ;
select *, FROM_UNIXTIME(note_time) as "Date:Time", count(*) as total from epcms_uni_student_log where note like 'IDCARD:REVEICED' group by collegeid order by total DESC;
-- [/statistics] --
select collegeid, name_en, specialization_en, name_ar, specialization_ar, mobile_number from epcms_uni_student where specialization_en like 'Master of Business Administration' and is_graduated = 1 order by name_ar ASC;
select epcms_uni_student.collegeid, epcms_uni_student.name_en, epcms_uni_student.specialization_en, epcms_uni_student.name_ar, epcms_uni_student.specialization_ar, epcms_uni_student.mobile_number, if(epcms_uni_student_extradata.email != '', epcms_uni_student_extradata.email, epcms_uni_student.student_email) as email from epcms_uni_student left join epcms_uni_student_extradata on epcms_uni_student.collegeid = epcms_uni_student_extradata.collegeid where epcms_uni_student.specialization_en like '%Business%' && epcms_uni_student.specialization_en not like '%Diploma%' && epcms_uni_student.specialization_en not like '%Master%' && epcms_uni_student.specialization_en not like 'English%' and epcms_uni_student.is_graduated = 1 order by name_ar ASC;
select epcms_uni_student.collegeid, epcms_uni_student.civil_id, epcms_uni_student.name_ar,
epcms_uni_student.specialization_ar, epcms_uni_student.gpa, epcms_uni_student_grade.* from epcms_uni_student left join epcms_uni_student_grade on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where epcms_uni_student.collegeid in (
140736,140826,140850,140865,150002,150291,150502,150533,150632,150639,150650,150655,150656,150667,150669,150671,150673,150675,150676,150677,150686,150687,150694,150697,150717,150724,150728,150735,150741,150742,150744,150747,150749,150750,150754,150762,150770,150775,150777,150783,150791,150796,150807,150826,150830
);
select * from epcms_uni_student inner join epcms_uni_student_status on epcms_uni_student.collegeid = epcms_uni_student_status.collegeid group by epcms_uni_student_status.collegeid;
-- discontinued students |SN5
select 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, (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 from epcms_uni_student std 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;
-- discontinued students with semesters |SN5
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;
-- Graduates students: without redundancy
select max(collegeid) as collegeid, cast(replace(civil_id, '/', '') as integer) as civil_id, name_ar, specialization_ar, count(*) as graduations from epcms_uni_student where is_graduated = 1 group by cast(replace(civil_id, '/', '') as integer) order by specialization_ar ASC, graduations DESC, name_ar ASC;
-- update discontinued students
select * from epcms_uni_student where study_status = 1 && study_nature = 5 && courses = '';
update epcms_uni_student_status set std_study_status = 2 where year = 2016 && semester = 1 && collegeid in (
120047,120059,120178,130059,130065,130114,130134,130142,130162,130580,130630,130632,130636,130638,130642,130645,130649,130653,130656,130673,130674,130684,130686,130690,130703,130709,130711,130712,130717,130721,130725,130739,130755,130767,130769,130773,130778,130796,130797,130803,130804,130834,140009,140015,140022,140040,140105,140126,140155,140195,140243,140261,140620,140632,140640,140662,140663,140713,150034,150120,150186,150208,150554,150567,160079,160098,160107,160131
);
-- student with CA less than 15 : Grouped
select epcms_uni_student.collegeid, epcms_uni_student.name_en, epcms_uni_student.specialization_en, epcms_uni_student.name_ar, epcms_uni_student.specialization_ar, (total_mta + total_midexam) as CA, GROUP_CONCAT(epcms_uni_student_grade.course_code) as "CA <= 15 Courses" from epcms_uni_student inner 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_grade.semester = 1 && epcms_uni_student_grade.grade != 'W' && (total_mta + total_midexam) <= 15 group by epcms_uni_student_grade.collegeid order by epcms_uni_student.specialization_en ASC, epcms_uni_student.name_en ASC;
-- student with CA less than 15 : Ungrouped
select epcms_uni_student.collegeid, epcms_uni_student.name_en, epcms_uni_student.specialization_en, epcms_uni_student.name_ar, epcms_uni_student.specialization_ar, (total_mta + total_midexam) as CA, epcms_uni_student_grade.course_code as "CA <= 15 Courses" from epcms_uni_student inner 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_grade.semester = 1 && epcms_uni_student_grade.grade != 'W' && (total_mta + total_midexam) <= 15 order by epcms_uni_student.specialization_en ASC, epcms_uni_student.name_en ASC;
-- User logins
select epcms_user_login.loginid, epcms_user.userid, epcms_user.firstname, epcms_user.lastname, from_unixtime(epcms_user_login.logintime) as logintime, from_unixtime(epcms_user_login.lastcheck) as lastcheck, if(epcms_user_login.logouttime > 0, from_unixtime(epcms_user_login.logouttime), 0) as logouttime, datediff(from_unixtime(epcms_user_login.lastcheck), from_unixtime(epcms_user_login.logintime)) as actvie_days, epcms_user_login.ip, epcms_user_login.client, epcms_user_login.status from epcms_user_login inner join epcms_user on epcms_user_login.userid = epcms_user.userid order by lastcheck ASC, loginid ASC;
-- Student who got less than 20 in MA100 & IT100
select epcms_uni_student.collegeid, epcms_uni_student.name_en, epcms_uni_student.specialization_en, epcms_uni_student.name_ar, epcms_uni_student.specialization_ar, epcms_uni_student.study_nature, epcms_uni_student.study_status, if(epcms_uni_student.is_sponsored = 1, "Yes", "No") as is_sponsored, epcms_uni_student_grade.course_code, epcms_uni_student_grade.section, (total_mta + total_midexam) as CA, total_final from epcms_uni_student inner 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_grade.semester = 1 && epcms_uni_student_grade.grade != 'W' && epcms_uni_student_grade.course_code in ('IT100', 'MA100') && total_final < 20 /*&& (total_mta + total_midexam) <= 15*/ order by epcms_uni_student_grade.course_code ASC, is_sponsored ASC, epcms_uni_student_grade.section ASC, epcms_uni_student.name_en ASC;
-- Expected to graduate
select collegeid as "Student ID", ouvs_code, if(SUBSTRING(REVERSE(SUBSTRING_INDEX(REVERSE(name_en), ' ', 2)), 1, 3) = 'Al ', REVERSE(SUBSTRING_INDEX(REVERSE(name_en), ' ', 2)), REVERSE(SUBSTRING_INDEX(REVERSE(name_en), ' ', 1))) AS Surname, replace(name_en, if(SUBSTRING(REVERSE(SUBSTRING_INDEX(REVERSE(name_en), ' ', 2)), 1, 3) = 'Al ', REVERSE(SUBSTRING_INDEX(REVERSE(name_en), ' ', 2)), REVERSE(SUBSTRING_INDEX(REVERSE(name_en), ' ', 1))), '') as Forename, "" as "Other Names", if(gender = 1, "F", "M") as gender, birthday, specialization_en as "Award Type"
from epcms_uni_student where collegeid in (
150791,150744,150783,150538,150671,150366,150632,150686,150513,150656,150754,150639,150676,150807,150735,150747,150770,150675,150477,160537
) order by specialization_en ASC, name_en;
Select * from epcms_uni_student where specialization_en like 'ITC / Computing with Business' && is_graduated = 1;
-- None Omani students:
select collegeid as "Student ID", name_ar, nationality_ar, specialization_ar, CONCAT_WS('/', beginning_year, beginning_year+1) as "starting year" from epcms_uni_student where nationality_code != 10 && study_status in (1,2,4);
-- Information with email
select collegeid, name_en, if(student_email = '', CONCAT('s', collegeid, '@aou.edu.om'), student_email) from epcms_uni_student where collegeid in (
120716,130037,130647,130659,130693,130814,140011,140058,140139
) group by collegeid order by field(collegeid,
120716,130037,130647,130659,130693,130814,140011,140058,140139
) ;
-- Get students by civil_id
select collegeid as "Student ID", name_en, mobile_number as "mobile", home_tel as "Phone 2", cast(replace(civil_id, '/', '') as integer) as civil_id2 from epcms_uni_student where cast(replace(civil_id, '/', '') as integer) in (
12994159,18240617,9775649,3967805,12897675
) group by civil_id2 order by field(civil_id2,
12994159,18240617,9775649,3967805,12897675
) ASC;
-- Passwords resets times
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;
-- pending equalizations
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;
-- equalizations per institations
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;
-- equalizations->other_college group by case sensitive
select *, other_college COLLATE utf8_bin as oc from epcms_uni_equalization where status not in (0,5) group by oc order by oc ASC;
-- Equalizations unmatching credit hours
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 ;
-- Equalizations : registered applicants
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;
-- Equalizations : Rejections
select rejection, count(*) as total from epcms_uni_equalization_entry where rejection != '' group by rejection order by total DESC, rejection ASC;
-- Students without adivsing -- get advisied students from oracle DB
select collegeid, name_en, specialization_en,
(select GROUP_CONCAT(course_code) from epcms_uni_student_grade where year = 2017 && semester = 1 && epcms_uni_student_grade.collegeid = epcms_uni_student.collegeid group by collegeid) as previous_semester,
(select advisor_en from epcms_uni_student_advisor where collegeid = epcms_uni_student.collegeid && CONCAT_WS('|', year,semester) = (select max(CONCAT_WS('|', year,semester)) from epcms_uni_student_advisor where collegeid = epcms_uni_student.collegeid) group by collegeid ) as last_advisor, gpa
from epcms_uni_student where study_status in (1,2,4) && locate('EF003', completed_courses) > 0
&& (gpa >= 2 || gpa < 2
&& CONCAT_WS('|', beginning_year,beginning_semester) != '2017|2' && collegeid not in (
120111,120234
) order by specialization_en ASC, last_advisor ASC, name_en ASC;
-- Students without adivsing -with advisor from temprory table -- get advisied students from oracle DB
select epcms_uni_student.collegeid, epcms_uni_student.name_en, epcms_uni_student.specialization_en, (select GROUP_CONCAT(course_code) from epcms_uni_student_grade where year = 2016 && semester = 1 && epcms_uni_student_grade.collegeid = epcms_uni_student.collegeid group by collegeid) as psemester, epcms_uni_student.gpa, EXPORT_TABLE.USER_FOREIGN_NAME as advisor from epcms_uni_student
LEFT JOIN EXPORT_TABLE on EXPORT_TABLE.STD_NO = epcms_uni_student.collegeid
where epcms_uni_student.study_status in (1,2) && CONCAT_WS('|', beginning_year,beginning_semester) != '2016|2' && (LOCATE('EF003', (select GROUP_CONCAT(course_code) from epcms_uni_student_grade where epcms_uni_student_grade.collegeid = epcms_uni_student.collegeid && epcms_uni_student_grade.grade not like '%F' group by collegeid)) > 0 || LOCATE('EF003', epcms_uni_student.completed_courses) > 0) &&
epcms_uni_student.collegeid in (
110633,120280,120570,150096,150409,130737,150240,150679,160339,130230,150232,150032,160331,150572,120636,110663,110538,130873,150849,160170,140206,100682,130981,100491,130349,130588,100223,13091
) group by epcms_uni_student.collegeid order by name_en ASC;
-- Only Arabic names:
select * from epcms_uni_equalization where status != 0 && name REGEXP '^[^a-zA-Z]+$';
-- [epcms_uni_equalization_entry update] --
select *, count(*) as total from epcms_uni_equalization_entry where epcms_uni_equalization_entry.local_course not in ('EF001', 'EF002', 'EF003', 'MA100', 'IT100', 'GR101', 'GR111', 'AR111', 'AR112') && epcms_uni_equalization_entry.foreigner_course_name like '' group by foreigner_course order by total DESC, foreigner_course ASC;
select epcms_uni_equalization_entry.*, epcms_uni_equalization.other_college from epcms_uni_equalization_entry inner join epcms_uni_equalization on epcms_uni_equalization.equalizationid = epcms_uni_equalization_entry.equalizationid where epcms_uni_equalization_entry.foreigner_course like '%ITSE2100%';
update epcms_uni_equalization_entry set foreigner_course_name = 'Object Oriented Programming (Java)' where epcms_uni_equalization_entry.foreigner_course like '%ITSE2100%';
update epcms_uni_equalization_entry set foreigner_course = 'ENTW1200', foreigner_course_name = 'TECHNICAL WRITING II' where epcms_uni_equalization_entry.foreigner_course like '%ENTW 1200-TECHNICAL WRITING II%';
-- [/epcms_uni_equalization_entry update] --
-- Ministry Students with first wanring
select epcms_uni_student.collegeid, epcms_uni_student.name_ar, epcms_uni_student.specialization_ar, epcms_uni_student_status.sem_gpa, epcms_uni_student.mobile_number, epcms_uni_student.home_tel from epcms_uni_student inner join epcms_uni_student_status on epcms_uni_student.collegeid = epcms_uni_student_status.collegeid where epcms_uni_student.study_status in (1,2,4) && epcms_uni_student.study_nature = 5 && epcms_uni_student_status.reg_status = 1 && epcms_uni_student_status.year = 2016 && epcms_uni_student_status.semester = 1 && epcms_uni_student_status.sem_gpa < 2 && (select COUNT(*) as total from epcms_uni_student_grade where epcms_uni_student_grade.course_code not in ('EF001', 'EF002', 'EF003', 'IT100', 'MA100') && epcms_uni_student_grade.collegeid = epcms_uni_student.collegeid && epcms_uni_student_grade.year = epcms_uni_student_status.year && epcms_uni_student_grade.semester = epcms_uni_student_status.semester ) > 0 group by epcms_uni_student.collegeid order by epcms_uni_student.name_ar;
-- All students who finished foundation english courses:
select * from epcms_uni_student inner join epcms_uni_student_status on epcms_uni_student.collegeid = epcms_uni_student_status.collegeid where epcms_uni_student.study_status in (1,2,4) && (select COUNT(*) as total from epcms_uni_student_grade where epcms_uni_student_grade.course_code not in ('EF001', 'EF002', 'EF003', 'IT100', 'MA100') && epcms_uni_student_grade.collegeid = epcms_uni_student.collegeid && epcms_uni_student_grade.year = epcms_uni_student_status.year && epcms_uni_student_grade.semester = epcms_uni_student_status.semester ) > 0 && epcms_uni_student.specialization_en not like 'Master%' group by epcms_uni_student.collegeid order by epcms_uni_student.name_ar;
-- افادات الخريجين
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 && epcms_uni_archive.messageid in (7, 58)
&& epcms_uni_student.collegeid not in (
131129,120366,160637,110212,150308,120571
) group by epcms_uni_student.collegeid order by epcms_uni_student.specialization_ar ASC, epcms_uni_student.name_ar ASC;
-- خريجين برقم جامعي
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_student where collegeid in (
120096,120036,120064,120017,120007,120003,120104,120068,120071,120038,130189,120048,100546,120298,110147,150508,150445,150151,150415,130599,150364,150087,150503,150191,150339,150461,150288,150316,150356,140752,150304
);
-- افادات لكن غير خريج في النظام
select * from epcms_uni_student where study_status != 8 && collegeid in (select epcms_uni_student.collegeid from epcms_uni_archive inner join epcms_uni_student on epcms_uni_archive.collegeid = epcms_uni_student.collegeid where epcms_uni_archive.archive_time >= 0 && epcms_uni_archive.messageid in (7, 58) group by epcms_uni_student.collegeid order by epcms_uni_student.specialization_ar ASC, epcms_uni_student.name_ar ASC);
-- Dismissed current semester
select collegeid as "Student ID", name_en, specialization_en from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_status where year = 2016 && semester = 1 && std_study_status in (2) and collegeid in (select collegeid from epcms_uni_student_status where year = 2016 && semester = 2 && std_study_status in (6))) order by specialization_en ASC, name_en ASC;
-- [HEAC Report] --
select epcms_uni_student.collegeid as "Student ID", epcms_uni_student.civil_id, epcms_uni_student.name_ar, epcms_uni_student.specialization_ar, epcms_uni_student.name_en, epcms_uni_student.specialization_en from epcms_uni_student inner join epcms_uni_student_status on epcms_uni_student_status.collegeid = epcms_uni_student.collegeid where epcms_uni_student_status.year = 2016 && epcms_uni_student_status.semester = 1 && epcms_uni_student_status.std_study_status = 16 && epcms_uni_student_status.collegeid not in (select epcms_uni_student_status.collegeid from epcms_uni_student_status where epcms_uni_student_status.year = 2015 && epcms_uni_student_status.semester = 3 && epcms_uni_student_status.std_study_status not in (6,14,16)) && epcms_uni_student.study_nature = 5 group by epcms_uni_student_status.collegeid;
select collegeid as "Student ID", civil_id, name_ar, specialization_ar, name_en, specialization_en, gpa, left_credit as "Credit Hours (Left)", current_credit as "Credit Hours (Registered)" from epcms_uni_student where collegeid in (
120012,120068,120051,120027,130189,110538,120007,120164,120098,120437,120046,120104,120109,110427,110578,120298,110435,100418,120419,120668,120064,120636,110111,120279,110715,120034,120003,120038,130531,120380,120096,110663,120017,100546,110672,120048,110147,120036,120008,120071,120511,100392,120235,131028,120757,100682,130915,160249,130290,130963,140791,130463,100659,140323,140880,140828,140758,131064,120314,90686,100629,110295,150541,120507,120307,110465,110229,160256,120218,120280,140898,100609,120630,110615,130442,110633,110739,120294,110736,120151,120295,140753,120069,120057,120037,110279,140483,120049,120075,120094,120015,120025,120353,120182,120110,120045,120016,100704,70026,120083,120102,90193,120089,131066,131108,120042,81380,110485,81356,120323,81051,110623,120052,80473,110530,131133,140360,120055,120179,100002,140764,110614,90377,131004,110014,120111,140545,140810,91226,110314,140918,120170,140375,110546,130440,140400,160358,130406,140309,120187,150329,120020,140904,100403,130411,130469,110056,130424,140452,130316,140357,131002,120274,90383,140717,140777,130981,140334,120147,140696,130969,130913,110568,140512,130891,130503,130588,130943,130910,130477,140358,150490,130955,130241,150804,110557,120354,110051,140728,140525,130865,131036,90353,100223,130221,120140,120646,140594,130407,120396,90962,120018,150757,120305,130313,120633,140302,150793,130167,130403,90995,70040,130399,80080,130266,80200,130942,160218,100409,120729,100512,91210,120150,131005,160428,130395,130979,100130,110624,90110,130242,100096,110455,140577,131085,140529,140453,140448,140612,130666,130982,130884,140790,150356,150304,140817,150007,150151,150415,150354,140433,150288,140825,150503,140759,150445,150508,150461,150316,140854,150515,150493,150087,140793,150535,140818,150486,150500,150191,131131,140464,150452,140752,140761,140804,150364,140805,150509,140492,150339
) && collegeid not in (
110014,140764,80473,81356,140375,120135,140545,100002,81380,110623,100704,110279,140880,100659,140791,130963,100682,120635,110538,130531,110435,120511,110672,110578,120636,100418,120668,110051,130910,110557,130981,140358,80912,120354,130588,130955,140334,150804,100223,130407,130503,131036,130406,130316,130865,120525,110295,120307 ,110465,80080,90793 ,120150,130167,130399,150757,130873,90110,130403 ,120280 ,110615 ,110229 ,120218 ,100609 ,120151,140753,120295,140836,140761,140804,140817,140854,140818,140759,140793,131131,140433,140805,140464,140748,140825,140790,130884,131085,130982,130666,140453,140448,140529,140577,120240,140612,140583
) && study_nature = 5 order by specialization_ar ASC, name_ar ASC;
-- [/HEAC Report] --
-- LRC Student List
select collegeid as "ID", name_en, if(student_email = '', CONCAT_WS('', 's',collegeid,'@aou.edu.om'), student_email) as student_email, mobile_number from epcms_uni_student where beginning_semester = 2 && beginning_year = 2016 order by collegeid ASC;
-- [equalization statistics] --
select other_college, count(*) as total from epcms_uni_equalization where CONCAT_WS('', year,semester) = 20171 && status not in (0,5) group by other_college order by total DESC;
select local_course, count(*) as total from epcms_uni_equalization_entry inner join epcms_uni_equalization on epcms_uni_equalization_entry.equalizationid = epcms_uni_equalization.equalizationid where CONCAT_WS('', year,semester) = 20171 && entry_status != '0' group by local_course order by total DESC;
select * from epcms_uni_equalization where collegeid != 0;
select * from epcms_uni_equalization_entry where rejection != '';
select *, count(*) as applications from epcms_uni_equalization where CONCAT_WS('', year,semester) = 20171 && status != 0 group by mobile_number order by applications DESC;
select count(*) as applications from epcms_uni_equalization where CONCAT_WS('', year,semester) = 20171;
select * from epcms_uni_equalization_entry inner join epcms_uni_equalization on epcms_uni_equalization_entry.equalizationid = epcms_uni_equalization.equalizationid where CONCAT_WS('', year,semester) = 20171 && entry_status > 0 && epcms_uni_equalization_entry.equalizationid not in (3,5);
select * from epcms_uni_equalization_entry where entry_status > 0 && local_course not in ('EF001', 'EF002', 'EF003', 'IT100', 'MA100') && equalizationid not in (3,5);
select * from epcms_uni_equalization_entry where entry_status > 0 && local_course not in ('EF001', 'EF002', 'EF003', 'MA100', 'IT100', 'GR111', 'GR112', 'GR115', 'GR131', 'AR111', 'AR112', 'BS100') && equalizationid not in (3,5);
select * from epcms_uni_equalization_entry where entry_status > 0 && local_course not in ('EF001', 'EF002', 'EF003', 'MA100', 'IT100', 'GR111', 'GR112', 'GR115', 'GR131', 'AR111', 'AR112', 'BS100') && equalizationid not in (3,5) && rejection != '';
select rejection, count(*) as total from epcms_uni_equalization_entry where entry_status > 0 && local_course not in ('EF001', 'EF002', 'EF003', 'MA100', 'IT100', 'GR111', 'GR112', 'GR115', 'GR131', 'AR111', 'AR112', 'BS100') && equalizationid not in (3,5) && rejection != '' group by rejection order by total DESC;
-- Top by time
select CONCAT_WS(' ', epcms_user.firstname, epcms_user.lastname) as name,
round(sum((epcms_uni_equalization_pc.sign_time - epcms_uni_equalization_pc.assign_time))/count(*), 0) as average_response,
TIME_FORMAT(SEC_TO_TIME(round(sum((epcms_uni_equalization_pc.sign_time - epcms_uni_equalization_pc.assign_time))/count(*), 0)),'%Hh %im') as "average time",
count(*) as entries
from epcms_uni_equalization_entry
inner join epcms_user on epcms_user.userid = epcms_uni_equalization_entry.userid
inner join epcms_uni_equalization on epcms_uni_equalization.equalizationid = epcms_uni_equalization_entry.equalizationid
inner join epcms_uni_equalization_pc on (epcms_uni_equalization.equalizationid = epcms_uni_equalization_pc.equalizationid && epcms_uni_equalization_entry.userid = epcms_uni_equalization_pc.to_userid)
where epcms_uni_equalization_entry.entry_status > 0 && epcms_uni_equalization_pc.signed > 0 &&
epcms_uni_equalization_pc.pc_status > 0 && epcms_uni_equalization_entry.equalizationid not in (3,5) &&
epcms_uni_equalization_pc.assign_time < epcms_uni_equalization_pc.sign_time && CONCAT_WS('', year,semester) = 20171
group by epcms_uni_equalization_entry.userid order by average_response ASC, entries DESC;
-- Top by entries
select CONCAT_WS(' ', epcms_user.firstname, epcms_user.lastname) as name, count(*) as entries
from epcms_uni_equalization_entry
inner join epcms_user on epcms_user.userid = epcms_uni_equalization_entry.userid
inner join epcms_uni_equalization on epcms_uni_equalization.equalizationid = epcms_uni_equalization_entry.equalizationid
inner join epcms_uni_equalization_pc on (epcms_uni_equalization.equalizationid = epcms_uni_equalization_pc.equalizationid && epcms_uni_equalization_entry.userid = epcms_uni_equalization_pc.to_userid)
where epcms_uni_equalization_entry.entry_status > 0 && epcms_uni_equalization_pc.signed > 0 &&
epcms_uni_equalization_pc.pc_status > 0 && epcms_uni_equalization_entry.equalizationid not in (3,5) &&
epcms_uni_equalization_pc.assign_time < epcms_uni_equalization_pc.sign_time
group by epcms_uni_equalization_entry.userid order by entries DESC;
-- Specialization level
select * from epcms_uni_equalization inner join epcms_uni_equalization_entry on epcms_uni_equalization.equalizationid = epcms_uni_equalization_entry.equalizationid where epcms_uni_equalization.collegeid != 0 && local_course not in ('EF001', 'EF002', 'EF003', 'IT100', 'MA100')
group by epcms_uni_equalization.equalizationid;
-- Foundation Level
select * from epcms_uni_equalization where equalizationid not in (select epcms_uni_equalization.equalizationid from epcms_uni_equalization inner join epcms_uni_equalization_entry on epcms_uni_equalization.equalizationid = epcms_uni_equalization_entry.equalizationid where epcms_uni_equalization.collegeid != 0 && local_course not in ('EF001', 'EF002', 'EF003', 'IT100', 'MA100') group by epcms_uni_equalization.equalizationid) && status not in (0,5) && CONCAT_WS('', year,semester) = 20171 && collegeid != 0 order by epcms_uni_equalization.name;
-- [/equalization statistics] --
-- Graduate update
select epcms_uni_student.collegeid, epcms_uni_student.name_en, epcms_uni_student.name_ar, epcms_uni_student.specialization_en, epcms_uni_student_extradata.email, epcms_uni_student_extradata.training_status, epcms_uni_student_extradata.work_status, epcms_uni_student_extradata.work_sector, epcms_uni_student_extradata.work_start, epcms_uni_student_extradata.work_situation, epcms_uni_student_extradata.job_title, epcms_uni_student_extradata.work_place, FROM_UNIXTIME(epcms_uni_student_extradata.update_time) as update_time from epcms_uni_student_extradata inner join epcms_uni_student on epcms_uni_student_extradata.collegeid = epcms_uni_student.collegeid where FROM_UNIXTIME(epcms_uni_student_extradata.update_time) >= '2017-03-13 00:00:00' order by epcms_uni_student_extradata.update_time DESC;
-- HEAC :
select epcms_uni_student.collegeid, epcms_uni_student.civil_id, epcms_uni_student.name_en, epcms_uni_student.specialization_en, (select GROUP_CONCAT(course_code) from epcms_uni_student_grade where epcms_uni_student_grade.collegeid = epcms_uni_student.collegeid && epcms_uni_student_grade.year = 2015 && grade != 'w' ) as courses from epcms_uni_student where epcms_uni_student.collegeid in (
110585,120306,130435,130185,140295,110469,140508,130234,131065,150727,150785,150744,110497,130948,140700,120263,110219,110540,140362,140263,90323,120156,140541,120201,81170,110708,131012,150427,140861,150713,150084,120121,130286,120389,140407,110476,130862,130932,150582,120228,140775,130928,130922,131011,120578,131098,130890,140706,140369,130953,130556,130320,150732,90838,140455,150780,131003,140924,120600,150798,120211,120250,150320,150674,150370,140458
) && epcms_uni_student.collegeid in (select epcms_uni_student_grade.collegeid from epcms_uni_student_grade where epcms_uni_student_grade.collegeid = epcms_uni_student.collegeid && epcms_uni_student_grade.year = 2015 && grade != 'w' ) order by name_en;
select epcms_uni_student.collegeid, epcms_uni_student.civil_id, epcms_uni_student.name_en, epcms_uni_student.specialization_en, (select GROUP_CONCAT(course_code) from epcms_uni_student_grade where epcms_uni_student_grade.collegeid = epcms_uni_student.collegeid && epcms_uni_student_grade.year = 2015 && grade != 'w' ) as courses from epcms_uni_student where epcms_uni_student.collegeid in (
110585,120306,130435,130185,140295,110469,140508,130234,131065,150727,150785,150744,110497,130948,140700,120263,110219,110540,140362,140263,90323,120156,140541,120201,81170,110708,131012,150427,140861,150713,150084,120121,130286,120389,140407,110476,130862,130932,150582,120228,140775,130928,130922,131011,120578,131098,130890,140706,140369,130953,130556,130320,150732,90838,140455,150780,131003,140924,120600,150798,120211,120250,150320,150674,150370,140458
) && epcms_uni_student.collegeid not in (select epcms_uni_student_grade.collegeid from epcms_uni_student_grade where epcms_uni_student_grade.collegeid = epcms_uni_student.collegeid && epcms_uni_student_grade.year = 2015 && grade != 'w') order by name_en;
-- Last Semester
select epcms_uni_student.collegeid, epcms_uni_student.name_ar, epcms_uni_student.specialization_ar, epcms_uni_student.study_status, (select max(CONCAT_WS('|', year, semester)) from epcms_uni_student_grade where epcms_uni_student_grade.collegeid = epcms_uni_student.collegeid && grade != 'w' ) as last_semester from epcms_uni_student where epcms_uni_student.collegeid in (120269,120657,150679,120508,140314,140720,120716,140908,150738,131051,150628,150845,140938,150235,120139,150626,150397,150550,140937,110583,140579,160213,150551) order by name_ar;
-- Master Students in 3rd and 4th semesters
select collegeid, name_en, if(gender = 1, 'Female', 'Male') as gender, specialization_en, (select count(*) as total from epcms_uni_student_status where epcms_uni_student_status.collegeid = .epcms_uni_student.collegeid && reg_status = 1) as semesters from epcms_uni_student where specialization_en like 'Master%' && (select count(*) as total from epcms_uni_student_status where epcms_uni_student_status.collegeid = .epcms_uni_student.collegeid && reg_status = 1) in (3,4) order by specialization_en ASC, name_en ASC;
-- CMS log : Equalization
select logid, FROM_UNIXTIME(logtime), url, ip_address, extra from epcms_log where
url like '%module/aou/equalization?ajaxMethod%' &&
url not like '%admin%' &&
extra not like '%"POST";a:0:%'
order by logid DESC;
-- EPCMS log by IP
select *, count(*) as total_per_ip from epcms_log where userid > 0 group by ip_address order by total_per_ip DESC;
-- Makeup check
select epcms_uni_student.name_en, epcms_uni_student_grade.*, epcms_uni_course.makeup_exam from epcms_uni_student_grade inner join
epcms_uni_student on epcms_uni_student_grade.collegeid = epcms_uni_student.collegeid inner join
epcms_uni_course on epcms_uni_course.code = epcms_uni_student_grade.course_code where
year = 2017 && semester = 1 && total_midexam > 0 && CONCAT_WS('|', epcms_uni_student_grade.collegeid, course_code) in (select CONCAT_WS('|', collegeid, course_code) from epcms_uni_temp_exam_postpone) order by epcms_uni_course.makeup_exam ASC, epcms_uni_student_grade.collegeid ASC;
select *, count(*) as total from epcms_uni_student_grade where year = 2016 && semester = 2 && total_midexam > 0 && CONCAT_WS('|', collegeid, course_code) in (select CONCAT_WS('|', collegeid, course_code) from epcms_uni_temp_exam_postpone) group by collegeid order by total DESC, collegeid ASC;
-- GCC countries (some of them)
select epcms_uni_student.collegeid, civil_id, passport_number, name_ar, specialization_ar, nationality_code, nationality_ar from epcms_uni_student left join epcms_uni_student_extradata on epcms_uni_student.collegeid = epcms_uni_student_extradata.collegeid where study_status in (1,2,4) && nationality_code in (1,6,8) order by name_ar ASC;
-- Private students (ol and full time)
select collegeid as "Student ID", name_en, specialization_en, name_ar, specialization_ar from epcms_uni_student where collegeid >= 160000 && specialization_en not like 'Master%' && (study_nature = 1 || study_nature = 5 && accept_nature = 6) order by collegeid ASC;
-- Auto password reset
select epcms_uni_student.collegeid, epcms_uni_student.name_ar, epcms_uni_password_reset.*, from_unixtime(response_time) from epcms_uni_password_reset inner join epcms_uni_student on epcms_uni_password_reset.collegeid = epcms_uni_student.collegeid where request_time = response_time ;
-- TEMP
select collegeid, name_en, specialization_en, name_ar, specialization_ar from epcms_uni_student where collegeid in (160146,160308,160338,160344,160395,160417,160418,160557,160626,160628,160630);
-- Failed more then 2 times
select *, count(*) as fails from epcms_uni_student_grade inner join epcms_uni_student on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where study_status in (1,2,4) && study_nature = 5 && accept_nature = 4 && epcms_uni_student_grade.grade like '%F%' group by concat_ws('|', epcms_uni_student.collegeid, course_code) ;
-- Failed more then 2 times
select * from (
select epcms_uni_student.collegeid, epcms_uni_student.name_ar, epcms_uni_student.specialization_ar, epcms_uni_student_grade.course_code, GROUP_CONCAT( concat_ws('|', year,semester)) as failed_semesters, count(*) as fails from epcms_uni_student_grade inner join epcms_uni_student on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where study_status in (1,2,4) && study_nature = 5 && accept_nature = 4 && epcms_uni_student_grade.grade like '%F%' && epcms_uni_student.courses != '' group by concat_ws('|', epcms_uni_student.collegeid, course_code)
) tbl where fails >= 2;
-- Failed more then 2 times for registered course
select * from (
select epcms_uni_student.collegeid, epcms_uni_student.name_ar, epcms_uni_student.specialization_ar, epcms_uni_student_grade.course_code, GROUP_CONCAT( concat_ws('|', year,semester)) as failed_semesters, count(*) as fails from epcms_uni_student_grade inner join epcms_uni_student on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where study_status in (1,2,4) && study_nature = 5 && accept_nature = 4 && epcms_uni_student_grade.grade like '%F%' && epcms_uni_student.courses != '' && concat_ws('|', year, semester) != '2016|2' && concat_ws('|', epcms_uni_student.collegeid, course_code) in (
select concat_ws('|', epcms_uni_student.collegeid, course_code) as idwc from epcms_uni_student_grade inner join epcms_uni_student on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where study_status in (1,2,4) && study_nature = 5 && accept_nature = 4 && epcms_uni_student_grade.grade like '%F%' && year = 2016 && semester = 1
) group by concat_ws('|', epcms_uni_student.collegeid, course_code)
) tbl where fails >= 2;
-- courses without marks
select *, count(*) as students from epcms_uni_student_grade where year = 2016 && semester = 2 && grade = '' && total_final = 0 && course_code not in ('EF001', 'EF002', 'EF003', 'IT100', 'MA100') && concat_ws('|', course_code, section) not in (
select concat_ws('|', course_code, section) as students from epcms_uni_student_grade where grade = '' && year = 2016 && semester = 2 && total_final > 0 && course_code not in ('EF001', 'EF002', 'EF003', 'IT100', 'MA100') group by concat_ws('|', course_code, section)
) group by concat_ws('|', course_code, section);
-- compare expected to graduate students after marks
select epcms_uni_student.collegeid, epcms_uni_student.name_en, epcms_uni_student.specialization_en, epcms_uni_student.gpa, GROUP_CONCAT(epcms_uni_student_grade.course_code SEPARATOR ' | ') as courses,
GROUP_CONCAT(epcms_uni_student_grade.total_mta + epcms_uni_student_grade.total_midexam SEPARATOR ' | ') as ca, GROUP_CONCAT(epcms_uni_student_grade.total_final SEPARATOR ' | ') as total_final,
GROUP_CONCAT(round(epcms_uni_student_grade.total_mta + epcms_uni_student_grade.total_midexam + epcms_uni_student_grade.total_final) SEPARATOR ' | ') as total_mark,
GROUP_CONCAT(if(epcms_uni_student_grade.total_mta + epcms_uni_student_grade.total_midexam >= 15 && epcms_uni_student_grade.total_final >= 20 && round(epcms_uni_student_grade.total_mta + epcms_uni_student_grade.total_midexam + epcms_uni_student_grade.total_final) >= 50, 'Passed', 'Failed') SEPARATOR ' | ') as status
from epcms_uni_student inner join epcms_uni_student_grade on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where epcms_uni_student.collegeid in () && CONCAT_WS('|', epcms_uni_student_grade.year, epcms_uni_student_grade.semester) = (select max(CONCAT_WS('|', year, semester)) from epcms_uni_student_grade grd where grd.collegeid = epcms_uni_student_grade.collegeid) group by epcms_uni_student.collegeid order by epcms_uni_student.specialization_en ASC, epcms_uni_student.name_en ASC;
-- compare expected to graduate after marks with SIS list
select collegeid, name_ar, specialization_ar, ch_mandatory, ch_elective, ch_mandatory_done, ch_elective_done, (ch_mandatory - ch_mandatory_done) as chml, (ch_elective - ch_elective_done) as chel, (ch_mandatory - ch_mandatory_done) + (ch_elective - ch_elective_done) as tchl, credit_hours, courses, left_courses, completed_courses, gpa, study_status from epcms_uni_student where collegeid in () && collegeid not in () && study_status != 8 order by specialization_en, name_ar;
-- Get total marks
select *, (total_mta + total_midexam + total_final) as total from epcms_uni_student_grade where collegeid = 120258 /*&& course_code like 'm27%' */ order by concat_ws('|', year, semester);
-- Courses for current semester:
select course_code from epcms_uni_student_grade where year = 2016 && semester = 3 group by course_code order by course_code ASC;
-- Discontinued students in specific semester
select epcms_uni_student.collegeid, epcms_uni_student.civil_id, epcms_uni_student.name_ar, epcms_uni_student_status.std_study_status as status from epcms_uni_student inner join epcms_uni_student_status on epcms_uni_student.collegeid = epcms_uni_student_status.collegeid where epcms_uni_student.study_status in (1,2,4) && epcms_uni_student.accept_nature = 4 && epcms_uni_student.study_nature = 5 && /*epcms_uni_student_status.reg_counter = 0 &&*/ std_study_status != 1 && epcms_uni_student_status.year = 2016 && epcms_uni_student_status.semester = 2;
-- Registered students in specific semester (Excluding ministry)
select collegeid from epcms_uni_student where (study_nature != 5 || accept_nature != 4 && study_nature = 5) && collegeid in (select collegeid from epcms_uni_student_grade where year = 2016 && semester = 3);
-- OL students registered in FT
select * from epcms_uni_student inner join epcms_uni_student_grade on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where epcms_uni_student.study_nature != 5 && epcms_uni_student_grade.year = 2016 && epcms_uni_student_grade.semester = 3 && epcms_uni_student_grade.section >= 60 ;
-- FT students registered in OL
select * from epcms_uni_student inner join epcms_uni_student_grade on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where epcms_uni_student.study_nature = 5 && epcms_uni_student_grade.year = 2016 && epcms_uni_student_grade.semester = 3 && epcms_uni_student_grade.section < 60 ;
-- Students finished certian hours
select collegeid as "Student ID", name_en, specialization_en, name_ar, specialization_ar, epcms_uni_courses_credit(completed_courses) as "Finished Hours", gpa, if(study_nature = 5 && accept_nature = 4, 'قبول موحد', 'تعلم مفتوح') as stype from epcms_uni_student where specialization_ar like 'تقنية%' && epcms_uni_courses_credit(completed_courses) >= 90 && study_status in (1,2,4) && collegeid >= 140000 order by specialization_en ASC, name_en ASC;
-- FT (ministry) students who didn't register EF003
select collegeid as "Student ID", civil_id, name_en, specialization_en, name_ar, specialization_ar, gpa, courses from epcms_uni_student where study_status in (1,2,4) && study_nature = 5 && accept_nature = 4 && collegeid in
(select collegeid from epcms_uni_student_grade where course_code in ('EF002', 'EF002') && grade like '%S%')
&& collegeid not in
(select collegeid from epcms_uni_student_grade where course_code in ('EF003') && grade like '%S%')
&& locate('EF003', courses) = 0
order by specialization_en ASC, name_en ASC;
-- FT (ministry) students who didn't register in the first or second semester
select collegeid as "Student ID", civil_id, name_en, specialization_en, name_ar, specialization_ar, gpa, courses from epcms_uni_student where study_status in (1,2,4) && study_nature = 5 && accept_nature = 4 && collegeid in
(select collegeid from epcms_uni_student_grade where course_code in ('EL111', 'AR111', 'GR101') )
&& collegeid in
(select collegeid from epcms_uni_student_status where year = 2016 && semester = 2 && std_study_status = 2)
&& locate('EF003', courses) = 0
order by specialization_en ASC, name_en ASC;
-- Students without ED698 // -- ED621, 241, 423
select collegeid as "Student ID", name_en, specialization_en, name_ar, specialization_ar, gpa, left_credit, completed_courses,
if((select count(*) as total from epcms_uni_student_grade where course_code in ('ED423', 'ED221', 'ED241') && epcms_uni_student_grade.collegeid = epcms_uni_student.collegeid) > 0, 'غير تربوي', 'تربوي') as stype,
if((select count(*) as total from epcms_uni_student_grade where course_code in ('ED423', 'ED221', 'ED241') && epcms_uni_student_grade.collegeid = epcms_uni_student.collegeid) > 0, (ch_mandatory + ch_elective) + 9, (ch_mandatory + ch_elective)) as calculated_ch,
(ch_mandatory_done + ch_elective_done) as calculated_ch_done
from epcms_uni_student where specialization_ar like '%تربية%' && study_status in (1,2,4,7) &&
if((select count(*) as total from epcms_uni_student_grade where course_code in ('ED423', 'ED221', 'ED241') && epcms_uni_student_grade.collegeid = epcms_uni_student.collegeid), (ch_mandatory + ch_elective) - (ch_mandatory_done + ch_elective_done) + 9, (ch_mandatory + ch_elective) - (ch_mandatory_done + ch_elective_done)) <= 0 && locate('ED698', completed_courses) = 0 && gpa >= 3 order by specialization_en ASC, name_en ASC;
-- Students with OUVS / OUID in certain semesters
select collegeid, ouvs_code,
if((select count(*) as total from epcms_uni_student_status where epcms_uni_student_status.collegeid = epcms_uni_student.collegeid && CONCAT_WS('|', year,semester) in ('2015|3', '2016|1', '2016|2') && reg_status = 1 group by collegeid ) > 0, 'Y', 'No') as active,
if((select count(*) as total from epcms_uni_student_grade where epcms_uni_student_grade.collegeid = epcms_uni_student.collegeid && course_code in ('B120', 'B121', 'B122', 'B140', 'B141', 'B203A', 'B203B', 'B291', 'B292', 'B301A', 'B301B', 'B321', 'B322', 'B324', 'B325', 'B628', 'B713', 'B820', 'B830', 'DD202A', 'DD202B', 'DD309A', 'DD309B', 'LB160', 'M248', 'MU123', 'T205A', 'T205B', 'T306A', 'T306B', 'M150A', 'M150B', 'M253', 'M256', 'M257', 'M359', 'M362', 'M363', 'M876', 'M883', 'T175A', 'T175B', 'T215A', 'T215B', 'T216A', 'T216B', 'T324', 'T325', 'T471', 'TT284', 'A150', 'A230A', 'A230B', 'A801', 'A802', 'A803', 'A804', 'A805', 'A806', 'A807', 'A808', 'A817', 'AA100A', 'AA100B', 'E301A', 'E301B', 'E303A', 'E303B', 'EA300A', 'EA300B', 'EL120', 'EL121', 'U214A', 'U214B', 'B716', 'B716A', 'B716B', 'B839', 'BB31', 'BB835', 'B859', 'B863', 'BB863', 'BB841', 'BB844', 'BB845', 'EL119', 'EL117', 'ED618', 'ED623', 'ED627', 'ED631', 'ED632', 'ED633', 'ED634', 'ED635', 'ED636', 'ED640', 'ED698') && CONCAT_WS('|', year,semester) in ('2015|3', '2016|1', '2016|2') && grade != 'W' group by collegeid ), 'Y', 'No') as has_OU_course
from epcms_uni_student where collegeid in (
140851,150864,160249,160400,160633,160622,130893,140715,140847,150358,150534,150835,150836,150842,150844,150855,150863,160202,160240,160260,160280,160289,160310,160344,160350,160388,160392,160435,160533,160556,160578,160582,160611,160656,160662,160663,160535,140322,140721,150160,150367,150720,150866,160256,160271,160402,160595,160638,160693,150688,150748,150843,160149,160585,160603,140844,140918,150663,150832,150838,150839,150840,150857,160152,160198,160293,160320,160606,160537
)
order by field(collegeid,
140851,150864,160249,160400,160633,160622,130893,140715,140847,150358,150534,150835,150836,150842,150844,150855,150863,160202,160240,160260,160280,160289,160310,160344,160350,160388,160392,160435,160533,160556,160578,160582,160611,160656,160662,160663,160535,140322,140721,150160,150367,150720,150866,160256,160271,160402,160595,160638,160693,150688,150748,150843,160149,160585,160603,140844,140918,150663,150832,150838,150839,150840,150857,160152,160198,160293,160320,160606,160537);
-- Statistics:
select * from epcms_uni_student_status tbl where CONCAT_WS('|', year, semester) = '2015|1' && std_study_status != (select std_study_status from epcms_uni_student_status where epcms_uni_student_status.collegeid = tbl.collegeid && CONCAT_WS('|', year, semester) = '2014|2') && std_study_status in (9);
-- Statistics:
select * from epcms_uni_student_status where CONCAT_WS('|', year, semester) = '2015|2' && std_study_status = 6 && collegeid in (
select collegeid from (select *, count(*) as total from epcms_uni_student_status where CONCAT_WS('|', year, semester) <= '2013|2' && std_study_status in (2,4) group by collegeid) tbl where total = 4);
-- Statistics:
select epcms_uni_student_status.*, epcms_uni_student.specialization_en from epcms_uni_student_status inner join epcms_uni_student on epcms_uni_student.collegeid = epcms_uni_student_status.collegeid where CONCAT_WS('|', year, semester) = '2015|1' && std_study_status = 6 && epcms_uni_student_status.collegeid in (
select collegeid from (select *, count(*) as total from epcms_uni_student_status where CONCAT_WS('|', year, semester) <= '2013|1' && std_study_status in (2,4) group by collegeid) tbl where total = 4) && epcms_uni_student.specialization_ar like '%أعمال%' && epcms_uni_student.specialization_ar not like '%تقنية%' && epcms_uni_student.specialization_ar not like '%اللغة%';
-- Certian semester students
select collegeid, name_ar, specialization_ar from epcms_uni_student where beginning_year = 2016 && beginning_semester = 3 order by collegeid ASC;
-- Students per semester and specialization (based on epcms_uni_student_grade table)
select specialization_ar,
(select count(*) as total from epcms_uni_student subtbl where subtbl.specialization_ar = tbl.specialization_ar && collegeid in (select collegeid from epcms_uni_student_grade where year = 2017 && semester = 1 group by collegeid) group by specialization_ar) as "First",
(select count(*) as total from epcms_uni_student subtbl where subtbl.specialization_ar = tbl.specialization_ar && collegeid in (select collegeid from epcms_uni_student_grade where year = 2017 && semester = 2 group by collegeid) group by specialization_ar) as "Second",
(select count(*) as total from epcms_uni_student subtbl where subtbl.specialization_ar = tbl.specialization_ar && collegeid in (select collegeid from epcms_uni_student_grade where year = 2017 && semester = 3 group by collegeid) group by specialization_ar) as "Summer"
from epcms_uni_student tbl group by specialization_ar order by specialization_ar ASC;
-- Students per semester and specialization (based on epcms_uni_student_status table)
select specialization_ar,
(select count(*) as total from epcms_uni_student subtbl where subtbl.specialization_ar = tbl.specialization_ar && collegeid in (select collegeid from epcms_uni_student_status where year = 2016 && semester = 1 && std_study_status in (1,2,4,7) group by collegeid) group by specialization_ar) as "First",
(select count(*) as total from epcms_uni_student subtbl where subtbl.specialization_ar = tbl.specialization_ar && collegeid in (select collegeid from epcms_uni_student_status where year = 2016 && semester = 2 && std_study_status in (1,2,4,7) group by collegeid) group by specialization_ar) as "Second",
(select count(*) as total from epcms_uni_student subtbl where subtbl.specialization_ar = tbl.specialization_ar && collegeid in (select collegeid from epcms_uni_student_status where year = 2016 && semester = 3 && std_study_status in (1,2,4,7) group by collegeid) group by specialization_ar) as "Summer"
from epcms_uni_student tbl group by specialization_ar order by specialization_ar ASC;
-- Student Letters statistics without duplicated college in <= 10 minutes | per userid
select tbl.userid, if(CONCAT_WS(' ', firstname, lastname) = "", username, CONCAT_WS(' ', firstname, lastname)) as name, title as "User Group", count(*) as total from (
select * from epcms_uni_archive group by CONCAT_WS('|', collegeid, archive_time div 600)
) tbl left join epcms_user on epcms_user.userid = tbl.userid inner join epcms_usergroup on epcms_user.usergroup = epcms_usergroup.usergroupid group by tbl.userid order by total DESC;
-- Student Letters statistics without duplicated college in <= 10 minutes | per userid and messageid
select tbl.userid, if(CONCAT_WS(' ', firstname, lastname) = "", username, CONCAT_WS(' ', firstname, lastname)) as name, title as "User Group", count(*) as total from (
select * from epcms_uni_archive group by CONCAT_WS('|', collegeid, messageid, archive_time div 600)
) tbl left join epcms_user on epcms_user.userid = tbl.userid inner join epcms_usergroup on epcms_user.usergroup = epcms_usergroup.usergroupid group by tbl.userid order by total DESC;
-- Student Letters statistics without duplicated college in <= 10 minutes | per collegeid
select tbl.collegeid, if(name_ar != "", name_ar, "NOT STUDENTS") as name, count(*) as total from (
select * from epcms_uni_archive group by CONCAT_WS('|', collegeid, archive_time div 600)
) tbl left join epcms_uni_student on epcms_uni_student.collegeid = tbl.collegeid group by tbl.collegeid order by total DESC;
-- Student Letters statistics without duplicated college in <= 10 minutes | per collegeid and messageid
select tbl.collegeid, if(name_ar != "", name_ar, "NOT STUDENTS") as name, count(*) as total from (
select * from epcms_uni_archive group by CONCAT_WS('|', collegeid, messageid, archive_time div 600)
) tbl left join epcms_uni_student on epcms_uni_student.collegeid = tbl.collegeid group by tbl.collegeid order by total DESC;
-- Connected Devices List
select *, from_unixtime(create_time) as create_time, from_unixtime(last_used) as last_used from epcms_uni_device_token order by last_used ASC;
-- Connected Devices, grouped by type
select device, count(*) as total, from_unixtime(last_used) as last_used from epcms_uni_device_token where collegeid != 160652 group by device;
-- Students installed the app and OS type
select epcms_uni_student.collegeid, epcms_uni_student.name_ar, epcms_uni_student.specialization_ar, epcms_uni_student.nationality_ar , group_concat(epcms_uni_device_token.device) as device, from_unixtime(epcms_uni_device_token.create_time) as create_time, from_unixtime(epcms_uni_device_token.last_used) as last_used from epcms_uni_student inner join epcms_uni_device_token on epcms_uni_student.collegeid = epcms_uni_device_token.collegeid
group by collegeid
order by
-- epcms_uni_student.collegeid ASC
specialization_en ASC, name_ar ASC
-- last_used DESC
-- nationality_ar ASC
;
-- Connected Devices log within 5 min
select * from epcms_log where url like '%api.aou.im%' && ip_address not like '96.9.135.7' && extra not like '160652' group by concat_ws('|', extra, logtime div 600) ;
-- Tokens per student:
select *, from_unixtime(last_used) as last_used_readable from epcms_uni_device_token where collegeid = 170280 order by last_used ASC;
delete from epcms_uni_device_token where tokenid = 1999;
-- Students don't have mobile app and graudated last 2 semesters
select * from epcms_uni_student where collegeid not in (select collegeid from epcms_uni_device_token) &&
(study_status in (1,2,4,7) || collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (7,8) && year = 2016 && semester in (1,2) ) && last_year = 2016 && last_semester in (1,2) ) ;
-- Master in Education : Student finished pre-requirment : تكنولوجيا
select * from epcms_uni_student where specialization_ar like '%تكنولوجيا%' && study_status in (1,2,4,6) && locate('ED221', completed_courses) > 0 && locate('ED241', completed_courses) > 0 && locate('ED423', completed_courses) > 0
&& locate('ED631', completed_courses) = 0 && locate('ED632', completed_courses) = 0 && locate('ED633', completed_courses) = 0;
-- Master in Education : Student finished pre-requirment : قيادة
select * from epcms_uni_student where specialization_ar like '%قيادة%' && study_status in (1,2,4,6) && locate('ED221', completed_courses) > 0 && locate('ED241', completed_courses) > 0 && locate('ED423', completed_courses) > 0
&& locate('ED631', completed_courses) = 0 && locate('ED633', completed_courses) = 0 && locate('ED613', completed_courses) = 0;
-- Students for test placement test:
select collegeid from epcms_uni_student where student_email2 != '' && collegeid >= 170000 && study_nature = 5 && accept_nature = 4 && locate('EF001', completed_courses) = 0 && locate('EF002', completed_courses) = 0 limit 18;
select collegeid as "Student ID", name_en, specialization_en, lower(student_email2) as 'Email' from epcms_uni_student where student_email2 != '' && collegeid in (
170025,170042,170047,170102,170111,170149,170161,170163,170013,170017,170018,170021,170024,170028,170032,170050,170053,170054,170062,170064,170090,170092,170094,170095,170096,170097,170138,170142,170148,170154,170155,170157,170014,170015,170016,170019,170020,170022,170023,170026,170027,170029,170030,170031,170033,170034,170035,170036,170038,170039
);
-- Student didn't receive their ID
select * from epcms_uni_student where study_status in (1,2,4) && beginning_semester = 3 && beginning_year = 2016 && collegeid not in (select collegeid from epcms_uni_student_log where note like 'IDCARD:REVEICED' );
-- Full Time student who discontinue in a specific semester
select collegeid, civil_id, name_en, name_ar, specialization_en, specialization_ar, "NONE" as "semester 2|2016", if(courses = '', "NONE", courses) as "semester 1|2017" from epcms_uni_student where study_status in (1,2,4) && study_nature = 5 && accept_nature = 4 && collegeid < 170000 && collegeid not in (select collegeid from epcms_uni_student_grade where year = 2016 && semester = 2);
-- LRC: students for borowing system
select collegeid as "OMCID", DATE_FORMAT(NOW(), '%d-%m-%Y') as dateadded, name_en as name, "STUDENT" as type, "" as designation, student_email as address, mobile_number as memtelr, home_tel as memtelr, "0" as block, "0" as deleted
from epcms_uni_student where collegeid >= 170000 && collegeid < 900000;
select collegeid as "OMCID", specialization_en from epcms_uni_student order by collegeid ASC;
-- Update >= 900000 for birthday
update epcms_uni_student set study_status = 1, birthday = DATE_FORMAT(NOW(), '%Y-%m-%d') where collegeid >= 900000;
-- Open University and old IDS:
select collegeid, ouvs_code, civil_id, name_ar,
(select group_concat(collegeid) from epcms_uni_student where collegeid != tbl.collegeid && civil_id = tbl.civil_id) as other_id1,
(select group_concat(collegeid) from epcms_uni_student where collegeid != tbl.collegeid && mobile_number = tbl.mobile_number) as other_id2,
(select group_concat(ouvs_code) from epcms_uni_student where collegeid != tbl.collegeid && civil_id = tbl.civil_id) as ouvs_code1,
(select group_concat(ouvs_code) from epcms_uni_student where collegeid != tbl.collegeid && mobile_number = tbl.mobile_number) as ouvs_code2,
completed_courses
from epcms_uni_student tbl where collegeid in (
140844,140905,150663,150832,150838,150839,150840,150852,150857,160152,160198,160293,160320,160606,160698,170183,170393,170394,150738,150795,150843,160149,160167,160275,160489,160585,160603,170333,170416,140851,150864,160264,160265,160400,160633,170309,130893,140715,150154,150358,150362,150534,150665,150734,150835,150836,150842,150844,150846,150855,150863,160202,160240,160260,160280,160289,160310,160344,160350,160388,160392,160430,160435,160473,160533,160541,160555,160556,160558,160578,160580,160582,160611,160622,160655,160656,160658,160662,160663,160667,160681,160692,160696,160699,160700,160702,170164,170196,170232,170264,170268,170350,170386,160535,140322,150367,150720,150866,160245,160271,160402,160595,160638,160693,160701,170282,160537,130603,130857,140463,140582,140613,140678,140732,140749,140755,140776,140838,140852,140864,140872,140876,140883,150237,150238,150252,150267,150270,150283,150300,150323,150338,150342,150355,150388,150389,150390,150404,150422,150425,150426,150435,150439,150453,150455,150496,150499,150518,130250
) order by field(collegeid,
140844,140905,150663,150832,150838,150839,150840,150852,150857,160152,160198,160293,160320,160606,160698,170183,170393,170394,150738,150795,150843,160149,160167,160275,160489,160585,160603,170333,170416,140851,150864,160264,160265,160400,160633,170309,130893,140715,150154,150358,150362,150534,150665,150734,150835,150836,150842,150844,150846,150855,150863,160202,160240,160260,160280,160289,160310,160344,160350,160388,160392,160430,160435,160473,160533,160541,160555,160556,160558,160578,160580,160582,160611,160622,160655,160656,160658,160662,160663,160667,160681,160692,160696,160699,160700,160702,170164,170196,170232,170264,170268,170350,170386,160535,140322,150367,150720,150866,160245,160271,160402,160595,160638,160693,160701,170282,160537,130603,130857,140463,140582,140613,140678,140732,140749,140755,140776,140838,140852,140864,140872,140876,140883,150237,150238,150252,150267,150270,150283,150300,150323,150338,150342,150355,150388,150389,150390,150404,150422,150425,150426,150435,150439,150453,150455,150496,150499,150518,130250
);
-- International Students
select nationality_ar,
(select count(*) from epcms_uni_student where nationality_code = tbl.nationality_code && gender = 1 && collegeid in (select collegeid from epcms_uni_student_grade where year = 2016)) as female,
(select count(*) from epcms_uni_student where nationality_code = tbl.nationality_code && gender = 2 && collegeid in (select collegeid from epcms_uni_student_grade where year = 2016)) as male, count(*) as total
from epcms_uni_student tbl where collegeid in (
select collegeid from epcms_uni_student_grade where year = 2016
) && nationality_code != 10 group by nationality_code order by total DESC;
select specialization_ar,
(select count(*) from epcms_uni_student where nationality_code = tbl.nationality_code && gender = 1 && collegeid in (select collegeid from epcms_uni_student_grade where year = 2016)) as male,
(select count(*) from epcms_uni_student where nationality_code = tbl.nationality_code && gender = 2 && collegeid in (select collegeid from epcms_uni_student_grade where year = 2016)) as female, count(*) as total
from epcms_uni_student tbl where collegeid in (
select collegeid from epcms_uni_student_grade where year = 2016
) && nationality_code != 10 group by specialization_ar order by specialization_ar ASC;
select FLOOR(DATEDIFF(now(), birthday) / 365.25) as age,
(select count(*) from epcms_uni_student where nationality_code = tbl.nationality_code && gender = 1 && collegeid in (select collegeid from epcms_uni_student_grade where year = 2016)) as female,
(select count(*) from epcms_uni_student where nationality_code = tbl.nationality_code && gender = 2 && collegeid in (select collegeid from epcms_uni_student_grade where year = 2016)) as male, count(*) as total
from epcms_uni_student tbl where collegeid in (
select collegeid from epcms_uni_student_grade where year = 2016
) && nationality_code != 10 && (ch_mandatory_done + ch_elective_done) >= 0 && (ch_mandatory_done + ch_elective_done) < 32;
select FLOOR(DATEDIFF(now(), birthday) / 365.25) as age,
(select count(*) from epcms_uni_student where nationality_code = tbl.nationality_code && gender = 1 && collegeid in (select collegeid from epcms_uni_student_grade where year = 2016)) as female,
(select count(*) from epcms_uni_student where nationality_code = tbl.nationality_code && gender = 2 && collegeid in (select collegeid from epcms_uni_student_grade where year = 2016)) as male, count(*) as total
from epcms_uni_student tbl where collegeid in (
select collegeid from epcms_uni_student_grade where year = 2016
) && nationality_code != 10 && FLOOR(DATEDIFF(now(), birthday) / 365.25) >= 26 && FLOOR(DATEDIFF(now(), birthday) / 365.25) <= 30;
select nationality_ar,
(select count(*) from epcms_uni_student where nationality_code = tbl.nationality_code && gender = 1 && collegeid in (select collegeid from epcms_uni_student_grade where year = 2011)) as female,
(select count(*) from epcms_uni_student where nationality_code = tbl.nationality_code && gender = 2 && collegeid in (select collegeid from epcms_uni_student_grade where year = 2011)) as male, count(*) as total
from epcms_uni_student tbl where collegeid in (
select collegeid from epcms_uni_student_grade where year = 2011
) && nationality_code != 10;
select is_sponsored,
(select count(*) from epcms_uni_student where nationality_code = tbl.nationality_code && gender = 1 && collegeid in (select collegeid from epcms_uni_student_grade where year = 2016)) as female,
(select count(*) from epcms_uni_student where nationality_code = tbl.nationality_code && gender = 2 && collegeid in (select collegeid from epcms_uni_student_grade where year = 2016)) as male, count(*) as total
from epcms_uni_student tbl where collegeid in (
select collegeid from epcms_uni_student_grade where year = 2016
) && nationality_code != 10 group by is_sponsored order by total DESC;
select *, is_sponsored
from epcms_uni_student tbl where collegeid in (
select collegeid from epcms_uni_student_grade where year = 2016
) && nationality_code != 10 order by is_sponsored DESC;
-- Statistics:
set @year= 2015; -- By Gender
select @year as year, (select count(*) as total from epcms_uni_student where gender = 1 && collegeid in (select collegeid from epcms_uni_student_grade where year = @year)) as female,
(select count(*) as total from epcms_uni_student where gender = 2 && collegeid in (select collegeid from epcms_uni_student_grade where year = @year)) as male,
count(*) as total from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_grade where year = @year);
set @year= 2016; -- Local VS International
select @year as year, (select count(*) as total from epcms_uni_student where nationality_code = 10 && collegeid in (select collegeid from epcms_uni_student_grade where year = @year)) as Omani,
(select count(*) as total from epcms_uni_student where nationality_code != 10 && collegeid in (select collegeid from epcms_uni_student_grade where year = @year)) as "Non-Omani",
count(*) as total from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_grade where year = @year);
set @year= 2016; -- Full Time VS Open Learning
select @year as year, (select count(*) as total from epcms_uni_student where study_nature = 5 && collegeid in (select collegeid from epcms_uni_student_grade where year = @year)) as FT,
(select count(*) as total from epcms_uni_student where study_nature != 5 && collegeid in (select collegeid from epcms_uni_student_grade where year = @year)) as "Non-Omani",
count(*) as total from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_grade where year = @year);
set @year= 2012; -- By age
select @year as year,
(select count(*) as total from epcms_uni_student where (DATEDIFF(concat_ws('', @year, '-06-01'), birthday) / 365.25) < 21 && collegeid in (select collegeid from epcms_uni_student_grade where year = @year)) as "<21",
(select count(*) as total from epcms_uni_student where (DATEDIFF(concat_ws('', @year, '-06-01'), birthday) / 365.25) > 65 && collegeid in (select collegeid from epcms_uni_student_grade where year = @year)) as ">65",
(select count(*) as total from epcms_uni_student where (DATEDIFF(concat_ws('', @year, '-06-01'), birthday) / 365.25) >= 21 && (DATEDIFF(concat_ws('', @year, '-06-01'), birthday) / 365.25) <= 24 && collegeid in (select collegeid from epcms_uni_student_grade where year = @year)) as "21 > 24",
(select count(*) as total from epcms_uni_student where (DATEDIFF(concat_ws('', @year, '-06-01'), birthday) / 365.25) >= 25 && (DATEDIFF(concat_ws('', @year, '-06-01'), birthday) / 365.25) <= 29 && collegeid in (select collegeid from epcms_uni_student_grade where year = @year)) as "25 > 29",
(select count(*) as total from epcms_uni_student where (DATEDIFF(concat_ws('', @year, '-06-01'), birthday) / 365.25) >= 30 && (DATEDIFF(concat_ws('', @year, '-06-01'), birthday) / 365.25) <= 39 && collegeid in (select collegeid from epcms_uni_student_grade where year = @year)) as "30 > 39",
(select count(*) as total from epcms_uni_student where (DATEDIFF(concat_ws('', @year, '-06-01'), birthday) / 365.25) >= 40 && (DATEDIFF(concat_ws('', @year, '-06-01'), birthday) / 365.25) <= 49 && collegeid in (select collegeid from epcms_uni_student_grade where year = @year)) as "40 > 49",
(select count(*) as total from epcms_uni_student where (DATEDIFF(concat_ws('', @year, '-06-01'), birthday) / 365.25) >= 50 && (DATEDIFF(concat_ws('', @year, '-06-01'), birthday) / 365.25) <= 59 && collegeid in (select collegeid from epcms_uni_student_grade where year = @year)) as "50 > 59",
(select count(*) as total from epcms_uni_student where (DATEDIFF(concat_ws('', @year, '-06-01'), birthday) / 365.25) >= 60 && (DATEDIFF(concat_ws('', @year, '-06-01'), birthday) / 365.25) <= 64 && collegeid in (select collegeid from epcms_uni_student_grade where year = @year)) as "60 > 64",
count(*) as total from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_grade where year = @year);
-- Year
set @year= 2013;
-- Students with status: 1,2,4
select Year, First, Second, Summer, Total, ceil((first + second + summer)/3) as Average from (
select @year as Year,
(select count(*) from epcms_uni_student_status where std_study_status in (1,2,4) && year = @year && semester = 1) as "First",
(select count(*) from epcms_uni_student_status where std_study_status in (1,2,4) && year = @year && semester = 2) as "Second",
(select count(*) from epcms_uni_student_status where std_study_status in (1,2,4) && year = @year && semester = 3) as "Summer",
count(*) as Total from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (1,2,4) && year = @year)) tbl;
-- Students with status: 1,2,4 (LIST)
select @year as year, LPAD(collegeid, 6, 0) as "Student ID" , name_en, specialization_en,
(select count(Distinct year) from epcms_uni_student_status where std_study_status in (1,2,4) && year <= @year && collegeid = tbl.collegeid ) as "duration (Academic Years)",
(select count(*) from epcms_uni_student_status where std_study_status in (2,4) && year <= @year && collegeid = tbl.collegeid && semester != 3 ) as discontinue
from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (1,2,4) && year = @year) order by specialization_en ASC, name_en;
-- Dismissal
select *, ceil((1_Discontinued + 2_Discontinued + 3_Discontinued)/3) as "Average Discontinue Dismissal",
ceil((1_Academic + 2_Academic + 2_Academic)/3) as "Average Academic Dismissal",
ceil((1_Total + 2_Total + 3_Total)/3) as "Average Dismissal"
from (
select @year as Year,
---- semester 1
(select count(*) from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (5,6) && year = @year && semester = 1 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (5,6) && year = (@year - 1) && semester = 2 )) as "1_Discontinued",
(select count(*) from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (16) && year = @year && semester = 1 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (16) && year = (@year - 1) && semester = 2 )) as "1_Academic",
(select count(*) from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (5,6,16) && year = @year && semester = 1 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (5,6,16) && year = (@year - 1) && semester = 2 )) as "1_Total",
---- semester 2
(select count(*) from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (5,6) && year = @year && semester = 2 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (5,6) && year = @year && semester = 1 )) as "2_Discontinued",
(select count(*) from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (16) && year = @year && semester = 2 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (16) && year = @year && semester = 1 )) as "2_Academic",
(select count(*) from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (5,6,16) && year = @year && semester = 2 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (5,6,16) && year = @year && semester = 1 )) as "2_Total",
---- semester 3
(select count(*) from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (5,6) && year = @year && semester = 3 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (5,6) && year = @year && semester = 2 )) as "3_Discontinued",
(select count(*) from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (16) && year = @year && semester = 3 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (16) && year = @year && semester = 2 )) as "3_Academic",
(select count(*) from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (5,6,16) && year = @year && semester = 3 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (5,6,16) && year = @year && semester = 2 )) as "3_Total") tbl;
-- Dismissal (List)
select @year as Year, LPAD(collegeid, 6, 0) as "Student ID" , name_en, specialization_en, if(study_status = 16, 'Academic Dismissal', if(study_status = 9, 'Withdrawl', 'Discontinued')) as "Dismissal Type",
(select count(*) from epcms_uni_student_status where std_study_status in (2,4) && year <= @year && collegeid = tbl.collegeid && semester != 3 ) as discontinue
from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (5,6,16) && year = @year ) && collegeid in (select collegeid from epcms_uni_student_status where std_study_status not in (5,6,16) && year = (@year - 1) ) order by study_status ASC, specialization_en ASC, name_en ASC;
-- Student Leaves / Postpone
select @year as Year,
(select count(*) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (4) && year = @year && semester = 1)) as "First",
(select count(*) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (4) && year = @year && semester = 2)) as "Second",
(select count(*) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (4) && year = @year && semester = 3)) as "Summer",
count(*) as Total, ceil(count(*)/3) as average
from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (4) && year = @year );
-- Student Leaves / Postpone (List)
select @year as Year, LPAD(collegeid, 6, 0) as "Student ID" , name_en, specialization_en,
(select count(*) from epcms_uni_student_status where std_study_status in (4) && year <= @year && collegeid = tbl.collegeid && semester != 3 ) as "Student Leaves"
from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (4) && year = @year ) order by study_status ASC, specialization_en ASC, name_en ASC;
-- Discontinue
select @year as Year,
(select count(*) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (2) && year = @year && semester = 1)) as "First",
(select count(*) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (2) && year = @year && semester = 2)) as "Second",
(select count(*) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (2) && year = @year && semester = 3)) as "Summer",
count(*) as Total, ceil(count(*)/3) as average
from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (2) && year = @year );
-- Discontinue (List);
select @year as Year, LPAD(collegeid, 6, 0) as "Student ID" , name_en, specialization_en,
(select count(*) from epcms_uni_student_status where std_study_status in (2) && year <= @year && collegeid = tbl.collegeid && semester != 3 ) as "Discontinues"
from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (2) && year = @year ) order by study_status ASC, specialization_en ASC, name_en ASC;
set @year = 2013;
-- Withdrawl
select * , ceil((1_Ministry + 2_Ministry + 3_Ministry)/3) as "Average Ministry",
ceil((1_Other + 2_Other + 3_Other)/3) as "Average Others",
ceil((1_Total + 2_Total + 3_Total)/3) as "Average Withdrawl"
from (
select @year as Year,
---- semester 1
(select count(*) from epcms_uni_student tbl where study_nature = 5 && accept_nature = 4 && collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 1 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = (@year - 1) && semester = 2 )) as "1_Ministry",
(select count(*) from epcms_uni_student tbl where (study_nature != 5 || study_nature = 5 && accept_nature != 4) && collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 1 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = (@year - 1) && semester = 2 )) as "1_Other",
(select count(*) from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 1 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = (@year - 1) && semester = 2 )) as "1_Total",
---- semester 2
(select count(*) from epcms_uni_student tbl where study_nature = 5 && accept_nature = 4 && collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 2 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 1 )) as "2_Ministry",
(select count(*) from epcms_uni_student tbl where (study_nature != 5 || study_nature = 5 && accept_nature != 4) && collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 2 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 1 )) as "2_Other",
(select count(*) from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 2 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 1 )) as "2_Total",
---- semester 3
(select count(*) from epcms_uni_student tbl where study_nature = 5 && accept_nature = 4 && collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 3 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 2 )) as "3_Ministry",
(select count(*) from epcms_uni_student tbl where (study_nature != 5 || study_nature = 5 && accept_nature != 4) && collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 3 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 2 )) as "3_Other",
(select count(*) from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 3 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 2 )) as "3_Total") tbl;
-- Withdrawl (List)
select @year as Year, LPAD(collegeid, 6, 0) as "Student ID" , name_en, specialization_en, if(study_nature = 5 && accept_nature = 4, 'Ministry', 'Other') as "Student Type"
from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year ) && collegeid in (select collegeid from epcms_uni_student_status where std_study_status not in (3,9) && year = (@year - 1) ) order by study_status ASC, specialization_en ASC, name_en ASC;
-- Students semesters and years
select year, years as "duration (by years)", count(*) as students from (
select *, ceil(if(semesters = 0, 1, semesters)/2) as years from (
select @year as Year, collegeid,
name_ar,
(select count(*) from epcms_uni_student_status where collegeid = tbl.collegeid && semester != 3 && std_study_status not in (11,23) && year <= @year) as semesters -- , count(*) as Total
from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (1,2,4) && year = @year)) tbl) mtbl group by years;
-- Students per academic year
set @pyear = 2012;
select @pyear as year,
(select count(*) as total from epcms_uni_student where if(study_nature != 5, 1, 5) = 1 && collegeid in (select collegeid from epcms_uni_student_grade where year = @pyear && semester = 1) group by if(study_nature != 5, 1, 5)) as "fall (OL)",
(select count(*) as total from epcms_uni_student where if(study_nature != 5, 1, 5) = 5 && collegeid in (select collegeid from epcms_uni_student_grade where year = @pyear && semester = 1) group by if(study_nature != 5, 1, 5)) as "fall (FT)",
(select count(*) as total from epcms_uni_student where if(study_nature != 5, 1, 5) = 1 && collegeid in (select collegeid from epcms_uni_student_grade where year = @pyear && semester = 2) group by if(study_nature != 5, 1, 5)) as "Spring (OL)",
(select count(*) as total from epcms_uni_student where if(study_nature != 5, 1, 5) = 5 && collegeid in (select collegeid from epcms_uni_student_grade where year = @pyear && semester = 2) group by if(study_nature != 5, 1, 5)) as "Spring (FT)",
(select count(*) as total from epcms_uni_student where if(study_nature != 5, 1, 5) = 1 && collegeid in (select collegeid from epcms_uni_student_grade where year = @pyear && semester = 3) group by if(study_nature != 5, 1, 5)) as "Summer (OL)",
(select count(*) as total from epcms_uni_student where if(study_nature != 5, 1, 5) = 5 && collegeid in (select collegeid from epcms_uni_student_grade where year = @pyear && semester = 3) group by if(study_nature != 5, 1, 5)) as "Summer (FT)";
-- Students of certian year for each specialization
set @noy = 3;
select specialization_en, count(*) as total from epcms_uni_student where courses != '' && collegeid in (
select collegeid from (select collegeid, count(*) as semesters, ceil(if(count(*) = 0, 1, count(*))/2) as years from epcms_uni_student_grade where course_code not in ('EF001', 'EF002', 'EF003', 'IT100', 'MA100', 'ED221', 'ED241', 'ED423') && semester != 3 group by collegeid) tbl where years = @noy
) group by specialization_en order by specialization_en ASC;
-- Students in first semester
select specialization_en, count(*) as total from epcms_uni_student where courses != ''
-- && (
-- (locate('EF001', completed_courses) > 0 &&
-- locate('EF002', completed_courses) > 0 &&
-- locate('EF003', completed_courses) > 0) ||
-- (locate('ED221', completed_courses) > 0 &&
-- locate('ED241', completed_courses) > 0 &&
-- locate('ED423', completed_courses) > 0)
-- )
&& (
locate('EL111', courses) > 0 ||
locate('AR111', courses) > 0 ||
locate('ED613', courses) > 0 ||
locate('ED618', courses) > 0 ||
locate('ED631', courses) > 0 ||
-- locate('ED632', courses) > 0 ||
locate('B716A', courses) > 0 ||
locate('B716', courses) > 0
) group by specialization_en;
-- Student Support | صندوق الطالب
select * from (
select tbl.collegeid, name_ar, specialization_ar, gpa, completed_courses, count(*) as semesters from (select collegeid from epcms_uni_student_grade where course_code not in ('EF001', 'EF002', 'EF003', 'IT100', 'MA100') group by concat_ws('|', collegeid, year, semester)) tbl inner join epcms_uni_student on epcms_uni_student.collegeid = tbl.collegeid
where gpa >= 2 && study_nature != 5 && courses != '' && specialization_en not like 'Master%'
group by tbl.collegeid) tbl2 where tbl2.semesters >= 2;
-- Students to create emails
select concat_ws('', 's', collegeid) as STUDENT_NO, name_en FULL_NAME, name_en as E_FAMILY_NAME, mobile_number as MOBILE_NO, home_tel as HOME_TEL, '' as POSTAL_ADDRESS, city_en as CITY_NAME, concat_ws('', 's', collegeid, '@omanbr.com') as DOMAIN, concat_ws('', 's', collegeid, '@aou.edu.om') as EMAIL, concat_ws('', 'aou', SUBSTR(civil_id, 1, 5)) as EMAIL_PASSWORD from epcms_uni_student where collegeid > 170401 && courses != '';
-- Students semester
select collegeid as "Student ID", name_ar, specialization_ar,
(select count(*) from epcms_uni_student_status where collegeid = tbl.collegeid && semester != 3) as semesters, completed_courses, courses as "Current Courses"
from epcms_uni_student tbl where courses != '' && specialization_en like 'Master%' && specialization_ar not like '%تكنولوجيا%' order by specialization_ar, name_ar ASC;
-- Incomplete:
select epcms_uni_student.collegeid, epcms_uni_student.name_en, epcms_uni_student.specialization_en, mobile_number, home_tel, civil_id, course_code, concat_ws('|', epcms_uni_student_grade.year, epcms_uni_student_grade.semester) as semester, epcms_uni_student_grade.total_mta, epcms_uni_student_grade.total_midexam, epcms_uni_student_grade.total_final, grade, gpa, (ch_mandatory_done+ch_elective_done) as "Completed CH" from epcms_uni_student inner join epcms_uni_student_grade on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where epcms_uni_student.study_status in (1,2,4) && grade like 'I' && concat_ws('|', year, semester) in ('2016|2', '2016|3', '2017|1') order by semester ASC, epcms_uni_student.name_en ASC;
-- IT students to change plan
select collegeid, name_en, specialization_en, name_ar, specialization_ar, ch_mandatory_done, ch_elective_done, completed_courses, courses, left_courses, study_status, study_nature from epcms_uni_student where specialization_ar like '%تقنية%' && specialization_en not like 'Diploma%' && study_status not in (6,8,9,14,16) && ch_mandatory_done = 0 && locate('MT', left_courses) = 0 ;
-- check inactive student in notification applicants || Mobile App
select collegeid from epcms_uni_student where study_status in (1,2,4,7) && concat_ws('|', beginning_year, beginning_semester) != '2017|2' && collegeid not in (select collegeid from epcms_uni_device_token where from_unixtime(last_used) >= '2017-12-17 01:34:50');
-- Student from certian nationality with first and last semester
select collegeid as "Student ID", name_ar, specialization_ar, nationality_ar, concat_ws('|', beginning_year, beginning_semester) as "Admission", if(concat_ws('|', last_year, last_semester) = '0|0', 'Never', concat_ws('|', last_year, last_semester)) as "Last Semester (Registered Courses)" from epcms_uni_student where study_status in (1,2,4,7) && nationality_code = 15 ;
-- Current Students grades
select * from (
select epcms_uni_student.collegeid, epcms_uni_student.name_en, epcms_uni_student.specialization_en, epcms_uni_student.gpa, GROUP_CONCAT(epcms_uni_student_grade.course_code SEPARATOR ' | ') as courses,
GROUP_CONCAT(epcms_uni_student_grade.total_mta + epcms_uni_student_grade.total_midexam SEPARATOR ' | ') as ca, GROUP_CONCAT(epcms_uni_student_grade.total_final SEPARATOR ' | ') as total_final,
GROUP_CONCAT(round(epcms_uni_student_grade.total_mta + epcms_uni_student_grade.total_midexam + epcms_uni_student_grade.total_final) SEPARATOR ' | ') as total_mark,
GROUP_CONCAT(if(epcms_uni_student_grade.total_mta + epcms_uni_student_grade.total_midexam >= 15 && (epcms_uni_student_grade.course_code in ('EF001', 'EF002', 'EF003') && epcms_uni_student_grade.total_final >= 16 || epcms_uni_student_grade.course_code not in ('EF001', 'EF002', 'EF003') && epcms_uni_student_grade.total_final >= 20)
&& round(epcms_uni_student_grade.total_mta + epcms_uni_student_grade.total_midexam + epcms_uni_student_grade.total_final) >= 50, 'Passed', 'Failed') SEPARATOR ' | ') as status
from epcms_uni_student inner join epcms_uni_student_grade on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where epcms_uni_student.collegeid in
(select collegeid from epcms_uni_student_grade where epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid && year = 2017 && semester = 1 ) &&
CONCAT_WS('|', epcms_uni_student_grade.year, epcms_uni_student_grade.semester) = (select max(CONCAT_WS('|', year, semester)) from epcms_uni_student_grade grd where grd.collegeid = epcms_uni_student_grade.collegeid)
-- && specialization_en not like 'Master%'
&& grade != 'FI' group by epcms_uni_student.collegeid order by epcms_uni_student.specialization_en ASC, epcms_uni_student.name_en ASC
) tbl; -- where tbl.status like '%Failed%';
-- Current Students grades : Last Semester
select * from (
select epcms_uni_student.collegeid, concat_ws('|', year, semester) as last_semester, epcms_uni_student.name_en, epcms_uni_student.specialization_en, epcms_uni_student.gpa, GROUP_CONCAT(epcms_uni_student_grade.course_code SEPARATOR ' | ') as courses,
GROUP_CONCAT(epcms_uni_student_grade.total_mta + epcms_uni_student_grade.total_midexam SEPARATOR ' | ') as ca, GROUP_CONCAT(epcms_uni_student_grade.total_final SEPARATOR ' | ') as total_final,
GROUP_CONCAT(round(epcms_uni_student_grade.total_mta + epcms_uni_student_grade.total_midexam + epcms_uni_student_grade.total_final) SEPARATOR ' | ') as total_mark,
GROUP_CONCAT(if(epcms_uni_student_grade.total_mta + epcms_uni_student_grade.total_midexam >= 15 && (epcms_uni_student_grade.course_code in ('EF001', 'EF002', 'EF003') && epcms_uni_student_grade.total_final >= 16 || epcms_uni_student_grade.course_code not in ('EF001', 'EF002', 'EF003') && epcms_uni_student_grade.total_final >= 20)
&& round(epcms_uni_student_grade.total_mta + epcms_uni_student_grade.total_midexam + epcms_uni_student_grade.total_final) >= 50, 'Passed', 'Failed') SEPARATOR ' | ') as status
from epcms_uni_student inner join epcms_uni_student_grade on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where epcms_uni_student.collegeid in
(select collegeid from epcms_uni_student_grade where epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid && concat_ws('|', year, semester) = (select max(concat_ws('|', year, semester)) from epcms_uni_student_grade where collegeid = epcms_uni_student.collegeid) ) &&
CONCAT_WS('|', epcms_uni_student_grade.year, epcms_uni_student_grade.semester) = (select max(CONCAT_WS('|', year, semester)) from epcms_uni_student_grade grd where grd.collegeid = epcms_uni_student_grade.collegeid)
-- && specialization_en not like 'Master%'
&& grade != 'FI' && epcms_uni_student.study_status in (1,2,4,7) group by epcms_uni_student.collegeid order by epcms_uni_student.specialization_en ASC, epcms_uni_student.name_en ASC
) tbl; -- where tbl.status like '%Failed%';
-- Student statistics per status : passed, failed
select course_code,
(select count(Distinct section) from epcms_uni_student_grade where year = 2017 && semester = 1 && course_code = tbl.course_code) as "Sections",
count(*) as "Students Without W",
(select count(*) from epcms_uni_student_grade where year = 2017 && semester = 1 && course_code = tbl.course_code) as "Students including W",
(select count(*) from epcms_uni_student_grade where year = 2017 && semester = 1 && (total_mta + total_midexam + total_final) >= 50 && grade != 'W' && course_code = tbl.course_code) as "Passed (Total)",
(select count(*) from epcms_uni_student_grade where year = 2017 && semester = 1 && (total_mta + total_midexam + total_final) < 50 && grade != 'W' && course_code = tbl.course_code) as "Failed (Total)",
(select count(*) from epcms_uni_student_grade where year = 2017 && semester = 1 && total_final > 0 && grade != 'W' && course_code = tbl.course_code) as "Attended Final Exam"
from epcms_uni_student_grade tbl where year = 2017 && semester = 1 && grade != 'W' && (course_code like 'M%' || course_code like 'T%') group by course_code;
-- Student statistics of Foundation
select course_code, concat_ws('|', year, semester) as semester,
(select count(*) from epcms_uni_student_grade where year = tbl.year && semester = tbl.semester && course_code = tbl.course_code && (grade like 'S' || grade like 'U')) as passed,
(select count(*) from epcms_uni_student_grade where year = tbl.year && semester = tbl.semester && course_code = tbl.course_code && grade like 'F%') as Failed,
(select count(*) from epcms_uni_student_grade where year = tbl.year && semester = tbl.semester && course_code = tbl.course_code && grade like 'I') as Incomplete,
(select count(*) from epcms_uni_student_grade where year = tbl.year && semester = tbl.semester && course_code = tbl.course_code && grade like 'W') as W
from epcms_uni_student_grade tbl where concat_ws('|', year, semester) in ('2016|1', '2016|2', '2016|3', '2017|1') && course_code in ('EF003', 'MA100', 'IT100') group by concat_ws('|', year, semester, course_code);
-- Studnets who get certain message
select epcms_uni_student.collegeid, epcms_uni_student.name_ar, from_unixtime(archive_time) as print_time from epcms_uni_archive inner join epcms_uni_student on epcms_uni_archive.collegeid = epcms_uni_student.collegeid && messageid in (50,59) group by epcms_uni_student.collegeid order by print_time ASC;
-- List of one student of each course
select * from (select * from epcms_uni_student_grade where year = 2017 && semester = 1 && grade not in ('W', 'I') group by course_code) tbl group by collegeid;
-- Students excluding new admitted students
select collegeid from epcms_uni_student where
study_status not in (5,6,8,9,14,16,28) -- && specialization_en not like '%Education%'
&& concat_ws('|', beginning_year, beginning_semester) != '2017|2';
-- Student who registered in specific section and Course:
select collegeid, name_en, courses from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = 2017 && semester = 2 && course_code like 'M106');
-- Check Expected to graduate from SIS after official results
select collegeid, name_en, specialization_en, study_status, ch_mandatory, ch_mandatory_done, ch_elective, ch_elective_done, left_courses, gpa,
if(ch_mandatory_done >= ch_mandatory && ch_elective_done >= ch_elective, 'Finished CH', 'Not Finished') as by_CH,
if(specialization_en like 'Master%', if(gpa >= 3, 'Passed', 'Failed'), if(gpa >= 2, 'Passed', 'Failed')) as by_GPA,
if(specialization_en like '%Edu%', if(locate('ED698', completed_courses) > 0, 'Passed', 'Failed'), 'Not Applicable') as "ED698"
from epcms_uni_student where collegeid in (
150477,150513,150632,150639,150656,150671,150675,150676,150735,150744,150754,150770,150783,150791,150807,160537,140463,140852,140876,150238,140460,140843,150302,150335,150386,091090,110455,110477,120062,120633,120761,130043,130222,130245,130252,131144,131152,140912,160218,160287,160428,160673,110451,120522,140503,140512,140866,140916,140931,150277,150308,150346,150464,150773,160637,110214,131000,110314,110336,120100,120179,120339,120503,090706,110082,120576,131132,150868,120139,130268,130272,131150,140307,140851,140894,100486,110212,110427,120009,120053,120060,120366,120397,120449,120500,120662,130016,130020,130080,130386,130491,130870,131027,131079,131129,131143,131149,140893
) order by specialization_en ASC;
-- Check students should be graduated internally
select collegeid, name_en, specialization_en, study_status, ch_mandatory, ch_mandatory_done, ch_elective, ch_elective_done, left_courses, gpa,
if(ch_mandatory_done >= ch_mandatory && ch_elective_done >= ch_elective, 'Finished CH', 'Not Finished') as by_CH,
if(specialization_en like 'Master%', if(gpa >= 3, 'Passed', 'Failed'), if(gpa >= 2, 'Passed', 'Failed')) as by_GPA,
if(specialization_en like '%Edu%', if(locate('ED698', completed_courses) > 0, 'Passed', 'Failed'), 'Not Applicable') as "ED698"
from epcms_uni_student where
ch_mandatory_done >= ch_mandatory && ch_elective_done >= ch_elective && study_status not in (8) && (specialization_en like 'Master%' && gpa >= 3 || specialization_en not like 'Master%' && gpa >= 2) && (specialization_en like '%Edu%' && locate('ED698', completed_courses) > 0 || specialization_en not like '%Edu%')
order by specialization_en ASC;
-- New Students per Specialization
select (
CASE WHEN specialization_ar LIKE 'إدارة الأعمال - إدارة الأعمال' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - إقتصاد' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - التسويق' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - محاسبة' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال -الانظمة' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'الدبلوم في اللغة الانجليزية وادابها ' THEN 'الدبلوم في اللغة الانجليزية وادابها'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وآدابها' THEN 'اللغة الإنجليزية وآدابها'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وادارة الأعمال' THEN 'اللغة الإنجليزية وآدابها'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الاتصالات' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الحوسبة' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / تقنية المعلومات والحوسبة' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الحوسبة وادارة الاعمال' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الشبكات وأمن البيانات' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'دبلوم في ادارة الاعمال' THEN 'دبلوم في ادارة الاعمال'
WHEN specialization_ar LIKE 'دبلوم في تقنية المعلومات والحوسبة' THEN 'دبلوم في تقنية المعلومات والحوسبة'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية - مسار الامتحان الشامل' THEN 'ماجستير التربية في القيادة التربوية'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية- مسار الأطروحة' THEN 'ماجستير التربية في القيادة التربوية'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم (مسار الأطروحة)' THEN 'ماجستير التربية في تكنولوجيا التعليم'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم(مسار الامتحان الشامل)' THEN 'ماجستير التربية في تكنولوجيا التعليم'
WHEN specialization_ar LIKE 'ماجستير في إدارة الأعمال' THEN 'ماجستير في إدارة الأعمال' END
) as sp, count(*) as total from epcms_uni_student where
beginning_year = 2017 && beginning_semester = 2
&& collegeid in (select collegeid from epcms_uni_student_grade where year = epcms_uni_student.beginning_year && semester = epcms_uni_student.beginning_semester )
&& (study_nature != 5 || study_nature = 5 && accept_nature != 4)
-- && study_nature = 5 && accept_nature = 4
&& study_status != 21
group by sp
order by sp ASC;
-- Registered Students per Specialization
select (
CASE WHEN specialization_ar LIKE 'إدارة الأعمال - إدارة الأعمال' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - إقتصاد' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - التسويق' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - محاسبة' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال -الانظمة' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'الدبلوم في اللغة الانجليزية وادابها ' THEN 'الدبلوم في اللغة الانجليزية وادابها'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وآدابها' THEN 'اللغة الإنجليزية وآدابها'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وادارة الأعمال' THEN 'اللغة الإنجليزية وآدابها'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الاتصالات' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الحوسبة' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / تقنية المعلومات والحوسبة' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الحوسبة وادارة الاعمال' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الشبكات وأمن البيانات' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'دبلوم في ادارة الاعمال' THEN 'دبلوم في ادارة الاعمال'
WHEN specialization_ar LIKE 'دبلوم في تقنية المعلومات والحوسبة' THEN 'دبلوم في تقنية المعلومات والحوسبة'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية - مسار الامتحان الشامل' THEN 'ماجستير التربية في القيادة التربوية'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية- مسار الأطروحة' THEN 'ماجستير التربية في القيادة التربوية'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم (مسار الأطروحة)' THEN 'ماجستير التربية في تكنولوجيا التعليم'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم(مسار الامتحان الشامل)' THEN 'ماجستير التربية في تكنولوجيا التعليم'
WHEN specialization_ar LIKE 'ماجستير في إدارة الأعمال' THEN 'ماجستير في إدارة الأعمال' END
) as sp, count(*) as total from epcms_uni_student where
collegeid in (select collegeid from epcms_uni_student_grade where year = 2017 && semester = 2)
&& (study_nature != 5 || study_nature = 5 && accept_nature != 4)
-- && study_nature = 5 && accept_nature = 4
&& study_status != 21
group by sp
order by sp ASC;
-- Students divided by Foundation, probation, Registered
select *,
(select count(DISTINCT collegeid) from epcms_uni_student_grade where year = tbl.year && semester = tbl.semester && course_code in ('EF001', 'EF002', 'EF003')) as Foundation,
(select count(DISTINCT epcms_uni_student_status.collegeid) from epcms_uni_student_status inner join epcms_uni_student on epcms_uni_student.collegeid = epcms_uni_student_status.collegeid where year = tbl.year && semester = tbl.semester && std_study_status = 1 && tot_hours > 0 && (epcms_uni_student.specialization_en not like 'Master' && sem_gpa < 2 || epcms_uni_student.specialization_en like 'Master' && sem_gpa < 3) ) as probation,
(select count(DISTINCT collegeid) from epcms_uni_student_grade where year = tbl.year && semester = tbl.semester) as Registered
from (
select year, semester
from epcms_uni_student_grade where year >= 2012 && semester != 3 group by concat_ws('|', year, semester)) tbl;
-- Students Registered via online services
select *, concat_ws('', round(Registered_Online/Registered * 100, 2), '%') as percentage from (
select year, if(semester = 1, 'Fall', 'Spring') as semester,
(select count(DISTINCT collegeid) from epcms_uni_student_grade where year = tbl.year && semester = tbl.semester) as Registered,
(select count(DISTINCT collegeid) from epcms_uni_student_grade where year = tbl.year && semester = tbl.semester && created_by = collegeid) as Registered_Online
from (
select year, semester
from epcms_uni_student_grade where year >= 2013 && semester != 3 group by concat_ws('|', year, semester)) tbl ) mtbl;
-- Admission and Withdrawl | set @year = 2017;
select *
from (
select @year as Year,
---- semester 1
(select count(*) from epcms_uni_student tbl where beginning_year = @year && beginning_semester = 1 && collegeid in (select collegeid from epcms_uni_student_grade where year = @year && semester = 1)) as "IN:Fall",
(select count(*) from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 1 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = (@year - 1) && semester = 2 )) as "OUT:Fall",
---- semester 2
(select count(*) from epcms_uni_student tbl where beginning_year = @year && beginning_semester = 2 && collegeid in (select collegeid from epcms_uni_student_grade where year = @year && semester = 2)) as "IN:Spring",
(select count(*) from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 2 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 1 )) as "OUT:Spring",
---- semester 3
(select count(*) from epcms_uni_student tbl where beginning_year = @year && beginning_semester = 3 && collegeid in (select collegeid from epcms_uni_student_grade where year = @year && semester = 3)) as "IN:Summer",
(select count(*) from epcms_uni_student tbl where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 3 ) && collegeid not in (select collegeid from epcms_uni_student_status where std_study_status in (3,9) && year = @year && semester = 2 )) as "OUT:Summer") tbl;
-- Students by monthly Foundation
select *,
(select count(*) from epcms_uni_student_grade where year = tbl.year && semester = tbl.semester && section = 50 && course_code = 'EF001') as EF001,
(select count(*) from epcms_uni_student_grade where year = tbl.year && semester = tbl.semester && section = 50 && course_code = 'EF002') as EF002,
(select count(*) from epcms_uni_student_grade where year = tbl.year && semester = tbl.semester && section = 50 && course_code = 'EF003') as EF003
from (
select year, semester
from epcms_uni_student_grade where year >= 2013 && semester != 3 group by concat_ws('|', year, semester)) tbl;
-- Student with no OU id
select collegeid as 'Inst Student Ref', concat_ws(' ', firstname_en, if(secondname_en != '', secondname_en, null), if(thirdname_en != '', thirdname_en, null)) as 'Forename(s)', lastname_en as 'Family Name', birthday as 'DOB', if(gender = 2, 'M', 'F') as 'Gender', '' as 'MODE_OF_STUDY', '' as 'ACADEMIC_LEVEL_OF_STUDY', if(gender = 2, 'Mr.', 'Ms.') as Title, city_en as 'Address Line 1', '' as 'Address Line 2', '' as 'Address Line 3', address as 'Address Line 4', '' as 'Address Line 5', 'Oman' as 'Country Name', '' as 'Postcode', home_tel as 'Telephone No', mobile_number as 'Mobile No', if(student_email = '', CONCAT('s', collegeid, '@aou.edu.om'), student_email) as 'Email Address', 'Muscat' as 'Location', specialization_en as 'Award -Prog' from epcms_uni_student where collegeid in (
select collegeid from epcms_uni_student where ouvs_code = '' && completed_courses != '' && study_status not in (6,8,9,14,16) && specialization_en in ('BSc Information Technology & Computing - Computing', 'BSc Information Technology & Computing - Information & Communication Technologies', 'English Language & Literature with Business Studies')
) order by collegeid ASC;
-- Students by age per year
select *,
(select count(distinct collegeid) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = tbl.year && grade != 'W' group by year) && FLOOR(DATEDIFF(REPLACE(now(), split_str(CURDATE(), '-', 1), tbl.year), birthday) / 365.25) < 20 ) as "<20",
(select count(distinct collegeid) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = tbl.year && grade != 'W' group by year) && FLOOR(DATEDIFF(REPLACE(now(), split_str(CURDATE(), '-', 1), tbl.year), birthday) / 365.25) >= 20 ) as ">=20"
from (select year from epcms_uni_student_grade where year >= 2013 && semester in (1,2,3) && grade != 'W' group by year) tbl;
-- Students by age range per year
select *,
(select count(distinct collegeid) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = tbl.year && grade != 'W' group by year) && FLOOR(DATEDIFF(REPLACE(now(), split_str(CURDATE(), '-', 1), tbl.year), birthday) / 365.25) < 21 ) as "< 21",
(select count(distinct collegeid) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = tbl.year && grade != 'W' group by year) && FLOOR(DATEDIFF(REPLACE(now(), split_str(CURDATE(), '-', 1), tbl.year), birthday) / 365.25) >= 21 && FLOOR(DATEDIFF(REPLACE(now(), split_str(CURDATE(), '-', 1), tbl.year), birthday) / 365.25) <= 24 ) as "21 - 24",
(select count(distinct collegeid) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = tbl.year && grade != 'W' group by year) && FLOOR(DATEDIFF(REPLACE(now(), split_str(CURDATE(), '-', 1), tbl.year), birthday) / 365.25) >= 25 && FLOOR(DATEDIFF(REPLACE(now(), split_str(CURDATE(), '-', 1), tbl.year), birthday) / 365.25) <= 29 ) as "25 - 29",
(select count(distinct collegeid) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = tbl.year && grade != 'W' group by year) && FLOOR(DATEDIFF(REPLACE(now(), split_str(CURDATE(), '-', 1), tbl.year), birthday) / 365.25) >= 30 && FLOOR(DATEDIFF(REPLACE(now(), split_str(CURDATE(), '-', 1), tbl.year), birthday) / 365.25) <= 39 ) as "30 - 39",
(select count(distinct collegeid) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = tbl.year && grade != 'W' group by year) && FLOOR(DATEDIFF(REPLACE(now(), split_str(CURDATE(), '-', 1), tbl.year), birthday) / 365.25) >= 40 && FLOOR(DATEDIFF(REPLACE(now(), split_str(CURDATE(), '-', 1), tbl.year), birthday) / 365.25) <= 49 ) as "40 - 49",
(select count(distinct collegeid) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = tbl.year && grade != 'W' group by year) && FLOOR(DATEDIFF(REPLACE(now(), split_str(CURDATE(), '-', 1), tbl.year), birthday) / 365.25) >= 50 && FLOOR(DATEDIFF(REPLACE(now(), split_str(CURDATE(), '-', 1), tbl.year), birthday) / 365.25) <= 59 ) as "50 - 59",
(select count(distinct collegeid) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = tbl.year && grade != 'W' group by year) && FLOOR(DATEDIFF(REPLACE(now(), split_str(CURDATE(), '-', 1), tbl.year), birthday) / 365.25) >= 60 ) as ">= 60"
from (select year from epcms_uni_student_grade where year >= 2013 && semester in (1,2,3) && grade != 'W' group by year) tbl;
-- Ministry VS Open Learning students by year
select *,
(select count(distinct collegeid) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = tbl.year && grade != 'W' group by year) && study_nature = 5 && accept_nature = 4 ) as "Ministry Students",
(select count(distinct collegeid) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = tbl.year && grade != 'W' group by year) && (study_nature != 5 || study_nature = 5 && accept_nature != 4) ) as "Open Learning"
from (select year from epcms_uni_student_grade where year >= 2012 && semester in (1,2,3) && grade != 'W' group by year) tbl;
-- Omani VS Non-Omani by year
select *,
(select count(distinct collegeid) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = tbl.year && grade != 'W' group by year) && nationality_code = 10 ) as "Omani",
(select count(distinct collegeid) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = tbl.year && grade != 'W' group by year) && nationality_code != 10 ) as "Non-Omani"
from (select year from epcms_uni_student_grade where year >= 2012 && semester in (1,2,3) && grade != 'W' group by year) tbl;
-- Students by gender per year
select *,
(select count(distinct collegeid) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = tbl.year && grade != 'W' group by year) && gender = 1 ) as "Female",
(select count(distinct collegeid) from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = tbl.year && grade != 'W' group by year) && gender = 2 ) as "Male"
from (select year from epcms_uni_student_grade where year >= 2012 && semester in (1,2,3) && grade != 'W' group by year) tbl;
-- Registered students per year and specialization
select *, (select count(DISTINCT epcms_uni_student_grade.collegeid) from epcms_uni_student_grade inner join epcms_uni_student on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where year = tbl.year && semester = tbl.semester &&
(CASE WHEN specialization_ar LIKE 'إدارة الأعمال - إدارة الأعمال' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - إقتصاد' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - التسويق' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - محاسبة' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال -الانظمة' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'الدبلوم في اللغة الانجليزية وادابها ' THEN 'الدبلوم في اللغة الانجليزية وادابها'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وآدابها' THEN 'اللغة الإنجليزية وآدابها'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وادارة الأعمال' THEN 'اللغة الإنجليزية وآدابها'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الاتصالات' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الحوسبة' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / تقنية المعلومات والحوسبة' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الحوسبة وادارة الاعمال' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الشبكات وأمن البيانات' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'دبلوم في ادارة الاعمال' THEN 'دبلوم في ادارة الاعمال'
WHEN specialization_ar LIKE 'دبلوم في تقنية المعلومات والحوسبة' THEN 'دبلوم في تقنية المعلومات والحوسبة'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية - مسار الامتحان الشامل' THEN 'ماجستير التربية في القيادة التربوية'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية- مسار الأطروحة' THEN 'ماجستير التربية في القيادة التربوية'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم (مسار الأطروحة)' THEN 'ماجستير التربية في تكنولوجيا التعليم'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم(مسار الامتحان الشامل)' THEN 'ماجستير التربية في تكنولوجيا التعليم'
WHEN specialization_ar LIKE 'ماجستير في إدارة الأعمال' THEN 'ماجستير في إدارة الأعمال' END
) like tbl.sp) as registered_students from (select year, semester, (CASE WHEN specialization_ar LIKE 'إدارة الأعمال - إدارة الأعمال' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - إقتصاد' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - التسويق' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - محاسبة' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال -الانظمة' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'الدبلوم في اللغة الانجليزية وادابها ' THEN 'الدبلوم في اللغة الانجليزية وادابها'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وآدابها' THEN 'اللغة الإنجليزية وآدابها'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وادارة الأعمال' THEN 'اللغة الإنجليزية وآدابها'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الاتصالات' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الحوسبة' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / تقنية المعلومات والحوسبة' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الحوسبة وادارة الاعمال' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الشبكات وأمن البيانات' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'دبلوم في ادارة الاعمال' THEN 'دبلوم في ادارة الاعمال'
WHEN specialization_ar LIKE 'دبلوم في تقنية المعلومات والحوسبة' THEN 'دبلوم في تقنية المعلومات والحوسبة'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية - مسار الامتحان الشامل' THEN 'ماجستير التربية في القيادة التربوية'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية- مسار الأطروحة' THEN 'ماجستير التربية في القيادة التربوية'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم (مسار الأطروحة)' THEN 'ماجستير التربية في تكنولوجيا التعليم'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم(مسار الامتحان الشامل)' THEN 'ماجستير التربية في تكنولوجيا التعليم'
WHEN specialization_ar LIKE 'ماجستير في إدارة الأعمال' THEN 'ماجستير في إدارة الأعمال' END
) as sp
from epcms_uni_student_grade inner join epcms_uni_student on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid
where year >= 2012 && semester != 3 group by concat_ws('|', sp, year, semester) order by concat_ws('|', year, semester) ASC) tbl;
-- Sponsored Registered students per year and specialization
select *, (select count(DISTINCT epcms_uni_student_grade.collegeid) from epcms_uni_student_grade inner join epcms_uni_student on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where year = tbl.year && semester = tbl.semester && epcms_uni_student.is_sponsored = 1 && (epcms_uni_student.study_nature != 5 || epcms_uni_student.study_nature = 5 && epcms_uni_student.accept_nature != 4) &&
(CASE WHEN specialization_ar LIKE 'إدارة الأعمال - إدارة الأعمال' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - إقتصاد' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - التسويق' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - محاسبة' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال -الانظمة' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'الدبلوم في اللغة الانجليزية وادابها ' THEN 'الدبلوم في اللغة الانجليزية وادابها'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وآدابها' THEN 'اللغة الإنجليزية وآدابها'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وادارة الأعمال' THEN 'اللغة الإنجليزية وآدابها'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الاتصالات' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الحوسبة' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / تقنية المعلومات والحوسبة' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الحوسبة وادارة الاعمال' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الشبكات وأمن البيانات' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'دبلوم في ادارة الاعمال' THEN 'دبلوم في ادارة الاعمال'
WHEN specialization_ar LIKE 'دبلوم في تقنية المعلومات والحوسبة' THEN 'دبلوم في تقنية المعلومات والحوسبة'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية - مسار الامتحان الشامل' THEN 'ماجستير التربية في القيادة التربوية'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية- مسار الأطروحة' THEN 'ماجستير التربية في القيادة التربوية'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم (مسار الأطروحة)' THEN 'ماجستير التربية في تكنولوجيا التعليم'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم(مسار الامتحان الشامل)' THEN 'ماجستير التربية في تكنولوجيا التعليم'
WHEN specialization_ar LIKE 'ماجستير في إدارة الأعمال' THEN 'ماجستير في إدارة الأعمال' END
) like tbl.sp) as registered_students from (select year, semester, (CASE WHEN specialization_ar LIKE 'إدارة الأعمال - إدارة الأعمال' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - إقتصاد' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - التسويق' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال - محاسبة' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'إدارة الأعمال -الانظمة' THEN 'إدارة الأعمال'
WHEN specialization_ar LIKE 'الدبلوم في اللغة الانجليزية وادابها ' THEN 'الدبلوم في اللغة الانجليزية وادابها'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وآدابها' THEN 'اللغة الإنجليزية وآدابها'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وادارة الأعمال' THEN 'اللغة الإنجليزية وآدابها'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الاتصالات' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الحوسبة' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / تقنية المعلومات والحوسبة' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الحوسبة وادارة الاعمال' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الشبكات وأمن البيانات' THEN 'تقنية المعلومات و الحوسبة'
WHEN specialization_ar LIKE 'دبلوم في ادارة الاعمال' THEN 'دبلوم في ادارة الاعمال'
WHEN specialization_ar LIKE 'دبلوم في تقنية المعلومات والحوسبة' THEN 'دبلوم في تقنية المعلومات والحوسبة'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية - مسار الامتحان الشامل' THEN 'ماجستير التربية في القيادة التربوية'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية- مسار الأطروحة' THEN 'ماجستير التربية في القيادة التربوية'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم (مسار الأطروحة)' THEN 'ماجستير التربية في تكنولوجيا التعليم'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم(مسار الامتحان الشامل)' THEN 'ماجستير التربية في تكنولوجيا التعليم'
WHEN specialization_ar LIKE 'ماجستير في إدارة الأعمال' THEN 'ماجستير في إدارة الأعمال' END
) as sp, specialization_en from epcms_uni_student_grade inner join epcms_uni_student on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid
where year >= 2012 && semester != 3 group by concat_ws('|', sp, year, semester) order by concat_ws('|', year, semester) ASC) tbl;
-- Sponsored Registered students per year and specialization in English
select *, (select count(DISTINCT epcms_uni_student_grade.collegeid) from epcms_uni_student_grade inner join epcms_uni_student on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where year = tbl.year && semester = tbl.semester && epcms_uni_student.is_sponsored = 1 && (epcms_uni_student.study_nature != 5 || epcms_uni_student.study_nature = 5 && epcms_uni_student.accept_nature != 4) &&
(CASE WHEN specialization_ar LIKE 'إدارة الأعمال - إدارة الأعمال' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'إدارة الأعمال - إقتصاد' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'إدارة الأعمال - التسويق' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'إدارة الأعمال - محاسبة' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'إدارة الأعمال -الانظمة' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'الدبلوم في اللغة الانجليزية وادابها ' THEN 'Diploma in English Language Studies and Literature'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وآدابها' THEN 'English Language & Literature'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وادارة الأعمال' THEN 'English Language & Literature'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الاتصالات' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الحوسبة' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / تقنية المعلومات والحوسبة' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الحوسبة وادارة الاعمال' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الشبكات وأمن البيانات' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'دبلوم في ادارة الاعمال' THEN 'Diploma in Business Studies Study'
WHEN specialization_ar LIKE 'دبلوم في تقنية المعلومات والحوسبة' THEN 'Diploma Information Technology & Computing'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية - مسار الامتحان الشامل' THEN 'Master of Education in Leadership'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية- مسار الأطروحة' THEN 'Master of Education in Leadership'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم (مسار الأطروحة)' THEN 'Master of Education in Educational Technology'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم(مسار الامتحان الشامل)' THEN 'Master of Education in Educational Technology'
WHEN specialization_ar LIKE 'ماجستير في إدارة الأعمال' THEN 'Master of Business Administration' END
) like tbl.sp) as registered_students from (select year, semester, (CASE WHEN specialization_ar LIKE 'إدارة الأعمال - إدارة الأعمال' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'إدارة الأعمال - إقتصاد' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'إدارة الأعمال - التسويق' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'إدارة الأعمال - محاسبة' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'إدارة الأعمال -الانظمة' THEN 'Business Studies Management Track'
WHEN specialization_ar LIKE 'الدبلوم في اللغة الانجليزية وادابها ' THEN 'Diploma in English Language Studies and Literature'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وآدابها' THEN 'English Language & Literature'
WHEN specialization_ar LIKE 'اللغة الإنجليزية وادارة الأعمال' THEN 'English Language & Literature'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الاتصالات' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / الحوسبة' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'تقنية المعلومات و الحوسبة / تقنية المعلومات والحوسبة' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الحوسبة وادارة الاعمال' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'تقنية المعلومات والحوسبة / الشبكات وأمن البيانات' THEN 'BSc Information Technology & Computing'
WHEN specialization_ar LIKE 'دبلوم في ادارة الاعمال' THEN 'Diploma in Business Studies Study'
WHEN specialization_ar LIKE 'دبلوم في تقنية المعلومات والحوسبة' THEN 'Diploma Information Technology & Computing'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية - مسار الامتحان الشامل' THEN 'Master of Education in Leadership'
WHEN specialization_ar LIKE 'ماجستير التربية في القيادة التربوية- مسار الأطروحة' THEN 'Master of Education in Leadership'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم (مسار الأطروحة)' THEN 'Master of Education in Educational Technology'
WHEN specialization_ar LIKE 'ماجستير التربية في تكنولوجيا التعليم(مسار الامتحان الشامل)' THEN 'Master of Education in Educational Technology'
WHEN specialization_ar LIKE 'ماجستير في إدارة الأعمال' THEN 'Master of Business Administration' END
) as sp from epcms_uni_student_grade inner join epcms_uni_student on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid
where year >= 2012 && semester != 3 group by concat_ws('|', sp, year, semester) order by concat_ws('|', year, semester) ASC) tbl;
-- Students with T471
select epcms_uni_student.collegeid, name_en, group_concat(course_code, '|', course_part separator ', ') as course, group_concat(year, '|', semester separator ', ') as years_semesters, group_concat(grade separator ', ') as grade
from epcms_uni_student inner join epcms_uni_student_grade on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where
course_code like 'T471%' group by epcms_uni_student.collegeid order by collegeid ASC, year ASC, semester ASC, course_code ASC, course_part ASC;
-- Students per course in specific semester
select concat_ws('|', year, semester) as "year|semester", course_code, count(*) as students from epcms_uni_student_grade where concat_ws('|', year, semester) = '2017|1' group by course_code;
select concat_ws('|', year, semester) as "year|semester", course_code, count(*) as students from epcms_uni_student_grade where concat_ws('|', year, semester) >= '2016|1' && concat_ws('|', year, semester) <= '2016|3' group by concat_ws('|', year, semester, course_code);
-- Students per section in specific semester
select concat_ws('|', year, semester) as "year|semester", course_code, course_part, section, count(*) as students from epcms_uni_student_grade where concat_ws('|', year, semester) = '2017|1' group by concat_ws('|', course_code, course_part, section);
select concat_ws('|', year, semester) as "year|semester", course_code, course_part, section, count(*) as students from epcms_uni_student_grade where concat_ws('|', year, semester) >= '2016|1' && concat_ws('|', year, semester) <= '2016|3' group by concat_ws('|', year, semester, course_code, course_part, section);
-- Admitted Students VS Continues Students:
select beginning_year, count(*) as "Admitted Students",
(select count(distinct epcms_uni_student_grade.collegeid) from epcms_uni_student_grade inner join epcms_uni_student on epcms_uni_student_grade.collegeid = epcms_uni_student.collegeid where beginning_year = tbl.beginning_year && year = tbl.beginning_year) as "Registered Students (New Only)",
(select count(distinct epcms_uni_student_grade.collegeid) from epcms_uni_student_grade where year = tbl.beginning_year) as "Registered Students (Old + New)",
(select count(*) from epcms_uni_student where beginning_year = tbl.beginning_year && beginning_semester = 1) as "Admitted Students in SEM 1",
(select count(distinct epcms_uni_student_grade.collegeid) from epcms_uni_student_grade inner join epcms_uni_student std on epcms_uni_student_grade.collegeid = std.collegeid where beginning_year = tbl.beginning_year && beginning_semester = 1 && year = std.beginning_year && semester = std.beginning_semester) as "Registered Students in SEM 1 (NEW Only)",
(select count(distinct epcms_uni_student_grade.collegeid) from epcms_uni_student_grade where year = tbl.beginning_year && semester = 1) as "Registered Students in SEM 1 (Old + NEW)",
(select count(*) from epcms_uni_student where beginning_year = tbl.beginning_year && beginning_semester = 2) as "Admitted Students in SEM 2",
(select count(distinct epcms_uni_student_grade.collegeid) from epcms_uni_student_grade inner join epcms_uni_student std on epcms_uni_student_grade.collegeid = std.collegeid where beginning_year = tbl.beginning_year && beginning_semester = 2 && year = std.beginning_year && semester = std.beginning_semester) as "Registered Students in SEM 2 (NEW Only)",
(select count(distinct epcms_uni_student_grade.collegeid) from epcms_uni_student_grade where year = tbl.beginning_year && semester = 2) as "Registered Students in SEM 2 (Old + NEW)",
(select count(*) from epcms_uni_student where beginning_year = tbl.beginning_year && beginning_semester = 3) as "Admitted Students in SEM 3",
(select count(distinct epcms_uni_student_grade.collegeid) from epcms_uni_student_grade inner join epcms_uni_student std on epcms_uni_student_grade.collegeid = std.collegeid where beginning_year = tbl.beginning_year && beginning_semester = 3 && year = std.beginning_year && semester = std.beginning_semester) as "Registered Students in SEM 3 (NEW Only)",
(select count(distinct epcms_uni_student_grade.collegeid) from epcms_uni_student_grade where year = tbl.beginning_year && semester = 3) as "Registered Students in SEM 3 (Old + NEW)"
from epcms_uni_student tbl where beginning_year >= 2000 group by beginning_year;
-- Ministry FT students who did not register
select collegeid, name_ar, specialization_ar, courses, study_nature, accept_nature, study_status,
(select count(*) from epcms_uni_student_status where collegeid = epcms_uni_student.collegeid && semester != 3 && std_study_status = 2) as "discontine",
(select count(*) from epcms_uni_student_status where collegeid = epcms_uni_student.collegeid && semester != 3 && std_study_status = 4) as "postpone"
from epcms_uni_student where study_nature = 5 && accept_nature = 4 && courses = '' && study_status not in (4,6,8,9);
-- New students OP in EF003
select collegeid, name_ar, specialization_ar, courses, if(study_nature = 5, 'نظامي - حسابه الخاص أو تمويل', 'تعلم مفتوح') as study_nature from epcms_uni_student where (study_nature != 5 || study_nature = 5 && accept_nature != 4) && beginning_year = 2017 && beginning_semester = 2 && (locate('EF003', courses) > 0 || courses = '' && locate('EF002', completed_courses) > 0 );
-- Students per admission year
select epcms_uni_student.collegeid, epcms_uni_student.name_ar, GROUP_CONCAT(epcms_uni_student_grade.course_code) as courses, GROUP_CONCAT(epcms_uni_student_grade.grade) as grade from epcms_uni_student inner join epcms_uni_student_grade on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where epcms_uni_student.beginning_year = 2008 && year=2008 group by epcms_uni_student.collegeid
-- Students per admission year who registered in same year
select epcms_uni_student.collegeid, epcms_uni_student.name_ar, GROUP_CONCAT(epcms_uni_student_grade.course_code) as courses, GROUP_CONCAT(epcms_uni_student_grade.grade) as grade from epcms_uni_student inner join epcms_uni_student_grade on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where epcms_uni_student.beginning_year = 2008 && year=2008 && grade != '' group by epcms_uni_student.collegeid
-- Passing rate of Foundation
select *, round(passed/total * 100, 2) as percentage_passed,
round(EF003_passed/EF003_total * 100, 2) as percentage_EF003_passed,
round(IT100_passed/IT100_total * 100, 2) as percentage_IT100_passed,
round(MA100_passed/MA100_total * 100, 2) as percentage_MA100_passed
from (
select grd.year as year,
(select count(*) from epcms_uni_student_grade where year = grd.year && course_code in ('EF003', 'IT100', 'MA100') && grade != '') as total,
(select count(*) from epcms_uni_student_grade where year = grd.year && course_code in ('EF003', 'IT100', 'MA100') && grade = 'S') as passed,
(select count(*) from epcms_uni_student_grade where year = grd.year && course_code in ('EF003') && grade != '') as EF003_total,
(select count(*) from epcms_uni_student_grade where year = grd.year && course_code in ('EF003') && grade = 'S') as EF003_passed,
(select count(*) from epcms_uni_student_grade where year = grd.year && course_code in ('IT100') && grade != '') as IT100_total,
(select count(*) from epcms_uni_student_grade where year = grd.year && course_code in ('IT100') && grade = 'S') as IT100_passed,
(select count(*) from epcms_uni_student_grade where year = grd.year && course_code in ('MA100') && grade != '') as MA100_total,
(select count(*) from epcms_uni_student_grade where year = grd.year && course_code in ('MA100') && grade = 'S') as MA100_passed
from epcms_uni_student inner join epcms_uni_student_grade grd on
epcms_uni_student.collegeid = grd.collegeid where grd.year = 2017
group by grd.year) tbl;
-- Ministry students who dropped by W
select * from epcms_uni_student inner join epcms_uni_student_grade on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where
year = 2017 && semester = 2 && grade = 'W' && study_nature = 5 && accept_nature = 4;
-- Students repeat courses >= 3
select * from (
select epcms_uni_student_grade.collegeid, name_ar, specialization_ar, study_status, course_code,
group_concat(concat_ws('|', year, semester) ORDER BY YEAR ASC, semester ASC separator ', ') as semesters,
group_concat(grade ORDER BY YEAR ASC, semester ASC separator ', ') as grades, count(*) as repeats from epcms_uni_student_grade
inner join epcms_uni_student on epcms_uni_student_grade.collegeid = epcms_uni_student.collegeid where
-- grade like '%F%' &&
epcms_uni_student_grade.collegeid in (
select collegeid from epcms_uni_student where study_status in (1,2,4,13) && study_nature = 5 && accept_nature = 4
) group by concat_ws('|', epcms_uni_student_grade.collegeid, course_code) ) tbl where tbl.repeats >= 3;
-- Playground
select * from epcms_uni_student inner join epcms_uni_student_grade on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where epcms_uni_student.study_status in (1,2,4) and epcms_uni_student.collegeid in (select collegeid from epcms_uni_student_status where collegeid = epcms_uni_student.collegeid );
select collegeid from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = 2016 && semester = 2) && nationality_code != 10 ;
select collegeid from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = 2016 && semester = 2 && course_code like 'B716' && course_part = 2);
select * from epcms_uni_student inner join epcms_uni_student_grade on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where year = 2016 && semester = 2 && course_code like 'T471' && course_part = 2;
select * from epcms_uni_student_grade where course_code in ('EF002', 'EF002', 'EF003') && grade not like '%F%';
select * from epcms_uni_student_grade where course_code in ('EL111', 'AR111', 'GR101') ;
select collegeid from epcms_uni_student_grade where course_code in ('T215A') && year = 2016 && semester = 2 && grade like '%F%';
select * from epcms_uni_student_status where collegeid = 100083;
select * from epcms_uni_student_status where CONCAT_WS('|', year, semester) <= '2012|2' group by std_study_status;
select * from epcms_uni_student where is_graduated = 1;
-- 0:deleted, 1:pending, 2:started, 3:pre-confirmed; 4:final-confirmation, 5: rejected
select other_college, count(*) as total, GROUP_CONCAT(equalizationid) as equalizations from epcms_uni_equalization where status not in (0,5) group by other_college order by other_college;
-- Student council voting:
select vote, if(vote = 1, 'إيثار', 'عيسى') as "for",count(*) as total, concat_ws('', (count(*)/ (select count(*) from epcms_uni_temp_poll_result) * 100 ), '%') as percantage from epcms_uni_temp_poll_result group by vote;
select vote, if(vote = 1, 'إيثار', 'عيسى') as "for", specialization_ar, count(*) as total from epcms_uni_temp_poll_result inner join epcms_uni_student on epcms_uni_temp_poll_result.collegeid = epcms_uni_student.collegeid
group by concat_Ws('|', vote, specialization_ar) order by vote ASC, total DESC;
select logid, FROM_UNIXTIME(logtime), url, ip_address, extra from epcms_log where
url like '%ajaxLogin%' &&
-- url not like '%admin%' &&
extra like '%abdulkader%'
order by logid DESC;
select * from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = 2016 && semester = 1 && grade != 'w') group by civil_id; -- 1864
select * from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = 2016 && semester = 2 && grade != 'w') group by civil_id; -- 1829
select * from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_grade where year = 2016 && semester = 3 && grade != 'w') group by civil_id; -- 827
select * from (
select epcms_uni_student.collegeid, epcms_uni_student.name_en, epcms_uni_student.specialization_en, epcms_uni_student.gpa, GROUP_CONCAT(epcms_uni_student_grade.course_code SEPARATOR ' | ') as courses,
GROUP_CONCAT(epcms_uni_student_grade.total_mta + epcms_uni_student_grade.total_midexam SEPARATOR ' | ') as ca, GROUP_CONCAT(epcms_uni_student_grade.total_final SEPARATOR ' | ') as total_final,
GROUP_CONCAT(round(epcms_uni_student_grade.total_mta + epcms_uni_student_grade.total_midexam + epcms_uni_student_grade.total_final) SEPARATOR ' | ') as total_mark,
GROUP_CONCAT(if(epcms_uni_student_grade.total_mta + epcms_uni_student_grade.total_midexam >= 15 && epcms_uni_student_grade.total_final >= 20 && round(epcms_uni_student_grade.total_mta + epcms_uni_student_grade.total_midexam + epcms_uni_student_grade.total_final) >= 50, 'Passed', 'Failed') SEPARATOR ' | ') as status
from epcms_uni_student inner join epcms_uni_student_grade on epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid where epcms_uni_student.collegeid in
(select collegeid from epcms_uni_student_grade where epcms_uni_student.collegeid = epcms_uni_student_grade.collegeid && year = 2016 && semester = 3 ) &&
CONCAT_WS('|', epcms_uni_student_grade.year, epcms_uni_student_grade.semester) = (select max(CONCAT_WS('|', year, semester)) from epcms_uni_student_grade grd where grd.collegeid = epcms_uni_student_grade.collegeid) && specialization_en not like 'Master%' && grade != 'FI' group by epcms_uni_student.collegeid order by epcms_uni_student.specialization_en ASC, epcms_uni_student.name_en ASC
) tbl where tbl.status like '%Failed%';
select * from epcms_uni_student where specialization_en not like 'master%' && collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (1,2,4) && year = 2016 );
select * from epcms_uni_student where collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (1,2,4) && year = 2016 );
select last_year, count(*) as total from epcms_uni_student where study_status = 8 group by last_year;
select count(*) from epcms_uni_student where specialization_en not like 'master%' && study_nature != 5 && collegeid in (select collegeid from epcms_uni_student_status where std_study_status in (1,2,4) && year = 2017 );
select count(*) from epcms_uni_student where study_nature = 5 && beginning_year = 2017;
select collegeid, name_ar, specialization_ar from epcms_uni_student where specialization_en like 'master%' && study_status in (8,6,14,16) && collegeid >= 160000 && beginning_semester = 2 order by collegeid ASC;
select collegeid, name_ar, specialization_ar from epcms_uni_student where collegeid in (
120280,110615,110229,120218,100609,160256,110739,110736,120294,120630,120129,110633,130442,120151,140753
) && collegeid not in (select collegeid from epcms_uni_student_extradata where is_attending in (0,1)) order by collegeid ASC ;
-- temp: missing phones:
select collegeid, name_en, name_ar, mobile_number, home_tel, study_status from epcms_uni_student where collegeid in (
100631,100675,100668,100670,100719,110040,110046,100696,110004,100663,100610,100603,80351,90240,100577
) order by study_status ASC;
-- Temp: قيادة تربوية
select * from (
select epcms_uni_student_status.collegeid, epcms_uni_student.name_ar, epcms_uni_student.specialization_ar, study_status, count(*) as semesters from epcms_uni_student_status inner join epcms_uni_student on epcms_uni_student.collegeid = epcms_uni_student_status.collegeid where study_status in (1,2,4,7) && reg_status = 1 && semester != 3 && specialization_ar like '%القيادة%' group by epcms_uni_student_status.collegeid) tbl where tbl.semesters = 2 order by tbl.semesters ASC, tbl.name_ar ASC;
-- اتصالات وحوسبة أول فصل في التخصص
select * from epcms_uni_student where (specialization_ar like '%اتصالات%' || specialization_ar like '%/ الحوسبة%') &&
study_status not in (6,8,9,12,16) &&
(locate ('EL111', courses) > 0 || locate ('EL111', completed_courses) = 0 );
select collegeid, name_en, specialization_en, name_ar, specialization_ar, courses, completed_courses, left_courses, study_status from epcms_uni_student where (specialization_ar like '%اتصالات%' || specialization_ar like '%/ الحوسبة%') && study_status not in (6,8,9,12,16) && ch_mandatory_done = 0 && collegeid < 170000;
select collegeid, name_en, specialization_en, name_ar, specialization_ar, courses, completed_courses, left_courses, study_status from epcms_uni_student where (specialization_ar like '%اتصالات%' || specialization_ar like '%/ الحوسبة%') && study_status not in (6,8,9,12,16) && collegeid >= 170000;