Transcript Oracle`s take on joins

Oracle’s take on joins
Where it differs from ANSI
standard
ANSI Standard SQL join
Select
supplier_name,
Stock_description
from supplier join stock on
supplier.supplier_id = stock.supplier_id;
• This also works in Oracle 10g, but
Oracle’s native method is…
Oracle SQL join
Select
supplier_name,
Stock_description
from supplier, stock where
supplier.supplier_id = stock.supplier_id;
• If you followed the relational algebra discussion
in Semester 1, you will know that this is a
selection and a projection on a Cartesian
product
This makes it easy…
• To do an inequality join:
– Select
Outer join
• This shows us all the data from one table
and anything that joins it from another
table:
– E.g. all the customers and any orders they
have made.
– Or All the suppliers and any stock they supply.
Standard outer join
• Select customer_name, corderno from
customer left join corder on
customer.customer_id =
corder.customer_id;
• These will work in Oracle, but Oracle
prefers:
Oracle outer join
• Select customer_name, corderno from
customer, corder where
customer.customer_id(+) =
corder.customer_id;
– The (+) is on the side that may return nulls.
Exercise
• Write the query to return all supplier
names and the description of the stock
they supply, from the builder schema.
– Using ‘left join’
– Using (+)
Non-equality joins
• These concern joins that are NOT
necessarily based on referential integrity
• E.g.
– Select all stock for a given supplier (equi-join)
where the stock value (i.e. unit_price *
stock_level) is less than the amount owed to
the supplier (non-equi-join)
– Try this in Oracle.
Self-join
• See the employee example in Tutorial
week 11 semester 1, where the employees
table has ‘reports to’ field, indicating
another row in the same table.
Substitution variables
• In order to force data entry, a ‘substitution
variable’ can be used.
• This is done by placing an ampersand & in front
of the variable name. When SQL*Plus interprets
the line, it immediately requests data entry from
the user
SQL> select * from builder.customer where customer_id
= &cid;
– SQL* plus will respond by:
Enter value for cid: 3
Further uses for substitution
variables
• Substitution variables can be used to
replace actual parameters in a procedure
call, or even fields:
• E.g.
SQL> Select &fld1,&fld2 from
builder.customer;
Enter value for fld1: ‘customer_id’
Enter value for fld2: ‘customer_name’
Things to remember
• When entering values for substitution
variables, the same rules apply as when
using inserts:
– Numerics can be entered without quotes
– Strings require single quotes
– Dates should be surrounded by single quotes.
Use of ‘ACCEPT’
• If you want to change the prompt, you may use
the ‘Accept’ command. This command:
– Sets up a variable that retains its value for the entire
session
– Doesn’t use an ampersand (&)
– Allows the programmer to design the prompt. E.g.
ACCEPT st_code PROMPT: ‘Please enter a 4-character stock
code, surrounded by single quotes’;
Script files
A script file is an anonymous block.
It can be opened and executed, without being
saved to the database.
Often, you need to run a sequence of SQL
statements together, to select, update, insert or
delete rows.
Sometimes, when a new row is being added, it is
necessary to generate a key (e.g. each new
student gets a new student number)
Declaring sequences
• A sequence is an object that is defined in the database and can be
incremented whenever a new value (such as a key) is needed.
• The sequence is created and given a name, an increment, a start
value and a possible maximum value.
• E.g.
Create SEQUENCE empIdSeq
Incremented by 1
Start with 100
Maxvalue 400;
• This creates an object empIdSeq of type Sequence in the database.
• To get rid of it, you must DROP SEQUENCE empIdSeq
• To use it:
Insert into staff values
(empIdSeq.nextval,’Harry’,’Foreman’);
• To see what the current value is;
Select empIdSeq.currval from DUAL;
The full syntax for a sequence
Create sequence sequencename
Increment by n
Maxvalue x / NoMaxValue
Minvalue m / nominvalue
Cycle / nocycle when max is reached, start at min again / not
Cache c / nocache Oracle pre-generates and saves
numbers in main memory. / not
Order / noorder The numbers are generated in chronological
order / not
Creating Indexes
• Sometimes it is necessary to look up data in an
order that is not related to the key.
• To do this, an index can be used:
• E.g.
CREATE INDEX ORDDATE ON SORDER
(DELIVEREDDATE);
This allows us to look up the orders by the date on
which they were delivered. If I were in charge of
taking in orders, this index may suit me very well.
– Only use indexes when considered absolutely
necessary. Every insert or delete must update any
indexes that are declared for the table.