Read about Select Statement article from www.devguru.com

 -Read this! It will help you get a basic idea of what a Select Statement is in T-SQL. If you have SQL Server Management Studio then you can try along.-

SELECT

Syntax:

SELECT [ predicate ]
{ * | table.* | [ table.]field1 [ AS alias1 ] [ , table.]field2 [ AS alias2 ] [ , ... ] }
FROM table [ , ... ]
[ WHERE criteria ] [ NOT ] [ IN ] [ ( value1, [ value2, [ ... ] ] ) ]
[ GROUP BY grouping_expression ]
[ HAVING criteria ]
[ ORDER BY order_expression [ ASC | DESC ] ]
[ subqueryclause [ subqueryclause [ ... ] ] ]

predicate

Keywords (ALL, DISTINCT, TOP) that can follow the SELECT statement used to limit the number of records returned.

table

Is the name of the table(s) that the information is being extracted from. It is used to indicate which field comes from which table during JOIN operations.

field1

Specifies the name of the fields to be displayed.

AS

The keyword that is used to signify that an alias is to be used in the place of the field name.

alias

Is an alternative name used to replace the field name in the query result set.

criteria

The condition(s) that determines which of the rows are to be retrieved.

NOT

Keyword used, as part of the criteria or along with the IN keyword, to select values other than the ones listed in the value list.

IN

Keyword used to determine if the values of a specified expression match any from a specified value list.

value1

Is a parameter used with the IN clause to designate a list of values to select from.

grouping_expression

The parameter that specifies a set of columns on which to group matching data.

order_expression

The parameter that indicates which field(s) and in which order the result set is to be sorted by.

ASC | DESC

Specifies whether the results of the query should be sorted in ascending or descending order.

subqueryclause

A SELECT statement nested within another SELECT statement.

The SELECT statement returns information from a database as a set of records without altering the database. It searches the database, extracts the chosen columns and selects those records that meet the criteria, sorting and grouping the results into a specified order.

A SELECT statement can be nested inside of another SELECT statement which is nested inside of another SELECT and so on.

Examples

Code:

SELECT * FROM MusicArtists;

Output:

MusicianID	FirstName	LastName	Instrument	Style
1	Elvis	Presley	Guitar	Rock and Roll
2	B. B.	King	Guitar	Blues
3	Charlie	Daniels	Fiddle	Country
4	Johnny	Cash	Guitar	Country
5	Henry	Erickson	Violin	Classical
6	Jonny	Lang	Guitar	Blues
7	Jerry	Jamison	Flute	Classical
8	Bobby	Lee	Fiddle	Classical

(8 row(s) affected)

Explanation:

This example demonstrates a very simple SELECT query, which uses an asterisk ( * ) to select all of the fields in a table (in this case the table 'MusicArtists').

Language(s): MS SQL Server

Code:

SELECT FirstName, LastName, Instrument FROM MusicArtists;

Output:

FirstName	LastName	Instrument
Elvis	Presley	Guitar
B. B.	King	Guitar
Charlie	Daniels	Fiddle
Johnny	Cash	Guitar
Henry	Erickson	Violin
Jonny	Lang	Guitar
Jerry	Jamison	Flute
Bobby	Lee	Fiddle

(8 row(s) affected)

Explanation:

This code shows how to be more selective and choose just one or a few of the fields in a table. They will be returned in the order listed.

Language(s): MS SQL Server

Code:

SELECT FirstName AS First, LastName AS Last, Instrument
FROM MusicArtists;

Output:

First	Last	Instrument
Elvis	Presley	Guitar
B. B.	King	Guitar
Charlie	Daniels	Fiddle
Johnny	Cash	Guitar
Henry	Erickson	Violin
Jonny	Lang	Guitar
Jerry	Jamison	Flute
Bobby	Lee	Fiddle

(8 row(s) affected)

Explanation:

This example shows how the reserved word AS is used to display an alias in the place of the field name used in the table.

Language(s): MS SQL Server

Code:

SELECT Name, City + ', ' + Country AS Location FROM Customers;

Output:

Name	Location
Margarett Anderson	Knoxville, USA
Ronnie Jones	Hong Kong, China
Mara Clark	Paris, France
William Locke	Copperhill, USA
Nicole O'Neal	Memphis, USA

(5 row(s) affected)

Explanation:

It is also possible to combine two or more fields from a table into a single field in the result set. This is accomplished with the use of the ampersand ( & ) character to concatenate a set of fields and/or strings together.

Language(s): MS SQL Server

Code:

SELECT FirstName + ' ' + LastName AS Musician FROM MusicArtists
WHERE Instrument = 'flute';

Output:

Musician

Jerry Jamison

