CS 430/630, Database Systems Nov. 3, 2009
Midterm Exam OPEN BOOK — OPEN NOTES E. O'Neil
NAME _____SOLUTION____________________________________________
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.
(a) Get pids of products ordered by all customers who have discounts between 8 and 10%, inclusive.
O[cid,pid] DIVIDEBY (C where discnt >= 8 and discnt <=10)[cid]
(b) Get pids of products ordered by any customer who does not place an order through agent a03.
(O[cid, pid] JOIN (C[cid] - (O where aid='a03')[cid]))[pid]
2. (30 points, 10 points per question.) Put the following queries in Basic SQL form. Be careful to use the "distinct" keyword appropriately. Use Basic SQL for these.
(a) Get names, numbers of customers served, and total dollars orders of all agents who have a percent commission greater than the average.
select aname, count(distinct cid), sum(dollars) from orders o, agents a
where a.aid = o.aid and percent > (select avg(percent) from agents)
group by o.aid, aname
(b) Get pids and pnames of products housed in Dallas ordered by all customers situated in Dallas.
select pid, pname from products p where city = 'Dallas' and not exists
(select * from customers c where city = 'Dallas' and not exists
(select * from orders x where x.cid = c.cid and x.pid = p.pid))
(c) Get names of agents each of whom places orders for some customer who orders some products, but this customer doesn't order product p02.
select distinct aname from agents a, orders o
where a.aid = o.aid and o.cid not in (select cid from orders where 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 or other intermediate results 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 Subselect will return the set of products {p01, p07} (from the last three rows of the orders table where c006 is the cid), and the table printed out as the answer will be the names of these products:
|
p.pname |
|
comb |
|
case |
(a) select pname, sum(dollars) from orders o, products p
where qty>=1000 and o.pid = p.pid
group by pid, pname
having count(distinct aid) = 1
Find product names and total dollars ordered for products, but consider only orders of at least 1000 units in the total, and only report on products where all such orders were placed by a single agent.
Before having: Final result displayed:
pname sum(dollars) pname sum(dollars)
------------------------------- ----------------------------
comb 1850 (agents a01, a06) pencil 2208
pencil 2208 (agent a03 only) razor 880
razor 880 (agent a03 only)
(b) select distinct cid from orders x, orders y
where x.cid = y.cid and x.pid = 'p01' and y.pid = 'p02';
Find customer ids for customers who order both product p01 and product p02.
cid
------
c001
4. (15 points) Advanced SQL Query
Suppose we want to examine the extent to which we could match up agents and customers because they are in the same city, but want to see mismatched people as well in the output. Use an Advanced SQL JOIN form to output (city, aid, cid) triples with some nulls in aid and/or cid to represent non matched people, plus other triples where the aid and cid are both in the city.
select c.city, aid, cid from agents a full outer join customers c on c.city = a.city
or: using (city)
5. (15 points) Consider the query:
select ordno from orders o, products p
where o.pid = p.pid and 100*qty > p.quantity and pname like 'p%'
Show you can parse this query by listing (separate listed items by semicolons):
the tablerefs(s): orders o; products p
the search_condition(s): o.pid = p.pid and 100*qty > p.quantity and pname like 'p%'
the predicate(s): o.pid = p.pid; 100*qty > p.quantity; pname like 'p%'
the expressions(s): ordno; o.pid; p.pid; 100*qty; p.quantity; pname, 'p%'
(6) (15 points) CS630 only. Consider relations R and S with one attribute in common, attribute A. In this case, show that
((R JOIN S) where A=a) = (R where A = a) JOIN (S where A=a)
Answer: Since A is the only join column here, the join matches A-values between R and S, in particular all rows in R with A=a matched with all rows in S with A=a. The left hand side finds these matches in the join result, while the right hand side finds the same matches by first expressing the set of rows with A=a in each table.
Another way, using Problem 2.12's result: Consider table T with the single column A with single row of value a. Then by 2.12 we showed that (R where A=a) = R JOIN T, so
(R where A = a) JOIN (S where A=a) = (R JOIN T) JOIN (S JOIN T) = R JOIN S JOIN T JOIN T by distribution and commutative properties of JOIN (pg. 57) and T JOIN T = T, so this gives the left hand side.
In case you think this is generalizable, note that (R where A = a) JOIN (S where A=a) only makes sense for A a join column for R JOIN S.