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.
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.
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.