SQL

Date: 2/25/2002

SQL stands for Structured Query Language. It is the language used to talk to databases.

The two basic categories of SQL are:

  1. Data manipulation
  2. Object manipulation

Data Manipulation

The four basic operations on data in a database:
  1. Create
  2. Read
  3. Update
  4. Delete

SQL is written using clauses. The clauses to match the operations above are:

  1. INSERT
  2. SELECT
  3. UPDATE
  4. DELETE

INSERT

This is used to insert values into a new row in a table. The syntax is:

INSERT INTO tableName(field-list) VALUES (value-list);

"field-list" and "value-list" are comma separated lists.

SELECT

This is used to retrieve records from a table. It creates a subset of the table called a ResultSet. The syntax is:

SELECT field-list FROM tableName

UPDATE

This is used to change field values in existing records. The syntax is:

UPDATE tableName SET field=value list

field=value list is a comma separated list in the format of field name = field value

DELETE

This is used to delete records from the table. The syntax is:

DELETE FROM tableName

The Select, Update, and Delete, as written, affect every record in the table. This is not always very desirable. 

You can restrict the records affected by using a WHERE clause. The syntax is:

WHERE field=value list

Another use of the WHERE clause is for joining tables together in order to combine fields from several tables. The syntax is:

WHERE tableName1.field = tableName2.field

This is most commonly used in the SELECT and DELETE queries. For each the FROM clause now becomes a comma-separated list of the tables, and the WHERE clause specifies how the tables are joined.

This is a simple example of a join, using an older SQL language specification. The newer SQL specifications allow for joins known as INNER JOIN and OUTER JOIN, but these are beyond the scope of this course.

You may also want to specify a sort order for the ResultSet. This is done with the ORDER BY clause. The syntax is:

ORDER BY field-list

By default, ordering is ascending (ASC). To specify descending, place the keyword DESC immediately after each field name you want in descending order.

Two other clauses I will mention in passing: GROUP BY and HAVING. These are used for aggregating data (count, sum, first, last, etc.). A discussion of these are beyond the scope of this course.

CRUD the CLOB

Object Manipulation

The three basic operations on an object in a database:

  1. CREATE
  2. ALTER
  3. DROP

CREATE

Used to CREATE either a table or an index. We will focus only on creating tables. In the simplest form, the syntax is:

CREATE TABLE tableName(field-definition-list)

"field-definition-list" is a comma-separated list containing the name of the field, the data type and, optionally, the field size (for text fields).

ALTER

This allows you to make changes to your object. For tables, this would mean adding or dropping columns or indexes.

DROP

This allows you to remove the named object from the database.

DROP tableName
DROP indexName ON tableName

One additional thing you will want to do is create CONSTRAINTS. A CONSTRAINT limits the values permitted in a field. A field CONSTRAINT can be: PRIMARY KEY, UNIQUE, NOT NULL. A field can also be constrained in that it must exist as a record in another table (FOREIGN KEY).

For our purposes, we want to create a PRIMARY KEY CONSTRAINT when we create our table. The syntax would be:

CREATE TABLE tableName(field-def CONSTRAINT indexName PRIMARY KEY, field-def-list)