SQL Basics

Training manual 1999-03-23

Copying forbidden: The material in this manual is protected by copyright law, and may not be copied entirely or in part, without the prior permission of IFS Research & Development AB.

Copyright 1999 IFS Research & Development AB

Table of Contents
1. Database.................................................................................................1
1.1. What is a database......................................................................................................1 1.2. Relational database.....................................................................................................1 1.3. RDBMS........................................................................................................................2 1.4. The features of a relational database........................................................................2 1.5. SQL Language............................................................................................................3 1.5.1. DDL.............................................................................................................3 1.5.2. DML............................................................................................................3 1.5.3. DCL.............................................................................................................3 1.5.4. Transaction commands................................................................................3 1.6. Oracle Server..............................................................................................................4 1.7. SQL*PLUS..................................................................................................................4

2. Queries....................................................................................................5
2.1. Writing SQL (general)...............................................................................................5 2.2. Useful to know.............................................................................................................6 2.2.1. Description of a database............................................................................6 2.2.2. The DUAL table..........................................................................................6 2.2.3. Listing every row in a table.........................................................................7 2.3. Simple queries.............................................................................................................7 2.3.1. SELECT (clause).........................................................................................7 2.3.2. Select with columns specified.....................................................................8 2.4. Queries with where clause (condition)......................................................................8 2.4.1. Logical operators (testing against a single value).......................................9 2.4.2. Pattern matching........................................................................................10 2.4.3. Test for existing value...............................................................................10 2.4.4. Combination of logic.................................................................................11 2.4.5. Logical operators, testing against a list of values......................................12 2.4.6. Combination of rows from different tables...............................................13 2.5. Order by (clause)......................................................................................................15 2.6. Datatypes...................................................................................................................15 2.6.1. Strings........................................................................................................16 2.6.2. Concatenate...............................................................................................16 2.6.3. Alias..........................................................................................................17 2.7. Queries with functions..............................................................................................18 2.7.1. Character functions (let you cut and paste)...............................................19 2.7.2. Number functions......................................................................................23 2.7.3. Date functions...........................................................................................28 2.7.4. Conversion functions.................................................................................31 2.7.5. Transformation functions..........................................................................33 2.8. The decode function..................................................................................................33 2.8.1. Combining functions.................................................................................33 2.9. Join.............................................................................................................................34 2.9.1. Joining using alias.....................................................................................35 2.9.2. Joining a table with itself..........................................................................35 2.9.3. Outer join...................................................................................................35 2.10. Group by (clause)....................................................................................................35 2.10.1. Group by with criteria.............................................................................36

Table of Contents I

2.10.2. Having.....................................................................................................37 2.11. Subquery..................................................................................................................37 2.11.1. Testing against a single value or a list of values.....................................37 2.12. Correlate query.......................................................................................................38

3. Data Manipulation Language (DML)..................................................40

3.1. Insert, Update, Delete...............................................................................................40 3.1.1. Insert..........................................................................................................40 3.1.2. Update.......................................................................................................41 3.1.3. Delete........................................................................................................41 3.1.4. Updating by value from other table...........................................................42 3.1.5. Updating by correlated subquery..............................................................42 3.2. Transaction................................................................................................................42

4. Substitution variables in SQL*PLUS.................................................44 5. Oracle Data Dictionary........................................................................45

5.1. How can I use the DD-information?.......................................................................45 5.2. List..............................................................................................................................45

6. Data Definition Language (DDL).........................................................47

6.1. Create, Alter, Drop...................................................................................................47 6.2. Create a table, view, index and synonym...............................................................47 6.2.1. Create a table.............................................................................................47 6.2.2. Create a view.............................................................................................48 6.2.3. Create index...............................................................................................48 6.2.4. Create synonym.........................................................................................49 6.3. Alter a table, view, index and synonym..................................................................49 6.3.1. Alter table..................................................................................................49 6.3.2. Alter view..................................................................................................50 6.3.3. Alter index.................................................................................................50 6.4. Drop table, view, index and synonym.....................................................................51

7. Guide to writing efficient SQL statements........................................52 8. Table description.................................................................................54

8.1. Demo_customer_tab.................................................................................................54 8.2. Demo_company_tab.................................................................................................54 8.3. Demo_order__tab.....................................................................................................55 8.4. Demo_order_item_tab..............................................................................................55 8.5. Demo_product_tab...................................................................................................55 8.6. Demo_inventory_tab................................................................................................56 8.7. Demo_project_tab.....................................................................................................56

Table of Contents II

1. Database
1.1. What is a database
A database is primarily a collection of data. In information science, we used to say that data is numbers, characters, figures and so on, the smallest unit we are able to deal with when we want to store information about some subject. In a database, these figures are structured in some specific way, so it will become easy to retrieve them whenever we want. There are also some kind of relation between them. That is why you may call it database. But this doesnt have to mean that every little piece of data is physically stored in the same physical media, on the same computer. Just like there are several ways to store and structure the data, there are also different ways to locate the data. We are able to share the database between several computers. But we are here focusing on how to store data and how to make the connections between them. A common way of structuring a database today is as a relational database.

1.2.

Relational database
The system designers identify and isolate types of information that need to be captured. Then they identify the relationships between those information types, and the result become a rational model. In a relational database, data are stored in what you call tables. These tables consists of columns and rows and one row is also called a record. The rational model with its structure is implemented on a computer disc and by writing SQL statements, you will get the database to behave the way you want and you can retrieve your selected data. Notice how figure 1 shows a demo_order_tab table, a demo_order_item_tab table, and a demo_product_tab table. Each is a separate table within the database. In figure 1, the relationship between demo_order_tab and demo_order_item_tab is represented by the crows feet, which shows that an order may have one or more order_items. There are also a single line between demo_order_item_tab and demo_product_tab governing a reference from demo_order_item_tab to demo_product_tab. Thus, we have some rules governing the relationships between our order and order _items data: Each order may have one or more order_items Each order_item belongs to one and only one order An order_item may have a product

Queries

Queries with functions 1

Figure 1.

Figure 1. DEMO_ORDER_TAB DEMO_PRODUCT_TAB

DEMO_ORDER_ITEM_TAB

1.3.

RDBMS
An RDBMS (Relational Database Management System) is a set of programs, designed to manage a database. A set of tools, with which you are able to create tables, indexes, define relations between tables and so on. It let you implement your database structure and it will let you retrieve your data in an easy and flexible way. Oracle, which is used by IFS is one of the largest RDBMS at the market today.

1.4.

The features of a relational database

The features of a relational database are the following: A relational database is data-driven, not design-driven which mean that it is designed once, and the data changes over time without affecting the applications. The data is self-describing. For example the phone number type is identified as phone_number_type. Data is stored in one place, read from one place, and modified in one place. Data is stored once, so maintaining consistency among all applications is easier. The main goal is that no duplication of data should exist in the database. Rules that control how the data will be stored are defined and enforced.

Queries

Queries with functions 2

1.5.

SQL Language
SQL (Structured Query Language) is the language we use to retrieve and manipulate data in the database, define and create its structure. SQL statements fall into two major categories: DDL (Data Definition Language) and DML (Data Manipulation Language). Lets take a closer look at their differences and similarities.

1.5.1.

DDL
Data Definition Language allows you to perform the following tasks: Command Create Drop Alter A purpose of each statement Create a database object Drop a database object Alter a database object

Its important to understand that when you issue a DDL SQL statement, Oracle commits the current transaction before and after every DDL-statement SQL commands that alter, drop, create, and grant are the most frequently used examples of the Data Definition Language.

1.5.2.

DML
Data Manipulation Language allow you to insert, update, delete and select data in the database. Just as the name implies, DML allows you to work with the contents of your database. Command insert delete update select A purpose of each statement Add rows of data to a table Delete rows of data from a table Change data in a table Retrieve rows of data from a table/view

1.5.3.

DCL
Data Control Language let you define what access to different objects and what permissions to change objects and data in the database, a specific user should has. Command Grant Revoke A purpose of each statement let you set permissions to a user let you set some contraints to the default permissions

1.5.4.

Transaction commands
With these two different transaction commands, you would be able to make changes in the database permanent or undo the changes from the last time you made it permanent. Command Commit [work] Rollback A purpose of each statement Make changes permanent for the current transactions. Undo all changes since the last commit.

Queries

Queries with functions 3

1.6.

