Tuesday, March 31, 2009

My talk this morning at ArcReady is Windows Live Services and on Live Mesh.

Click the icons below to download the slides and demos.

Here is a preview of the slides:
Tuesday, March 31, 2009 8:45:24 AM (Eastern Standard Time, UTC-05:00)
 Monday, March 30, 2009

I have secheduled a lot of public speaking for the next 30 days.  The sessions are listed below.  All these events are either free or charge a nominal fee.  Most will even provide some food. I believe that none of them are sold out yet, so I provided a link to each so you can register.

Architecting for the cloud: Mesh and Live Services

March 31
Microsoft ArcReady
Southfield, MI

Live Mesh is a data synchronization client that has a rich API to build applications on. Live services are a collection of APIs that can be used to create rich applications for your customers. Live Services are based on Internet standard protocols and data formats.


An Introduction to SQL Server

(with Shane Jordan)
April 4
West Michigan .Net University
Grand Rapids, MI

Want to learn about the SQL Server 2008? Want to build normalized data models and learn Transact SQL? Join us in this Introduction to SQL Server session to get a good introduction to building your own data models and queries.


Using Microsoft Distributed Cache to speed your application

April 18
Central Ohio Day of .Net
Wilmington, OH

Retrieving data from a disc or a database can be a time-consuming operation.  Data that is accessed frequently can be stored in an in-memory cache, which can speed up its retrieval considerably.  Microsoft Distributed Cache (aka “Velocity”) provides a framework for storing and managing cached data. 

In this session, we will discuss how to use this framework in your application and demonstrate some code that implements this framework.


Proactive communication with your manager or customer

April 25
Kalamazoo X Conference
Kalamazoo, MI

After 15 years in the IT industry, I've discovered that the single biggest contributor to success is effective communication. In this situation, I'll describe lessons I've learned about how to improve communication with a customer or manager and how to use effective communication to increase the probability of high customer satisfaction.


This is the most ambitious month of speaking I've ever scheduled.  Microsoft Distributed Cache is the only topic above on which I have previously presented and this presentation will be longer and feature a newer API than the one I gave at three user groups last September.

If you attend any of these events, please feel free to find me and say 'Hello'.

Monday, March 30, 2009 12:00:40 PM (Eastern Standard Time, UTC-05:00)
 Saturday, March 28, 2009

Last year, I noticed there were more local community-driven events than I had time to attend.  I love seeing that kind of enthusiasm for technology among the community. 

These events are great for me because - although I've been in software development for many years - I love to learn and frequently jump at the chance to learn from and share ideas with other experienced developers and architects.

I have noticed that most of these events targeted experienced developers.  Topics tended to be moderate to advanced and speakers assumed a certain level of expertise from their audience in order to grasp their talk. 

This can be frustrating for less-experienced developers.  It's difficult to understand the details of NHibernate when you are still trying to figure out how to write simple queries against SQL Server.

Chris Woodruff first described to me the idea of a .Net University - a community event targeted at those new to .Net programming.  All the content would be introductory, allowing people to learn fundamentals.

I liked the idea immediately.  When I was a trainer, I always spent time on the first day reviewing the basic prerequisites of the class material.  I knew that no one would understand anything about web development if they didn't know how to construct an HTML document. 

So Chris is now organizing the West Michigan .Net University that will be held April 4 in Grand Rapids. 

The morning sessions will all be introductory and the afternoon will feature more intermediate topics.  Each session will be about 3 hours - longer than at a typical code camp.

I'll be there delivering a morning session titled SQL 101, in which I'll explain the basics of working with a relational database - SQL Server in particular.  I"ll describe how to work with data and how to use code and tools to interact with SQL Server.  I've been preparing for this talk by writing a 'Back To Basics' series on this blog.

Other sessions include .Net Bootcamp; Intro To ASP.Net and AJAX; and Real World Architecture.  You can view the complete session list at http://dodn.org/WestMichiganDotNetU/Sessions.aspx

