SQL Selects

Select Statements

So now that we have a place for our data, we need a way of looking at it. The easiest way to do this is through the use of the SELECT statement. This statements allows you to look at all the data in your tables, or only select (thus the name) properties and/or conditions related to your data that you wish to view. For the sake of argument, we have the following data in our table.


		tblChair

	nID


	nLegs


	strColor


	strSize


	strType


	nWeight

1	0	Black	Big	Lounger	250
2	4	Brown	Little	Kitchen	50
3	3	Red	Medium	Kitchen	100
4	4	Blue	Huge	Stool	75

To have a look at the data in our table, we will write a simple SELECT statement.

SELECT * FROM tblChair;

This statement will bring back all the data in our table and display it for us. Note the use of the * (asterix), this is not recommended practice for any programming selects. It is a risky approach as often you do not know how much data is in the table you are accessing, and limits what can be managed programatically as you will have to use the value property instead of proper column names.

This can be rather difficult to maintain if you have a table that contains 250 columns, as you must know the exact order in which they are being returned by the SELECT * statement in order to utilize the data. In addition to a maintenance nightmare, you are also risking significant performance issues, as the database must now do a table scan, regardless of your indexes (think of a phone book, and imagine finding your desired information by going through it one page at a time until you find what you want.) It is highly recommended (by me anyway) that you take the time to create your SELECT statement with proper column names.

The syntax for a SELECT statement is below:

SELECT [Alias].<Column Name>,[Alias].<Column Name>,[Alias].<Column Name>,etc...

FROM <Table Name> [Alias]

WHERE <Column Name> [= < > != BETWEEN NOT] <Column Name or Value> [AND/OR] [<Column Name> [= < > != BETWEEN NOT] etc.]

GROUP BY <Column Name>

ORDER BY <Column Name> [Sort Order] <ASC or DESC>;

I know the syntax looks rather intimidating if you are just starting out, but it is rather simple once it has been broken down for you. There are 2 Mandatory parts to a SELECT statement. The SELECT and the FROM. The other three peices (WHERE, GROUP BY, ORDER BY) are optional, I highly recommend the use of WHERE in all your SELECT statements, as there are very few occations where you will require all of the data in your table.

So back to our example, we will get all of the data in our table for display. We will need the following statement to do this. (Watch how the alias is used and don’t forget to terminate your statement with a semi-colon)

SELECT TC.nID, TC.nLegs, TC.strColor, TC.strSize, TC.strType, TC.nWeight

FROM tblChair TC;

All of our table data is returned to us using this statement.


	nID


	nLegs


	strColor


	strSize


	strType


	nWeight

1	0	Black	Big	Lounger	250
2	4	Brown	Little	Kitchen	50
3	3	Red	Medium	Kitchen	100
4	4	Blue	Huge	Stool	75


	Now we will only return the data in our table where the chairs have 4 legs. This will demonstrate the use of the WHERE clause

SELECT TC.nID, TC.nLegs, TC.strColor, TC.strSize, TC.strType, TC.nWeight

FROM tblChair TC

WHERE TC.nLegs = 4;

Only the data where the chairs have 4 legs is returned to us.


	nID


	nLegs


	strColor


	strSize


	strType


	nWeight


2	4	Brown	Little	Kitchen	50

4	4	Blue	Huge	Stool	75


	Well, now we only want to see if we have a chair in our data that has 4 legs, and we don’t really want to return more that one row. This will demonstrate the use of the GROUP BY clause, which will take all common elements in a record set and display them only once.

SELECT TC.nLegs

FROM tblChair TC

WHERE TC.nLegs = 4

GROUP BY TC.nLegs;

Only the data where the chairs have 4 legs is returned to us, and even though we have more than one row with this criteraa, our SELECT/GROUP BY will only return the following.


	nLegs

4

The last thing we need to know about SELECT Statements is the ORDER BY clause. This allows you to sort your data in ASC (Ascending) or DESC (Descending) order by the column(s)/field(s) you choose. If you leave the sort order blank, it will default to Ascending order.

I will remove the GROUP BY clause for demonstration purposes.

SELECT TC.nID, TC.nLegs, TC.strColor, TC.strSize, TC.strType, TC.nWeight

FROM tblChair TC

WHERE nID IS NOT NULL

ORDER BY TC.nWeight DESC;


	nID


	nLegs


	strColor


	strSize


	strType


	nWeight

1	0	Black	Big	Lounger	250
3	3	Red	Medium	Kitchen	100
4	4	Blue	Huge	Stool	75
2	4	Brown	Little	Kitchen	50


	At this point, I have given you the basics, now the best thing you can do is practice. Visit the SQL Links page for some great Tutorial sites with online practice zones or continue on to the next lesson.