Find Students with Scores Above 80
The "UptoSkills Arena" college wants to identify high-achieving students. They have a "Students" table with student information and an "Exams" table containing their exam scores.
- Students table:
- student_id (integer, PRIMARY KEY)
- name (varchar(100))
- major (varchar(50))
- Exams table:
- exam_id (integer, PRIMARY KEY)
- student_id (integer, FOREIGN KEY to Students)
- subject (varchar(50))
- score (integer)
Return the names of all students who have scored strictly greater than 80 in any subject. The names should be in alphabetical order. Handle ties by alphabetical order. NULL scores are not possible as the column is NOT NULL. Empty results are handled by returning an empty table. Duplicate student names should appear only once.
Tables
CREATE TABLE Students ( student_id integer PRIMARY KEY, name varchar(100) NOT NULL, major varchar(50) ); CREATE TABLE Exams ( exam_id integer PRIMARY KEY, student_id integer NOT NULL REFERENCES Students(student_id), subject varchar(50) NOT NULL, score integer NOT NULL );
Example data
INSERT INTO Students (student_id, name, major) VALUES (1, 'Alice', 'Computer Science'), (2, 'Bob', 'Physics'), (3, 'Charlie', 'Computer Science'), (4, 'David', 'Mathematics'), (5, 'Eve', 'Physics'); INSERT INTO Exams (exam_id, student_id, subject, score) VALUES (101, 1, 'Calculus', 90), (102, 1, 'Linear Algebra', 85), (103, 2, 'Classical Mechanics', 75), (104, 3, 'Data Structures', 88), (105, 3, 'Algorithms', 92), (106, 4, 'Calculus', 70), (107, 5, 'Quantum Physics', 95), (108, 5, 'Thermodynamics', 80);
Your query is also checked on hidden datasets with different data.
Hints
-- Select student names
SELECT DISTINCT S.name
FROM Students S
Your query runs on the example tables shown with the problem, then on hidden datasets when you submit.
Write standard SQL. MySQL habits work too: IFNULL, IF, YEAR(), DATE_FORMAT, GROUP_CONCAT, `backticks` and LIMIT 5, 10. It runs on PostgreSQL, so integer division keeps whole numbers (5 / 2 = 2): write 5.0 / 2 for 2.5.