You have a table Scores(student_id, score) and a table Grades(grade, min_score, max_score). Write a query to assign each student their grade based on their score using a non-equi join.
hard📝 Application Q15 of 15
SQL - Advanced Joins
You have a table Scores(student_id, score) and a table Grades(grade, min_score, max_score). Write a query to assign each student their grade based on their score using a non-equi join. Which query correctly implements this?
ASELECT s.student_id, g.grade FROM Scores s JOIN Grades g ON s.score >= g.min_score AND s.score < g.max_score;
BSELECT s.student_id, g.grade FROM Scores s JOIN Grades g ON s.score <= g.min_score AND s.score >= g.max_score;
CSELECT s.student_id, g.grade FROM Scores s JOIN Grades g ON s.score > g.min_score AND s.score <= g.max_score;
DSELECT s.student_id, g.grade FROM Scores s JOIN Grades g ON s.score BETWEEN g.min_score AND g.max_score;
Step-by-Step Solution
Solution:
Step 1: Understand grading ranges
Grades are assigned where score is between min_score (inclusive) and max_score (exclusive) to avoid overlap.
Step 2: Check each join condition
SELECT s.student_id, g.grade FROM Scores s JOIN Grades g ON s.score >= g.min_score AND s.score < g.max_score; uses s.score >= g.min_score AND s.score < g.max_score, correctly defining non-overlapping ranges.
Step 3: Verify other options
SELECT s.student_id, g.grade FROM Scores s JOIN Grades g ON s.score BETWEEN g.min_score AND g.max_score; includes max_score in BETWEEN (inclusive), which may cause overlap. SELECT s.student_id, g.grade FROM Scores s JOIN Grades g ON s.score > g.min_score AND s.score <= g.max_score; reverses inclusivity. SELECT s.student_id, g.grade FROM Scores s JOIN Grades g ON s.score <= g.min_score AND s.score >= g.max_score; reverses logic incorrectly.
Final Answer:
SELECT s.student_id, g.grade FROM Scores s JOIN Grades g ON s.score >= g.min_score AND s.score < g.max_score; -> Option A
Quick Check:
Use >= min and < max for non-overlapping ranges [OK]
Quick Trick:Use >= min_score and < max_score for clean grade ranges [OK]
Common Mistakes:
MISTAKES
Using BETWEEN which includes max_score causing overlap
Swapping < and > operators
Using incorrect inclusivity causing duplicate grades
Master "Advanced Joins" in SQL
9 interactive learning modes - each teaches the same concept differently
