SQL Challenges-1: Highest Single marks

Last update on December 20 2024 11:39:23 (UTC/GMT +8 hours)

Table students contain marks of mathematics for several students in a class. It may same marks for more than one student.
From the following table write a SQL table to find the highest unique marks a student achieved. Return the marks.

Input:

Table: students

Structure:

Field	Type	Null
student_id	int(11)	YES
student_name	varchar(255)	YES
marks_achieved	int(11)	YES

Data:

student_id	student_name	marks_achieved
1	Alex	87
2	Jhon	92
3	Pain	83
4	Danny	87
5	Paul	92
6	Rex	89
7	Philip	87
8	Josh	83
9	Evan	92
10	Larry	87

Sample Solution:

SQL Code(MySQL):

CREATE TABLE students(student_id int, student_name varchar(255), marks_achieved int);

INSERT INTO students VALUES(1, 'Alex',87);
INSERT INTO students VALUES(2, 'Jhon',92);
INSERT INTO students VALUES(3, 'Pain',83);
INSERT INTO students VALUES(4, 'Danny',87);
INSERT INTO students VALUES(5, 'Paul',92);
INSERT INTO students VALUES(6, 'Rex',89);
INSERT INTO students VALUES(7, 'Philip',87);
INSERT INTO students VALUES(8, 'Josh',83);
INSERT INTO students VALUES(9, 'Evan',92);
INSERT INTO students VALUES(10, 'Larry',87);

SELECT * FROM students;
 
SELECT MAX(marks_achieved) as marks
FROM (
	SELECT marks_achieved
	FROM students
	GROUP BY marks_achieved
	HAVING COUNT(*) = 1
) z;

Sample Output:

marks|
-----|
   89|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Sales Person.
Next: Internal changes of beds.