Oracle Server
The Oracle Server is a State of the art information management environment. It is a repository for very large amounts of data, and gives users rapid access to the data. The Oracle Server provides for the sharing of data between application; the information is stored in one place and used by many systems. The Oracle Server runs on dozens of different computers, supporting the following configurations: Host based: Users are connected directly to the same computer on which the database resides. Client/Server: Users access the database from their personal computer (Client) via a network, and the database sits on a separate computer (Server) Distributed processing- Users access a database that resides on more than one computer. The database is spread across more than one machine, and the users are unaware of the physical location of the data they work with.

1.7.

SQL*PLUS
SQL*PLUS is an extension of the ordinary SQL Language. It contains an amount of commands, useful when you want to make queries to a database. SQL*PLUS is made up by ORACLE. You may call SQL*PLUS a sort of environment, a co worker. It lets you write your questions in an editor, using SQL statements and then sends them to the ORACLE engine, to process them and finally display the result to you.

Queries

Queries with functions 4

2. Queries
2.1. Writing SQL (general)
A SQL statement has a very logical structure and once you know the table and column names in your database, you will feel comfortable writing SQL. There are several reserved words in SQL, that allow you to manipulate the data and creating and defining the datastructure. Knowing these words and column and table names, you will get a deep understanding of what different statements will do and how to write it. The structure of SQL is builded by several clauses which follow each other. We are going to get througt this clauses, each by each and explain the other parts of SQL as we get through the course. Below we are going to describe and show some examples of the SQL language beginning with DML-statements, continuing with DDL-statements and finishing with a description how to write efficient SQL. Writing SQL we can use either editor we want. To run the query, we though have to make a connection to an SQL-compiler. The easiest way to get the SQL-statement run immediately is to write it in SQL-PLUS, an Oracle product that is connected to an own editor and a powerful tool that will run your query and return a message if there is an error in your statement. An SQL-statement always ends with a semicolon ( ; ). Example 2.1 select order_id from demo_order_tab; In SQL-PLUS, there also will be an in built tiny little editor, which is useful if you want to add, delete or modify something in your query without having to rewrite the whole statement. This editor is called Command Line Editor. SQL-PLUS is also a more powerful language than the standard SQL. When you call for Command Line Editor, with the command List, you will get this: Example 2.2 1 2 3* select company_id, order_id, delivery_date from demo_order_tab where delivery_type = 2;

The asterisk show what line you are able to affect. If you want to affect another line instead of line three because you want to edit the word surname, you will have to type list 1, now showing:

Queries

Queries with functions 5

Example 2.3 1* select company_id, order_id, deliveru_date

To make the change, type Change /deliveru_date/delivery_date 1* select company_id, order_id, delivery_date

Using this editor, is though somewhat time-consuming. Instead you will be able to give the command edit(ed)at the SQL-prompt. This will put your text usually into the editor of your choice, at IFS its usually Notepad that is linked to SQL*PLUS SQL-PLUS will not be Case Sensitive. Its only when checking for equality you have to remember to give the soughed variable in same letters and format as in the database.

2.2.

Useful to know
In the following three sections there are some useful tips, you will need before going on with writing SQL questions.

2.2.1.

Description of a database
To get a description of the database, i.e the tables in it, just type select table_name from tabs and you will se all the tables that exist in the database. You can also get a description of each of the tables, i.e the column in it which means their name, their datatype and the maximum length of their fields. You only have to give the statement describe (desc) followed by the table name and you will get a brief description of the content in the database. A description which will be very useful to you in order to write questions and get the data you want. Example 2.4 describe demo_order_tab;

2.2.2.

The DUAL table

To be able to make quick calculations, testing function or somewhat else, there is a little help table offered by SQL called DUAL. This table has only one column and only one row. Example 2.5 SQL> select 765+960 from dual; 765+960 --------1725

Queries

Queries with functions 6

2.2.3.

Listing every row in a table

An easy way to list every row in a table, including all its fields, is by using an asterisk. Instead of specifying several column names, you only give an asterisk ( * ) and tell SQL from what table you want the rows. Example 2.6 SQL> select * from demo_document; DOC_NO -----1 2 3 4 TITLE -----------------------------A sample document A letter from the CEO of KADU Ad from Yog-Sothoth Candy, Inc Order confirmation VERSION ------1 19970510 1 2 ROWVERSION ---------11-MAY-98 11-MAY-98 11-MAY-98 11-MAY-98

2.3.

Simple queries
SELECT (clause)
To retrieve data from an Oracle database the select statement will be used. The Select command has two basic parts: The keyword select followed by an asterisk to get all from a table. After that the name of the table where you get it from (i.e., the name of one or more tables where the data resides). This is mandatory. Example 2.7 SQL> select * from demo_inventory_tab; CO INV DESCRIPTION 01 10 01 20 01 30 01 40 01 50 02 10 02 20 03 10 Base warehouse 23 Factory warehouse 10 Local store warehouse 72 Airport warehouse 42 Reserve warehouse 87 Base warehouse 54 Factory warehouse 654 Base warehouse 14 ROWVERSIO 11-MAY-98 11-MAY-98 11-MAY-98 11-MAY-98 11-MAY-98 11-MAY-98 11-MAY-98 11-MAY-98

2.3.1.

-- --- ------------------------------ ---------

Queries

Queries with functions 7

2.3.2.

Select with columns specified

The keyword select followed by the columns where you get it from (i.e. the names of the columns in the table) and finally in what table the column resides. Example 2.8 SQL> select company_id, inventory_id, description 2 from demo_inventory_tab;

CO INV DESCRIPTION -- --- -----------------------------01 10 Base warehouse 23 01 20 Factory warehouse 10 01 30 Local store warehouse 72 01 40 01 50 02 10 02 20 03 10 03 20 Airport warehouse 42 Reserve warehouse 87 Base warehouse 54 Factory warehouse 654 Base warehouse 14 Factory warehouse 82

9 rows selected.

2.4.

Queries with where clause (condition)

By using select statement you retrieve all data in the database, but if you want to see only certain rows of the data, a select statement with a where clause should be used. A where clause instructs Oracle to search the data in a table and return only those rows that meet your criteria. If the criteria contains several criteria you should to use logical expressions within the where statement. Example 2.9 SQL> select company_id, inventory_id, description 2 3 from demo_inventory_tab where company_id=02;

CO INV DESCRIPTION -- --- -----------------------------02 10 02 20 Base warehouse 54 Factory warehouse 654

Queries

Queries with functions 8

2.4.1.

Logical operators (testing against a single value)

When you give a condition with where, the value of both sides of the logical operator will be tested against each other. Those rows where the condition hold, will be set to true and sent back. The other rows will be set to false. Every rows are first checked and will then be sent back.

Testing against a value in the price column in demo_order_item_tab table. Price is a column with numerical values. = > >= < <= != ^= <> Test for equality Greater than Greater than or equal to Less than Less than or equal to Test for inequality Test for inequality Test for inequality Price = 7 Price >7 Price >= 7 Price < 7 Price <= 7 Price != 7 Price ^= 7 Price <> 7

You can also test against a literal, just a simple letter or a simple figure. If you have a column with only one letter for each row it might look like the following: Example 2.10 SQL> select * from demo_product_tab 2 where unit = 'M'; DESCRIPTION UNIT COMMENTS PRICE ROWVERSION ROWTYP -----37 11-MAY-98 DemoSal 87 11-MAY-98 DemoSal 165 11-MAY-98 DemoSal

CO PRODUCT_ID -- ---------01 01 01 .. .. .. 03 514 103 104 105

---------------- ---- -------- ----- ---------Steel wire 1 mm M Steel wire 3 mm M Steel wire 10 mm M

Book panel 10 tumM

236 11-MAY-98 DemoSal

69 rows selected.

Note! The single quotes tell Oracle, that this is not a column name.

Queries

Queries with functions 9

2.4.2.

Pattern matching
If you want to search for certain records whose value in the column name for example begins with the letters "NO" you may use the statement LIKE for pattern matching. In addition you use the percent sign (%), also called a wild card, which represents any number of spaces or characters. You can also use an underline (_), which represents one space. Example 2.11 SQL> Select * from demo_inventory_tab 2 where description like 'Base warehouse%'; ROWVERSIO 11-MAY-98 11-MAY-98 11-MAY-98

CO INV DESCRIPTION 01 10 02 10 03 10 Base warehouse 23 Base warehouse 54 Base warehouse 14

-- --- ------------------------------ ---------

2.4.3.
2.4.3.1.

