Home » Get the Students who scored higher than 80 Marks

Get the Students who scored higher than 80 Marks

Suppose you are asked to write a query to get the student details such as StudID, StudName, and Marks  from table STUDENTS_MARKS who have scored higher than 80 marks, and sort the result by the last three characters of student names in ascending order.



If two or more student have same last three characters in name such as (i.e.: Mandeep, Jaydeep both name have same last three characters as ‘eep’ ), then sort the records by StudId in ascending order.

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 
(StudId, StudName, 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 T-SQL query, returns the student details who have got higher than 80 marks and sorts the result based on the last three character of names in ascending order, and if two or more student have same last three characters in name then sort the records by StudId in ascending order.

SELECT StudID, StudName ,Marks FROM STUDENT_MARKS

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

You can see, it returns the records of only those students who have scored more than 80 marks.

Also you can see, student Jaydeep and Mandeep both have got marks higher than 80 and the last three character of their name is ending with same characters that is ‘eep’ so the records are sort by StudId in ascending order, that’s why Jaydeep comes before Mandeep in the result set.

Also Read..

SQL Server Interview Q & A

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




 6,280 total views,  38 views today

Leave a Reply

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