Welcome to Sign in | Help
in Search

SQL Tutorial For Beginners

Last post 11-18-2010, 3:08 PM by chloegreen. 0 replies.
Sort Posts: Previous Next
  •  11-18-2010, 3:08 PM 8504

    SQL Tutorial For Beginners

    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.

    My technical experience includes:-
    ·Oracle database administration (v7- 11g)
    ·Oracle Application Server (9i,10g)
    ·Oracle Forms and Reports (v3 - 10g)
    .Oracle Application Express
    .Datawarehouse design and build

    Currently working as a writer in a private company and doing freelance writing of research papers and web designing.
View as RSS news feed in XML
Powered by Community Server (Commercial Edition), by Telligent Systems