Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation

Answer: Sorting and Calculations

Explore SQL techniques to sort data using the ORDER BY clause, replace NULL values with COALESCE, and limit query results. Understand filtering, aliasing, and subqueries to refine data sorting for effective query building.

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 ...