A lot of great speakers have signed up for this event so I'm counting on some great sessions. 

WM .Net University April 4, 2009 - I'll be there!

Saturday, March 28, 2009 5:35:47 PM (Eastern Standard Time, UTC-05:00)
 Thursday, March 26, 2009
Back To Basics

NOTE:

For demos in this article, we will use a table named Customer that contains the following columns:

Name Data Type
FirstName nvarchar(50)
LastName nvarchar(50)
StreetAddress nvarchar(255)
City nvarchar(255)
State char(2)
ZipCode nvarchar(10)
TotalSales decimal(18,2)

In addition, I created a primary key on the CustID column and set it to autoincrement by setting the following properties:

Is Identity Yes
Identity Seed 1
Identity Seed 1

Afer adding a couple rows to the table, the data looks like this.

CustID FirstName LastName StreetAddress City State ZipCode TotalSales
1 Steve Smith 900 Belle St Detroit MI 48888 5000.00
2 Ryan Miller 1 Shutout Ct Buffalo NY 32323 250.00

We can use the INSERT command to add a new row to this table. The syntax of the INSERT command is

INSERT INTO [TableName]
    (
    [Column List]
    )
VALUES
    (
    [Values List]
    )

We can insert a row for a new Customer - Brad Van Pelt - with the following code.

INSERT INTO Customer
    (
    FirstName, 
    LastName, 
    StreetAddress, 
    City, 
    State, 
    ZipCode, 
    TotalSales
    )
VALUES
    (
    'Brad',
    'Van Pelt', 
    '99 Linebaker Ln', 
    'Owosso', 
    'MI', 
    '47777', 
    4000
    )

Notice that each column name in the first set of parentheses matches a value in the second set of parentheses: 'Brad' with FirstName, 'Van Pelt' with LastName and so on. Notice also that we did not provide a value for the CustID column. This is because CustID is an identity column and, therefore, gets populated with an incremented number when a new row is added.

After executing the above INSERT statement, our data should look like this:

CustID FirstName LastName StreetAddress City State ZipCode TotalSales
1 Steve Smith 900 Belle St Detroit MI 48888 5000.00
2 Ryan Miller 1 Shutout Ct Buffalo NY 32323 250.00
3 Brad Van Pelt 99 Linebaker Ln Owosso MI 47777 4000.00

In this article, we showed how to use T-SQL's INSERT, UPDATE, and DELETE commands to modify the data in a table.

The new customer was automatically assigned a CustID value of 3. Because this value uniquely identifies the newly-added row, we can use it to find and update that row. The syntax to update a row in SQL Server is

UPDATE [Table]
    SET [Column1] = [New Value 1],
        [Column2] = [New Value 2],
        [Column3] = [New Value 3],
        etc...
    WHERE [Filter Condition]

Only rows that match the filter condition will be updated and only those columns specified in the SET clause will be updated.  We will use the following command to update the StreetAddress, City, State and ZipCdoe columns of Customer 3:

UPDATE Customer
    SET StreetAddress = '100 Safety St',
        City='New York',
        State='NY'
        ZipCode='01111'
    WHERE CustID = 3

After executing the above UPDATE command, our data should like this:

CustID FirstName LastName StreetAddress City State ZipCode TotalSales
1 Steve Smith 900 Belle St Detroit MI 48888 5000.00
2 Ryan Miller 1 Shutout Ct Buffalo NY 32323 250.00
3 Brad Van Pelt 100 Safety St New York NY 01111 4000.00

We use the DELETE command to delete rows in a SQL Server table. The DELETE syntax is

DELETE Customer
    WHERE CustID = 3

The following code will delete Customer 3

DELETE Customer
    WHERE CustID = 3

After executing the above DELETE command, our data will look like this:

