Find top 3 students who have the highest total score across all subjects from the exam table...

INPUT : i have 3 tables students,Exams and Grades tables

**Students** table
+---------------+-------------------+
| Name | description |
+---------------+-----------+
| student_id | student Id number |
| First_name | student first name
| Last_name | student last name |
+---------------+-------------------+

**Exams** Table:
+---------------+------------------+
| Name | description |
+---------------+------------------+
| Name | exam name |
| Exam_ID | exam identification |
| Date | date of exam |
+---------------+------------------+

**Grades** Table:
+---------------+------------------+
| Name | description |
+---------------+------------------+
| Student_ID | student Id number|
| Exam_ID | exam identification|
| grades | score obtained |
+---------------+------------------+


CREATE TABLE Exams (
ID BIGINT (20) UNSIGNED NOT NULL AUTO_INCREMENT
,name VARCHAR(20) NOT NULL
,exam_id INT (10) NOT NULL
,DATE DATE NOT NULL
,PRIMARY KEY (ID)
);

INSERT INTO Exams(name,exam_id,date) values ('mathematics',1,'2018-06-01'),('linear algebra',2,'2018-06-06'),('chemistry',3,'2018-06-11'),('physics',4,'2018-06-15'),('english language',5,'2018-06-20');

CREATE TABLE Graded (
ID BIGINT (20) UNSIGNED NOT NULL AUTO_INCREMENT
,student_ID INT (10) NOT NULL
,exam_id INT (10) NOT NULL
,grades INT (10) NOT NULL
,PRIMARY KEY (ID)
);

INSERT INTO Graded(student_ID,exam_id,grades) values (1001,1,61), (1002,1,5), (1003,1,28), (1004,1,90), (1005,1,13), (1006,1,34), (1007,1,81), (1008,1,45), (1009,1,63), (1010,1,85), (1011,1,70), (1012,1,64), (1013,1,9), (1014,1,2), (1015,1,68), (1016,1,69), (1017,1,50), (1018,1,69), (1019,1,31), (1020,1,13), (1001,2,98), (1002,2,71), (1003,2,83), (1004,2,69), (1005,2,36), (1006,2,7), (1007,2,42), (1008,2,94), (1009,2,87), (1010,2,42), (1011,2,54), (1012,2,29), (1013,2,37), (1014,2,97), (1015,2,94), (1016,2,84), (1017,2,39), (1018,2,73), (1019,2,86), (1020,2,60), (1001,3,60), (1002,3,62), (1003,3,16), (1004,3,56), (1005,3,100), (1006,3,95), (1007,3,13), (1008,3,76), (1009,3,3), (1010,3,12), (1011,3,34), (1012,3,83), (1013,3,59), (1014,3,44), (1015,3,84), (1016,3,19), (1017,3,32), (1018,3,99), (1019,3,75), (1020,3,15), (1001,4,36), (1002,4,85), (1003,4,1), (1004,4,41), (1005,4,64), (1006,4,100), (1007,4,8), (1008,4,90), (1009,4,38), (1010,4,87), (1011,4,14), (1012,4,1), (1013,4,94), (1014,4,20), (1015,4,1), (1016,4,77), (1017,4,35), (1018,4,15), (1019,4,26), (1020,4,20), (1001,5,90), (1002,5,21), (1003,5,85), (1004,5,15), (1005,5,68), (1006,5,70), (1007,5,0), (1008,5,86), (1009,5,14), (1010,5,84), (1011,5,26), (1012,5,21), (1013,5,68), (1014,5,20), (1015,5,84), (1016,5,69), (1017,5,51), (1018,5,84), (1019,5,91), (1020,5,10);

CREATE TABLE Students (
ID BIGINT (20) UNSIGNED NOT NULL AUTO_INCREMENT
,student_ID INT (10) NOT NULL
,first_name VARCHAR(25) NOT NULL
,last_name VARCHAR(25) NOT NULL
,PRIMARY KEY (ID)
);

INSERT INTO Students(student_ID,first_name,last_name) values (1001,'Dillon','Neitzel'), (1002,'Bridgette','Viruet'), (1003,'Lean','Wessel'), (1004,'Corey','Mogan'), (1005,'Amberly','Schneiderman'), (1006,'Pei','Mulhall'), (1007,'Sebrina','Nowicki'), (1008,'Anya','Ausherman'), (1009,'Laurice','Brannum'), (1010,'Fae','Southern'), (1011,'Alberto','Gilcrease'), (1012,'Keila','Wildt'), (1013,'Velvet','Wisneski'), (1014,'Gerard','Reinke'), (1015,'Awilda','enley'), (1016,'Leena','Hose'), (1017,'Myron','Karney'), (1018,'Collin','Wiles'), (1019,'Kiera','Benford'), (1020,'Hillary','Knaack');


Output: The top three students (both first name and last name) who have the highest total score across all the subjects. The name should be in the format 'First name Last name' eg. Tim Smith The column name must be "top_3_leaders"

Here's how I tried:

SELECT
CONCAT(students.first_name , " " , students.last_name) as Top_3_leaders,
(
SELECT max(g.grades) FROM graded g
WHERE g.student_ID = Students.student_ID
AND g.exam_id = exams.exam_id
) AS total
FROM exams , students
ORDER BY total desc LIMIT 3;


This's result I got

top_3_leaders | total |
----------------------------------|
Amberly Schneiderman | 100 |
Pei Mulhall | 100 |
Collin Wiles | 99 |


Instead of getting a total score of all subjects ,it's returning only for a single highest subject. I was unable to return the right information. Please help.

Continue reading...