Comp 353 midterm study guide

The final will be Monday, April 30. Note that this is listed in some places as a "reading day", but that only applies to day classes, and ours is an evening class.

The exam will be open book, though you will not be allowed to use notes.

Topics

• continued SQL
  • Find customers who have ordered ALL parts
  • Find oldest student
• Basics of PL/SQL
• PL/SQL select into a variable
• PL/SQL cursors and for loops
• JDBC basics
• JDBC: passing parameters in java to PL/SQL
• java cursors
• triggers
• Basics of web_PL/SQL    

The exam will not include html forms. Any htp methods you need will be provided, as in #5 below. The exam will also not include any Java GUI programming.

Sample problems

Solutions will be coming by Sunday.

1. For the supplemental database after Chapter 9, list the item that was ordered most often.

2. For the supplemental database, list purchasers who ordered every item costing more than $5. (Hint: not exists ((select all items over $5) minus (select everything the purchaser ordered))

3. Use a java cursor to find the sum of the salaries of all the employees in the Naman-Navan database hired before Jan 1, 2002. The SQL version of this is:

	select	sum(e.salary)
	from	employee e
	where	e.hiredate < '01-JAN-2002';

4. Do the same with a PL/SQL cursor.

5. Create in web PL/SQL an html table, like the one below, containing a list of department names and employees in the "lname, fname" format indicated. Note that the entries are ordered by deptname.

Use the htp interface.

Finance	Smith, John
Finance	Chen, Sunny
Finance	Roberts, Sandi
InfoSys	McCall, Alex
InfoSys	Dev, Derek
Marketing	Houston, Larry
Sales	Shaw, Jinku
Sales	Garner, Stanley

Start with an appropriate cursor based on a select join, and then use a for loop to create the table. Note that the select-statement can either list deptname, lname, fname, in which case you combine the latter two strings in the for loop, or list deptname, e.lname || ', ' || e.fname AS name, in which case you print the deptname and name fields from the cursor.

Handy summary of htp table operations. All htp operations you will need for the final will be provided, in a form similar to this:
htp.tableOpen:     start the table
htp.tableClose:     done with the table
htp.tableRowOpen:     start a new row
htp.tableRowClose:     close the row
htp.tableData(str):     Put str in the next column of the current row

6. Using the Inventory database in the Supplement following chapter 9, write a trigger for the InvItem database so that when a new row is added, you check to see if invitem.QTY <= ITEM.QTYONHAND. If so, you decrement ITEM.QTYONHAND appropriately; if not, you raise an exception.