(1 row(s) affected)

Explanation:

By using the WHERE clause, you can focus your selection by specifying certain criteria to be met by the values. The above example returns the names of all musicians who play the flute.

Language(s): MS SQL Server

Code:

SELECT CharacterName AS Name, CreatedBy AS Creator FROM Toons
WHERE ToonType = 'Duck';

Output:

Name	Creator
Donald Duck	Disney
Daisy Duck	Disney
Daffy Duck	Warner Bros.

(3 row(s) affected)

Explanation:

This code returns the names of all cartoon characters who are ducks and their creators from the 'Toons' table.

Language(s): MS SQL Server

Code:

SELECT Item, UnitPrice AS Price FROM Products
WHERE Color = 'blue' AND UnitPrice < 1800;

Output:

Item	Price
Dell Inspiron 300m	1600.00
Dell Axim X30	279.00
Intellisense Mouse	20.00

(3 row(s) affected)

Explanation:

Multiple criterion in a WHERE clause can be combined using any of the logical operators. Here the query returns a list of all items which are blue and cost less than $1800.

Language(s): MS SQL Server

Code:

SELECT * FROM PianoTuners
WHERE State='TN' AND City IN ( 'Knoxville', 'Nashville', 'Memphis' );

Output:

Name	Address	State	City	Phone
Harry Ford	22nd Avenue	TN	Memphis	(555) 555-2287
Henry Smith	2287 Walker Rd.	TN	Knoxville	(555) 555-5998
James Galloway	1818 Vista Dr.	TN	Nashville	(555) 555-1234

(3 row(s) affected)

Explanation:

The IN operator can determine if the values of a specified expression matches any values in a specified list. This example determines if any piano tuners live in 'Knoxville', 'Nashville', or 'Memphis'. A pair of single quotes must enclose each value and commas must separate the values.

Language(s): MS SQL Server

Code:

SELECT * FROM PianoTuners
WHERE State='TN' AND City NOT IN ( 'Knoxville', 'Nashville', 'Memphis' );

Output:

Name	Address	State	City	Phone
Kenny O'Neal	1st Street	TN	Copperhill	(555) 555-5155
Jerry Allison	Broad St.	TN	Cleveland	(555) 555-3528

(2 row(s) affected)

Explanation:

The addition of a NOT clause retrieves all records whose value is not in the list.

Language(s): MS SQL Server

Code:

SELECT *
FROM RockAndRoll
WHERE Artist = 'Elvis';

SELECT ALL *
FROM RockAndRoll
WHERE Artist = 'Elvis';

Output:

Artist	Song	Status	ReleaseDate
Elvis	Heartbreak Hotel	Double Platinum	1956
Elvis	Blue Suede Shoes	Gold	1956
Elvis	Jailhouse Rock	Double Platinum	1957
Elvis	Teddy Bear	Double Platinum	1957
Elvis	My Wish Came True	Platinum	1959

(5 row(s) affected)

Artist	Song	Status	ReleaseDate
Elvis	Heartbreak Hotel	Double Platinum	1956
Elvis	Blue Suede Shoes	Gold	1956
Elvis	Jailhouse Rock	Double Platinum	1957
Elvis	Teddy Bear	Double Platinum	1957
Elvis	My Wish Came True	Platinum	1959

(5 row(s) affected)

Explanation:

The SELECT statement can optionally be followed by one of these three predicates: ALL, DISTINCT and TOP. These limit the number of records returned. The ALL predicate is the default, but it is rarely used.

Note: These two code examples yield the exact same results.

Language(s): MS SQL Server

Code:

SELECT DISTINCT LastName
FROM SongWriters;

Output:

LastName

Osbourne

Presley

Jones

Smith

Jamison

King

Daniels

(7 row(s) affected)

Explanation:

The DISTINCT predicate is used to omit duplicate values in a field. Consider a table of names, where you have the last name, 'Jones', repeated numerous times. This code returns only one 'Jones'.

Language(s): MS SQL Server

Code:

SELECT TOP 3 FirstName + ' ' + LastName AS Musician, Instrument
FROM MusicArtists;

Output:

Musician	Instrument
Elvis Presley	Guitar
B. B. King	Guitar
Charlie Daniels	Fiddle

(3 row(s) affected)

Explanation:

The TOP predicate returns the specified number of records from the top of the specified table. This example returns the first 3 records from the 'MusicArtists' table.

Language(s): MS SQL Server

Code:

SELECT COUNT(SalePrice) AS ReducedCount
FROM Products;

Output:

ReducedCount

4

(1 row(s) affected)

Explanation:

This example uses the COUNT function to count the number of items that have an entry in the 'SalePrice' field (i.e. they are on sale) and returns that number under the heading 'ReducedCount'. You can also carry out calculations on fields containing numeric values using the aggregate functions:

