CS 430/630, Database Systems Fall
‘09
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%.
O[cid,
aid] DIVIDEBY (C where discnt < 10) [cid]
(2) Get pids of products ordered by
any customer who has never ordered any products named ‘pencil’.
Customers
who have never ordered any products named ‘pencil’ =
C1
:= C[cid] – (O JOIN P where pname = ‘pencil’)[cid]
Answer:
(O JOIN C1)[pid] = (O JOIN (C[cid] – (O JOIN P where pname = ‘pencil’)[cid]))[pid]
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.
Select
aname, sum(dollars) from orders o, agents a
where
o.aid = a.aid and a.percent + 2 >= (select
max(percent) from agents)
group
by aid, aname
Note: The subquery needs to be by itself
on the right hand side of the comparison predicate to qualify as Basic SQL.
Note:
It’s OK to report the aid also here, to discriminate between duplicate anames.
(b) Get aids of agents with aname beginning with
the letter "S" and who place orders for all products in Newark.
Select
aid from agents a
where
aname like ‘S%’ and not exists (select * products p where p.city = ‘Newark’
and
not exists (select * from orders x where x.aid = a.aid and x.pid = p.pid));
(c) Get names of agents who place orders
for some customer who orders product p02.
Here
are 3 ways to do it. There are others too.
Select
distinct aname from agents a
where
aid in (select aid from orders where cid
in (select cid from orders where product = ‘p02’);
Select
distinct aname from agents a, orders o
where
a.aid = o.aid and o.cid in (select cid from orders where product = ‘p02’);
Select
distinct aname from agents a, orders o, orders x
where
a.aid = o.aid and o.cid = x.cid and x.pid = ‘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;
Find products priced under $2.00 ordered
by customer ‘coo1’, and for each, report the total number of units ordered by
c001 along with the product id and name.
|
(b) select
distinct cid from orders x where not exists (select * from
orders
o where o.month = ‘jan’ and o.cid = x.cid);
Find customers who have orders for which
there are no orders for that same customer in jan, i.e., have orders but only
orders not in January.
|
cid |
|
c004 |
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.
Select bucket, count(*) from product
p, (select floor(price) as bucket from products) b
where floor(price) = b.bucket
group by bucket;
select bucket, count(*) from (select
pid, floor(price) as bucket from products) x
group
by bucket;
Basic SQL version (partial
credit)
Create table buckets(int bucket, primary
key(bucket));
Insert into buckets select floor(price)
from products;
Select bucket, count(*) from product
p, buckets b
where floor(price) = b.bucket
group by bucket;
Drop table buckets;
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.
Inset
into customers (cid, cname) values (‘c007’, ‘Widgets’);
(b) Give the output for
select
cid from customers where city <> ‘Dallas’;
|
cid |
|
c001 |
|
c004 |
|
c006 |
(c) Explain your reasoning for the treatment
of the rows with null city.
The
predicate city <> ‘Dallas’ evaluates to U for null city, and U rows are not
selected, only T rows.
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 [A]
Proof: R JOIN S will result in rows that have matching A values in R and S by definition of JOIN. When projected on A, this results in rows having only A values, the ones which (still) match in R and S. The right hand side expresses the same idea, values of A that match in R and S.