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 OR: Get 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
|
EmoticonEmoticon