Test for existing value

Exists With the exist command, you will be able to test if there exist a certain value or row in the database. To be able to use exists, you have to write a SQL-statement with a subquery (see section 2.12 below). A subquery is a select statement, returning a value or a complete row(s) that then are tested against the main query. Example 2.12 SQL> 2 3 4 5 select order_id, delivery_date from demo_order_tab where exists (select * from demo_order_tab where delivery_type='3');

ORDER_ID DELIVERY_ --------- --------10002 26-NOV-96

Queries

Queries with functions 10

2.4.3.2.

The NULL value

The cross reference of a certain column and a certain row is as you know, called a cell. A cell contains a specific value. In the definition of the database (its tables) you will be able to specify if a certain cell is allowed to be empty or not, i.e. if a cell must contain a value or not. If the cell doesnt contain a value, you will say that the cell contains a NULL value, which in the applications means that the value is unknown or simply does not exist. See for example the column comments in the demo_product_tab table in example 2.10 above, which not contain any values. In the definition of a table, there can be specified that you must put in a value in every field in a row. No field is allowed to be empty. This is done by enter a NOT NULL-statement connected to the column specification. If you dont set it to NOT NULL, you may leave the field empty and Oracle would not react. In calculating, (Number functions) for example when summarizing, the result would also be NULL. But this is the only group value function that is affected by NULL. All the other group value functions can be calculated, although there is a Null value. These group value functions ignore NULLS, which is different from single value functions, because the single value functions only operate at one value at a time. In a real world situation you maybe work as a package carrier, getting weighes from several shippers on their cargo. Say that some shippers of some reason cant tell the weigh of there cargo. To leave the cell empty would mean that you wont be able to calculate the total weigh. Because you know the average weigh since before, you now make a substitution for the NULL value. This is done by the statement NVL and is done like below.

2.4.4.
AND

Combination of logic
By a couple of statements you may combine several logic statements.

Choose rows where all logic values is true. Example 2.13 SQL> 1 2 3 4 CO -01 01 select company_id, order_id, row_no, description, price, quantity from demo_order_item_tab where quantity>100 and quantity<500; ORDER_ID -------10001 1000 ROW_NO -----9 10 DESCRIPTION ------------------Motor Oil Very Fine Diesel PRICE ----58 34 QUANTITY -------200 300

Queries

Queries with functions 11

OR Choose rows where either of or both logic values are truth. Example 2.14 SQL> 1 3 4 CO -01 01 01 1 01 select company_id, order_id, row_no, description, price, from demo_order_item_tab where price<10 or quantity>=2000; ORDER_ID -------10002 10003 10012 10012 10012 ROW_NO -----11 24 43 44 45 DESCRIPTION ------------------Battery Water Battery Water Petrol Galvoline 93 Petrol Galvoline 95 Petrol Galvoline 98 PRICE ----9 9 8 8 8 QUANTITY -------1000 100 1000 1000 1000 2 quantities

AND and OR may also be combined. Though remember that AND have higher priority than OR, which means that AND will be executed first. To avoid confusion, put the statements you want to be executed together, within parenthesis. If there are two operators of the same, execution will be done from left to right in the statement.

2.4.5.

Logical operators, testing against a list of values

IN Selects every fits criteria row that

Ex.Delivery_type IN (1,2,3)

NOT IN

Selects every row not fits criteria

that

Ex. Delivery_type NOT IN (1,2)

BETWEEN

Selects every row whose value in certain field is between two criteria Price BETWEEN 100 AND 1000

Queries

Queries with functions 12

NOT BETWEEN

Selects every row whose value in a certain field is either below one criteria and above another criteria. Ex. Price NOT BETWEEN 300 AND 600

2.4.6.

Combination of rows from different tables

The following statements let you choose and combine different rows without a condition. UNION Chooses all rows in several tables. If there are equal values listed in both tables, its only listed once is the result of the query. Example 2.15 SQL> select name from demo_company_tab 2 unions 3 select description from demo_inventory_tab; NAME -------------------------------------------------ACME Business Partners ACME Jurassic Submarines ACME Rocket Scientists Airport warehouse 42 Base warehouse 14 Base warehouse 23 Base warehouse 54 Factory warehouse 10 Factory warehouse 654 Factory warehouse 82 Local store warehouse 72 Reserve warehouse 87 12 rows selected.

Queries

Queries with functions 13

INTERSECT Chooses all rows that only exists in all tables you have specified.

Example 2.16 SQL> select delivery_date from demo_order_tab 2 intersect 3 select invoice_date from demo_invoice_tab; DELIVERY_ --------04-NOV-96 MINUS Chooses those rows which only exist in the first specified table. Example 2.17 SQL> select delivery_date from demo_order_tab 2 minus 3 select invoice_date from demo_invoice_tab; DELIVERY_ --------11-NOV-96 26-NOV-96 05-DEC-96 14-DEC-96 24-DEC-96 31-DEC-96 01-JAN-00 02-JAN-00 04-JAN-00 14-JAN-00 04-NOV-00 26-NOV-00 12 rows selected.

Queries

Queries with functions 14

2.5.

Order by (clause)
To request a list ordered by some columns values in alphabetical (or in numerical order depending on what kind of values there are in the column), the statement to use is ORDER BY followed by column name. With the descending statement right after you have specified what column you want to order by, you will get the list in descending alphabetical order (or in descending numerical order). If you do not, you will get the list in ascending order, which also is the default state. Example 2.18 SQL> select * from demo_inventory_tab 2 order by inventory_id, description; ROWVERSIO 03-APR-98 03-APR-98 03-APR-98 03-APR-98 03-APR-98 03-APR-98 03-APR-98 03-APR-98 03-APR-98

CO INV DESCRIPTION 03 10 01 10 02 10 01 20 02 20 03 20 01 30 01 40 01 50 Base warehouse 14 Base warehouse 23 Base warehouse 54 Factory warehouse 10 Factory warehouse 654 Factory warehouse 82 Local store warehouse 72 Airport warehouse 42 Reserve warehouse 87

-- --- ------------------------------ ---------

9 rows selected.

2.6.

Datatypes
To be able to store different kind of data in the database, ORACLE offers several datatypes. You would specify one of these for a certain column and then youre allowed to input that format of data. But also note that youre only allowed to input just the format of data you have specified for the column. If you try to input another format of data, you will get an error. Depending on datatype, the column name in the presentation will be either adjusted to left or adjusted to the right.

Queries

Queries with functions 15

Number Character, Char (size) Date Varchar2 (size) Long Raw (size) Longraw

A datatype for maximum 40 digits with numbers 0 9 and the signs +, -, Fixed length of characters, both figures, letters and others For valid dates from 1 January to 31 December Variable length character string with a maximum specified A Char datatype of variable size up to 65000 characters A datatype for raw binary data which allow a maximum of a defined size The same as the Raw datatype except for allowing up to a size of 65000 binary digits

(price NUMBER [n] ) (name CHAR [n] ) (order_date DATE) (address VARCHAR2 [n] ) (comments LONG) (collect RAW) (score LONGRAW)

There are a lot of other datatypes too, available in Oracle. The datatypes above, are though the primary ones, the most common used when writing SQL-PLUS. The other datatypes that are available, is often used for compatibility with other SQL databases. For a complete reference of those, please see some reference guide from Oracle

2.6.1.

Strings
A certain value of a datatype is usually a string. A string consists of numbers, letters, symbols and other characters. Though note that its not always a string. When the value only consists of numbers, it is said not to be a string. To tell the database that it should be handled like a string, you put single quotes around it. Without quotes, Oracle will handle it like a column name.

2.6.2.

Concatenate
There is a powerful way to concatenate strings. When you for example select two columns from a table, to show them on a screen, you may get it as in the figure below. Example 2.19 SQL> select company_id, order_id 2 from demo_order_tab;

CO ORDER_ID -- --------01 01 01 01 01 01 10000 10001 10002 10005 10006 10007

Queries

Queries with functions 16

01 01 01 01 1

10008 10009 10010 10011 10012

There will be a lot of empty space between the two column. It will depend upon your definiton of maximum length for the column NAME in this case. To get rid of this empty space, you may for example dont want it to be there in a report, you will have to concatenate it. You do the concatenation by using the symbol || between the texts. It will put the text together but also remember that it is not only the space on the display that visually disappear, but also the strings.

Example 2.20 SQL> select company_id||', '||order_id 2 from demo_order_tab;

