Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation

Answer: Sorting and Calculations

Explore how to write SQL queries that sort and calculate data effectively. Understand the use of SELECT, WHERE, ORDER BY with calculations, COALESCE for NULL handling, and LIMIT to restrict results to master sorting and filtering 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 ...