CS 430/630, Database Systems Fall, ‘09

Practice Midterm Exam OPEN BOOK — OPEN NOTES E. O'Neil

NAME _________________________________________________

The questions below relate to our standard CAP database, with table content given in Figure 2.2, pg. 28 of the text. CS430 students: do 1-5, for 100 points. CS630 students do 1-6, and the resulting 115 points will be rescaled to 100. Use the backs of pages for more room if you need to, but point out such uses on the main sheets!

I. (20 points, 10 points per question) Put the following queries in Relational Algebra form.

(1) Get aids of agents handling orders by all customers who have discounts less than 10%.

(2) Get pids of products ordered by any customer who has never ordered any products named ‘pencil’.

2. (30 points, 10 points per question.) Put the following queries in SQL form. Be careful to use the "distinct" keyword appropriately. Use Basic SQL for these.

(a) Get names and total dollars orders of all agents who have a percent commission within 2% (inclusive, i.e. including any exactly 2% less than the max) of the maximum commission over all agents.

(b) Get aids of agents with aname beginning with the letter "S" and who place orders for all products in Newark.

(c) Get names of agents who place orders for some customer who orders product p02.

3. (20 points, 10 points each.) For the following SQL queries, state in words what will be retrieved (don't just repeat what the predicates mean, but INTERPRET what is being retrieved) and, using the values from Figure 2.2, print the table you would expect SQL to print as an answer. (For partial credit, show the sets of elements returned by Subqueries and give reasoning for final answer. ) As an example, we would want the following answer for the query:

select p.pname where pid in (select pid from orders where cid = 'c006);

A: The select retrieves the name of products that are ordered by customer c006. (The Subquery will return the set of products {p01, p07} (from the last three rows of the orders table where c006 is the cid)) The table printed out as the answer will be the names of these products:

p.pname

comb

case

(a) select pid, pname, sum(qty) from orders o, products p

where cid = ‘c001’ and price < 2.00 and o.pid = p.pid

group by pid, pname;

(b) select distinct cid from orders x where not exists (select * from

orders o where o.month = ‘jan’ and o.cid = x.cid);

4. (15 points) Advanced SQL Query

Suppose we need to break down products by price intervals, that is, produce a report that says how many products are priced 0 - .99, how many are priced 1.00 – 1.99, and so on. Give a single query that would work in Oracle that reports these counts, along with the start-price in each interval.

5. (15 points) Assume that two new customers are added to the customers table of Figure 2.2, as follows:

(c007, Widgets, null, null)

(c008, DotCom, null, 8.00)

(a) Show an insert statement that works to insert c007's row without using "null" in the statement.

(b) Give the output for

select cid from customers where city <> ‘Dallas’;

(c) Explain your reasoning for the treatment of the rows with null city.

6. (CS630 only, 15 points) Suppose A is an attribute of both R and S, and no other attributes are common to both relations.  In this case prove that:

(R JOIN S)[A] = R[A]  INTERSECT S[A]