This SQL tutorial is designed just to serve as an
introduction to the things you can do with SQL (i.e. add data, remove
data, modify data and query your data). It is not designed to be a
complete guide to the language and its syntax as that would take a
whole book! 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 columns FROM my_table;
Where columns represents
the list of data items in the table that you are interested in with
each column separated from the next by a comma. If all columns are
required this can be abbreviated with "*". Therefore a very simple
example would be:- SELECT * FROM customers;
This query
would retrieve every attribute of every customer whose details are held
in the customers table in our mythical database. The rows selected from
the table can be restricted with an optional where clause which has the
syntax WHERE conditions.
The conditions are
a comma separated list of conditions that a record must meet in order
to be returned to the user as part of the results. Each condition can
be defined as value_or_column operator value_or_column where operator is one of =,<,>,or <> and value or column is either a value (e.g.. 1 or "1") or the name of a column in the table.
Therefore the simplest query would be of the form SELECT customer_name FROM customers;
Where customers is the name of the table and customer_name is a column in the table. This would select the customer_name column from every row in the table called customers.
If we only wanted some of the names we could restrict the names selected with a where clause, for example:
SELECT customer_name FROM customers WHERE customer_name > 'Jones';
would
only retrieve the department names that would be after Jones
alphabetically (e.g. Smith, Weston, Swarbrick, Zachary, King, Loundes,
Munden etc.).
Simple Queries To Retrieve Data From The Database
Having
looked at the basic syntax and a couple of simple examples so far in
this sql tutorial, let's move on to build up some more complicated and
more interesting queries.
We've already seen that the simplest query is of the form: SELECT * FROM the_table; where the_table is any table in our database (actually the_table
could be a view or even a sub query but to keep this tutorial simple
let's pretend it can only be a table). This query would return all the
columns in all rows from whatever table we specify.
For example, let us suppose we're a bookseller and in our database we have a table called technical_books which has the following columns: book_id, title, format, price, author
Let us further suppose that we have the details of over 10,000 technical books held in our table, so running the query SELECT * FROM technical_books; would return all the details for all 10000 books, but we're only interested in one book - "Learn SQL From Scratch In 40 Minutes Flat" !
So,
how do we restrict our query to just return the details of that one
book? Actually this is very straightforward. All we need to do is to
add some conditions to our query, like so:-
SELECT * FROM technical_books WHERE title = 'Learn SQL From Scratch In 40 Minutes Flat'
This query will now return the details for all courses entitled 'Learn SQL From Scratch In 40 Minutes Flat'
(there may be more than one edition or there may be more than one
format). To refine the query further we would add more conditions.
For example, let's assume that the format of the book 'Learn SQL From Scratch In 40 Minutes Flat' that we are interested in is "Ebook". In which case we can just add that condition to our query like so:
SELECT * FROM technical_books
WHERE title = 'Learn SQL From Scratch in 40 Minutes Flat'
AND format = 'Ebook';
Assuming
that there is only one ebook with this title in our database, this
query would return just one row. This shows how to restrict the number
of rows returned by our queries to exactly the ones we're interested in
by adding as many conditions to the query (the where clause to be
precise) as are needed. in this case there are two conditions combined
by "and". This means both conditions must be met by a particular record
(row) in our table, for that record to be in the results.
To make a condition optional we just need to replace the "and" with an "or". For example:
SELECT * FROM technical_books
WHERE title = 'Learn SQL From Scratch in 40 Minutes Flat'
OR format = 'Ebook';
would
retrieve details of all the ebooks we have in our table as well as the
details of all books entitled 'Learn SQL From Scratch in 40 Minutes
Flat'.
How To Modify Data In The Database
Up
'til now we've learnt how to construct queries to retrieve information
from our database and this is what 90% of our time is spent on with any
application and is fundamental to our understanding of SQL therefore.
Queries are also often used in update, insert and delete statements as
we'll see later, so they're doubly important.
Now let's learn how to update 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... colZ) VALUES (val1,val2... valz);
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 more complicated so it is best illustrated by an
example. We'll re-use our table called technical_books from the
previous examples. To insert data into the table we would write
something like this:
INSERT INTO technical_books(book_id, title, format, price, author)
VALUES (1,'Learn SQL From Scratch in 40 Minutes Flat', 'Ebook',free,'asktheoracle.net');
Furthermore we would need one statement like that for each book the details of which we wished to add to the database.
There are a couple of general points that need to be mentioned before we continue. First, in the insert
statement, if we don't specify the column names, 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 a
table course with the columns course_id, name, tutor, and price then the statement:
INSERT INTO course VALUES (1,'SQL Tutorial');
would
fail as we only supplied 2 values instead of 4. If we only want to
insert 2 columns then we have to specify which columns we are providing
values for.
By specifying the names of the columns we can also
list them in any order we choose, otherwise they have to be inserted in
the same order as they are in the table. For example, using the table
course as defined earlier, we can insert one rows with the following
statement:
INSERT INTO course VALUES (1,'SQL Tutorial','ebook',0,'asktheoracle.net');
This supplies a value for every column and in the order in which they exist in the table. This statement: however:
INSERT INTO course(title,format) VALUES ('ebook','sql tutorial');
supplies
values for just 2 of the columns and in a different order to the order
in the table. This statement also shows that as long as the data type
is correct, Oracle has no way of validating whether our command makes
sense (foreign-key and other types of constraints can be used for this
but discussion of of those is outside the scope of this introductory
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.
We also need to be aware that if the "where" clause is not added to the update and delete
statements then these statements will affect every row in the table.
For example if we run the following statement in our hr database
UPDATE employees SET salary = salary*1.1;
we'll
give every employee a 10% pay rise. That might be popular with the rest
of the organisation but the finance director is unlikely to be
impressed!
This statement:
DELETE FROM employees;
would
remove the details of every employee from the database which is
unlikely to go down well with the HR manager, 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
We
still have plenty of ground to cover so let's look at the alternative
syntax of the insert statement which uses what's known as a sub-query.
INSERT INTO my_table1 (col1,col2,...col) SELECT col1,col2,...colZ FROM my_table2 WHERE conditions;
In
the above example we could again leave out the names of the columns
that we are providing values for but they do need to be specified in
the sub-query or we need to use "*" to indicate all columns. This
alternative syntax has the advantage that it can be used to insert
multiple rows in one statement. The number of rows that would be
inserted is limited only by the number of rows that meet the conditions
specified in the where clause of the sub query. This format is often
used to create an empty table 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 look at sub-queries in
more detail before looking at multi-table queries. Sub-queries are
exactly what they say they are - queries embedded in another sql
statement. When embedded in another query they allow us to answer
multi-part questions. For example "give me the names of all the
employees in all departments which had a budget over $120,000 this
year". Assuming that budget is a column in the department table we can
answer the question by breaking it down into two parts: part 1 - "which
departments had a budget over $120,000 this year?" and part 2 - "which
employees are in those departments?" This is translated into sql as
follows:
SELECT employee_id, name FROM employees WHERE department_id IN
( SELECT department_id FROM department WHERE budget > 120000 );
Note
that the query is written top down, but executed form the bottom up ie.
the sub query is run first. The beauty of sub-queries is that we can
keep nesting them almost infinitely to answer more and more complex
questions. In practice, however, 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 tutorial) and because
the code becomes hard to understand and therefore hard to maintain.
We've
seen that sub-queries can be used to answer multi-part questions,
however there is another way of doing the same thing and that is to
convert the sub-query into a join. There may be performance
implications for doing this and these would have to be established by
trial and error. To 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 as a join. We started with
SELECT employee_id FROM employees WHERE department_id IN
( SELECT department_id FROM department WHERE budget > 120000 );
we can change this to:-
SELECT employee_id FROM employees, departments
WHERE budget > 120000
AND employees.department_id = departments.department_id;
Note
that in the join condition we had to specify the table names because
department_id is a column in both tables. If we didn't do this, Oracle
would raise an error because it wouldn't know to which department_id we
were referring. To answer a three part question we could add more
tables to the join or add a sub query as in the following example:-
SELECT employee_id FROM employees, departments
WHERE budget = (SELECT MAX(budget) FROM departments)
AND employees.department_id = departments.department_id;
This shows the flexibility of sql because the order of the conditions does not matter in terms of the final result.
I
have over 15 years experience with Oracle as dba, designer, developer
and trainer on UNIX (Solaris, Linux,HP-UX) and other platforms with a
long track record in delivering high quality results on time and on
budget for many blue-chip companies.
Currently working as a writer in a private company and doing freelance writing of
and web designing.