SQL Tutorial For Beginners

This SQL tutorial is designed just to serve as an introduction to the sort of things you can do with SQL. It is not designed to be a complete guide to the language and its syntax - that would take a whole book! At the end of this tutorial there are recommendations for further reading, including books on SQL and Oracle that would be worthwhile buying if you are serious about learning SQL.

The first thing we need to do in this sql tutorial is to define SQL: SQL (pronounced either seequel or esscuell) is the language used to communicate with the database and to retrieve/update/delete data stored in the database as well as maintaining the data structures (tables, indexes, constraints etc).

The name SQL is an acronym for Structured Query Language. It is a non-procedural language designed to allow end-users to be able to retrieve or modify their data (and data structures) in a database without having to worry about how this is achieved. In other words it concentrates on what is being done rather than how to it is done.

As already hinted at, there are two types of SQL statements

DML (data manipulation language) for querying and updating data
DDL (data definition language) which is used for the maintenance of the data structures.

This introductory SQL tutorial will just cover data manipulation, as this is what SQL is used for most of the time.

The most common SQL statements are database queries. The simplest form of the syntax for queries is

SELECT column1 [,column2 [,column3 [...,columnN]]]
FROM the_table;

Where column1,column2 etc. represent the data items in the table that you are interested in. The square brackets "[" "]" indicate that the item enclosed within is optional.

If all columns are required this can be abbreviated with "*". Therefore a very simple example would be :-

SELECT * FROM emp;

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

column operator value_or_column

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

Therefore the simplest query would be of the form

SELECT name FROM dept;

"dept" is the name of the table and it has a column called "name".

This would select the "name" column from every row in the table called "dept".

If we only wanted some of the names we could restrict the names selected with a where clause, for example

SELECT name FROM dept WHERE name > 'ACCOUNTS';

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

Simple Queries To Retrieve Data From The Database

Let's just recap a little. Earlier in this sql tutorial we said that the simplest query is of the form :

SELECT * FROM my_table;

where my_table is any table in our database (not strictly true but for the sake of simplicity assume it is).

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

For example, let us suppose we have a table called course which has the following columns:

id, title, format, price, author

Let us further suppose that we have 1,000 courses and that obviously we have one row for each course.

In this case running the query:-

SELECT * FROM course;

would return all the details for all the courses - all 1,000 of them. But we're only interested in one course - the SQL tutorial - so, obviously, we would like to restrict our query to just select those details.

How we do this ? Simple, we just need to add some conditions to our query as follows:

SELECT * FROM course WHERE title = 'SQL TUTORIAL';

This query will now return the details for all courses entitled "SQL TUTORIAL" (there may be more than one).

If we wanted to refine the query further we would add more conditions, if we knew any more facts about the "SQL TUTORIAL" that we are interested in.

For example, let's assume that the format of the "SQL TUTORIAL" that we are interested in is "Ebook".

In which case we can just add that condition to our query like so:

SELECT * FROM course
WHERE title = 'SQL TUTORIAL'
AND format = 'Ebook' ;

Assuming that there is only one SQL tutorial ebook in our database, this query would then return just one row.

This demonstrates that we can restrict the number of rows returned by our queries by adding as many conditions to it as we like. Each condition, in this case, is combined with the previous condition by an "and". Obviously in this case, all the conditions must be met by a particular record (row) in our table, for that record to be retrieved.

If we wanted to make a condition optional we would replace the "and" with an "or".

For example the following query:

SELECT * FROM course
WHERE title = 'SQL TUTORIAL'
OR format = 'Ebook' ;

would retrieve details of all courses called "SQL TUTORIAL" as well as the details of all the ebooks.

Updating Existing Data In The Database

Another good reason is that queries are often used in update, insert and delete statements as we'll see later.

Now it's time to move on and to devote some time in this sql tutorial to looking at how to update our data in the database.

There are three commands to do this: update, delete and insert. The basic syntax of each of these is as follows:

UPDATE my_table
SET col1 = val1, col2 = val2, ... colz = valz
WHERE conditions;

DELETE FROM my_table WHERE conditions;

INSERT INTO my_table (col1, col2 ... coln) VALUES (val1,val2 ... valn);

where my_table is the table name, col1, col2, colz are the column names and conditions determine which rows are deleted or updated in the same way as they determine rows that are retrieved in a select statement.

The insert statement is different and is best illustrated by an example. Let's assume we have a table of courses course with the same columns as before.

To insert data into the table we would write something like this:

INSERT INTO course(id, title, format, price, author) VALUES (1,'SQL Tutorial','online',free,'asktheoracle.net');

Furthermore we would need one statement like that for each course the details of which we wished to add to the database.

