Higher Than 75 Marks | SQL Basic Select

Lesson 26

Courses / HackerRank SQL Problem Solving Solution Challenges

Higher Than 75 Marks | SQL Basic Select | HackerRank Solution

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

Summary

# SQL Lesson Summary: Querying Students with Marks Higher than 75

In this lesson, we are tasked with querying a table of students to find those who have scored more than 75 marks. The query must return only the names of these students, ordered by the last three characters of their names and then by their IDs in ascending order in case of ties.

## Steps Covered:

1. **Understanding the Data**: 
   - The `students` table contains columns: `ID`, `Name`, and `Marks`.

2. **Filtering the Data**:
   - We need to filter the records for students with marks greater than 75 using a `WHERE` clause.

3. **Selecting the Required Columns**:
   - Only the `Name` column will be queried.

4. **Ordering the Results**:
   - Use the `RIGHT()` function to get the last three characters of the name for sorting:
     - Syntax: `RIGHT(name, 3)` 
   - Add a secondary sort based on the `ID` column to ensure a consistent order for entries with the same last three characters.

## SQL Query Example:

Here’s a sample SQL query based on the steps outlined:

```sql
SELECT Name
FROM students
WHERE Marks > 75
ORDER BY RIGHT(Name, 3), ID;

Execution:
- After writing the SQL query, execute it and verify the output conforms to the requirements.

The fulfilled query successfully filters and orders the student names, achieving the objective.

Video Transcript

Hey everybody, welcome to another lesson. I will do higher than 75 marks from the basic select subsection of SQL and HackerRank. Here we're given the stable of students with the column's ID name and marks and we're asked to query the name of any student that is we're going to query only the value from the name column who will score higher than 75 marks that is we have to filter by the marks being greater than 75. Then we got an order you output by the last three characters each name. So for that we have to take the name, get only the last three characters and use that to order and if we have the same last three characters for the same for different names then we have to order secondarily by ascending ID. So that's the second clause of the order by. So let's get started. Let's first see what the data looks like. I'll do select star from students and now click run code. So there you go we have ID, the name and marks. So let's first filter out all the ones that have marks less than or equal to 75 so we're going to add a where and the name of the call is marks. We have to have it greater than 75 and that's going to take care of eliminating for example this line here 5. Now we have to query only the name so the name of the column for the select is only name we don't care about the other columns. Now we have to order by and the last three characters of a name. How can we get the last three characters of name? There's a function you can use that's right here's the docs for DB2. This function will do the job just pass the column in this case name comma the number of characters you want from the right hand side of that string. In this case would be three and if you're using my SQL it's the same function and same arguments here. Going back there I'm going to do write of name comma since we want only the last three I have to say three and that's going to take only the last three characters of the string and that's going to be ordered alphabetically by default right ASC is the default implicit so I don't have to write it. Now if it happens that the two names or more will have the same last three characters we have to find another way of sorting those and that's by ascending ID as required by the question. So here I have to say comma and I'm going to say ID that's the name of the column ID and it's by default ASC so you can either write it or not. So let's run the code and see what we get and there you go that's the solution let me submit the code and that's higher than 75 marks.