Aggregate Function	Description
AVG	Average of values in a column.
COUNT	Counts how many rows.
MAX	Maximum value in a column.
MIN	Minimum value in a column.
STDEV	Sample standard deviation of the values in a column.
STDEVP	Standard deviation of the values in a column.
SUM	Adds the values in a column.
VAR	Sample variance of the values in a column.
VARP	Variance of the values in a column.

Language(s): MS SQL Server

Code:

SELECT Item, UnitPrice AS CurrentPrice, UnitPrice * 1.1 AS IncreasedPrice
FROM Products;

Output:

Item	CurrentPrice	IncreasedPrice
Dell Inspiron 2650	1500.00	1650.00
Dell Flat Panel LCD Monitor	700.00	770.00
Travelstar 30gb Hard Drive	100.00	110.00
Dell Inspiron 300m	1600.00	1760.00
Intellisense Mouse	20.00	22.00
Dell Axim X30	279.00	306.90

(6 row(s) affected)

Explanation:

This code returns current prices along with what the prices would be after a 10% increase.

Language(s): MS SQL Server

Code:

SELECT Item, SalePrice AS Price, UnitPrice - SalePrice AS Reduction
FROM Products WHERE SalePrice IS NOT Null;

Output:

Item	Price	Reduction
Dell Flat Panel LCD Monitor	600.00	100.00
Dell Inspiron 300m	1439.00	161.00
Intellisense Mouse	10.00	10.00
Dell Axim X30	251.00	28.00

(4 row(s) affected)

Explanation:

This example lists all items that are reduced along with the price and the amount of the reduction.

Language(s): MS SQL Server

Code:

SELECT Task.Name, Task.TaskID
FROM Task INNER JOIN Assignment
ON Task.TaskID = Assignment.TaskID
WHERE Assignment.CompletionDate Is Null;

Output:

Name	TaskID
Paint House	2312
Complete Kitchen	2348

(2 row(s) affected)

Explanation:

It is possible to select fields from more than one table. In this case it is best to precede a field name with the name of the table from which it comes, followed by the dot operator ( . ). This must be done for fields of the same name, but from different tables that are used in the SELECT statement. The preceding example uses two tables, 'Task' and 'Assignment', and returns the names of all tasks belonging to assignments that are incomplete.

Language(s): MS SQL Server

Code:

SELECT Task.Name, Task.TaskID
FROM Task, Assignment
WHERE Task.TaskID = Assignment.TaskID
AND Assignment.CompletionDate Is Null;

Output:

Name	TaskID
Paint House	2312
Complete Kitchen	2348

(2 row(s) affected)

Explanation:

As an alternative to using the explicit INNER JOIN syntax, columns from multiple tables can be combined in a single query by specifying the appropriate table list, and applying the filter condition in the WHERE clause. This is illustrated in this query, which returns the same recordset as the previous example.

Language(s): MS SQL Server

Code:

SELECT Product FROM Suppliers
GROUP BY Product;

Output:

Product

Desktop

Laptop

Mouse

Network Card

Hard Drive

Software

Book

Accessory

(8 row(s) affected)

Explanation:

The optional GROUP BY clause groups into a single record all records that have identical values in a particular field or combination of fields. This code returns a list of the different products in the 'Product' field of the 'Suppliers' table.

Language(s): MS SQL Server

Code:

SELECT Product, Count(Supplier) AS Tally FROM Suppliers
GROUP BY Product HAVING Count(Supplier) > 1;

Output:

Product	Tally
Desktop	25
Laptop	19
Mouse	33
Network Card	3
Software	4
Book	6
Accessory	7

(7 row(s) affected)

Explanation:

The HAVING clause is optional and qualifies a GROUP BY clause. It is similar to the WHERE clause but determines which records are displayed after they have been grouped. This example displays a list of different products, along with the number of suppliers available for each, but only where there are multiple suppliers available for the product.

Language(s): MS SQL Server

Code:

SELECT ToonType AS Type, CharacterName AS Name FROM Toons
WHERE ToonType = 'Duck' OR ToonType = 'Mouse'
ORDER BY ToonType, CharacterName;

Output:

Type	Name
Duck	Daffy Duck
Duck	Daisy Duck
Duck	Donald Duck
Mouse	Jerry
Mouse	Mickey Mouse
Mouse	Minnie Mouse

(6 row(s) affected)

Explanation:

The ORDER BY clause can be used to dictate the order of the records returned. The preceding example returns records listed primarily in order of toon type (duck then mouse), and then for each type the relevant names are also listed in alphabetical order.

Language(s): MS SQL Server