UptoSkills Logo
Login
Students with Above Average GPA in Their DepartmentMedium

Students with Above Average GPA in Their Department

MediumSQLPractice library · +20 XPsubqueriessqlaggregation

At UptoSkills Arena, we want to identify students who are performing exceptionally well in their academic programs. We need a list of students whose Grade Point Average (GPA) is higher than the average GPA of all students within their department.

  • Students table:
    • student_id (integer, primary key)
    • name (varchar(100))
    • department (varchar(50))
    • gpa (numeric(3,2))

Return the student_id, name, and department for these high-performing students. The results should be ordered by department and then by student_id.

Handle ties by including all students whose GPA equals the calculated department average plus a small epsilon (effectively greater than the average). NULL GPAs should be ignored in calculations. If a department has no students with a non-NULL GPA, it should not appear in the results.

Tables

CREATE TABLE Students (
  student_id integer PRIMARY KEY,
  name varchar(100) NOT NULL,
  department varchar(50) NOT NULL,
  gpa numeric(3,2)
);

Example data

INSERT INTO Students (student_id, name, department, gpa)
VALUES
(1, 'Alice', 'Computer Science', 3.8),
(2, 'Bob', 'Computer Science', 3.5),
(3, 'Charlie', 'Computer Science', 3.9),
(4, 'David', 'Electrical Engineering', 3.6),
(5, 'Eve', 'Electrical Engineering', 3.4),
(6, 'Frank', 'Mechanical Engineering', 3.7),
(7, 'Grace', 'Mechanical Engineering', 3.7),
(8, 'Heidi', 'Physics', NULL);

Your query is also checked on hidden datasets with different data.

Hints

Similar problems

More in: SQL and Databases problems