CustID FirstName LastName StreetAddress City State ZipCode TotalSales
1 Steve Smith 900 Belle St Detroit MI 48888 5000.00
2 Ryan Miller 1 Shutout Ct Buffalo NY 32323 250.00

In this article, we showed how to use the INSERT, UPDATE and DELETE commands to modify data in a SQL Server table.

Thursday, March 26, 2009 11:18:46 PM (Eastern Standard Time, UTC-05:00)

Episode 16

Microsoft Technology Specialist Randy Pagels describes the benefits of Microsoft Visual Studio Team System.  You can learn more about VSTS from Randy at http://www.teamsystemcafe.net/

4 mins, 18 secs

Thursday, March 26, 2009 5:32:10 AM (Eastern Standard Time, UTC-05:00)
 Monday, March 23, 2009

This screencast describes the basic concepts of caching and the upcoming Microsoft Distributed Cache, which is code named "Velocity"

Monday, March 23, 2009 6:13:55 AM (Eastern Standard Time, UTC-05:00)
 Saturday, March 21, 2009
Back To Basics

NOTE:

For demos in this article, we will use three tables: Customer, SalesOrder and OrderLine. 

The structure of the Customer table is:

Name Data Type
CustID int
FirstName nvarchar(50)
LastName nvarchar(50)
StreetAddress nvarchar(255)
City nvarchar(255)
State char(2)
ZipCode nvarchar(10)

The Customer table contains the following data.

CustID FirstName LastName StreetAddress City State ZipCode
1 David Giard 123 Oxford Ct Erlanger KY 40111
2 Magic Johnson 456 Hollywood Blvd Lansing MI 45222
3 Bubba Smith 789 Killbubbakill St Baltimore MD 10111

The structure of the SalesOrder table is
Name Data Type
OrderID int
CustID int
OrderDate datetime

The SalesOrder table contains the following data.

OrderID CustID OrderDate
1 1 2009-03-01
2 1 2009-03-02
3 2 2009-03-07
4 2 2009-03-14
5 3 2009-03-21

The structure of the OrderLine table is
Name Data Type
OrderID int
LineNumber int
ProductName nvarchar(255)
Quantity int

The OrderLine table contains the following data  
OrderID LineNumber ProductName Quantity
1 1 Widget 7
1 2 Super Widget 4
2 1 Widget 5
2 2 Super Widget 3
3 1 Widget 2
4 1 Super Widget 3
5 1 Widget 6
5 2 Super Widget 1

In a previous article, I explained how we can (and often should) split a table into multiple tables in order to eliminate data redundancy - a process known as "normalization".

In this article, I'll explain how to retrieve related data from multiple tables and return them in a single result set.

Recall from the Normalization article that - in order to relate to tables - we add a key to each table.  The Primary key in the parent table is a column that is unique for each row and, therefore, servers to uniquely identify a row.  The child table contains a foreign key which is the same value as a Primary key in the parent table, so it points to a given row in the parent.

To retrieve data from multiple tables into a single result set, we do something called a "JOIN".  In SQL, there are two ways to JOIN tables:

  • Using the JOIN keyword
  • Adding the join condition on a WHERE clause

JOIN keyword

The syntax for joining tables with the JOIN keyword is

The syntax for joining tables with the JOIN keyword is

SELECT [List of Columns]
    FROM [Table 1]
    JOIN [Table 2]
        ON [Join Condition]

 For example, to retrieve the Name, SalesOrder Date and SalesOrder Amount of each customer in our sample tables, use the following query:

SELECT 
        FirstName, 
        LastName, 
        OrderDate 
    FROM Customer 
    JOIN SalesOrder 
        ON Customer.CustID = SalesOrder.CustID

Notice that we need to prefix the CustID column name with the table name in our filter condition.  This is because the CustID column name is not unique.  We need to tell SQL to which column we are referring.

The results of this query are

FirstName LastName OrderDate
David Giard 2009-03-01
David Giard 2009-03-02
David Giard 2009-03-07
Magic Johnson 2009-03-14
Bubba Smith 2009-03-21

