Hackerrank - SQL - Higher Than 75 Marks
#sql#beginners#hackerrank#learning
Mr Punk da Silva

Mr Punk da Silva @mrpunkdasilva

About: I'm a Brazilian Software Engineer passionate about creating elegant solutions for complex problems. I specialize in full-stack development with a focus on clean code, solid architecture, and exception

Location:
In my mind
Joined:
Jul 19, 2023

Hackerrank - SQL - Higher Than 75 Marks

Publish Date: Jul 12
0 0

Problem Description

Query the Name of any student in STUDENTS who scored higher than 75 Marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

Input Format

The STUDENTS table is described as follows:

Column Type
ID INTEGER
NAME STRING
MARKS INTEGER

The Name column only contains uppercase (A-Z) and lowercase (a-z) letters.

Solution Approach

  1. Use a SELECT statement to retrieve the NAME column from the STUDENTS table
  2. Apply a WHERE clause to filter for students with marks greater than 75
  3. Order the results by:
    • The last three characters of each name (using the RIGHT function)
    • Student ID as a secondary sort criterion

Step-by-Step Explanation

  1. Start with the SELECT statement to retrieve the NAME column: 
   SELECT NAME
Enter fullscreen mode Exit fullscreen mode
  1. Specify the table to query from: 
   FROM STUDENTS
Enter fullscreen mode Exit fullscreen mode
  1. Add the WHERE clause to filter for students with marks greater than 75: 
   WHERE MARKS > 75
Enter fullscreen mode Exit fullscreen mode
  1. Add the ORDER BY clause with two sort criteria:
    • First, sort by the last three characters of each name using the RIGHT function
    • Then, sort by ID in ascending order 
   ORDER BY RIGHT(NAME, 3), ID
Enter fullscreen mode Exit fullscreen mode
  1. The final query: 
   SELECT NAME
   FROM STUDENTS
   WHERE MARKS > 75
   ORDER BY RIGHT(NAME, 3), ID
   ;
Enter fullscreen mode Exit fullscreen mode

Expected Output

The query will return a single column containing the names of students who scored more than 75 marks, ordered by the last three characters of their names. If multiple students have names ending with the same three characters, they will be sorted by their ID in ascending order.

Repo: https://github.com/mrpunkdasilva/hackerrank/tree/main/sql/basic/more-than-75-marks

Comments 0 total

    Add comment