Writing two queries (sort students and cities according to average grades)

Question

I have three tables:

1) Students: studentID (KEY), name, surname, address
2) Exams: examID (KEY), examName
3) Grades: studenID (KEY), examID(KEY), grade

How to write SQL query to show the best students (for example those with average grade above 9)?

How to write SQL query to rank Cities (column address) according to the average grade of their students?

I'm a system engineer, working with Unix and Linux systems and I am new in SQL, I only know about SQL basics, and I was trying to do this for past three days, with no success, so please help me. I presume it's not a complex thing for one who's experienced in SQL. Thanks a lot.

Could we see some of the code from the past three days? Maybe we'll all learn something from seeing why your attempts didn't work. — Halvor Holsten Strand, Aug 10 '14 at 11:31
My attempts didn't work cause I didn't have an idea how to write suitable queries. I wrote some very basic that list some data, but I don't know how to combine these three tables and calculate average grade for each student.. — MilosC, Aug 10 '14 at 11:41
Rather than fumbling about, it might be time well spent learning the basics first. I've heard good things about the book, Teach Yourself SQL in 10 Minutes. — Dan Bracuk, Aug 10 '14 at 11:57

Bahrami-Reza · Answer 1 · 2014-08-10T13:11:29.870

your first query to show the best students :

  SELECT student.surname, students.surename, students.address
  FROM 
  students INNER JOIN Grades ON Grades.StudentID=Students.StudentID 
  INNER JOIN Exams ON Grades.examID=exams.examID WHERE Grades.grade=
  (SELECT MAX(grade) FROM Grades WHERE examID=exams.examID)

your second query to rank cities:

  SELECT students.address
  FROM 
  students INNER JOIN Grades ON Grades.StudentID=Students.StudentID 
  INNER JOIN Exams ON Grades.examID=exams.examID order by grades.grade DESC

score 0 · Answer 2 · edited May 23 '17 at 10:33

Refer the fiddle here:

LINK 1 : http://sqlfiddle.com/#!4/ab4de6/19

LINK 2 : http://sqlfiddle.com/#!4/ab4de6/32

Below Queries should help you in Oracle:

--List of Students having Average grade >=9

SELECT S.studentID, S.NAME, S.SURNAME, S.ADDRESS, A.AVG_GRADE FROM 
STUDENTS S JOIN
( 
   SELECT studentID, AVG(GRADE) AVG_GRADE FROM GRADES
   GROUP BY studentID

)  A
ON S.studentID = A.studentID
AND A.AVG_GRADE >=9

ORDER BY A.AVG_GRADE, S.studentID;


--------------------------------------------------------------------

--- Rank cities
SELECT A.ADDRESS, A.AVG_GRADE, ROWNUM RANKING FROM
(
    SELECT S.ADDRESS, AVG(G.GRADE) AVG_GRADE FROM
    STUDENTS  S JOIN GRADES  G
    ON S.STUDENTID = G.STUDENTID
    GROUP BY S.ADDRESS
    ORDER BY 2 DESC
  ) A;

You need to know about the following concepts.

INNER QUERY / SUB QUERY
JOINS
AGGREGATE FUNCTIONS (Average Calculations)
GROUP BY
ORDER BY
ROWNUM

Writing two queries (sort students and cities according to average grades)

2 Answers2