4GuysFromRolla.com - SQL, the Language of Databases

Contract Web Developers Needed
Aquent
US-PA-Philadelphia

SQL, the Language of Databases

So, you've connected to your favorite database, now what? Well, you've got to be able to tell your database what you want to do, and to do that, you have to speak its language. All relational databases speak in a language known as SQL, or Structured Query Language. MS-SQL Server speaks in SQL; Access speaks in SQL; Oracle and Informix speak in SQL. It's good to know SQL!

SQL is basically good for two things: updating/deleting/inserting information, and retrieving information. Let's look at retrieving information first.

To tell a database that you want to retrieve some information, you've got to supply the following information: what columns to get from what table. That's all that is required, although you can be more specific. For example you can limit data on certain boolean conditions; we'll deal with this later.

So, the way you tell your database that you want to select a column from a database is by using a SELECT statement. A SELECT statement takes the following form:

SELECT ColumnList FROM TableName

ColumnList is a comma-delimited list of columns in the table specified by TableName. To select all of the columns, use an asterisk (*). Now, let's say we had a database which had a table named Products, and Products had three columns: ProductID, Name, and Price. If we wanted to get the ProductIDs of all products, we would simple issue this SQL query:

SELECT ProductID FROM Products

It's that simple. The thing to remember with relational databases is that sets of information are returned. So in the above example we are getting back all of the ProductIDs from the Products table. If we wanted to get all of columns from the Products table, we would need but issue the following command:

SELECT * FROM Products

We can limit the set of data being returned from a query by using the WHERE clause. The WHERE clause comes after the TableName in the FROM clause. WHERE statements must evaluate to boolean expressions (expressions that return either true or false). Here are some examples of WHERE statements on our Products table example:

Get all of the names of the products that are priced at 5 dollars:
SELECT Name FROM Products WHERE Price = 5

Get all of the columns from the products that do not equal 5 dollars:
SELECT * FROM Products WHERE Price <> 5

Get all of the columns from the products that are less than or equal to 10 dollars:
SELECT * FROM Products WHERE Price <= 10

Get the price for all of the products whose name is "Chair":
SELECT Price FROM Products WHERE Name = "Chair"

I hope that the above examples make sense. There are many other clauses in the SELECT statement which are optional (remember - the only two required ones are SELECT and FROM). I will not be discussing them in this FAQ, though.

Updating/Inserting/Deleting Data
Looking at data is nice enough, but we have to have a way to put data into the database, a way to take it out, and a way to modify it. To do this, we just use one of three SQL statements: UPDATE, INSERT, or DELETE. You must be very careful when using these, and keep in mind that relational databases deal with sets of data, not individual items of data.

UPDATE
The UPDATE statement is fairly simple:

UPDATE TableName SET ColumnName = Value [WHERE ...]

The WHERE part is very important. If you don't use it, you basically are saying to the database, UPDATE every row in the table TableName! So, back to our Products example, the following command:

UPDATE Products SET Price = 5

Would set EVERY product in the Product table at a price of 5 dollars. This is probably not what we want to do. Using the WHERE clause, though, will allow us to update a single row, or a smaller set of rows.

UPDATE Products SET Price = 5 WHERE Name = "Chair"

Would set all products whose name was Chair to a price of 5 dollars. You can set more than one column at a time, just delimit the list with commas:

UPDATE Products SET Price = 5, Name = "Chair" WHERE ProductID = 4

DELETE
The DELETE statement has a very similar syntax to the UPDATE statement:

DELETE FROM TableName [WHERE ...]

99.99% of the time you will definitely want to have a WHERE statement here. Again, it is optional, but if you were to issue:

DELETE FROM Products

all of the data in the table would vanish in a poof of smoke. Perhaps you might want to do something like:

DELETE FROM Products WHERE Price > 100

which would delete all products with a price tag greater than 100 dollars. Or maybe you want to just delete the product with ProductID 88:

DELETE FROM Products WHERE ProductID = 88

In any case, be sure to be careful when using DELETE. I've gotten myself into trouble more than once by forgetting to use a WHERE clause!

INSERT
The INSERT statement is a bit more tricky. There are two forms of the INSERT statement, but I am only going to discuss one of them here.

INSERT INTO TableName (ColumnList) VALUES(ValueList)

ColumnList is a comma-delimited list of columns in the table in any order. The ValueList is a comma-delimited list of values corresponding to the columns specified in the ColumnList. Let's look at a quick example:

INSERT INTO Products (ProductID, Name, Price) VALUES(10, "Sofa", 90)

This would add a row to the Products table with a ProductID of 10, a Name of "Sofa" and a Price of 90 dollars. Just be sure to match up your ColumnList values and ValueList values. For example, if your ColumnList is ProductID, Name, Price, make sure that your ValueList has the value that you want to insert for the ProductID first, then for the Name, then for the Price.

Well, you now have a better understanding of how to talk to your database! You're definately a bit wiser now, and a bit more lingual! :)

Happy Programming!

FAQ Table of Contents Database Connectivity Talking to your Database: The Recordset Object

Whitepapers and eBooks

Webcasts

Downloads and eKits

Tutorials and Demos