_Oracle books

Oracle books - 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:

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:

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:

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.

Powered by Create your own unique website with customizable templates.