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 Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 30,634 total views,  4 views today