hi

SELECT statement in sql server

SELECT is one of the basic topic for Sql Server, which basically used to retrieval of information from Tables. Using some conditions or without conditions.
General syntax for the retrieve data from database is
Select * from TableName
 For example I create a one Student table for understanding the concept of select statement .
 Retrieve All the Records from the Student Table
SELECT * FROM dbo.student


Output

 Retrieve only the Required Information
SELECT ID,Name,SchoolName FROM dbo.student


Output

Using WHERE Clause
Get all the Student whose SchoolName is ‘ABC’
SELECT * FROM student WHERE SchoolName = ‘ABC’



Get all the Student whose SchoolName is not ABC
SELECT * FROM student WHERE SchoolName <> ‘ABC’



Get all the Student whose Gender is not ‘1’
SELECT * FROM student WHERE gender <> 1



Note: This query ignored the Student whose Gender column value is NULL,
because NULL value can’t be compared with some value (i.e. Null means unknown value, so it can’t be used to compare with any know values).
The only operation we can do with NULL is we can check whether it is NULL or NOT NULL .
 Get all the Student whose Gender column has some value (i.e. Student  whose Gender column value is NOT NULL)
SELECT * FROM student WHERE gender IS NOT NULL



Get all the Student whose Gender column value is null
SELECT * FROM student WHERE gender IS  NULL


Using Boolean Operators AND, OR and NOT
Using Boolean Operator AND: Get all the Student  whose SchoolName is ‘ ABC ’ and Gender is 0
SELECT * FROM student WHERE SchoolName = ‘ABC’ AND Gender = 0


Using Boolean Operator ORGet all the Student  whose schoolname  is either ‘ABC’  or have gender 1
SELECT * FROM student WHERE SchoolName = ‘ABC’ OR Gender = 1


Using Boolean Operator NOT: Get all the Student  whose SchoolName is other than ‘ABC’ and ‘PQR’
SELECT * FROM student WHERE SchoolName NOT IN (‘ABC’,’PQR’)


Using LIKE Predicate
Get all the Student who have word ‘SH’ anywhere in their Name.
SELECT * FROM student WHERE Name LIKE ‘%sh%’


Get all the Student whose Name starts with the word ‘Mun’
SELECT * FROM student WHERE Name LIKE ‘Mun%’


Get all the Student whose Name starts with the character ‘L or M or N’
SELECT * FROM student WHERE Name LIKE ‘[L-N]%’
SELECT * FROM student WHERE Name LIKE ‘[L,M,N]%’
SELECT * FROM student WHERE Name LIKE ‘[LMN]%’


Get all the Student whose Name starts with the character L or M or N and second character must be a’
SELECT * FROM student WHERE Name LIKE ‘[L-N][a]%’


Get all the Student  whose Name is not starting with letter L or M or N 
SELECT * FROM student WHERE Name LIKE ‘[^L-N]%’


Using BETWEEN Clause
Get all the Student whose date is between ‘1991-01-01 00:00:00.000’ AND ‘1993-01-01 00:00:00.000’
SELECT * FROM student WHERE DateOfBirth BETWEEN ‘1991-01-01 00:00:00.000’ AND ‘1993-01-01 00:00:00.000’


Using ORDER BY Clause
ORDER BY Clause can be used to sort the result set based on the Column Value.
SELECT name,SchoolName FROM student ORDER BY Name


If you want to sort name according to descending the use following query
SELECT name,SchoolName FROM student ORDER BY Name desc
Concatenation
‘+’ symbol  is used for concatenating string values
SELECT name + SchoolName as [Name & SchoolName] FROM student


Note- AS keyword is used as alies name
Note:  If ‘+’ symbol is used to concatenate the values, then if one of the values is NULL then resultant concatenated value will also be NULL.

 One way of avoiding NULL as the RESULT of concatenation if one of the value of the to be concatenated is NULL is to use the ISNULL function like below. Here ISNULL function returns an empty string if the value is NULL otherwise the specified value.
SELECT ISNULL(name,’ ‘) + ISNULL(SchoolName,’ ‘) AS [Name & City]
FROM dbo.student


In  our case Table does not contain NULL in Name or schoolName column.
 Add an empty space between Name and SchoolName.
SELECT ISNULL(Name,”) + ‘ ‘ + ISNULL(SchoolName,”) AS [Name & SchoolName] FROM dbo.Student




Share this

Related Posts

Previous
Next Post »