Getting Student Name With Highest Total Mark in SQL

Getting student name with highest total mark in SQL

get the max() total first then use a subquery

    select * from marks where (social+math+science) =    
      (select max(social+math+science) from marks); 

Highest Mark in each course with student name

You can use window functions. One method is:

select . . .   -- whatever columns you want
from (select sc.*,
             rank() over (partition by course_id order by exam_mark desc) as seqnum
      from student_courses sc
     ) sc join
     students s
     on sc.student_id = s.id join
     courses c
     on sc.course_id = c.id
where seqnum = 1;

How to get the name of a the student who got max marks in each subject?

You can use the ROW_NUMBER function to return only the "best" row per subject:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE Student
    ([Name] varchar(1), [Subject] varchar(1), [Marks] int)
;

INSERT INTO Student
    ([Name], [Subject], [Marks])
VALUES
    ('a', 'M', 20),
    ('b', 'M', 25),
    ('c', 'M', 30),
    ('d', 'C', 44),
    ('e', 'C', 45),
    ('f', 'C', 46),
    ('g', 'H', 20)
;

Query 1:

SELECT Name, Subject, Marks
FROM(
  SELECT *, ROW_NUMBER()OVER(PARTITION BY Subject ORDER BY Marks DESC) rn
    FROM dbo.Student
)X
WHERE rn = 1

Results:

| NAME | SUBJECT | MARKS |
--------------------------
|    f |       C |    46 |
|    g |       H |    20 |
|    c |       M |    30 |

Students with Highest Mark

You don't need subject there. Question asks Max mark per student, regardless of subject:

SELECT s.Student_Name, MAX(M.VALUE) as MAX_MARK
from Student s
inner Join Mark M on m.student_id = s.student_id  
group by s.student_id, s.student_name 
order by s.student_name;

Find the name of the student who has scored highest marks in every sem and each course?

First, learn to use proper, explicit, standard JOIN syntax. Second, the best way to do this uses window functions:

SELECT sm.*
FROM (SELECT s.course as course_name, mc.sem, s.name, mc.tot,
              MAX(tot) OVER (PARTITION BY s.course, mc.sem) as max_tot
      FROM markscard mc JOIN
           student s
           ON s.regno = mc.regno
     ) sm
WHERE tot = max_tot;

Sql Query to fetch highest marks

This is a solution without using RANK or ROW_NUMBER. Also this is much simplified to meet your output. Hope it helps

SELECT st.StudentName ,s.Score ,su.SubjectName  FROM (SELECT
SubjectID,MAX(Score) as Max   FROM Score GROUP BY SubjectID) a
INNER JOIN Score s  on a.SubjectID=s.SubjectID AND a.MAX=s.Score
INNER JOIN Student st on s.StudentID=st.StudentID 
INNER JOIN Subject su on s.SubjectID=su.SubjectID 

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

Apart from the fact that you're using outdated implicit comma syntax for joins, you are also combining columns of the tables in the wrong way in the sub query.

subject_name is a column of subject which has nothing to do with the student's relation to marks. So, mark may be joined separately with subject while determining the student_ids with highest mark. We can then obtain the name of the student using those student_ids

So, In Oracle 12c and above, you could do

SELECT s.student_name
   FROM student s
WHERE s.student_id IN ( SELECT m.student_id
                        FROM mark m JOIN subject su 
                         ON su.subject_id = m.subject_id
                        WHERE lower(su.subject_name) = 'software engineering'
                           ORDER BY m.value DESC
                        FETCH FIRST 1 ROWS WITH TIES ) order by 1;  

For previous versions, you may use dense_rank or rank

SELECT s.student_name
   FROM student s
WHERE s.student_id IN ( SELECT student_id
                        FROM ( SELECT m.*,DENSE_RANK() OVER(
                                    ORDER BY m.value DESC
                               ) AS rnk
                               FROM mark m  JOIN subject su 
                                ON su.subject_id = m.subject_id
                        WHERE lower(su.subject_name) = 'software engineering'
                    ) WHERE rnk = 1
               ) order by 1;     

Write a query to display the student names and the maximum mark scored by them in any subject

If you only required student_id and MAX number, you can use only tables Student and Marks as below-

SELECT A.stident_id,MAX(B.Value) max_marks
FROM Student A
INNER JOIN Mark B ON A.Student_id = B.Student_id
GROUP BY A.stident_id

But if you need subject name as well, you can try this below logic-

SELECT AA.stident_id,AA.stident_name,
D.Subject_name,AA.max_marks
FROM
(
    SELECT A.stident_id,A.stident_name,MAX(B.Value) max_marks
    FROM Student A
    INNER JOIN Mark B ON A.Student_id = B.Student_id
    GROUP BY A.stident_id
)AA 
INNER JOIN Marks C ON AA.stident_id = C.stident_id 
AND AA.max_marks = C.Value
INNER JOIN Subject D ON C.subject_id = D.subject_id

---

Related Topics