Class 9.
Note: loadcap, Oracle script to load CAP database, is available, linked to class web page
Try out your Oracle account by Thursday!
Steps:
1. login to dbs2.cs.umb.edu
2. edit your .cshrc as instructed in DBA email
3. log out and in again
4. Use sqlplus to access Oracle
5. Try “create table t1(x int);” to make sure your account allows table creation
6. If time, use loadcap to load up CAP.
Loadcap use:
1. cd cs430 (or cs630)
2. cp -r /data/htdocs/cs430/loadcap . (recursive copy to current directory, .)
3. cd loadcap (cd to your own copy of the loadcap directory)
4. ls (look at files in directory, see script file loadcap)
5. loadcap user/pw (to load CAP: need to be logged in on dbs2 for this)
Another FORALL example
When have to do FORALL: Go through steps on pg 110.
Example 3.5.5. Find cids for customers who order all products ordered by customer c006.
RA: O[cid,pid] DIVIDEBY (O where cid = ‘c006’)[pid]
OK, we are retrieving c.cid. How disqualify it? (1) Give Counter example in English
There is a product ordered by customer c006 that customer c.cid does not order.
(2) Select all counter-examples for a certain c.cid
select pid from orders x where x.cid = 'c006' -- products ordered by 'c006'
and not exists -- c.cid does not order this x.pid
(select * from orders y where x.pid= y.pid and y.cid= c.cid);
(3) OK, now say no counter-example exists: not exists (subquery above)
(4) Let the row we want to retrieve be range variable in outer select with no counter-examples. (Usually choose range variable in smallest table.)
select c.cid from customers c where not exists
(select pid from orders x where x.cid = 'c006' and not exists
(select * from orders y where x.pid = y.pid and y.cid = c.cid));
OK, subtraction and union in Section 3.5 are very easy.
Section 3.6 Advanced SQL Syntax
Most of the SQL syntax presented in this Section is NOT AVAILABLE in all DBMS products; what we define here in Section 3.6 is called Advanced SQL Syntax, and other sections that follow DO NOT ASSUME this syntax.
The syntax assumed in all other sections is called General Form SQL Syntax. When you are asked to use Advanced Sql in homework or exam questions, it will be asked specifically; otherwise, use General Form.
Preview General Form Select Statement syntax in Figure 3.14, pg 135. You won't understand many of the clauses yet, but this gives you an idea.
We start with how to do Union, Difference, Intersect in SQL. Advanced SQL adds new operators to perform these operations. We already covered a simple Union clause in the General Form SQL of Section 3.5.
New Example. List all cities that contain the products costing less than $1.00 or that contain agents who have made no sales.
Can easily do this with UNION of relational algebra, and General Form SQL has a union clause THAT ALL VENDORS HAVE IMPLEMENTED. Answer
select city from products where price < 1.00
union
select city from agents where aid not in (select aid from orders);
CAP result: Dallas, Newark
Suppose additional agent a07 based in Newark, no orders
Then, same result, UNION removes dups
UNION ALL would keep dups: Dallas, Newark, Newark
Here is the Advanced SQL format (Fig. 3.10, pg 115) for UNION, INTERSECT, and EXCEPT (EXCEPT is like relational algebra DIFFERENCE or MINUS)
/- MINUS in Rel. Alg.
subquery {UNION [ALL] | INTERSECT [ALL] | EXCEPT [ALL]}
Consider the Subquery form used here. See Fig 3.14 on pg 135 again. Two forms: Subquery and Select statement. Subquery is most of Select SQL syntax, but Select statement adds a clause (ORDER BY) that cannot appear in (subquery). Otherwise, Subquery and Select statement are identical.
Idea of ALL in these three cases. UNION ALL can cause several copies of a row to exist after being applied to multiple subqueries. Takes number into account.
Example 3.4.14. Find cids of customers who don't place any order through agent a03. We've already seen how to subtract using not exists (or not in).
select cid from customers c where not exists
(select * from orders where cid = c.cid and aid = 'a03');
Customers c includes customers who have placed no orders, which we decided earlier in Example 2.2.27 was probably meant. If we had asked for customers who placed some order but none through a03, would change customers c above to orders x and use select distinct cid.
Now with our new operator, we can write this as:
First try:
select cid from customers
except
select cid from orders where cid = c.cid and aid = 'a03';
But this is no good: c.cid is out of scope here. These two subqueries are not nested, so a table alias cannot cross from one to the other.
RA: C[cid] – (O where aid = ‘a03’)[cid]
translates directly to
select cid from customers
except
select cid from orders where aid = 'a03';
=======
Question in class, too late to handle in class: can we similarly simplify the first version (the one not using except)?
Answer: this doesn’t do the right thing:
select cid from customers c where not exists
(select * from orders where aid = 'a03');
It would either deliver all cids in customers or none at all, depending on whether a03 has any orders.
But this works:
select cid from customers c where cid not in
(select cid from orders where aid = 'a03');
Because “not in” deals in actual values, it can do more than “not exists”, with the same subquery.
=======
Example for "intersect": Get list of cities that contain products AND agents.
select city from products where city in (select city from agents);
Given operator INTERSECT, we write:
select city from products intersect select city from agents;
Recall idea of too many equivalent forms in SQL. These INTERSECT and EXCEPT operators make it worse. ALL is a new capability, but is it ever used? (Yes: think in terms of giving greater weight to things that occur multiple times.)
Major DBMS products have implemented most of the forms of Figure 3.10: Microsoft SQL Server and DB2 UDB have; In ORACLE 10g, UNION [ALL] exists, and there is INTERSECT and MINUS but without ALL. Also, no precedence between UNION and other operators, so use parentheses as needed.
OK, now expanded definition of FROM Clause! SQL-89 Definition was:
from tablename [[AS] corr_name] {, tablename [[AS] corr_name]...}
We name the clause "tablename [[AS] corr_name]" a tableref.
Advanced SQL FROM Clause general form has much more complex tableref:
----------------------------------------------------------------
NOTE: [AS] prior to corr_name illegal in ORACLE, so leave it out.
tableref::= tablename [corr_name] [(colname {, colname ...})]]
| (subquery) corr_name [(colname {, colname ...})] (corr_name REQUIRED here!)
| tableref1 CROSS JOIN tableref2
| tableref1 [INNER | {LEFT | RIGHT | FULL} OUTER] JOIN tableref2
{ON search_condition | USING (columnname {, column-name . . .})}
| (tableref) --should add to Fig. 3.11
FROM clause ::= FROM tableref {, tableref . . .}
----------------------------------------------------------------
Note need ON... or USING... after each JOIN except CROSS JOIN.
Note that tableref is recursive: tableref can be any kind of join of two tablerefs. This tableref form is available only in Advanced SQL
Silly examples: These all get the same results in Oracle or other product with advanced forms:
select * from customers;
select * from (customers);
select * from ((customers));
select * from (select * from customers) t;
select * from ((select * from customers) t);
Oracle also allows, but this does not fit Fig. 3.11 (SQL92 standard):
select * from (select * from customers);
We start by considering the fact that with this definition one can Select FROM a Subquery. This is a very powerful capability, which provides true recursiveness. see, for example, Exercise 3.19 at end of chapter calculating median.
Recall that in Relational Algebra, could put parens () around any query and use that table as if it were a table; up until now, we didn't have that power with SQL!
Example 3.6.3. Retrieve all customer names where the customer places at least two orders for the same product. Here's a new way:
select distinct cname from -- add distinct on pg. 116
(select o.cid as spcid from orders o, orders x
where o.cid = x.cid and o.pid = x.pid and o.ordno <> x.ordno) y,
customers c where y.spcid = c.cid;
Note again: corr_name y for Subquery is crucial here: need a name for where clause.
Foreshadowing in this query form of something called Views. Will see even more interesting uses of this recursion after we cover Set functions. Next, talk about the various Join operators on lines 3 & 4 (and 5). To start:
tableref1 CROSS JOIN tableref2 (CROSS JOIN is relational product)
e.g.:
select cname from customers c cross join orders o where . . .
This has the same effect as
select cname from customers c, orders o where . . .
Cross Join is just a way to provide a way for users to perform a Relational product without needing to use commas between tables: relational product can now be performed as a type of Join.
OK, now we can also use the simple JOIN keyword in the general form of Figure 3.11, representing the JOIN operator of Relational Algebra. Dropping the optional keywords before JOIN we are left with the default join, i.e., the inner join:
tableref1 JOIN tableref2 {ON search_condition | USING (colname {, colname . . .})}
In this case, we need to use either the ON search_condition or USING, to specify what the join columns are. SQL doesn’t trust that columns to join two tables are named identically.
When two different named columns are to be compared in an ON clause, we call this a condition join E.g. if had cities table with columns named cityname, latitude and longitude, could write:
select city, latitude, longitude from customers c join cities x
on (c.city = x.cityname); -- city and cityname must be matched in join