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.

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.

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 146 total views,  10 views today

Leave a Reply

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