CS430/630                                                                O'Neil
		      Homework Assignment 2

Due midnight, Sun., Oct. 18

First use the loadcap script to load up a CAP database in your own Oracle
account.  Read Appendix A for loading tables and using sqlplus.

Solve all Exercises at the end of Chapter 3 from 3.1 to 3.7 that are undotted
(not solved at the back of the book).

****************************************************************************
NOTE: In this and all future assignments, all exercise parts that can be done
in a database environment should be executed in Oracle. 
****************************************************************************
Make a file hw2.sql, that contains all your queries, with a
header comment on each to identify it, like this (i.e. abbreviations OK)
plus a "repheader" to identify it, like this:

--[3.2](b)
--Retrieve aid vals of agents who receive the max % commission

For the final run, run it like this:
%sqlplus user/pw 
SQL> spool hw2.out
SQL> set echo on
SQL> @hw2.sql

Then hw2.out will have the queries, the query output, and the comments.
Please keep the queries in the right order, without extra ones, so we can
check your answers easily.

Example hw2.sql:
------------------------------------------------------------
--CS 430 hw 2 by Foo Bar          (or CS630)
--Example format--you start with [3.1](b)

--[3.1](a)
--Find all (cid, aid, pid) triples for cust., agent, prod. combos all in the
--same city.

select c.cid, a.aid, p.pid  
   from customers c, agents a, products p
      where c.city = a.city and a.city = p.city;

--------------------------------------------------------------
Example hw2.out:
--------------------------------------------------------------
SQL> set echo on
SQL> @hw2.sql
SQL> --CS230 hw 2 by Foo Bar
SQL> --Example format--you start with [3.1](b)
SQL> 
SQL> --[3.1](a)
SQL> --Find all (cid, aid, pid) triples for cust., agent, prod. combos all in the
SQL> --same city.
SQL> 
SQL> select c.cid, a.aid, p.pid
  2  	from customers c, agents a, products p
  3  	   where c.city = a.city and a.city = p.city;

[3.1](a)                                                                        
CID  AID PID                                                                    
---- --- ---                                                                    
c002 a06 p01                                                                    
c003 a06 p01                                                                    
c001 a05 p03                                                                    
c004 a05 p03                                                                    
c001 a05 p04                                                                    
c004 a05 p04                                                                    
c002 a06 p05                                                                    
c003 a06 p05                                                                    
c002 a06 p06                                                                    
c003 a06 p06                                                                    

10 rows selected.

SQL> 
SQL> exit
-------------------------------------------------------------

For the conceptual problems, you are asked to enter solutions online
and put them in file hw2answers.txt.  For hw2, problems
3.3(b), 3.3(d), 3.4(b) and 3.5 should show up in hw2answers.txt.
Some problems may show up in both hw2.sql and hw2answers.txt,
if both discussion and execution are involved.  In that case,
also incorporate the results of the execution in the discussion.

At midnight on the due date, the following files should be
in your cs430/hw2 or cs630/hw2 dir:

README--optional, special notes to grader
hw2.sql
hw2.out
hw2answers.txt