Convert the Students Marks into Respective Grades

Convert the students marks into respective grades

You need to concatenate the id and the letter grade inside the phrase:

'Student ? has grade: ?' 

with this:

select 
 'Student ' || id || ' has grade: ' || CASE 
    WHEN max_grade < 20 then 'F' 
    WHEN max_grade < 40 then 'D'
    WHEN max_grade < 60 then 'C'
    WHEN max_grade < 80 then 'B'
    ELSE 'A'
  END AS Grade_details 
FROM students 
ORDER BY id;

SQL Query for Student mark functionality

You just have to partition the problem into some bite-sized steps and solve each one by one

First, get the maximum score in each subject:

select SubjectID, max(MarkRate)
from Mark
group by SubjectID;

Then query who are those that has SubjectID with max MarkRate:

select SubjectID, MarkRate, StudentID
from Mark
where (SubjectID,MarkRate)
in
  (
  select SubjectID, max(MarkRate)
  from Mark
  group by SubjectID
  )
order by SubjectID, StudentID;

Then obtain the Student's name, instead of displaying just the StudentID:

select SubjectName, MarkRate, StudentName
from Mark
join Student using(StudentID)
join Subject using(SubjectID)
where (SubjectID,MarkRate)
in
  (
  select SubjectID, max(MarkRate)
  from Mark
  group by SubjectID
  )
order by SubjectName, StudentName

Database vendors' artificial differences aside with regards to joining and correlating results, the basic step is the same; first, partition the problem in a bite-sized parts, and then integrate them as you solved each one of them, so it won't be as confusing.

---

Sample data:

CREATE TABLE Student
    (StudentID int, StudentName varchar(6), Details varchar(1));    
INSERT INTO Student
    (StudentID, StudentName, Details)
VALUES
    (1, 'John', 'X'),
    (2, 'Paul', 'X'),
    (3, 'George', 'X'),
    (4, 'Paul', 'X');

CREATE TABLE Subject
    (SubjectID varchar(1), SubjectName varchar(7));    
INSERT INTO Subject
    (SubjectID, SubjectName)
VALUES
    ('M', 'Math'),
    ('E', 'English'),
    ('H', 'History');

CREATE TABLE Mark
    (StudentID int, SubjectID varchar(1), MarkRate int);    
INSERT INTO Mark
    (StudentID, SubjectID, MarkRate)
VALUES
    (1, 'M', 90),
    (1, 'E', 100),
    (2, 'M', 95),
    (2, 'E', 70),
    (3, 'E', 95),
    (3, 'H', 98),
    (4, 'H', 90),
    (4, 'E', 100);

Live test here: http://www.sqlfiddle.com/#!1/08728/3

---

IN tuple test is still a join by any other name:

Convert this..

select SubjectName, MarkRate, StudentName
from Mark
join Student using(StudentID)
join Subject using(SubjectID)

where (SubjectID,MarkRate)
in
  (
  select SubjectID, max(MarkRate)
  from Mark
  group by SubjectID
  )

order by SubjectName, StudentName

..to JOIN:

select SubjectName, MarkRate, StudentName
from Mark
join Student using(StudentID)
join Subject using(SubjectID)

join
  (
  select SubjectID, max(MarkRate) as MarkRate
  from Mark
  group by SubjectID
  ) as x using(SubjectID,MarkRate)

order by SubjectName, StudentName

Contrast this code with the code immediate it. See how JOIN on independent query look like an IN construct? They almost look the same, and IN was just replaced with JOIN keyword; and the replaced IN keyword with JOIN is in fact longer, you need to alias the independent query's column result(max(MarkRate) AS MarkRate) and also the subquery itself (as x). Anyway, this are just matter of style, I prefer IN clause, as the intent is clearer. Using JOINs merely to reflect the data relationship.

Anyway, here's the query that works on all databases that doesn't support tuple test(IN):

select sb.SubjectName, m.MarkRate, st.StudentName
from Mark as m
join Student as st on st.StudentID = m.StudentID
join Subject as sb on sb.SubjectID = m.SubjectID

join
  (
  select SubjectID, max(MarkRate) as MaxMarkRate
  from Mark
  group by SubjectID
  ) as x on m.SubjectID = x.SubjectID AND m.MarkRate = x.MaxMarkRate

order by sb.SubjectName, st.StudentName

Live test: http://www.sqlfiddle.com/#!1/08728/4

How to calculate student grade with SQL

Try this -

 SELECT R.STUDENT, (Asst1+Asst2+Exam) AS TotalMark, 
    CASE WHEN (Asst1+Asst2+Exam) > 84.5
    THEN 'A'
    WHEN (Asst1+Asst2+Exam) < 84.5 AND (Asst1+Asst2+Exam) > 64.5
    THEN 'B'
    WHEN (Asst1+Asst2+Exam) < 64.5 AND (Asst1+Asst2+Exam) > 49.5
    THEN 'C'
    WHEN (Asst1+Asst2+Exam) < 49.5 AND (Asst1+Asst2+Exam) > 29.5
    THEN 'D'
    ELSE 'E'
    END AS GRADE
    FROM RESULTS R;

Find list of topper across each class when given individual scores for each subject

It would be clearer to use temporary tables to store results along the way and make the result traceable, but the solution can be achieved with a single query:

WITH student_marks AS (
    SELECT Class_num, Name, SUM(Marks) AS student_total_marks
    FROM School
    GROUP BY Class_num, Name
) 
SELECT Class_num, Name, student_total_marks 
FROM ( 
    SELECT Class_num, Name, student_total_marks, ROW_NUMBER() OVER(partition by Class_num order by student_total_marks desc, Class_num) AS beststudentfirst
    FROM student_marks
) A
WHERE A.beststudentfirst = 1

The query within WITH statement calculate a sum of marks for every student in a class. At this point, subject is not required anymore. The result is temporarily stored into student_marks.

Next, we need to create a counter (beststudentfirst) using ROW_NUMBER to number the total marks from the highest to the lowest in each class (order by student_total_marks desc, Class_num). The counter should be reinitiated each time the class changes (partition by Class_num order).

From this last result, we only need the counter (beststudentfirst) with the value of one. It is the top student in each class.

---

Related Topics