SQL Server – Data Retrieval with Clauses and Operators

This post aims to give some examples to novices on how to obtain data from SQL Server using clauses and operators. The examples contain not only clauses and operators but also some arithmetic expressions.

To retrieve data from a table named “tbStudent”
SELECT * FROM tbStudent;

To retrieve information about a specific student (whose StudentID is 1001) from “tbStudent”
SELECT * FROM tbStudent WHERE iStudentID = 1001

To retrieve students’ information whose ID numbers are between in a range.
SELECT * FROM tbStudent WHERE iStudentID BETWEEN 1001 AND 1010

To retrieve students’ information whose ID numbers are NOT between in a range.
SELECT * FROM tbStudent WHERE iStudentID NOT BETWEEN 1001 AND 1010

To retrieve students’ information whose ID numbers are in any of the provided values.
SELECT * FROM tbStudent WHERE iStudentID IN(1001,1010)

To retrieve students’ information whose ID numbers are in any of the provided values.
SELECT * FROM tbStudent WHERE iStudentID NOT IN(1001 , 1010)

To retrieve students’ information whose name has “Abd” in it.
SELECT * FROM tbStudent WHERE vcStudentName LIKE ‘%Abd%’

To retrieve students’ information whose name doesn’t have “Abd” in it
SELECT * FROM tbStudent WHERE vcStudentName NOT LIKE ‘%Abd%’

To retrieve students’ information who applied in the year 2010.
SELECT * FROM tbStudent WHERE YEAR(dtApplicationDate) = 2010

To retrieve students’ information who didn’t apply in the year 2009.
SELECT * FROM tbStudent WHERE YEAR(dtApplicationDate) <> 2009

To retrieve students’ information who applied in the month of January. The month function returns an integer value. If the month of the given date is January then it returns 1, if February then 2, and so on.
SELECT * FROM tbStudent WHERE MONTH(dtApplicationDate) = 1  

To retrieve students’ information who applied on the first day of the month. The DAY function returns an integer value. 
SELECT * FROM tbStudent WHERE DAY(dtApplicationDate) = 1

To retrieve students’ information who applied in the month of January and the year 2010. 
SELECT * FROM tbStudent WHERE YEAR(dtApplicationDate) = 2010 AND MONTH(dtApplicationDate) = 1

Information should be retrieved for students whose ID numbers are between 1001 and 1010. The specified IDs will not be included because we are specifying greater-than and less-than operators.
SELECT * FROM tbStudent WHERE iStudentID > 1001  AND iStudentID < 1010

To retrieve students’ information whose payment is over 9000.
SELECT * FROM tbStudent WHERE dcPayment > 9000

To obtain the student’s name and ID if they have less than or equivalent to 1,000 as a discount. If the student’s name is not provided, return “NoName” as the value rather than leaving it empty.
SELECT iStudentID, ISNULL(vcStudentName, ‘NoName’) FROM tbStudent WHERE dcConcession <= 1000

To retrieve TEN students’ information from the Student table.
SELECT TOP 10 * FROM tbStudent

To retrieve unique students’ names from the Student table.
SELECT DISTINCT vcStudentName FROM tbStudent

To retrieve the number of students from each city.
SELECT vcCity, COUNT(*) FROM tbStudent GROUP BY vcCity

To retrieve the student enrollment figures for each city and make sure the results are sorted with the lowest enrollment figures at the top.
SELECT vcCity, COUNT(*) FROM tbStudent  GROUP BY vcCity  ORDER BY COUNT(*)

To retrieve the student enrollment figures for each city and make sure the results are sorted with the highest enrollment figures at the top.
SELECT vcCity, COUNT(*) FROM tbStudent  GROUP BY vcCity  ORDER BY COUNT(*) DESC

To retrieve the student enrollment figures for each city and make sure the results are sorted with the highest enrollment figures at the top and ignore the cities if there is only one student from it.
SELECT vcCity, COUNT(*) FROM tbStudent  GROUP BY vcCity   HAVING COUNT(*)>1  ORDER BY COUNT(*) DESC

To retrieve the student information whose city is not specified.
SELECT * FROM tbStudent  WHERE vcCity IS NULL

To retrieve the student ID and name sorted by the city in alphabetical order.
SELECT iStudentID, vcStudentName  FROM tbStudent ORDER BY vcCity

To obtain the student’s ID and the total amount they have paid thus far.
SELECT iStudentID, SUM(dcAmount) FROM tbStudent GROUP BY iStudentID

Retrieve the student’s ID, the dates of the first and most recent payments from the Student table.
SELECT
aaa iStudentID,
aaa MIN(dtPayment) As FirstPaymentDate,
aaa MAX(dtPayment) As RecentPaymentDate
FROM tbStudent
GROUP BY iStudentID

Retrieve the number of students, and their total and average payment from the Student table.
SELECT COUNT(iStudentID), SUM(dcPayment), AVG(dcPayment) FROM tbStudent

Please provide the student’s ID and the outstanding balance.
SELECT iStudentID, SUM(dcTotalAmount-dcPayment) As Balance FROM tbStudent GROUP BY iStudentID

There will be added statements. Please subscribe to receive updates on this article’s revisions or the newest blog postings.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s