Find High-Scoring Students in Python
UptoSkills Arena needs to identify high-performing students in the 'Python Programming' course for an advanced workshop. The results will be used to invite students for a special training session.
Tables:
Students(student_id integer, student_name varchar(100))Scores(score_id integer, student_id integer, course_name varchar(50), score integer)
Return the student_name and score for all students who scored greater than 75 in the 'Python Programming' course. The results should be ordered by score in descending order, then by student_name in ascending order to break ties. Ties in scores will be handled by alphabetical order of names. NULL scores are not considered. An empty result set is possible if no student meets the criteria.
Tables
CREATE TABLE Students (
student_id integer PRIMARY KEY,
student_name varchar(100) NOT NULL
);
CREATE TABLE Scores (
score_id integer PRIMARY KEY,
student_id integer REFERENCES Students(student_id),
course_name varchar(50) NOT NULL,
score integer
);Example data
INSERT INTO Students (student_id, student_name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie'), (4, 'David'), (5, 'Eve'); INSERT INTO Scores (score_id, student_id, course_name, score) VALUES (101, 1, 'Python Programming', 85), (102, 1, 'Data Structures', 90), (103, 2, 'Python Programming', 70), (104, 3, 'Python Programming', 95), (105, 3, 'Algorithms', 88), (106, 4, 'Python Programming', 75), (107, 5, 'Python Programming', 80), (108, 5, 'Web Development', 78);
Your query is also checked on hidden datasets with different data.
Hints
-- Select student names and scores for high achievers
SELECT
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.