SQL stands for Structured Query Language. It is the language used to talk to databases.
The two basic categories of SQL are:
The four basic operations on data in a database:
SQL is written using clauses. The clauses to match the operations above are:
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.
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
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
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
The three basic operations on an object in a database:
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).
This allows you to make changes to your object. For tables, this would mean adding or dropping columns or indexes.
This allows you to remove the named object from the database.
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)