Accelerate Your Learning with this Oracle SQL Tutorial

This Oracle SQL tutorial will cover some of the theory of relational databases, introduce the syntax and then provide an introduction to the language.

History of SQL

SQL (officially pronounced "ess kew ell" and unofficially "seekwell") is the lingua franca of databases. It was developed at IBM in the early 1970s to enable the retrieval and modification of data held in IBM's relational database management system Sytem R (based on the relational data model concieved by E. F. Codd in 1970) and is now an international standard (maintained by the ISO).

Theory Of Relational Databases

Relational databases are based on set theory (also known as relational calculus) which enables sets (relations) to be combined in various ways

• they can be "JOIN"ed (also known as "intersect", or "and");
• "UNION"ed (also known as "or", "add","sum");
• "MINUS"ed (ie. one set is subtracted from the other)
• and outer-joined which is a combination of intersecting and "OR"ing

We will see the applications of this as we go through this Oracle SQL tutorial, so don't worry if this doesn't mean much yet.

Introduction To SQL

Now let's examine the language used to communicate with the database and to retrieve/update/delete data stored in the database.

The difference between SQL and a programming language like C is that SQL is a set-based language (i.e. operations are carried out on sets of data) that describes what is to be done (in terms of what sets are to be manipulated) rather than how it is to be done (pure SQL has no flow-control statements).

But SQL is not just about retrieving or modifying data - it is also used to define the meta data, that is the structure of the tables in the data and the other objects in the database such as views, indexes and tablespaces and to create the database itself. This is known as DDL - data definition language. The statements used for inserting, updating and retrieving data are known as DML - data manipulation language.

Unfortunately, there isn't the space in this Oracle SQL tutorial to go into much depth so we'll only be looking at queries.

Simple Queries To Retrieve Data From The Oracle Database

Select column1

Where a_table is any table in our database (not strictly true but for the sake of keeping this tutorial simple assume it is) and column1,column2 etc. represent the data items of the table that you are interested in. If all columns are required this can be abbreviated with "*". The square brackets "[" indicate that the item enclosed within is optional.

This query would return all the columns in all rows from whatever table we specify.

The rows selected from the table can be restricted with an optional "where" clause which has the following syntax:

where condition1[,condition2[,...conditionx]]

Condition1,condition2 etc can be defined as

"value or column" "operator" "value or column"

where "operator" is one of "=","<",">","#" or "<>"; "value or column" is either a value (eg. 1 or "BILL") or the name of a column in the table.

Therefore the simplest query would be of the form:

Select * from dept;

Where "dept" is a table.

This would select all the columns from every row in the table called "dept" (assuming that there is a table called "dept", if not an error will be generated, but we won't worry about that for now). If we only wanted one particular column (the department name in this case) the query would become:

Select name from dept;

If we only wanted some of the rows we could restrict the rows returned with a where clause, for example:

Select * from dept where name > 'ACCOUNTS';

would only retrieve the department names that would be after ACCOUNTS alphabetically (ie. sales, marketing, hr, etc.)

This Oracle SQL tutorial only scratches the surface. If you'd like to learn more sign up for our free 7 Day SQL tutorial and master the fundamentals of SQL quickly and easily.

Return from Oracle SQL tutorial to Oracle tutorial