SQL Primer

SQL is a nonprocedual programming language designed to work with data sets in relational database management systems. SQL lets you define, modify and remove database objects, transact against data, control the flow of transactions and query data. The SQL language commands are often grouped by functions into four groups

While DDL and DDL have been around for a while DCL and DQL are new references that Oracle is accepting.

Data Types

The SQL language has many data types which can be viewed here.

Data Definition Language (DDL)

The DDL commands let you create, replace, alter, drop, rename and truncate the database objects. The DDL commands can be used against the following

Follow the links on how you mange these

One point to remember is that all DCL statements execute a commit before and after the DCL statement, so be careful if you wish to rollback any DML statements because you will not be able too if you have run a DCL statement.

Data Query Language (DQL)

DQL commands are select commands, usinf select statements to can query the database to find information in one or more tables, and return the query as a result set. The result is an array structure a two-dimensional array. DQL statements can be standalone queries, in-line views, subqueries or correlated subqueries, they can return scalar or compound values.

Databases do not like zero based numbering schemas and queries return rows by row numbers starting with 1.

The select statement can have a number of clauses

FROM contains a list of tables and joing conditions between the listed tables. Tables can be tables, views and in-line views (subqueries). Tables can be aliased which givens the table a shorter name. As mentioned you can join tables together and you can find more details in join methods.
WHERE contains a list of column names compared against column names or string literals. It acts like a filter to reduce the number of rows that you want returned.
HAVING eliminates groups basic on an expression
ORDER BY This sorts the order on which the result set is returned.
GROUP BY You can group rows together on a specific criteria
FOR UPDATE You can lock a set of rows, thus not allowing anyone to change or delete the rows
RETURNING INTO allows to to return variables and assign them to bind variables.

Data Manipulation Language (DML)

There are 3 commands that makeup the DML command set

INSERT allows to to insert row or groups of rows into a table
UPDATE allows you to update (change) a single row or many rows. It is important to use the where clause otherwise all rows in the table will be affected.
DELETE allows you to delete a single row or many rows. It is important to use the where clause otherwise all rows in the table will be affected.

The 3 commands only affect your current session until you use the DCL command commit, which then commits the transaction to the database allowing others to see the changes. To get more information on how transactions work in Oracle see Transactions.

Data Control Language (DCL)

The data control language is the ability to quarantee an all-or-nothing approach when changing data in more than one table. There are 3 commands that makeup the DCL set

COMMIT

makes permanent all DML changes to data up to the point in the user session. Once committed in theoy that data is recoverable no matter what happens to the system (disk failure, database shutdown)

Remember that all DCL statements also run a commit before and after they execute.

ROLLBACK This reverses changes to data that have not yet permanent through being committed during a users session.
SAVEPOINT sets a point-in-time marker in the current session, this marker can then be used to rollback transactions to a point-in-time.

To get more information on how transactions work in Oracle see Transactions.