WHERE clause

The syntax for joining two tables with the WHERE clause is

SELECT [List of Columns]
    FROM [Table 1], [Table 2]
    WHERE [Join Condition]

The syntax to return the same result set as above is

SELECT
        FirstName,
        LastName,
        OrderDate
    FROM Customer, SalesOrder
    WHERE Customer.CustID = SalesOrder.CustID

Recall that the WHERE clause is also used to filter your result set.  In fact, you can use it for both filtering and joining.  The following two queries yield the same results (showing only those records that match customer 1.

SELECT
        FirstName,
        LastName,
        OrderDate
    FROM Customer
    JOIN SalesOrder
        ON Customer.CustID = SalesOrder.CustID
    WHERE Customer.CustID = 1

SELECT
        FirstName,
        LastName,
        OrderDate
    FROM Customer, SalesOrder
    WHERE Customer.CustID = SalesOrder.CustID
        AND Customer.CustID = 1

Here is the result set for either of the above two queries:

FirstName LastName OrderDate
David Giard 2009-03-01
David Giard 2009-03-02
David Giard 2009-03-07

You can use these same techniques to join more than two tables. Here is the syntax to add the OrderLine table to our queries

SELECT
        SalesOrder.OrderID,
        FirstName,
        LastName,
        OrderDate,
        ProductName,
        Quantity
    FROM Customer
    JOIN SalesOrder
        ON Customer.CustID = SalesOrder.CustID
    JOIN OrderLine
        ON SalesOrder.OrderID = OrderLine.OrderID

SELECT
        SalesOrder.OrderID,
        FirstName,
        LastName,
        OrderDate,
        ProductName,
        Quantity
    FROM Customer, SalesOrder, OrderLine
    WHERE Customer.CustID = SalesOrder.CustID
        AND SalesOrder.OrderID = OrderLine.OrderID

Here is the result set of either of these 3-table queries

OrderID FirstName LastName OrderDate ProductName Quantity
1 David Giard 2009-03-01 Widget 7
1 David Giard 2009-03-01 Super Widget 4
2 David Giard 2009-03-02 Widget 5
2 David Giard 2009-03-02 Super Widget 3
3 David Giard 2009-03-07 Widget 2
4 Magic Johnson 2009-03-14 Super Widget 3
5 Bubba Smith 2009-03-21 Widget 6
5 Bubba Smith 2009-03-21 Super Widget 1

I prefer to use the JOIN keyword syntax when joining tables together because it is more clear what part of the query is a filter and what part of a query is a join. 

In this article, we showed the ways to use SQL Server to join multiple tables into a single result set.

Saturday, March 21, 2009 5:31:36 PM (Eastern Standard Time, UTC-05:00)
 Friday, March 13, 2009

Episode 15

A Give Camp hooks up software developers with charities. This provides these charities with custom applications that they otherwise might not be able to afford.

This year, Michael Eaton is coordinating upcoming Give Camps in Michigan.

In this conversation, He describes how it worked last year and what he hopes to happen differently next year.

The Lansing Give Camp is scheduled for April 24-26 at the Impressoin 5 Science Center.  The ann arbor Give Camp is scheduled for July 17-19 at Washtenaw Community College.  You can get more information and register to help at these events by visiting http://michigangivecamp.org/

10 min, 31 sec

Note: This interview was recorded near the Open Spaces area at the Detroit Microsoft Developer Conference, so the sound quality suffers due to background noise.  However Michael has so much good information that I encourage you to ignore this and listen to the end.
Friday, March 13, 2009 9:25:31 AM (Eastern Standard Time, UTC-05:00)
 Thursday, March 12, 2009
Back To Basics

NOTE:

For demos in this article, we will use a table named Customer that contains 7 columns:

Name Data Type
FirstName nvarchar(50)
LastName nvarchar(50)
StreetAddress nvarchar(255)
City nvarchar(255)
State char(2)
ZipCode nvarchar(10)
TotalSales money

Afer adding a few rows to the table, the data looks like this.

CustID FirstName LastName StreetAddress City State ZipCode TotalSales
1 David Giard 123 Oxford Ct Erlanger KY 40111 500.00
2 Magic Johnson 456 Hollywood Blvd Lansing MI 45222 1500.00
3 Bubba Smith 789 Killbubbakill St Baltimore MD 10111 1000.00
4 Ron Mason 501 E Grand River Ave Lansing MI 45333 2000.00
5 Steve Smith 900 Belle St Detroit MI 48888 5000.00
6 Ryan Miller 1 Shutout Ct Buffalo NY 32323 250.00
7 Brad Van Pelt 99 Linebaker Ln Owosso MI 47777 4000.00

In the last article in this series, I expleined the basic functionality of the SELECT statement. I showed how to select columns from a table and sort or filter the results.

In this article, I'll show some more things you can do with the SELECT statemnts.

Sometimes we want our results to aggregate data. When aggregating data, we use functions that consolidate multiple rows and return the result of this aggregate function applied to many rows. The most common aggregate functions I use that SQL Server supports are:

Function Descripiton
MAX The maximum value of a column across all included rows
MIN The minimum value of a column across all included rows
SUM The sum of all values in a column across all included rows
AVG The arithmetic average of all values in a column across all included rows
COUNT The number of included rows

The MIN, MAX, SUM, and AVG functions accept a parameter - the name of the column on which to calculate these values. The column must hold a numeric data type, such as an INT, FLOAT or MONEY.

You may pass a column name as a parameter to the COUNT function but it doesn't matter which column because - in SQL Server - every column appears exactly once in each row and the COUNT function is used to count rows. By convention, we pass "*" as the parameter to the COUNT aggregate function to represent all rows.

A few examples will help clarify this. Since our table contains only one numeric column - TotalSales - we will use this in most of our aggregate functions.

The following query returns the maximum value of the TotalSales column.


SELECT MAX (TotalSales) AS MaxSales
    FROM Customer

Here are the results - one row with one column containing the highest numerical value in the TotalSales column.

MaxSales
5000.00

Similarly, the following query returns the sum of all values in the TotalSales column.

SELECT SUM (TotalSales) AS SumSales
    FROM Customer

This returns 14,750 which is the sum of 1000 + 1500 + 1000 + 2000 + 5000 + 250 + 4000

SumSales
14750.00

We can filter before applying an aggregate function. If we only want to sum of TotalSales for customers in michigan, we simply add a WHERE clause to our query.

SELECT SUM (TotalSales) AS SumSales
    FROM Customer
    WHERE State = 'MI'

This returns 12,500, which is 1500 + 2000 + 5000 + 4000, or the sum of the TotalSales column for only those customers in Michigan.

SumSales
12500.00

As mentioned before, we do not need to specify a particular column for the COUNT function.

SELECT COUNT (*) AS NumCusts
    FROM Customer

returns the a row and column containing number 7, which is how many rows are in our table.

So far, each query we have written has returned only one row. Often, however, we want to calculate an aggregation for each distinct value in a column or columns. We can do this by adding a GROUP BY clause to the query. For example, we may want to see the SUM of TotalSales for each state.

SELECT 
        State, 
        SUM (TotalSales) AS SumSales
    FROM Customer
    GROUP BY State

The above query returns a row for each distinct value in the State column and calculates the sum of TotalSales of all rows corresponding to that state.

State SumSales
 KY 500.00
MD 1000.00
MI 12500.00
NY 500.00

It's important to note that, when using the GROUP BY clause, you cannot return a column that is not part of the grouping. So

SELECT 
        State, 
        City, 
        SUM (TotalSales) AS SumSales
    FROM Customer
    GROUP BY State

results in an error because we are trying to return the city column, but we are not grouping on that column. Because a given state can have multiple cities, SQL does not know which one to display for the row returned.

You can group on multiple columns as in the following query

SELECT 
        State, 
        City, 
        SUM (TotalSales) AS SumSales
    FROM Customer
    GROUP BY State, City

In this case, we get a row with a sum for each combination of state and city.

State City SumSales
 KY Erlanger 500.00
MD Baltimore 1000.00
MI Lansing 1500.00
MI Okemos 2000.00
MI Owosso 4000.00
NY Buffalo 250.00

By using the GROUP BY clause on a large table, we may end up with so many rows that it becomes difficult to find relevant data. Sometimes, we are only interested in those times when the aggregate value exceeds some threshhold. In these cases, it would be nice to only show aggregate rows that exceed that threshhold. This sounds like a good place to use a filter. Unfortunately, we cannot use the WHERE clause to accomplish this task because the WHERE clause filters data before the aggregation. We have to wait until after calculating the aggregate values becuase it is the aggregate values on which we want to filter. The HAVING clause is used to filter on aggregate values.

We can run our query to get the TotalSales sum for each state, but show only those states that have total sales of more than 2000, using the following query

SELECT 
        State, 
        SUM (TotalSales) AS SumSales
    FROM Customer
    GROUP BY State
    HAVING SUM (TotalSales) >= 1000

In this case, we don't see the row for Kentucky and New York because they had total sales summing less than $1000

State SumSales
MD 1000.00
MI 112500.00

In this article, we showed how to use T-SQL's grouping and aggregate functions to return summary data from a database.

Thursday, March 12, 2009 9:17:07 AM (Eastern Standard Time, UTC-05:00)
 Wednesday, March 11, 2009

Saturday April 4 at the Robert C. Pew Grand Rapids Campus of Grand Valley State Universityin Grand Rapids, Shane Jordan and I will be delivering a session on the basics of SQL Server.  This will cover many of the same topics as the Back To Basics: SQL 101 series I have writton on this blog.

The session is part of the West Michigan .Net University event.  You can get more informaiton and register at http://www.dayofdotnet.org/WestMichiganDotNetU/

West Michigan .Net University

Wednesday, March 11, 2009 9:59:10 AM (Eastern Standard Time, UTC-05:00)
 Tuesday, March 10, 2009
Back To Basics

NOTE:

For demos in this article, we will use a table named Customer that contains the followong columns:

Name Data Type
FirstName nvarchar(50)
LastName nvarchar(50)
StreetAddress nvarchar(255)
 City nvarchar(255)
State char(2)
 ZipCode nvarchar(10)
TotalSales money

Afer adding a few rows to the table, the data looks like this.

CustID FirstName LastName StreetAddress City State ZipCode TotalSales
1 David Giard 123 Oxford Ct Erlanger KY 40111 1000.00
2 Magic Johnson 456 Hollywood Blvd Lansing MI 45222 1500.00
3 Bubba Smith 789 Killbubbakill St Baltimore MD 10111 1000.00
4 Ron Mason 501 E Grand River Ave Okemos MI 45333 2000.00
5 Steve Smith 900 Belle St Detroit MI 48888 5000.00

Retrieving data is one of the most common tasks performed on a relational database.

Fortunately, SQL Server includes a language that allows users (and programs) to retrieve the data they want. This language is called Structured Query Language. It is usually abbreviated "SQL" and often pronounced "SEE-kwuhl".

Getting data from a database is known as querying the database. The code to retrieve that data is a query. When this code is written in SQL (as most of my queries are), it is known as a SQL query.

The basic syntax for a SQL query that retrieves data from a single table is

SELECT [List of Columns]
    FROM [Table Name]
    WHERE [Filter Condition]
    ORDER BY [Sort column or columns]

In SQL, line breaks and extra spaces are not important. The language parser is smart enough to figure out when a statement or command ends, so I usually try to format my SQL statements to make them easy to read.

Only the SELECT and FROM parts of the query are required, so let's start with those.

EvenEven though the "SELECT" keyword typically comes first in this type of query, I'll start by explaining the "FROM" keyword. When getting data from only one table, follow the keyword FROM with the name of that table. For example, the clause

    FROM Customer 

indicates that we are getting data from a table named Customer. We'll talk later about how to get data from multiple tables in the same query.

It is possible to provide an alias for a table by following the table name with a space, and the alias. For example

    FROM Customer cust

The above SQL clause says that we will get data from the Customer table, but that we will use the string “cust” to refer to this table elsewhere in our query.

This is useful in the following situations

  • You want to provide a shorter name for the table in order to avoid retyping a long name elsewhere in the query
  • You want to avoid ambiguity when listing the same table name twice in the FROM clause.

SELECT is the first keyword of this type of query and tells SQL Server that we want to retrieve data from the database. The word "SELECT" is followed by a list of columns that the query will return. If the column list contains duplicate column names (as when you are getting data from two different tables and they each have a column with the same name), you should precede the column name with the table name or alias.

You can also use the special character "*" in place of (or in addition to) the list of column names in order to return all columns in the tables.

The following query returns all columns and rows in the Customer table.

SELECT * 
    FROM Customer 
CustID FirstName LastName StreetAddress City State ZipCode TotalSales
1 David Giard 123 Oxford Ct Erlanger KY 40111 1000.00
2 Magic Johnson 456 Hollywood Blvd Lansing MI 45222 1500.00
3 Bubba Smith 789 Killbubbakill St Baltimore MD 10111 1000.00
4 Ron Mason 501 E Grand River Ave Okemos MI 45333 2000.00
5 Steve Smith 900 Belle St Detroit MI 48888 5000.00

We can return onWe can return only the FirstName and LastName columns from the Customer table with the following table

SELECT 
        FirstName, 
        LastName 
    FROM Customer 
FirstName LastName
David Giard
Magic Johnson
Bubba Smith
Ron Mason
Steve Smith

In the queries above, it is possible to qualify the column names, explicitly indicating that they are from the Customer table. Of course, in this case it is unnecessary because the column names are unique within the Customer table. The following examples qualify the column names and returns the same data.

SELECT
        Customer.FirstName,
        Customer.LastName
    FROM Customer

SELECT
        cu.FirstName,
        cu.LastName
    FROM Customer cu

By default, each column returned by the query retains the name of the corresponding column in the source table. you want to change the name of a If you want to change the name of a column in the query, alias that column by appending the keyword " AS " followed by the alias you want. For example

SELECT
        FirstName AS FirstNm,
        LastName AS LastNm
    FROM Customer

This will return the following output
FirstNm LastNm
David Giard
Magic Johnson
Bubba Smith
Ron Mason
Steve Smith

If wIf we want to sort the output we can add the ORDER BY clause to our query. The syntax for this clause is

ORDER BY [List of Columns on which to sort]

For example, we can sort our output on Last Name by changing our query to

SELECT
        FirstName,
        LastName
    FROM Customer
    ORDER BY LastName

FirstName LastName
David Giard
Magic Johnson
Steve Smith
Bubba Smith
Ron Mason

We can add more columns to the list of sort columns if we separate each with a comma. The second column is only appropriate in our sort if two rows have identical values for the first column.

For example

SELECT FirstNameFor example

SELECT
        FirstName,
        LastName
    FROM Customer
    ORDER BY LastName, FirstName

FirstName LastName
David Giard
Magic Johnson
Bubba Smith
Steve Smith
Ron Mason

The above result set contains 2 rows with the last name "Smith".  These two rows were sorted in order of their FirstName column.

In many cases, we may not want to return eIn many cases, we may not want to return every row in a table. We can use the WHERE clause to filter data. The syntax of the WHERE clause is

   WHERE [Filter Condition]

The Filter condition is a Boolean expression, meaning it evaluates to either TRUE or FALSE for every row. The query will return only those rows for which this condition evaluates to TRUE.

For example, if we want to get only those customers in Michigan we can use the query

SELECT
   FROM Customer 
   WHERE State = 'MI'

CustID FirstName LastName StreetAddress City State ZipCode TotalSales
2 Magic Johnson 456 Hollywood Blvd Lansing MI 45222 1500.00
4 Ron Mason 501 E Grand River Ave Okemos MI 45333 2000.00
5 Steve Smith 900 Belle St Detroit MI 48888 5000.00

This query only returned those rows that match our filter condition.

Of course, we can combine several of these clauses as in the following

SELECT
        cu.FirstName,
        cu.LastName,
        cu.State AS ResidencyState
    FROM Customer cu
    WHERE cu.State = 'MI'
    ORDER BY cu.LastName

which returns the following results
FirstName LastName ResidencyState
Magic Johnson MI
Ron Mason MI
Steve Smith MI

As you can see, we can use the SELECT commmand to retrieve data from a table in a database and customize the way that data comes back.  We've just scratched the surface of this command.  In the next article, we'll look at more options of the SELECT command

Tuesday, March 10, 2009 11:36:54 AM (Eastern Standard Time, UTC-05:00)
 Sunday, March 08, 2009

Episode 14

Jim Holmes takes a break from organizing and coordinating CodeMash to talk about the conference.

2 minutes, 38 seconds

Sunday, March 08, 2009 11:11:04 PM (Eastern Standard Time, UTC-05:00)
 Saturday, March 07, 2009

Episode 13

Matt Pizzimenti is involved in a2geeks.org - a social network for techies and entrepeneurs.  He discusses it in this interview.

Saturday, March 07, 2009 7:45:47 AM (Eastern Standard Time, UTC-05:00)
 Thursday, March 05, 2009

Episode 12

Mark Hindsbo is the General Manager of the Microsoft Evangelism group in the United States. In this interview, Mark talks about what Microsoft can do to help the developer community succeed.

2 minutes, 24 seconds

Thursday, March 05, 2009 6:52:25 AM (Eastern Standard Time, UTC-05:00)
 Wednesday, March 04, 2009

I woke up this morning and was surprised and deleted to see the following e-mail in my inbox


Congratulations! We are pleased to present you with the 2009 NVP Award! The NVP Award is our way to say thank you for promoting bad behavior and general revelry in the office. We appreciate your extraordinary efforts at “Video interviewing everyone on the planet” during the past year..

To celebrate this tremendous achievement, please join your other NVPers at the NVP Summit at SRT Solutions Thursday afternoon where we will talk about the future of <__>, drink lots of <__>, play a little <__>, and not do any <__>. As we NVPers like to say – “Anything that happens at NVP Summit, stays at NVP Summit”. NDA restrictions apply. You will also receive a special gift for being a NVP and some useless swag.

--Mike (NVP Regional Director)

I cannot tell you how proud I am to receive this award. I certainly would have dreamed of it all my life had I heard of it before yesterday.

I would be thrilled to share all I learn with you - my readers - were it not for the attached 423-page non-disclosure agreement I was forced to sign. But rest assured that this knowledge and its accompanying free drinks will ultimately help the community and be good for all software developers.

Thank you to the committee, to those who nominated me and seconded my nomination and especially to <__>, without whom I would not have won this award.

Wednesday, March 04, 2009 7:58:00 AM (Eastern Standard Time, UTC-05:00)
 Tuesday, March 03, 2009

Episode 11

After writing a distributed application, software architect Phil Japikse needed a way to deploy updates to users across the state.  In this conversation, Phil describes the deployment strategy he implemented using tools provided by the .Net framework. 

Tuesday, March 03, 2009 6:55:01 AM (Eastern Standard Time, UTC-05:00)