Finding Out Who Got the Highest Mark Among the Students

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;

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 |

how can i find the total and the The highest marks in 2d array

Prerequisite

Before calculating highest mark and total marks for each student, you need two create two arrays to store that data. The size of the array will be the number of students.

int[] totalMarks = new int[studentsCount];
int[] highestMark = new int[studentsCount];


Logic

Once you have these, total and highest marks can be calculated in two different loops or one single loop.

For any student i,

highestMark[i] = max(marks[0][i], marks[1][i])
totalMarks[i] = marks[0][i] + marks[1][i]

To do the same process for all the students, use a simple for loop from i = 0 to studentCount.



Improvements

Your program can also be improved. Start giving meaningful name to your variables. Instead of NUMBER use studentCount. Even though Sc works okay, scanner is better. Another improvement is:

for(j=0;j<studentCount;j++) 
{
System.out.println ("For student " + (j+1) + ": ");

System.out.println("Enter the marks of the first semster: ");
marks [0][j]= scannner.nextInt();

System.out.println("Enter the marks of the second semseter: ");
marks [1][j]= scanner.nextInt();
}

You do not need for loop to input the mark for any student i for two semester.


I have not provided any code. I have given many hints which should help you solve the problem on your own. However, if you still face any problem. Do comment.

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;

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;

Program to enter 5 student mark across 4 subjects and output highest average mark of student and subject

First, create a dictionary to take the inputs of marks for each student. Lets call it d_marks. Create a dictionary to get the average of all students. Let's call it avg_marks. Create a dictionary to get the total marks of all courses. Let's call it avg_course. After that get the max of them. Here's how you can do it:

d_marks = {}
avg_marks = {}
avg_course = {}
for i in range(1,6): # We loop for 5 students
d_marks[f'Student {i} (courses 1-4)'] = list(map(int, input(f"Student {i} (courses 1-4): ").split())) # Split the string and creates an integer list
avg_marks[f'Average of Student {i}'] = sum(d_marks[f'Student {i} (courses 1-4)']) / len(d_marks[f'Student {i} (courses 1-4)']) #Create average dictionary for Students
for j in range(1, len(d_marks[f'Student {i} (courses 1-4)'])+1):
if f'Course {j} sum' in avg_course: # if course sum already in dictionary then add it to previous.
avg_course[f'Course {j} sum'] += d_marks[f'Student {i} (courses 1-4)'][j-1]
else:
avg_course[f'Course {j} sum'] = d_marks[f'Student {i} (courses 1-4)'][j-1] # if course sum not in dictionary then create one.

print("The Highest average mark of students =", max(avg_marks.values()))
print("The Highest average mark of courses =", max(avg_course.values())/ len(d_marks))

Output:

Student 1 (courses 1-4): 50 60 70 60
Student 2 (courses 1-4): 100 90 87 90
Student 3 (courses 1-4): 70 100 90 90
Student 4 (courses 1-4): 30 65 50 50
Student 5 (courses 1-4): 58 50 74 43
The Highest average mark of students = 91.75
The Highest average mark of courses = 74.2


Related Topics



Leave a reply



Submit