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.
Big Data and SQL 
One comment