COMPANY_ID||','||ORDER_ID -------------------------------------------01, 10000 01, 10001 01, 10002 01, 10005 01, 10006 01, 10007 01, 10008 01, 10009 01, 10010 01, 10011 01, 10012 01, 10013 01, 10014 13 rows selected.

2.6.3.

Alias
As a programmer you are allowed to give an alias name to a column name with the presentation of columns data.

Queries

Queries with functions 17

This is very useful when you use a function on a column, which in that case will give you an rather cryptic column name at the presentation. At least, it will be more user friendly if you are able to rename the column to a more explainable name. You type the alias name right after you have specified the column name as it appears in the database. Example 2.21 SQL> select company_id||', '||name companies 2 from demo_company_tab;

COMPANIES ----------------------------------------------01, ACME Rocket Scientists 02, ACME Jurassic Submarines 03, ACME Business Partners As you see there is some limitation to assign alias. The limitation is that the alias name must be a single word and it will be displayed with only uppercase. If you want to give a free alias name to the column name and want it to be displayed exactly as you write it you must use double quotes. Alias is especially useful when columns have difficult and strange names and when there are some concatenation or other functions assigned to the question as in example above. Note that column and table names, will always be saved in uppercase in the database.

When you have create an alias, you will also be able to refer to that name wherever in the same statement.

2.7.

Queries with functions

SQL contains a number of functions to manipulate the presentation of information. The functions fall into four categories: Character functions Number functions Date functions Conversion and Transformation functions List functions A function produces two kind of results (output). First, a function can change or create an object. Secondly a funcion can produce some information about an object. Each function category will be described in the following sections

Queries

Queries with functions 18

2.7.1.

Character functions (let you cut and paste)

Single row character functions accept character as 0 as input and return either a character or a number value.

ASCII(char)
Returns the decimal representation in the database character set of the first byte of char. If your database character set is 7-bit ASCII, this function returns an ASCII value. If your database character set is EBCDIC Code Page 500, this function returns an EBCDIC value. SELECT ASCII('Q') FROM DUAL; Result: 81

CHR(char)
Returns the string having the binary equivalent to char in the database character set. SELECT CHR(75) FROM DUAL; Result: K

INITCAP(char)
Returns string, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric. SELECT INITCAP('the soap') FROM DUAL; Result: The soap

INSTR(char1, char2, [,n [,m]])

Searches the character string for what you have specified as char1, starting at n, looking for the position of the char2, according to m. SELECT INSTR('Crocodile', 'o ', 1,1) FROM DUAL; Result: 3

Queries

Queries with functions 19

LENGTH(char)
Returns the length of char in characters. If char has datatype CHAR, the length includes all trailing blanks. If char is null, this function returns null. SELECT LENGTH('CANDIDE') FROM DUAL; Result: 7

LOWER(char)
Returns char, with all letters in lowercase. The return value has the same datatype as the argument char (CHAR or VARCHAR2). SELECT LOWER('MR. SAMUEL') FROM DUAL; Result: Mr.Samuel

LPAD(char1, n, [,char2])
Returns char1, left-padded to length n with the sequence of characters in char2; char2 defaults to ' ', a single blank. If char is longer than n, this function returns the portion of char that fits in n. The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multi-byte character sets, the display length of a character string can differ from the number of characters in the string. SELECT LPAD('Page 1',15,'*.') "LPAD example" FROM DUAL; Result: *.*.*.*.*Page 1

LTRIM(char, set)
Removes characters from the left of char, with initial characters removed up to the first character not in set; set defaults to ' ', a single blank. SELECT LTRIM ('SSSet your business free ', 'SS ') FROM DUAL; Result: Set your business free

Queries

Queries with functions 20

NLSSORT(char [, 'nlsparams'])
Returns the string of bytes used to sort char. The value of 'nlsparams' can have the form 'NLS_SORT = sort' where sort is a linguistic sort sequence or BINARY. If you omit 'nlsparams', this function uses the default sort sequence for your session. If you specify BINARY, this function returns char. SELECT * FROM emp WHERE NLSSORT(ename, 'NLS_SORT = German') > NLSSORT('B', 'NLS_SORT = German');

REPLACE(char, search_string [,replacement_string])

Returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, all occurrences of search_string are removed. If search_string is null, char is returned. This function provides a superset of the functionality provided by the TRANSLATE function. TRANSLATE provides single character, one to one, substitution. REPLACE allows you to substitute one string for another as well as to remove character strings. SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL; Result: BLACK and BLUE

RPAD(char1, n, [,char2])
Returns char1, right-padded to length n with char2 replicated as any times as necessary; char2 defaults to ' ', a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n. The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multi-byte character sets, the display length of a character string can differ from the number of characters in the string. SELECT RPAD(description,32, '(temporary)') " FROM demo_inventory_tab WHERE inventory_id = '50'; Result: Reserve warehouse 87 (temporary)

RTRIM(char [,set])

Queries

Queries with functions 21

Returns char, with final characters removed after the last character not in set; set defaults to ' ', a single blank. SELECT RTRIM('Resevrve warehouse 87','warehouse 87') FROM DUAL; Result: Reserve

SUBSTR(char, m, n)
Returns a portion of char, beginning at character m, n characters long. If m is positive, Oracle counts from the beginning of char to find the first character. If m is negative, Oracle counts backwards from the end of char. The value m cannot be 0. If n is omitted, Oracle returns all characters to the end of char. The value n cannot be less than 1. SELECT SUBSTR('ABCDEFG',3,2) "Substring" FROM DUAL; Result: CD

