A common SQL convention is for keywords and instructions to be capitalized. There are four basic tasks that SQL can perform on a database table. It can INSERT new rows of data, SELECT data from the table, UPDATE data, or DELETE a row from the table. Look at each of these four tasks.

INSERT Statement

An INSERT statement is exactly how it sounds: You use it to insert a row of data into a table. To perform an INSERT operation, you need to specify what table you're adding data to and what data you're adding. The full statement looks similar to this:

INSERT (value1, value2) INTO table_name

If you're not filling every column in a specific order in your insert, you have to additionally specify which values are destined for which column.

SELECT Statement

Use a SELECT statement to retrieve values from a database. You can specify which values you want returned; you don't have to get back a whole row of data. You can also add other specifications to this statement, as discussed earlier in the flyer mailing example.

A typical SELECT statement looks similar to the following:

SELECT column_name FROM table_name WHERE column_name = someValue

The WHERE clause is how you specify what data you want returned. Note that the column_name doesn't have to be the same as the one you're returning. You can add multiple conditions to the WHERE clause by using AND or OR and then adding more conditions. There are many ways this statement can be extended to do different tasks and perform more complicated comparisons; for the purpose of this lesson, however, just know this is possible.

UPDATE Statement

As the name implies, an UPDATE statement is used to update data already in the database. To do this, specify which tables you're modifying, and which columns of data you want to update. You can also use a WHERE clause to specify which rows are updated. (If you say WHERE some_column = 2 , for example, only rows in which the column value is 2 will be updated. You can use this statement to modify the values of one or more columns.)

The full statement looks something similar to this:

UPDATE table_name
SET column_name = new_value
WHERE conditions . . ..

DELETE Statement

A DELETE statement is used to remove rows from a database. Because databases require information to be stored in rows, you have to remove an entire row at a time. Specify which table from which you want to remove rows and what conditions describe the rows you want to delete (using WHERE ).

A simple delete statement looks similar to this:

DELETE
FROM table_name
WHERE conditions . . ..

SQL also includes other ways to modify these queries. For example, you can have it sort the output of a person and address alphabetically by name. There's much more that can be done, but most SQL is built around those four commands, which manipulate database data. Manipulating database data is the point of a query language.

Moving On

This lesson covered how programs deal with the new programming environment in which everything is connected. It also gave you a short overview of how programs talk to databases using query languages. In the final lesson, you get some practical advice on programming from a seasoned programmer. Lesson 8 discusses what programmers are working on these days and the kinds of tools they use.

Before you move on, be sure to complete the assignment and quiz for this lesson. Don't forget to drop by the Message Board to see what your fellow students have to say.