1

I need to write a SQL query which Displays the Name of the Courses which the students have an average greater than 55 in it

TABLES : STUDENTS,GRADES,COURSES,DEPARTMENTS

WHAT I DID :

SQL> SELECT COURSE_NAME
  2  FROM COURSES
  3  where
  4  (select avg(grade) 
  5  from grades,courses
  6  where   
  7  grades.course_id=courses.course_id)>55;

and the result is bad ! (It displays all the courses)

TABLES :

 create table DEPARTMENTS
(
DEPARTMENT_ID char(2),
NAME varchar2(20),
HEAD varchar2(20));

 create table COURSES
 (
COURSE_ID char(10), 
COURSE_NAME varchar2(20),
TYPE char(6),
POINTS number(2),
DEPARTMENT_ID char(2));

create table GRADES
(
STUDENT_ID number(3),
COURSE_ID char(10), 
SEMESTER varchar2(10),
TERM char(1), 
GRADE number(3),
GRADE_SEM number(3));

create table STUDENTS   
(
STUDENT_ID number(3),
NAME char(15),
CITY char(15));
Waseem Gabour
  • 39
  • 1
  • 7

3 Answers3

5

Your query returns every record or no records from COURSES based on whether the subquery returns more than 55 or less than 55, because the outer query is not related to the subquery in any way. You want to JOIN the tables, GROUP BY the course name, and use a HAVING clause to filter:

SELECT c.course_name 
FROM grades g
JOIN courses c
  ON   g.course_id = c.course_id
GROUP BY c.course_name
HAVING AVG(grade) > 55
Hart CO
  • 34,064
  • 6
  • 48
  • 63
  • Ah ! it works , but what if i wanted to find the maximum of all averages of courses grades ? I do ( **HAVING MAX(AVG(GRADE))** ) ? – Waseem Gabour Jan 09 '14 at 22:16
  • If you wanted to find all that had the max average grade, you could use a subquery in the having clause: `HAVING AVG(grade) = (SELECT MAX(AVG(grade) OVER(PARTITION BY course_id)) max_avg_gd FROM grades)` – Hart CO Jan 09 '14 at 22:28
  • No . its not "ALL" .. i am talking about a course for every course there are average of grades now from all the averages of courses .. i need to find the maximum average of a single course , And ah ! am sorry .. i need to find the COURSE_ID of the maximum of all averages .. not the maximum value – Waseem Gabour Jan 09 '14 at 23:12
-1

Try this query :

select COURSE_NAME,  avg(grade) from grades,courses where grades.course_id=courses.course_id
group by COURSE_NAME
having avg(grade) > 55
dardar.moh
  • 5,987
  • 3
  • 24
  • 33
-1

This is your subquery:

select avg(grade) from grades,courses where
grades.course_id=courses.course_id

Try running that query first. You should see it return a single number. But there is nothing else attached to that number. So the outer query can't do anything with it except return all courses.

user3179536
  • 1