TRANSLATE(char,from,to)
Returns char with all occurrences of each character in from replaced by its corresponding character in to. Characters in char that are not in from are not replaced. The argument from can contain more characters than to. In this case, the extra characters at the end of from have no corresponding characters in to. If these extra characters appear in char, they are removed from the return value. You cannot use empty string for to in order to remove all characters in from the return value. Oracle interprets the empty string as null, and if this function has a null argument, it returns null. SELECTTRANSLATE('2KRW229','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ','9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "Translate example" FROM DUAL;

UPPER(char)
Returns char, with all letters uppercase. The return value has the same datatype as the argument char SELECT UPPER('Large') FROM DUAL; Result: LARGE

Queries

Queries with functions 22

2.7.2.

Number functions
Number functions accept numeric input and return numeric values. Most of these functions return values that are accurate to 38 decimal digits. The transcendental functions (COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, TANH) are accurate to 36 decimal digits. There are also three different groups or classes of number funcions. Single value functions Group value functions List value functions

Single value functions (arithmetic functions) ABS(n)

Returns the absolute value of n. An absolute value will always be positive SELECT ABS(-15) FROM DUAL; Result: 15

CEIL(n)
Returns the smallest integer greater than or equal to n. SELECT CEIL(15.7) FROM DUAL; Result: 16

COS(n)
Returns the cosine of n (an angle expressed in radians). SELECT COS(180 * 3.14159265359/180) FROM DUAL; Result: -1

COSH(n)
Returns the hyperbolic cosine of n.

Queries

Queries with functions 23

SELECT COSH(0) FROM DUAL; Result: 1

EXP(n)
Returns e raised to the nth power; e = 2.71828183 SELECT EXP(4) FROM DUAL; Result: 54.59815

FLOOR(n)
Returns the largest integer equal to or less than n. The opposite to CEIL. SELECT FLOOR(15.7) FROM DUAL; Result: 15

LN(n)
Returns the natural logarithm of n, where n is greater than 0 SELECT LN(95) FROM DUAL; Result: 4.55387689

LOG(m, n)
Returns the logarithm, base m, of n. The base m can be any positive number other than 0 or 1 and n can be any positive number SELECT LOG(10,100) FROM DUAL; Result: 2

MOD(m, n)
Returns the remainder of m divided by n. Returns m if n is 0. SELECT MOD(11,4) FROM DUAL; Result: 3

Queries

Queries with functions 24

POWER(m, n)
Returns m raised to the nth power. The base m and the exponent n can be any numbers, but if m is negative, n must be an integer. SELECT POWER(3,2) FROM DUAL; Result: 9

ROUND(n[, m])
Returns n rounded to m decimal places; if m is omitted, to 0 places. M can be negative to round off digits left of the decimal point. M must be an integer. SELECT ROUND(15.193,1) FROM DUAL; Result: 15.2

SIGN(n)
If n<0, the function returns -1; if n=0, the function returns 0; if n>0, the function returns 1. SELECT SIGN(-15) FROM DUAL Result: -1

SIN(n)
Returns the sine of n (an angle expressed in radians). SELECT SIN(30*3.14159265359/180) FROM DUAL; Result: .5

SQRT(n)
Returns the square root of n SELECT SQRT(64) FROM DUAL; Result: 8

Queries

Queries with functions 25

TRUNC(n[, m])
Returns n truncated to m decimal places; if m is omitted, to 0 places. M can be negative to truncate (make zero) m digits left of the decimal point. SELECT TRUNC(15.79,1) FROM DUAL; Result: 15.7

Group value functions (statistical) SUM(n)

Returns the sum for a group of values SELECT SUM(15+14) FROM DUAL; Result: 29

AVG(n)
Returns the average for a group of values SELECT AVG(price) FROM demo_product_tab; Result: 422.6375

COUNT(n)
Count rows in a table and return the amount of rows SELECT COUNT * FROM demo_order_tab; Result: 15

MIN(n)
Returns the lowest value of a specified range of values SELECT MIN(price) FROM demo_product_tab; Result: 8

Queries

Queries with functions 26

MAX(n)
Returns the highest value of a specified range of values SELECT MAX(price) FROM demo_product_tab; Result: 7623

STDDEV(n)
Returns the standard deviation of n, a number. Oracle calculates the standard deviation as the square root of the variance defined for the VARIANCE group function. SELECT STDDEV(price) FROM demo_product_tab; Result: 1103.4936

VARIANCE(n)
Returns the variance (the square root of the standard deviation) of a specified range of values SELECT VARIANCE(price) FROM demo_product_tab; Result: 1217698.1

DISTINCT(n)
Collects rows that look the same and put them into categories SELECT DISTINCT product_id FROM demo_product_tab;

Queries

Queries with functions 27

+ * /

Addition Subtraction Multiplication Division

List value functions GREATEST(value1, value2, [])

Pick out the highest value in a row, which for example consists of several values in different columns. SELECT GREATEST(order_seq, invoice_seq) FROM demo_company_tab WHERE company_id= '03'; Result: 1

LEAST(value1, value2, [])

Pick out the lowest value in a row, which for example consists of several values in different columns. SELECT LEAST(order_seq, invoice_seq) FROM demo_company_tab WHERE compamy_id= '03';

Result:

2.7.3.

Date functions
Date functions operate on values of the DATE datatype. All date functions return a value of DATE datatype, except the MONTHS_BETWEEN function, which returns a number.

Queries

Queries with functions 28

ADD_MONTHS(d, n)
Returns the result of date d plus n months. The argument n can be any Integer. If d is the last day of the month or if the resulting Month has fewer days than the day component of d, then the result is the last day of the resulting month. Otherwise, the result has the same day component as d. SELECT TO_CHAR(ADD_MONTHS(delivery_date,3),'DD-MON-YYYY') FROM demo_order_tab WHERE order_id=10001; Result: 04-FEB-1997

LAST_DAY(d)
Returns the date of the last day of the month that contains d. You might use this function to determine how many days are left in the current month. SELECT SYSDATE,LAST_DAY(SYSDATE) , LAST_DAY(SYSDATE) SYSDATE FROM DUAL; Result: 12-JUN-98 30-JUN-98 18

MONTHS_BETWEEN(d1,d2)
Returns number of months between dates d1 and d2. If d1 is later than d2, result is positive; if earlier, negative. If d1 and d2 are either the same days of the month or both last days of months, the result is always an integer; otherwise Oracle calculates the Fractional portion of the result based on a 31-day month and also Considers the difference in time components of d1 and d2. SELECT MONTHS_BETWEEN(TO_DATE('02-02-1992', 'MM-DD-YYYY'), TO_DATE('01-01-1992', 'MM-DD-YYYY')) FROM DUAL; Result: 1.0322581

NEW_TIME(d,z1,z2)
Returns the date and time in time zone z2 when date and time in time zone z1 are d SELECT NEW_TIME(order_date, 'EST', 'HST')

FROM DUAL;

Queries

Queries with functions 29

NEXT_DAY(d,char)
Returns the date of the first weekday named by char that is later Than the date d. The argument char must be a day of the week in your session's date language. The return value has the same hours, minutes, and seconds component as the argument d SELECT NEXT_DAY('15-MAR-98','TUESDAY') FROM DUAL; Result: 17-MAR-98

ROUND(d[,fmt])
Returns d rounded to the unit specified by the format model fmt. If you omit fmt, d is rounded to the nearest day. SELECT NEXT_DAY('15-MAR-98','TUESDAY') FROM DUAL; Result: 17-MAR-98

SYSDATE
Returns the current date and time. Requires no arguments. In distributed SQL statements, this function returns the date and time on your local database. You cannot use this function in the condition of a CHECK constraint.

SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') FROM DUAL; Result: 06-12-1998 08:56:24

TRUNC(d[,fmt])
Returns d with the time portion of the day truncated to the unit specified by the format model fmt. If you omit fmt, d is truncated to the nearest day. SELECT TRUNC (TO_DATE('27-OCT-97', 'DD-MON-YY'), 'YEAR') FROM DUAL; Result: 01-JAN-97

Queries

Queries with functions 30

2.7.4.

Conversion functions
Conversion functions convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype TO datatype. The first datatype is the input datatype; the last datatype is the output datatype.

CONVERT(char, dest_char_set [,source_char_set])

Converts a character string from one character set to another. SELECT CONVERT('Grob','WE8HP','WE8DEC') FROM DUAL Result: Grob

HEXTORAW(char)
Converts char containing hexadecimal digits to a raw value. INSERT INTO graphics (raw_column) SELECT HEXTORAW('7D') FROM DUAL;

RAWTOHEX(raw)
Converts raw to a character value containing its hexadecimal SELECT RAWTOHEX(raw_column) FROM graphics; Result: 7D Equivalent.

ROWIDTOCHAR(rowid)
Converts a ROWID value to VARCHAR2 datatype. The result of this conversion is always 18 characters long. SELECT ROWID FROM graphics WHERE ROWIDTOCHAR(ROWID)LIKE '%F38%'; Result: 00000F38.0001.0001

Queries

Queries with functions 31

TO_CHAR(d [, fmt [, 'nlsparams'] ])

Converts d of DATE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, d is converted to a VARCHAR2 value in the default date format. SELECT TO_CHAR(order_date,'Month DD, YYYY') FROM demo_order_tab WHERE order_id='10003'; Result: November 04, 1996

TO_CHARn [, fmt [,'nlsparams'] ])

Converts n of NUMBER datatype to a value of VARCHAR2 datatype, using the optional number format fmt. If you omit fmt, n is converted to a VARCHAR2 value exactly long enough to hold its significant digits. SELECTTO_CHAR(17145,'L099G999','NLS_NUMERIC_CHARACTERS = ''.,'' NLS_CURRENCY = ''AUD'' ') "Char" FROM DUAL; Result: AUD017,145

TO_DATE(char [, fmt [, 'nlsparams'] ])

Converts char of CHAR or VARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, char must be in the default date format. If fmt is 'J', for Julian, then char must be a number. INSERT INTO bonus (bonus_date). SELECT TO_DATE('January 15, 1989, 11:00 A.M.', 'Month dd, YYYY, HH:MI A.M.','NLS_DATE_LANGUAGE = American') FROM DUAL;

TO_DATE(char [, fmt [, 'nlsparams'] ])

