Final exam study guide solutions

WARNING: some parts here have not been actually tested.

Furthermore, the solution that I thought worked for #1 does NOT.
Ignore that; as long as you can do the similar problem on homework 3
(for each dept, list the employee with the most seniority),
you should be fine.


1. List the item ordered most often

The max quantity is
	select max(sum(qty)) from invitem group by itemno;
The item for which that max is achieved MIGHT HAVE BEEN:

select itemno, sum(qty) from invitem where sum(qty) = 
(select max(sum(qty)) from invitem group by itemno)
group by itemno;

BUT THIS DOES NOT WORK, and right now I'm not sure how to fix it.
The problem is that sum(qty) cannot be in the WHERE part.

I may be able to fix this, but the important point is that the
way I had intended for *you* to do it does not work, and so this
problem is much harder than intended.


2. Purchasers who ordered every item over $5

Select 	c.custname from customer c
where	not exists (
	(select itemno from item where itemprice >= 5) 
	minus 
	(select i.itemno from invitem i, invoice v 
	 where c.custno = v.custno and i.invno = v.invno)
	);


3. There are some problems here. First, this is only a fragment,
not the full Java program (that's ok). More seriously, there are a 
few glitches with Date, although java.sql.Date and java.util.Date are
supposed to be comparable. I'm not sure which Date I'm using at times.

Like I said, this date stuff (which is what makes this hard) WON'T 
be on the final.

Note that I'm trying to do all the comparisons in java here, not in sql. 
The study guide problem statement was not clear on that.

Date cutoff = new Date("01-JAN-2002");	// probably WRONG init of java.util.Date
ResultSet rset = stmt.executeQuery("select salary, hiredate from employee e");
int sum = 0;
while (rset.next() ) {
        int sal = rset.getInt(1);
	Date d = rset.getDate(2);	// I had to look this up: java.sql
	if (d.after(cutoff)) {
		sum += sal;
	}
}
System.out.println("total salary is " + sal);


4. Again, I'm trying to do the comparisons in PL/SQL rather than in SQL.
It's easier to do date arithmetic here than in Java.

declare
	cursor cur is select salary, hiredate from employee e;
	rec cur%rowtype;
	int sal = 0;
begin
	for rec in cur loop
		if rec.hiredate > to_date('01-JAN-2002') then
			sal := sal + rec.salary;
		end if;
	end loop;
	dbms_output.put_line('total salary is ' || sal);
end;


5. This is my actual code, in which I had to rename table "employee" to 
"employee3" to load it all into the indo DB instead of the naman DB.
Ignore that. Also ignore the cattributes thingie in htp.tableOpen.


-- table3 builds an html table from a database table

create or replace procedure table3 as
    cursor c1 is select d.deptname, e.lname , e.fname 
    		from employee3 e, dept d
		where d.deptid = e.deptid 
		order by d.deptname;
    rec c1%rowtype;
begin
    htp.htmlOpen;
    htp.headOpen;
    htp.title('table of employees');
    htp.headClose;
    htp.bodyOpen;
    htp.line;
    htp.header(1,'Table of employees');
    htp.line;
    htp.paragraph;

    htp.tableOpen(cattributes => 'border=2 width=60%' );
    htp.tableRowOpen;
    for rec in c1 loop
    	htp.tableRowOpen;
    	htp.tableData(rec.deptname);
    	htp.tableData(rec.lname || ', ' || rec.fname);
    	htp.tableRowClose;
    end loop;
    htp.tableClose;


    htp.bodyClose;
    htp.htmlClose;
end;


6. Check QTY <= ITEM.QTYONHAND; yes: change QTYONHAND; no: raise NOT_ENOUGH_STOCK
(user-defined exception, not spelled out here).


create or replace trigger insert_invoice2
before insert on invitem
declare my_quantity item.qtyonhand%type;
	my_itemname    item.itemname%type;
	out_of_stock exception;
begin
    select item.qtyonhand
    into my_quantity 		-- existing quantity
    from item
    where item.itemno = :new.itemno;
    
    if  :new.qty <= my_quantity then
    	update item
    	set qtyonhand = qtyonhand - new:qty
    	where itemno = :new.itemno;
    else
    	raise not_enough_stock;
    end if;
    
    exception
        when not_enough_stock then
        raise_application_error(-20222, 'cannot add this INVOICE row; insufficent qty');
end;