Question

Database design

On Blackboard, I work with your grades. There is a table (called Grades) that includes course, firstName, lastName, lastAccessDate, and all of your grades for each assignment and exam (a1, a2, a3, a4, a5, a6, a7, m1, m2, final). Assume that each of my classes has exactly the same distribution of assignments, namely that there are seven assignments, two midterms, and a final.

Write a query that will determine the number of students who have averaged at least an 83 on the two exams and have not yet submitted the final in each of my courses, sorted by the course ascending.

DO NOT generate the tables. Just provide the query. You have everything you need in the question to answer it.

Answer 1

SQL Query:

SELECT course, COUNT(*) FROM grades where ((m1+m2)/2) >= 83 and final IS NULL GROUP BY course ORDER BY course ;