HackerRank: [SQL Basic Join] (4/8) THE REPORT | case when & left join in SQL

I started studying SQL from a very famous site - HackerRank. Here I will try to provide multiple approaches & solutions to the same problem. It will help you learn and understand SQL in a better way.

Please make use of my blog posts for learning purpose only and feel free to ask your questions in the comment box below in case of any doubt.

Click Here for the previous blog-post in the series.

Recommended SQL Courses:

1. Udemy: SQL - MySQL for Data Analytics and Business Intelligence
2. LinkedIn: Master SQL for Data Science
3. edX: Databases: Advanced Topics in SQL
4. edureka: SQL Essentials Training & Certification
5. Coursera: Learn SQL Basics for Data Science
6. Eduonix: SQL Queries

---

SQL Problem Statement:

Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.

Write a query to help Eve.

Input Format:

You are given two tables: Students and Grades. Students contains three columns ID, Name and Marks:

Students

Grades

Sample Input:

Sample Output:

Maria 10 99 
Jane 9 81 
Julia 9 88 
Scarlet 8 78 
NULL 7 63 
NULL 7 68

Note: Print "NULL" as the name if the grade is less than 8.

Explanation:

Consider the following table with the grades assigned to the students:

So, the following students got 8, 9 or 10 grades:

• Maria (grade 10)
• Jane (grade 9)
• Julia (grade 9)
• Scarlet (grade 8)

---

Solution: Using LEFT JOIN & CASE WHEN (MySQL Query):

SELECT
  CASE
    WHEN GRADES.GRADE >= 8 THEN STUDENTS.NAME
    WHEN GRADES.GRADE < 8 THEN NULL
  END AS NAME,
  GRADES.GRADE,
  STUDENTS.MARKS
FROM STUDENTS
  LEFT JOIN GRADES ON STUDENTS.MARKS >= MIN_MARK
  AND STUDENTS.MARKS <= MAX_MARK
ORDER BY
  GRADES.GRADE DESC, STUDENTS.NAME ASC, STUDENTS.MARKS ASC;

NOTE: 

1. CASE WHEN is used to just like IF... Else... in other programming languages.
   
   
2. LEFT JOIN returns all the records from the left table with records that have matching values in the right table.

---

Expected Output:

Britney 10 95
Heraldo 10 94
Julia 10 96
Kristeen 10 100
Stuart 10 99
Amina 9 89
Christene 9 88
Salma 9 81
Samantha 9 87
Scarlet 9 80
Vivek 9 84
Aamina 8 77
Belvet 8 78

--------------------------------------------------------------------------------

Click here to see solutions for all Machine Learning Coursera Assignments.

&

Click here to see more codes for Raspberry Pi 3 and similar Family.

&

Click here to see more codes for NodeMCU ESP8266 and similar Family.

&

Click here to see more codes for Arduino Mega (ATMega 2560) and similar Family.

Feel free to ask doubts in the comment section. I will try my best to answer it.

If you find this helpful by any mean like, comment and share the post.

This is the simplest way to encourage me to keep doing such work.

Thanks & Regards,

-Akshay P Daga