Write a query to find the name of the student(s) who has scored maximum mark in Software Engineering. Sort the result based on name.
Answers
Explanation:
Assumption:
Table: "Marks" which contains details of marks along with “student id”
Table: "Student" which contains student name, id, etc
Table:"Subject" which contains subject name and subject ID
We achieve the result using sub query. The sub query retrieve the student id who scored the maximum mark and then through the outer query we retrieve the name of the respective student.
Note that we have used "IN" because there might me more than one student who have achieved the maximum mark.
Finally we sort them using "order by" clause.
To Know More:
1. https://brainly.in/question/9085753
How can we nest a subquery within another subquery?
2. https://brainly.in/question/9358326
In a subquery, the all operator compares a value to every value returned by the inner query. True or false?
Answer:
SELECT student_name
FROM student
INNER JOIN mark
ON student.student_id=mark.student_id
INNER JOIN subject
ON mark.subject_id=subject.subject_id
WHERE subject_name='Software Engineering'
AND value=(
SELECT max(value)
FROM mark
INNER JOIN subject
ON mark.subject_id=subject.subject_id
WHERE subject_name='Software Engineering'
)ORDER BY student_name;
Explanation: