SQL's DML is used to:

• Add new rows (INSERT)
• Delete rows (DELETE)
• Edit existing rows ( UPDATE)
• Retrieve rows (SELECT)
  

SQL was originally to be called SEQUEL.

These four SQL commands are usually embedded into another language, such as Visual Basic, C++ and others. This is known as embedded SQL. We can also interact directly with the database (known as ISQL, or Interactive SQL).

(There are other SQL commands, for example the CREATE TABLE command which can be used to actually create the table. However, we usually do this visually with the tools of the DBMS, instead of with the SQL language.)

SELECT

The SELECT command is the one we use most often. It retrieves rows from one or more tables, based on conditions, called predicates. A predicate is any rule, whose validity can be verified from the rows in one or more tables. For example, we might choose to pull information from an Users table only when the User's last name is "DuPont".

Here are a few facts about the SELECT statement:

• It extracts a subset of rows
• It can extract a set of columns, fewer than all the columns.
• It is used to JOIN tables, that is combine data from several tables.
• It can perform calculations, such as Units_on_Hand * Average_Cost
• Finally, the SELECT command can use built-in column functions. For example, to display the maximum value in a particular column (MAX)

The simplest form of the select command is:

SELECT fields from table

In this form, fields is the list of all fields we want from the table. We can use the asterisk to indicate that we want all fields. But if we don't need all of them, it might be better to restrict the returned fields to only the ones we need - remember, this information will be shuttled between the database and the application, which takes time.

This simple form selects all rows of the specified table. More often, we want to restrict the result set to values that match certain criteria - we will do this by appending a where clause. We provide conditional expressions to filter out unneeded rows. (This form of the SQL Select statement is sometimes called SELECT FROM WHERE (SFW)).

Using the sample database, run the following query:

select * from users where Lastname="DuPont"

Simple Conditionals

To restrict the rows that are displayed, use the WHERE clause. The clause usually contains restrictions such as:

WHERE AMT < 400

or

WHERE Lastname="DuPont"

to provide a final form of:

select * from users where lastname="dupont"

You can also use compound contditionals - for example:

select * from users where lastname="dupont" or lastname="smith"

However, a common mistake I see is people trying to write like this:

select * from users where lastname="dupont" or "smith"

Wrong. If you have multiple expressions, you must list the column each time!

Select - Order by

If you want to change the order of records in your display, you use the order by clause. It comes at the end of the statement, like this:

Select * from users where lastname="smith" order by lastname

You specify a column and a sort order to arrange the records by. If you want to break ties, you can order by multiple columns:

select * from users where lastname="DuPont" order by lastname, firstname

This would break any "ties" between records - if 2 records had the same value for lastname, they would be further sorted by firstname.

Finally, we can change the ordering in one of two modes - ascending or descending (keywords asc and desc). We can sort either way. For example:

select * from users order by age Desc, lastname ASC

which would give us a list first listed by age starting with the oldest and then growing smaller, and then any people with the same age would be alphabetically sorted by their last name in alphabetical order.

INSERT

The INSERT command adds one row to an existing table. Notice the word "one" .If you need to add 1000 rows to a table, you'll need to create, then execute, 1000 INSERT commands. As you'll see, there are two syntaxes for INSERT, one can be used to extract rows from other tables and insert them into a second table. This optional syntax can load multiple rows via one command.

If the new row violates any referential integrity rules (parent must exist prior to you storing child row, plus insertion, update and deletion integrity) , the INSERT fails, and your program (ESQL) or you directly (ISQL) is notified.

Syntax: There are two ways to formulate the INSERT command:

Syntax one:
INSERT INTO TABLENAME (Column list) VALUES (Value list)

This is used to add a single row to an existing table. It starts by using INSERT INTO TABLENAME. You would substitute the actual table's name for the TABLENAME.

Optional Clause
The (Column list) is optional. It's only required if either of the following is true:

• You are not supplying values for every column in the new row, or
• The actual data values are not in the same order as that of the columns
  An example comes later.

Having said this, it's is a good idea to list the column values, even though they may not be required. Listing the columns helps accuracy.

Values
After the optional list of column names, you enter a value for each column, separated by commas. Enclose text data within quotation marks - essentially, the same way we treat text as a string in a programming language. Usually you can use either single or double quotation marks, but don't mix them. That is, use them in pairs.

The list of values might be something like: "r10", "Bob", "555-1212", "Don't".

Why do we have the phone number in quotes? Isn't it a numerical value?

So, here's an example:

insert into users (firstname, lastname) values ("Phil", "DuPont")

Note that you can't overwrite a primary key in the field if it is generated by an autonumber. If we are not using providing the values in the same order as they are structured in the database, we must remember to list the field names and the values in the proper order!

The order by clause or where clauses are not supported here with insert.

Update

The update statement looks like this:

Update table set column=value, column=value where column=value

So, for example:

Update Users set firstname="Phil", lastname="Smith" where UID=2

Note - you can't change an autonumber primary key. Also, can update one or all of the fields in the record.

Delete

The delete statement is probably the simplest. It reads:

Delete from table where column=value

as in:

delete from users where username="Phil"

The delete statement doesn't need column values, because it deletes a record in it's entirety. If you are trying to erase a specific column's value for a record, don't use delete - you should be using the update statement to change that value to null, like this:

Update users set age=null where UID=2

NOTE!

Note that the where clause doesn't apply in the insert statement (because you are inserting a record, and where only applies to existing records). However, I have a little rule. Technically, you don't need a where clause for the update or delete statements; however, if you don't use one, those statements would update or delete EVERY RECORD IN THAT TABLE. I have witnessed thousands of records be deleted because someone was debugging the statement and "temporarily" erased the where clause... all records gone. Which leads us to a little saying:

"I will never run an update or delete statement without a where clause!"

Say it with me, won't you?