Home » Get the Students who scored higher than 80 Marks

Get the Students who scored higher than 80 Marks

Query to Get the Students who scored higher than 80 Marks ?

Query to get the StudID, StudName ,Marks of student in STUDENTS_MARKS table who scored higher than Marks. Sort your result by the last three characters of each student name. If two or more students both have names ending in the same last three characters (i.e.: Mandeep, Jaydeep so on..), secondary sort them by ascending ID.

Student_marks table

Following is the table scripts for your reference.

CREATE TABLE STUDENT_MARKS (StudId INT NOT NULL, StudName VARCHAR(30), Marks INT)

INSERT INTO STUDENT_MARKS VALUES
(1, 'Jaydeep',85),
(2, 'Mandeep',81),
(3, 'Aakash',89),
(4, 'Suraj',75),
(5, 'Mahesh',80),
(6, 'Rajan',90),
(7, 'Viaksh',88),
(8, 'Sumit',60),
(9, 'Vishwajeet',50)

Following is the query , which returns student details who have got higher than 80 marks and sorts the result based on last three character of names , and studID in ascending order.

SELECT StudID, StudName ,Marks FROM STUDENT_MARKS

WHERE MARKS >80 ORDER BY RIGHT(StudName,3), StudID ASC ;

As you can see, student Jaydeep and Mandeep both have got marks higher than 80 but the last three character of their name is ending with same characters ‘eep’ so they are sort by studId in ascending order, thats why jaydeep comes before mandeep in result set.

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.