Search⌘ K
Home/
Courses/
SQL Interview Preparation

Answer: Sorting and Calculations

Find a detailed explanation of using advanced sorting techniques.

We'll cover the following...

Solution

The solution is given below:

MySQL
/* Query to retrieve top 3 students who received highest cumulative marks in Science */
SELECT   StudentName, TheoryMarks, PracticalMarks, (TheoryMarks + PracticalMarks) AS CumulativeMarks
FROM     StudentGrades 
WHERE    Subject = 'Science' 
ORDER BY (COALESCE(TheoryMarks,0) + COALESCE(PracticalMarks,0)) DESC 
LIMIT    3;

Explanation

The explanation of the solution code is given below:

  • Line 2: The SELECT query selects StudentName, TheoryMarks, PracticalMarks, and CumulativeMarks.

  • Line 3: The FROM clause specifies the table name as StudentGrades.

  • Line 4: The WHERE clause selects the subject in hand.

  • Line 5: The ORDER BY clause is applied to the addition of TheoryMarks and PracticalMarks and sorts the data in descending order. The COALESCE() function ensures that if there is a NULL value in the column, it is replaced with 0 ...