Display all students that have promoted all courses-CodePudding

I have the following tables:

Students (id, name, surname, study_year, department_id)

Courses(id, name)

Course_Signup(id, student_id, course_id, year)

Grades(signup_id, grade_type, mark, date), where grade_type can be 'e' (exam), 'l' (lab) or 'p' (project)

I want to display the students with their yearly grade (average of the final grade for all courses), but only for those students that have passed ALL the courses that they have signed up for. For example, if a student signs up for 3 courses but only gets a final grade >= 5 to 2 of those courses, that student should not appear in the result. I wrote this so far:

SELECT
  new_table.id,
  new_table.name,
  AVG(CASE WHEN grade_type  = 'e' THEN new_table.mark END) AS "Average Exam Grade",
  AVG(CASE WHEN GRADE_TYPE <> 'e' THEN new_table.mark END) AS "Average Activity Grade",
  (2*AVG(CASE WHEN grade_type  = 'e' THEN new_table.mark END)   AVG(CASE WHEN GRADE_TYPE <> 'e' THEN new_table.mark END))/3 AS "Course Final Grade"
FROM
(
  SELECT s.id, c.name, g.grade_type, g.mark
    FROM Students s
    JOIN Course_Signup csn
         ON s.id = csn.student_id
    JOIN Courses c
         ON c.id = csn.course_id
    JOIN Grades g
         ON g.signup_id = csn.id
) new_table
GROUP BY new_table.id, new_table.name
HAVING ((2*AVG(CASE WHEN grade_type  = 'e' THEN new_table.mark END)   AVG(CASE WHEN GRADE_TYPE <> 'e' THEN new_table.mark END))/3) >= 5.00
ORDER BY new_table.id ASC

This gives me the students with every course that they promoted. What would be the easiest way to check that a student has a course final grade >= 5, for every course that they signed up for?

CodePudding user response：

You can use the MIN statement in your query to check that the minimal score is more or equal to 5.

This assumes that you will have every grade recorded in the database.

Also, in your query you have Recap_ prefix for your table names. I removed it to align with the table names in the question.

SELECT
  new_table.id,
  new_table.name,
  AVG(CASE WHEN grade_type  = 'e' THEN new_table.mark END) AS "Average Exam Grade",
  AVG(CASE WHEN GRADE_TYPE <> 'e' THEN new_table.mark END) AS "Average Activity Grade",
  (2*AVG(CASE WHEN grade_type  = 'e' THEN new_table.mark END)   AVG(CASE WHEN GRADE_TYPE <> 'e' THEN new_table.mark END))/3 AS "Course Final Grade"
FROM
(
  SELECT s.id, c.name, g.grade_type, g.mark
    FROM Students s
    JOIN Course_Signup csn
         ON s.id = csn.student_id
    JOIN Courses c
         ON c.id = csn.course_id
    JOIN Grades g
         ON g.signup_id = csn.id
) new_table
GROUP BY new_table.id, new_table.name
HAVING MIN(new_table.mark) >= 5.00
ORDER BY new_table.id ASC

CodePudding user response：

To get the students satisfying the conditions "grades on all sign-up and mark >=5", you can use traditional relational division query:

select s.id, s.name, cs.year, g.grade_type, g.mark 
from students s
    join Course_Signup cs on cs.student_id = s.id
    join Grades g ON cs.id = g.signup_id
where not exists(
    select 1 from Course_Signup cs
    where s.id = cs.student_id
    and not exists(
        select 1 from grades g 
        where g.signup_id = cs.id 
        and g.mark >= 5
    )
);