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