Converts char of CHAR or VARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, char must be in the default date format. If fmt is 'J', for Julian, then char must be a number. INSERT INTO bonus (bonus_date). SELECT TO_DATE('January 15, 1989, 11:00 A.M.', FROM DUAL; 'Month dd,

YYYY, HH:MI A.M.','NLS_DATE_LANGUAGE = American')

Queries

Queries with functions 32

TO_NUMBER (char [, fmt [, 'nlsparams'] ])

Converts char of CHAR datatype to a value of NUMBER datatype.

2.7.5.

Transformation functions TRANSLATE(char, if, then)

Takes a character string, one character at a time and if the caracter exists in the if, then the actual character will be substituted to the caracter in the then, in the same position SELECT TRANSLATE (123456, 64532, 'ABCDEFG') FROM DUAL Result: 1EDBCA

DECODE(char, if1, then1, if2, then2else)

See next section for a deeper description

2.8.

The decode function

The decode statement is how you implement if-then logic in SQL*Plus. One of the most powerful functions within SQL*Plus is the decode statement. Most people shy away from it, due to its ugly syntax. As soon as you add other functions onto the columns, you can quickly get what we call ugly SQL. Like a small dog, its bark is far worse than its bite. With that in mind, lets take a look at the format for a decode statement Decode (column_name, .else action) comparison, action, comparison, action,

The decode statement compares the column contents to the comparison field. If they are equal, then decode does the action. If they are not equal, decode goes on to the next comparison. If none of the comparisons match, then the else action is performed. Also note that you are able to have as many if-then combinations as you want. The last else statement would not just has to be a simple instruction to substitute something. It can also be for example a function.

2.8.1.

Combining functions
When you use combinations of functions, dont forget to enclose each of it with a single parentheses.

Queries

Queries with functions 33

2.9.

Join
To retrieve a record of data which belongs in several different tables we should join all relevant tables together. By definition, relational databases such as Oracle allow you to relate for two or more tables based on common fields. Most often, these fields are what we refer to as key fields. There are two types of keys: primary and foreign. A primary key is what makes a row of data unique within a table. One tables foreign key is used to get information out of another ( foreign) table. Note that when we are referring to the column in a certain table in the select clause (the primary key), which set the relation with its duplication in the other table (the foreign key) we have to specify the table name, followed by a dot, followed by the column name. Otherwise Oracle wont be able to know from what table to retrieve the column. In the from clause we specify the name of all column we are using in the statement. The relation between the tables is then specified in the where clause, usually by an equality. This are called an equi-join. You may also use another logical operator such as ">="or "<". This would though seldom be meaningful. Below follows an example of joining two different columns together. Example 2.22 SQL> select o.name, i.description 2 3 from demo_company_tab o, demo_inventory_tab i where o.company_id=i.company_id;

NAME ACME Rocket Scientists ACME Rocket Scientists ACME Rocket Scientists ACME Rocket Scientists ACME Rocket Scientists ACME Jurassic Submarines ACME Jurassic Submarines ACME Business Partners ACME Business Partners 9 rows selected.

DESCRIPTION Base warehouse 23 Factory warehouse 10 Local store warehouse 72 Airport warehouse 42 Reserve warehouse 87 Base warehouse 54 Factory warehouse 654 Base warehouse 14 Factory warehouse 82

-------------------------------------------------------------

A joined table is sometimes called a projection or a result table.

Queries

Queries with functions 34

It wont matter what keycolumn (primary or foreign) you specify to the left or to the right of the equal sign.

2.9.1.

Joining using alias

To simplify the coding in SQL, especially when joining tables, its very useful to assign alias to the table name. If you give a short alias, just a few character, the benefit will be that you dont have to type the whole table name every time you are referring to it, which can be many times in a join statement. For an example, see the example above.

2.9.2.

Joining a table with itself

Joining a table with itself is though rather rare, but there is occasionswhen we want to do it. For example in a member table, we have a column with names on those persons who is member, of couse. But we also have a column with names on those persons who is a leader. By looking at the two columns, we will see that persons who is members, also can be leaders. We have all the information we want in the same table. But we want to the output to present only those members who also are leaders. Therefore we must join the two columns, which exists in the same table.

2.9.3.

Outer join
A so called outer join is used when we have to or more tables and we want to join them but when we test for an equality, we discover that not every time we test for an equality between the primary key and the foreign key we got a hit. So what you maybe think. The hatch is that in one of the tables there are some column that doesnt exist in the other table, but which we want to display. To solve this problem we put a the sign "+" behind the equality statement. This makes SQL to present, also that rows where a certain value doesnt exist in the other table, so it will be filled out with a NULL.

2.10. Group by (clause)

The group by statement has some parts in common with the Group value functions in an earlier section. Group value functions return results based on the groups criteria, rather than on single rows. The differences are that mostly of group value functions, which is a part of number functions, only can operate on numbers. Both group value functions and singe value functions, is numerical functions which only can operate on figures. For example, you can not calculate a standard deviation of a column of names. The group by statement also often returns a list of values. You can group values together in different categories and get a single value for each category.

Example 2.23 SQL> select order_id, count(*) 2 from demo_order_item_tab

Queries

Queries with functions 35

3 ORDER_ID 10000 10001 10002 10003 10004 10005 10006 10007 10008 10009 10010 10011 10012 10013 10014

group by order_id; COUNT(*) 7 3 8 2 2 2 1 2 1 5 3 3 3 2 1

--------- ---------

15 rows selected.

2.10.1.

Group by with criteria

Sometime you wish to have conditions with a group function so the retrieved data should be less. It that case you make a group of the retrieve data and then apply the criteria. Example 2.24 SQL> select order_id, count(*) "Number of order_items" 2 3 4 from demo_order_item_tab where price>=100 group by order_id;

ORDER_ID Number of order_items --------- --------------------10000 4

Queries

Queries with functions 36

10002 10003 10004 10006 10007 10009 10011 10014 9 rows selected.

4 1 2 1 1 2 3 1

Note that if you use a group value function, as SUM and COUNT on one column each and you want to present the counted rows in a third table, you have to refer this third column in a group by clause to get Oracle to execute correctly. The reason for this, is that a group function as SUM for example, tells something about a group of rows and not a single row.

2.10.2.

Having
To be able to give a criteria beyond the Group by clause which has its logic related to the group by you must use the reserved word Having. You cannot use the Where Clause while Where selects a single row in a table. Group by together with having, is somewhat parallel to Order by with where. You may also, after using a group by, followed by Having, add an Order by Clause to order by another column. Then use ascending or descending to order it in the way you want. Note that group by is not used to change ordering. Order by only collects the rows that look similar to each other, i.e. have something in common. Instead of showing four similar rows, you only show one row.

2.11. Subquery
A subquery is used to a form of the SELECT command that appears inside another SQL statement. A subquery is sometimes called a nested query. The statement containing a subquery is called the parent statement. The rows returned by the subquery are used by parent statement.

2.11.1.

Testing against a single value or a list of values

A subquery can return either a single value or a list of values. Below we give two examples of selecting with a subquery. In example 2.??, the subquery returns only one single value. we use a single value function and in example 2.?? we are using a group value function.

Queries

Queries with functions 37

Example 2.25 SQL> 1 2 3 4 ORDER_ID 10002 10008 10012 10012 10012 10014 select order_id, row_no, description from demo_order_item_tab where quantity = (select max(quantity) from demo_order_item_tab) ROW_NO DESCRIPTION 11 Battery Water 31 Coca Cola 1L 43 Petrol Galvoline 93 octan 44 Petrol Galvoline 95 octan 45 Petrol Galvoline 98 octan 48 Nitrox gas verfy nice

--------- --------- ------------------------------

Of course there might be several rows which matches the condition, i.e the singel value calculated in the subquery. Example 2.26 SQL> 1 2 3 4 5 select order_id, order_date, delivery_date from demo_order_tab where order_id in (select order_id from demo_order_tab where delivery_type = 3)

ORDER_ID ORDER_DAT DELIVERY_ --------- --------- --------10002 04-NOV-96 26-NOV-96 10006 04-NOV-96 14-DEC-96

2.12. Correlate query

A correlate query is a subquery that is evaluated once for each row processed by the parent (main) statement. The parent statement can be a SELECT, UPDATE, or DELETE statement.

Queries

Queries with functions 38

You can use a correlated subquery to answer a multi part question whose answer depends on the value in each row processed by the parent statement.

Queries

Queries with functions 39

3. Data Manipulation Language (DML)

3.1. Insert, Update, Delete
You will probably use these SQL commands very often. Many SQL program you write will have a mixture of these statements coupled with queries starting with select.

3.1.1.

Insert
The insert command is used when you want to insert one row of data into a table. The command has two parts: the word insert into followed by the table name and column name you want to insert data into. This is mandatory. the word values followed the columns value . This is optional. Example 3.1 SQL> 1 2 3 insert into demo_inventory_tab (company_id, inventory_id, description, rowversion) values('02', '60', 'Main office inventory','15-MAY-98');

1 row created. The complete syntax: INSERT INTO [schema.]{table | view}[@dblink] [ (column [, column] ...) ] {VALUES (expr [, expr] ...) | subquery}

Note! You do not have to specify the column names, when you are going to insert values in all columns of a table. See the example below.

Queries

Queries with functions 40

Example 3.2 SQL> 1 2 3 insert into demo_product_tab values('02', 515, 'Videotape recorder M741', 'PIECE', 'A high quality product', 2500, '15-MAY-98', 'DemoSalesProduct');

1 row created.

3.1.2.

Update
Sometimes it is necessary to update stored data within a table. You do this with the update command. The command has three parts: the word update followed by the table you want to change. This is mandatory. the word set followed by one or more columns you want to change. This is mandatory. the word where followed by selection criteria. This is optional. Example 3.3 SQL> update demo_product_tab 2 3 set price = price * 0.70 where price> 1500;

9 rows updated. The complete syntax: UPDATE [schema.]{table | view}[@dblink] [alias] SET { (column [, column] ...) = (subquery) | column = { expr | (subquery) } } [, { (column [, column] ...) = (subquery) | column = { expr | (subquery) } } ] ... [WHERE condition]

3.1.3.

Delete
The delete command is used when you want to remove one or more rows of data form a table. The command has two parts: the word delete from followed by the table name you want to remove data form. This is mandatory. the word where followed by the criteria for the delete. This is optional.

Queries

Queries with functions 41

Example 3.4 SQL> delete demo_customer_tab 2 where valid = 'FALSE';

11 rows deleted. The complete syntax: DELETE [FROM] [schema.]{table | view}[@dblink] [alias] [WHERE condition]

3.1.4.

Updating by value from other table

If you want to update your table by a value which will retrieve from other table or statements the syntax for the statement is: Syntax: update tablename set { column name |(column name1 |column name2, ) }=(select-sats)where criteria;

NOTE: The subquery should not retrieve more than one row.

3.1.5.

Updating by correlated subquery

Sometimes the subquery depends on a value from the updated column. In that case you should construct the subquery as correlated subquery.

3.2.

Transaction
Data manipulation is accomplished when you commit the transaction. Before that all data manipulation is temporary and it can be undo the by using the rollback command. In SQL*PLUS, there is also a posibility to let SQL do the commit and/or rollback for you. This is done by the autocommit and autorollback command. Commit Make changes permanent for the current transaction. Rollback Undo all changes since the last commit. Autocommit (make it active), autocommit OFF set the default

To roll back or commit your work, you simply have to type the following at the prompt. Then you also will get a message, telling you that the execution of your statement went succesfully. SQL> commit;

Queries

Queries with functions 42

Commit complete. Respectively: SQL> rollback; Rollback complete. To set autocommit you will have to type the following. SQL> set autocommit on Then you will be able to check, whether autocommit is on or off. SQL> show autocommit autocommit OFF

Queries

Queries with functions 43

4. Substitution variables in SQL*PLUS

Many times when we are running a query, we do not know beforehand what value the user wants to use for the query. When the query is started, you want to type in a value to be used for the query. You do this by placing an ampersand (&) in front of the section of code you want replaced. Many times you want to avoid being prompted multiple times for a variable you use more than once. An easy way to do this is using double ampersands. This tells Oracle to ask for the variable once and issue a define command on it. If you want to change the default prompt you use the prompt and accept command to give us a more descriptive prompt. Example: Clear screen Prompt ********************* Prompt the value should be between 1 to 1000 Accept tvalue prompt "enter a numeric value:" select value from user_values where value = &tvalue; When this is run, the following output is produced for the user: the value should be between 1 to 1000 enter a numeric value: 19 old 3: where value = &tvalue new 3: where value = 19

Queries

Queries with functions 44

5. Oracle Data Dictionary

One of Oracles RDBMS most important part is data catalog or data dictionary (DD). DD contain information about object and occurrence within the database for example a user, table and column. DD is available for all appropriate users in the form of a view. The database administrator (DBA) has more available views than a normal a user. You can see a list over DD by all-selecting from DICT view. SELECT * FROM DICT;

5.1.

How can I use the DD-information?

If you want to see other user for instance: SELECT * FROM ALL_USER; The same format, <select*DD statement> works for the statements in next section. If you want a brief description of the content of the different statement, just write <describe statement>.

5.2.

List
A list over common views which are available resides in DD. ALL_CATALOG All tables, views, synonyms, sequences accessible to the user. ALL_COL_COMMENTS Comments on columns of accessible tables and views. ALL_COL_GRANT_MADE Grants on columns of accessible tables and views. ALL_INDEXES Descriptions of indexes on tables accessible to the user. ALL_OBJECT Objects accessible to the user. ALL_SYNONYMS All synonyms accessible to the user. ALL_TABLES Descriptions of tables accessible to the user.

Queries

Queries with functions 45

ALL_TAB_COMMENTS Comments on tables and views accessible to the user. ALL_TAB_GARANTS_MADE Users grants and grants on users objects. ALL_TAB_GARANTS_RECD Grants on objects for which the user or PUBLIC is the grantee. ALL_USER Information about all users of the database. ALL_VIEW Text of view accessible to the user. COLUMN_PRIVILEGES Grants on columns for which the user is the grantor, grantee, or owner or PUBLIC is the grantee. DICTIONARY Description of data dictionary tables and views. USER_CATALOG Tables, views, synonyms, sequences accessible to the user. USER_INDEXES Description of the users own indexes. USER_OBJECTS Objects owned by the user. USER_TABLES Description of the users own tables. USER_USERS Information about the current user. USER_VIEW Text of views owned by the user.

Queries

Queries with functions 46

6. Data Definition Language (DDL)

6.1. Create, Alter, Drop
The DDL Language is used to build the database itself. With the three DDL Commands that follows, you will be able to create new tables with certain columns, permitted length of the data the use should be allowed to store in the different columns and so on. If you want to change the the initial definition of a table, you can easily change it and if you want to get rid of a certain table, you simply use one of the DDL Commands to drop it from the database. There are also other objects in a database that you will be able to create, alter and drop with the DDL Language. A description with a few examples of what you will be able to do, follows in the next three sections.

6.2.

Create a table, view, index and synonym

Create a table
A table is the database object that holds your data. The way you can create a table in Oracle is through the Create table command. The syntax is: CREATE TABLE [schema.]table ( { column datatype [DEFAULT expr] [column_constraint] ... | table_constraint} [, { column datatype [DEFAULT expr][column_constraint] ... | table_constraint} ]...) Example 6.1 SQL> 1 2 3 4 5 6 create table demo_temporary_tab( temporary_id temporary_name temporary_address temporary_phone temporary_city number(5) not null, varchar2(30) not null, varchar2(30), number(15) not null, varchar2(25),

6.2.1.

Queries

Queries with functions 47

7 8

temporary_country temporary_comments

varchar2(25) not null, varchar2(40));

Table created.

6.2.2.

Create a view
A view is a database object that allows you to create a customized slice of a table or a collection of tables. Unlike a table, a view contains no data, just a SQL query. The data that is retrieved from this query is presented like a table. The reasons to use a view are the following: 1. View can provide an additional level of security 2. View allow you to hide data complexity 3. Views help you maintain sanity. 4. Views are always up to date with the content of the database. 5. Views can be reached whenever you want, with the same query behind it, which gives you the same selection of information at a later moment The syntax is: CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW [schema.]view [(alias [,alias]...)] Example 6.2 SQL> 1 2 3 create view demo_temporary as select temporary_name, temporary_phone, temporary_comments from demo_temporary_tab;

View created. 6.2.2.1. Create a view out of joined tables

You may also create a view out of two or more joined columns. You will be able to ask questions against the view as you may see as an own table. You can also make a description of it, with the statement describe. Though you can not modify (alter) this kind of view (see later section). You are not allowed to create an index of this kind of view.

6.2.3.

Create index
Just like an index in a book, which helps you find information faster, an index placed on a table helps you retrieve your data faster. A well-placed index on a table will help the database retrieve your data faster. You can think of index as mini tables, it that case it should be easier to understand how they work.

Queries

Queries with functions 48

Syntax: CREATE INDEX [schema.]index ON { [schema.]table (column [ASC|DESC][, column [ASC|DESC]] ...) Example 6.3 SQL> 1 2 create index demo_temp_tab_temporary_name on demo_temporary_tab (temporary_name);

Index created.

6.2.4.

Create synonym
Just as many actors change their name to make themselves easier to remember, and more recognizable, you can do the same for an Oracle table. A synonym is a database object that allows you to create alternate names for Oracle tables and views. You may decide to set up synonyms for any Oracle table for a variety of reasons: 1. You want to hide the true owner or name of a table 2. You want or need to hide the true location of a table. 3. You want to provide users with a table name less complicated than the real table name. Syntax: CREATE [PUBLIC] SYNONYM [schema.]synonym FOR [schema.]object[@dblink] Example 6.4 SQL> 1 2 create synonym everybody_demo_temporary for viktor.demo_temporary;

6.3.

Alter a table, view, index and synonym

Alter table
To alter a table, you can either change a columns definition or adding a completely new table.

6.3.1.

Queries

Queries with functions 49

ALTER TABLE [schema.]table ( { column datatype [DEFAULT expr] [column_constraint] ... | table_constraint} [, { column datatype [DEFAULT expr] [column_constraint] ... | table_constraint} ]...) Example 6.5 SQL> alter table demo_temporary_tab add( 2 3 ); temporary_account number(10)

Table altered.

6.3.2.

Alter view
To alter a view, the syntax is: ALTER [OR REPLACE] [FORCE | NOFORCE] VIEW [schema.]view [(alias [,alias]...)] Example 6.6 SQL> alter view demo_temporary 2 compile;

View altered. 6.3.2.1. Changing a view, created of joined columns

To change this kind of view, you have to delete it first, then do the changes and create once again.

6.3.3.

Alter index
Just like an index in a book, which helps you find information faster, an index placed on a table helps you retrieve your data faster. A well-placed index on a table will help the database retrieve your data faster. You can think of index as minitables, it that case it should be easier to understand how they work. Syntax: ALTER INDEX [schema.]index ...) ON { [schema.]table (column [ASC|DESC][, column [ASC|DESC]]

Queries

Queries with functions 50

Example 6.7 SQL> 2 alter index demo_temp_tab_temporary_name on demo_temporary_tab (temporary_name);

Index altered.

6.4.

Drop table, view, index and synonym

You just use the same command to remove any database object, i.e. table , view, index and etc. The command is drop as the name implies, it remove database object from the database. Syntax: DROP TABLE (tablename) DROP VIEW (viewname) DROP INDEX (indexname) DROP synonym (synonymname) Example 6.8 SQL> drop table demo_temporary_tab; SQL> drop view demo_temporary; SQL> drop index demo_temp_tab_temporary_name; SQL> drop synonym everybody_demo_temporary;

Queries

Queries with functions 51

7. Guide to writing efficient SQL statements

At joins, each table should be indexed as well as possible considering what we know about it. Tip: Never (almost) trust reports telling you how indices look. Look in the database with a simple SQL script. SELECT table_name, index_name, column_name FROM all_ind_columns WHERE table_name like UPPER('&tabell') ORDER BY table_name, index_name, column_position; For an index to be utilized by ORACLE, the first column of the index must be given a value in the WHERE clause. The more of the columnsthat are indexed, the more efficient ORACLE will work. If all indices in a unique index are indexed, an INDEX UNIQUE SCAN ismade. If only the first parts of a unique index are indexed or if a non-unique index is utilized, an INDEX RANGE SCAN is made. A RANGE SCAN means that ORACLE will get more that one hit in the index tree (more than one record). If you fail to state the value of the first column in an index, that index won't be used at all. ORACLE will instead make a FULL TABLE SCAN in the table without index. If you are searching for a value that is not part of the index, ORACLE will to a TABLE ACCES BY ROWID after that a match has been found in the index tree. Rule 1 Find out what indices are available for the tables and in what order the columns are stored. Always index the first column in the index of the tables you wish to join. If you have a table where you don't know of any column usable for indexing, make sure that this table drives the query (see below), or create another index that suits your in parameters better. 1. A typical SELECT statement Tables Department -------------DEP_NO NAME LEADER Indices DEP_NO Employee ------------EMP_NO NAME PHONE DEP_NO EMP_NO (unique) DEP_NO (non-unique)

Queries

Queries with functions 52

SELECT dep.dep_no, dep.name, emp.emp_no, emp.name FROM department dep, employee emp WHERE emp.dep_no = dep.dep_no; An explain plan for this statement looks as follows: TABLE ACCESS FULL EMPLOYEE TABLE ACCESS BY ROWID DEPARTMENT INDEX UNIQUE SCAN DEPARTMENT 1. ORACLE will drive the query from the EMPLOYEE table since it is placed last in the FROM clause. 2. ORACLE uses DEP_NO in EMPLOYEE to find the correct DEPARTMENT. This is done with a unique index. 3. To find the name of the department, ORACLE must make a table access in DEPARTMENT. This is done with the ROWID that was acquired from the index in stage 2. If we want to find employees on a certain department, we need to add another condition for our query: SELECT dep.dep_no, dep.name, emp.emp_no, emp.name FROM department dep, employee emp WHERE emp.dep_no = dep.dep_no AND dep.dep_no = 1

The explain plan is unchanged, which means that ORACLE will read all the employees unnecessarily. If we change the places of DEPARTMENT and EMPLOYEE in the FROM clause, ORACLE will process the statement like this instead: SELECT dep.dep_no, dep.name, emp.emp_no, emp.name FROM employee emp, department dep /* Tables have changed places */ WHERE emp.dep_no = dep.dep_no AND dep.dep_no = 1

Queries

Queries with functions 53

8. Table description
8.1.
COMPANY_ID CUSTOMER_ID NAME VALID CREDIT_LIMIT DISCOUNT ADRESS CITY COUNTRY PHONE COMMENTS INVOICE INVOICE_VALUE STATISTIC_GENERATED EMAIL URL TYPE ROWVERSION

Demo_customer_tab
NOT NULL VARCHAR2 (2) NOT NULL NUMBER (6) NOT NULL VARCHAR2 (50) NOT NULL VARCHAR2 (5) NOT NULL NUMBER (17) NOT NULL NUMBER (5) VARCHAR2 (40) VARCHAR2 (40) VARCHAR2 (20) VARCHAR2 (20) VARCHAR2 (2000) NUMBER NUMBER DATE VARCHAR2 (100) VARCHAR2 (100) VARCHAR2 (1) NOT NULL DATE

8.2.
COMPANY_ID NAME COMMENTS

Demo_company_tab
NOT NULL VARCHAR2 (2) NOT NULL VARCHAR2 (50) VARCHAR2 (2000) NUMBER (6) NUMBER (6) NUMBER (3) NUMBER (6) VARCHAR2 (128) NOT NULL DATE

CUSTOMER_SEQ ORDER_SEQ ORDER_ITEM_SEQ INVOICE_SEQ LOGO_FILE_NAME ROWVERSION

Queries

Queries with functions 54

8.3.
COMPANY_ID ORDER_ID ORDER_DATE

Demo_order__tab
NOT NULL VARCHAR2 (2) NOT NULL NUMBER (6) DATE NOT NULL DATE NUMBER VARCHAR2 (2000) NOT NULL VARCHAR2 (20) NUMBER (6) NOT NULL DATE VARCHAR2 (30)

DELIVERY_DATE DISCOUNT COMMENTS DELIVERY_TYPE CUSTOMER_ID ROWVERSION ROWSTATE

8.4.
COMPANY_ID ORDER_ID ROW_NO DESCRIPTION PRICE QUANTITY AMOUNT PRODUCT_ID ROWVERSION

Demo_order_item_tab
NOT NULL VARCHAR2 (2) NOT NULL NUMBER (6) NOT NULL NUMBER (3) NOT NULL VARCHAR2 (30) NOT NULL NUMBER (17) NOT NULL NUMBER (9) NOT NULL NUMBER (17) NUMBER (6) NOT NULL DATE

8.5.
COMPANY_ID PRODUCT_ID DESCRIPTION UNIT COMMENTS PRICE ROWVERSION ROWTYPE

Demo_product_tab
NOT NULL VARCHAR2 (2) NOT NULL NUMBER (6) NOT NULL VARCHAR2 (30) NOT NULL VARCHAR2 (5) VARCHAR2 (2000) NOT NULL NUMBER (17) NOT NULL DATE NOT NULL VARCHAR2 (30)

Queries

Queries with functions 55

8.6.
COMPANY_ID

Demo_inventory_tab
NOT NULL VARCHAR2 (2) NOT NULL VARCHAR2 (3) NOT NULL VARCHAR2 (30) NOT NULL DATE

INVENTORY_ID DESCRIPTION ROWVERSION

8.7.
PROJECT_NO NAME STATUS ROWVERSION

Demo_project_tab
NOT NULL NUMBER NOT NULL VARCHAR2 (35) VARCHAR2 (25) NOT NULL DATE

Queries

Queries with functions 56