To be submitted via email or in class on April 21, 2008 by 5PM.
In your own words, discuss this query in as much detail as possible. Pretend a friend of yours sees this and says, "Oh my, what is this thing? What does that COUNT thing mean? And getyear? What is GROUP BY? What does that HAVING line mean? What's that, another SELECT statement? What is that? What's SUM?" One more hint: what's special about the subquery? (25 pts)
SELECT c.id, (COUNT(*)*75) as rebate
FROM customers c, orders o
WHERE c.id = o.customer_id
AND getyear(yy,o.order_date) = '1998'
GROUP BY c.id
HAVING 750 < ALL (SELECT SUM(price * quantity * (1-discount))
FROM o, order_details od
WHERE o.id = od.order_id
AND o.customer_id = c.id
AND getyear(yy,o.order_date) = '1998'
GROUP BY o.id)
What is the point of normalization? (5 pts)
What is an index, what is it used for, and how do you declare one? (10 pts)
We've covered a lot of ground regarding programming with databases, including patterns, tools and paradigms. Pretend that a friend of yours, who has programmed a lot but has never used a relational database, was hanging out with you and asked "How can I use a database from my application?" In a few paragraphs, write your explanation below. Do not discuss specific languages/platforms, but rather the patterns, concepts, etc that span all programmatic use of relational databases. Use the class lecture summaries to guide you. (60 pts)
[end delightful homework 2]