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.
Lets create a sample table named Student_marks and insert some records into table.
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)
Now we have a table as shown below.
SELECT * FROM STUDENT_MARKS
Lets write the query to get the student who have secured more than 80 marks.
Following is the query, 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, that’s why Jaydeep comes before Mandeep in result set.
146 total views, 10 views today