Retrieving Data Using T-SQL

Retrieving Data Using T-SQL
One of the primary operations you need to do using T-SQL on SQL SERVER is to retrieve data from the database. We will use AdventureWorks2008 database throughout our tutorials. To download go to http://msftdbprodsamples.codeplex.com/.

The most basic SQL statement will have a SELECT and a FROM clause as we have seen in our previous posts.

(1)USING SELECT STATEMENT
Below is the example using AdventureWorks database.

SELECT * FROM HumanResources.Employee;
To return relevant columns, replace the asterisk (*) with the
required column names, as code below.

SELECT LoginID, JobTitle, BirthDate, MaritalStatus, Gender FROM humanResources.Employee;

A where clause can also be added to select statement to limit the number of rows in the output based on a condition.

Try following code:

SELECT DISTINCT Color from Production.Product

The above select will select all the distinct colors from “Production.Product“ table.To limit the number of rows try the following code.

SELECT DISTINCT Color from Production.Product WHERE Color IS NOT NULL;

The above code will eliminate the null values from the output because we have specified a condition using WHERE clause for it.

Similarly, we try below sample code to eliminate silver color product and products ListPrice ranging below 200 and 0.

SELECT * FROM Production.Product
WHERE Color = 'Silver'AND (ListPrice > 200 OR ListPrice = 0)

We can specify various other operators in WHERE clause.
An Example of each is given below.

(i) BETWEEN OPERATOR

SELECT * FROM Production.Product WHERE ListPrice BETWEEN 0 AND 100

BETWEEN operator is used to specify a range of integer values in where condition. AND operator is compulsory while using this operator.

(ii)LIKE OPERATOR

SELECT * FROM Production.Product WHERE color LIKE ‘ %silv%’

When using LIKE operator we should use single quote to enclose a string. There are various ways you can use LIKE operator .For Example

Percent (%)-Replaces any number of characters (including 0 characters) in the
string. For Example.

‘%silv%’-will look at a color value(Data Type is String) which is having ‘silv’ in its value at any position.

Underscore(_)-Replaces only one character in the string. . For Example.

‘_silv%’ –Undescore will replace single character of a string and the word having ‘silv’ from second character and anything after that will be selected .

Square brackets ([ ])-Replaces any one character within a set or a range of characters. A set is frequently displayed as a straight list of characters, for example, [abcd]; but the characters can be separated by commas to add clarity, for example, [a,b,c,d]. A range is separated by a dash, for example,
[a–d]. Each of these three options includes all rows where the specified character is an a, b, c, or d. caret (^) Any character not within a set or range of characters.
For example, [^ad] would be equal to [e–z].

The following code sample returns all employees whose job title starts with the word
market. Because we are using a case-insensitive database, capitalization is not considered as part of the search criteria:

SELECT BusinessEntityID, JobTitle FROM HumanResources.Employee WHERE JobTitle LIKE 'Market%';

To search for all employees where the word market appears anywhere in the job title, you should change the LIKE expression to ‘%Market%’.