Structured Query Language (SQL)

So you've decided that you need to use some data from a database (MS Access, Oracle, MS SQL Server, mySQL, DB2, XML, etc) for your application, or perhaps more accurately for an assignment. Visual Studio .NET provides many methods to access data easily, but we're going to start at the bottom with the more complex ways and work our way up. SQL is a powerful language that allows for its user to pull data from a given database. There are flavors of SQL that allow for even more power and control, but we will focus entirely on SQL queries, and specifically the basics of SQL queries.

There are four basic types of SQL queries: select (getting data), insert (adding new data), update (changing existing data), and delete (removing data). These four types allow for just about everything that you could want to do with your data (there are a few other's but we won't cover them here).

SELECT

A select statement follows the form:

SELECT [column names] FROM [table name] [optional conditions]

The [column names] are a comma delimited list of the columns of data that you want to retrieve. In addition to a list of column names you can supply a single asterisk character to indicate that you want all of the available columns. There are a number of functions that are also can be used here and we will talk about a couple of them later, first some examples. Let's say that you have a table named people with the columns user_id, first_name, last_name, city, state, zipcode, country, email, and webpage. The following SQL queries are valid and will work:

SELECT user_id, first_name, last_name, city, state, zipcode, country, email, webpage FROM people

SELECT * FROM people

At first these two statements might appear to do the exact same thing, but they do not. The first statement pulls the nine columns from the database but in the specific order listed, whereas the second pulls all columns (which in this case are the nine columns) in the order that they appear in the database, which in general will be the same every time, but you need to look at the database to know what that order is. If you know the order in the database (or it doesn't matter to you), then the second example is obviously the simpler choice.

WHERE

These two examples don't make use of the [optional conditions] and as such will pull all data from the database in the order that it is stored. We won't go into great detail about all the optional conditions that can be specified but we will cover two of them: WHERE and ORDER BY.

The WHERE clause specifies a single boolean statement that must be true in order for a row to return from the SQL statement, similar in many regards to the IF statement in most programming languages. This condition can be multiple boolean conditions combined by a boolean operator such as AND and OR. Lets show an example to show how the WHERE clause can be used:

SELECT * FROM people WHERE user_id = 5

This statement will return all columns from the people table where the user id is the number 5 (if it is a properly created table this will be only one record since user_id should be unique, but if not it will return all records that match the condition user_id=5).

SELECT * FROM people WHERE state='UT' AND city=' Provo'

This statement would return all records from the table that have UT as a state and Provo as the city. As you can see it is still a single boolean statement but we're using the AND keyword to combine two boolean conditions.

SELECT * FROM people WHERE (state='UT' AND city=' Provo') OR zipcode=84604

Here we use parenthesis to specifically define how the boolean conditions will be combined. This shouldn't be new to anyone who has done even a little bit of programming since this is the basic structure in almost all languages for the IF statement. I'll lead it up to the reader to determine what would be returned from this example SQL query.

ORDER BY

The ORDER BY clause of a SQL statement provides the user a manner of specifying the order in which the records are returned. When the ORDER BY clause is used in conjunction with a WHERE clause, it is generally used after the WHERE clause so that the sorting is done on a set or records that is smaller than the entire set. The ORDER BY clause has the following format:

 ORDER BY [[column name] (ASC | DESC)] +,

Where " +," denotes a comma delimited Kleene plus (i.e. one or more items separated by a comma). ASC and DESC specify the specific order to use (i.e. ascending or descending), if not specified ASC is assumed on most databases. For example:

SELECT * FROM people ORDER BY last_name, first_name

This example will return all records from the table in alphabetical order by ascending last name, then ascending first name.

COLUMN FUNCTIONS

There are many functions that can be applied to the columns and each database usually adds their own functions as well. For this document we will cover only three standard SQL column functions: COUNT, MAX, and MIN. As you might expect these functions are pretty self explanatory, but for the sake of the tutorial we will give some examples and explain them briefly. In general, however, these column functions will return only one record, the exception being when used in conjunction with the GROUP BY clause (which we will not go into in this tutorial).

 COUNT([column name])

Count takes either a column name or an asterisk as an argument and returns the number of records.

MAX([column name])

Max returns the max value of the specified column, so if you had a column named grade and it had values 80, 30, 90, 50, and 87, MAX(grade) would return a single record with a value of 90

MIN([column name])

Min works like Max but returns the lowest value, so in the above example MIN(grade) would return 30

PUTTING IT ALL TOGETHER

SELECT COUNT(*) FROM people WHERE zipcode = 84604 AND last_name = 'Smith'

 This returns a single column, single row that contains the number of records where the zipcode is 84604 and the last name is Smith. As can be seen these functions, used together with all of the other functions in SQL, provide a powerful framework for data access regardless of the platform of the server or the client application.

Last Updated: 15 April 2005 9:54 AM
Up | Tutorial Main | Home | email author