Before we look in more detail at these statements, there are a couple of general points that need to be mentioned in this sql tutorial.

First, in the insert statement, specifying the column names is optional. If they are not supplied, Oracle assumes that all columns are being inserted and will generate an error if a value is not supplied for every column in the table.

For example if we defined course as having the 4 columns id, title, price, format then the following statement:

INSERT INTO course VALUES (1,'SQL Tutorial');

would generate an error as just 2 values were supplied for 4 columns.

Naming the columns in the insert statement gives an advantage as they can then be inserted in any order, otherwise they have to be inserted in the order in which they exist in the table and a value must be supplied for every column.

For example, using the table course as defined earlier, we can insert rows with this statement:

INSERT INTO emp VALUES (1,'SQL Tutorial','ebook',0,'asktheoracle.net');

which supplies a value for every column in the order in which they exist in the table.

Whereas the following statement:

INSERT INTO course(title,format) VALUES ('ebook','sql tutorial');

only supplies a value for 2 of the columns and they are not in the order in which they are defined in the table.

The 2nd insert statement above demonstrates that Oracle doesn't care about the data we put in as long as the data type is correct (foreign-key constraints can be used to protect the database, but discussion of of their use is outside the scope of this introductory sql tutorial).

In this case we have specified the title as ebook and the format as sql tutorial which is unlikely to be what we want.

The second thing we need to aware of is that if the "where" clause is not added to the update and delete statements then they will affect every row.

eg. UPDATE emp SET salary = salary*1.1;

would give every employee a 10% pay rise and

DELETE FROM emp ;

would remove every employee from the database so we would nearly always specify the conditions for the update or delete by using the "where" clause.

If no rows meet the conditions in the "where" clause, then obviously no changes are made by the update or delete statements.

Multi-Table Queries

INSERT INTO my_table1 (col1,col2,...colz)
SELECT col1,col2,...colz
FROM my_table2
WHERE conditions;

Again the names of the columns in the insert statement are optional but obviously they need to be specified in the sub-query or we need to use "*" to indicate all columns.

This form of the statement has the advantage that it can be used to insert more than one row at a time. In fact the number of rows that can be inserted in one go, is limited only by the number of rows that meet the conditions specified in the where clause.

In fact this is often used to create an empty table but one that has the columns we want by specifying a condition that is never true such as "1=2" . That way the table is created but not populated.

Let's continue this sql tutorial by looking at sub-queries in more detail before we move on to look at multi-table queries.

First a quick definition: sub-queries are exactly what they say they are - queries embedded in another sql statement, often a query.

What are they for? Well they allow us to answer multi-part questions. For example "give me the names of all the employees in the departments that had a budget of more than £20,000 this year".

Assuming that budget is an attribute of dept (department) we can answer the question by breaking it down into two parts:

part 1 - "which departments had a budget over £20,000 this year ?"

part 2 - "who works for those departments ?"

This can be translated into sql as follows:

SELECT name FROM emp WHERE dept_id IN
(SELECT dept_id FROM dept WHERE budget > 20,000);

Note that the query is written top down, but executed bottom up ie. the sub query is run first.

That was a 2 part question, but the beauty of sub-queries is that we can keep nesting them almost infinitely to answer more and more complex questions.

However, in practice it is unlikely that you would ever nest a query more than 3 levels deep because of performance issues (discussion of which is outside the scope of this sql tutorial).

Multi-table joins

How do we convert a sub query into a join ? We just promote the table to the "from" clause in the first part of the query and add the conditions in the inner "where" clause to the outer "where" clause and specify the join condition between the tables.

Let's re-work the first example used in this sql tutorial as a join. We started with

SELECT name FROM emp WHERE dept_id IN
(SELECT dept_id FROM dept WHERE budget > 20,000);

This is transformed to :-

SELECT name
FROM emp , dept
WHERE budget > 20,000
AND dept.dept_id = emp.dept_id;

Note that in the join condition we had to specify the table names because dept_id is a column in both tables. If we didn't do this, the database would raise an error because it wouldn't know to which dept_id we were referring.

This is just a simple join involving two tables and answering a two part question.

To answer a three part question we would need to add more tables to the join or perhaps add a sub query as in the following example:-

SELECT name
FROM emp , dept
WHERE dept.dept_id = emp.dept_id
AND budget = (SELECT MAX(budget) FROM dept).

This is a good example of the flexibility of sql, showing that the order of the conditions does not matter in terms of the final result, although it may have an impact on performance.

Recommened Books and Other Resources

Smartsoft Computing provide an excellent list of hints and tips for SQL which is particularly useful if you want to start off on the right track.

Internet.com also provide a SQL tutorial