Grading Query Attempt

Other SQL Server Topics
1

Playing around with SQL and found this question in a book I picked up at the library and was attempting to figure it out. Just would like some one who knows more about sql to take a look and see if what I have down will complete what they asked.

Here the question:
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.

This is my query:

query="
SELECT AVG(m1+m2)
From grades
Where AVG >= "83"
ORDER BY course ASC,

"
sqldf(query)

2

This query will not work. What I would suggest is that you create a "grades" table to test your work.

3

I would say the question is somewhat advanced for one who has just picked up a book.
Writing 'SELECT AVG(m1+m2)' is good. But writing 'Where AVG >= "83"' is not correct syntax of the AVG function.

4

Can't tell if all the grades are on one row or different rows. If they are all on the same row then:

SELECT COUNT(DISTINCT lastName + firstName)
From grades
WHERE ((m1 + m2) / 2.0) >= 83 AND final IS NULL
5

image

This is the way the tables are set-up

6

Have attempted this and when I run the query I get :
image

It should be 3

7

it is:

WITH grades (lastname, firstname, m1, m2, final) AS
(SELECT * FROM (VALUES
    ('Faith', 'Weymouth', 92, 84, NULL),
    ('Marc',  'Weymouth', 88, 84, NULL),
    ('Alan',  'Weymouth', 77, 66, NULL),
    ('Sadie', 'Weymouth', 88, 79, NULL)
    ) v(a,b,c,d,e)
)

SELECT COUNT(DISTINCT lastName + firstName)
From grades
WHERE ((m1 + m2) / 2.0) >= 83 AND final IS NULL

image

8

Thanks gbritton.

I am attempting this using RGui 64bit. When I attempt to run it I get this error:
Error in rsqlite_send_query(conn@ptr, statement) : near ")": syntax error

Not sure why could it be something with RGui?

9

Are you using SQLite? Not sure it supports the syntax I gave you. Since this is a SQL Server forum, I wrote the query for SQL Server. I don't have SQLite handy at the moment to play with that. You might want to try SQLLite forums, though.