WHAT IS YOUR NAME? (1 point) (KEY IS AT END OF DOCUMENT)
Exam # 2
This test is closed book and closed notes.
1) (5 pts) Consider a one-attribute table T(A). Write the shortest SQL query you can come up with that returns all values for attribute A that appears in table T at least 3 times. You may assume there are no NULLs, and do not include duplicate A values in your result.
2) (10 pts) Consider a table Chess(player, points) recording points earned by chess players. Each attribute independently is a key—that is, each player appears only once and there are never ties in points—and there are no NULL values. Consider the following SQL query over this table:
select player
from Chess C1
where 2 > (select count(*) from Chess C2
where C2.points > C1.points)
State in English what is returned by this query. Please state the interpretation precisely and succinctly—the correct answer requires only a few well-chosen words
Use the items_ordered table and the customers table for the next 3 questions.
3) (10 pts each) Write SQL queries for the following.
a) Select the average price of all of the items ordered.
b) What is the total number of rows in the items_ordered table?
c) For all of the tents that were ordered in the items_ordered table, what is the price of the lowest tent? Your query should return the price only.
d) From the items_ordered table, select the item, maximum price, and minimum price for each specific item in the table for which the quantity=1.
e) Answer this question using a join. Which state had the greatest total purchase sum? List the state and amount.
f) Answer this question using a nested correlated query. Only considering ordered items for which the quantity = 1, which customerid/order_date records show a price for an item that is greater than the average price for that item?
4) (4 pts) What would be included in a left outer join of customers and items_ordered that would not appear in a regular join? (Describe the type of data. Do NOT write out the actual data!)
5) (5 points) Consider the following constraint defined in the item_ordered table. What event (SQL statement and target) would cause this action to be invoked? (Note this is not asking what the result is).
FOREIGN KEY customerid REFERENCES customers.customerid ON DELETE CASCADE
6) (3 points) Simple JDBC. JDBC allows one to write a Java program to interact with a DBMS. Once an appropriate JDBC driver is loaded on your machine and the classpath set, you can write your program. What are the 3 things we do first in our program? (Just describe them, you do not have to write the syntax)
7) (5 points) Write a TRIGGER on the items_ordered table that calls a plpgsql function called big_spender() to check if the sum of ordered items exceeds a threshold. Your syntax does not have to be perfect, but please try. I’m mainly looking that you understand the appropriate trigger options for this case.
8) (7 points) Complete the following plpgsql function big_spender() decribed in the previous question. Let’s keep it simple and only consider the case where new item_ordered row(s) are being inserted.
CREATE FUNCTION big_spender () RETURNS ______AS '
DECLARE
TotalSum FLOAT:=0.0;
LIMIT FLOAT := 1000.0
BEGIN
--SELECT INTO QUERY TO GET THE SUM SHOULD GO HERE;
-- DON’T FORGET THE NEW AMOUNT
-- If exceeds limit, raise an exception.
IF (TotalSum>LIMIT) THEN
RAISE EXCEPTION ''Big Spender can’t have no more'';
END IF;
RETURN NEW;
END;
' LANGUAGE '______';
items_ordered (key is {customer_id, order_date}, customerid is FK to customer
customerid / order_date / item / quantity / price10330 / 30-Jun-1999 / Pogo stick / 1 / 28.00
10101 / 30-Jun-1999 / Raft / 1 / 58.00
10298 / 01-Jul-1999 / Skateboard / 1 / 33.00
10101 / 01-Jul-1999 / Life Vest / 4 / 125.00
10299 / 06-Jul-1999 / Parachute / 1 / 1250.00
10339 / 27-Jul-1999 / Umbrella / 1 / 4.50
10449 / 13-Aug-1999 / Unicycle / 1 / 180.79
10439 / 14-Aug-1999 / Ski Poles / 2 / 25.50
10101 / 18-Aug-1999 / Rain Coat / 1 / 18.30
10449 / 01-Sep-1999 / Snow Shoes / 1 / 45.00
10439 / 18-Sep-1999 / Tent / 1 / 88.00
10298 / 19-Sep-1999 / Lantern / 2 / 29.00
10410 / 28-Oct-1999 / Sleeping Bag / 1 / 89.22
10438 / 01-Nov-1999 / Umbrella / 1 / 6.75
10438 / 02-Nov-1999 / Pillow / 1 / 8.50
10298 / 01-Dec-1999 / Helmet / 1 / 22.00
10449 / 15-Dec-1999 / Bicycle / 1 / 380.50
10449 / 22-Dec-1999 / Canoe / 1 / 280.00
10101 / 30-Dec-1999 / Hoola Hoop / 3 / 14.75
10330 / 01-Jan-2000 / Flashlight / 4 / 28.00
10101 / 02-Jan-2000 / Lantern / 1 / 16.00
10299 / 18-Jan-2000 / Inflatable Mattress / 1 / 38.00
10438 / 18-Jan-2000 / Tent / 1 / 79.99
10413 / 19-Jan-2000 / Lawnchair / 4 / 32.00
10410 / 30-Jan-2000 / Unicycle / 1 / 192.50
10315 / 2-Feb-2000 / Compass / 1 / 8.00
10449 / 29-Feb-2000 / Flashlight / 1 / 4.50
10101 / 08-Mar-2000 / Sleeping Bag / 2 / 88.70
10298 / 18-Mar-2000 / Pocket Knife / 1 / 22.38
10449 / 19-Mar-2000 / Canoe paddle / 2 / 40.00
10298 / 01-Apr-2000 / Ear Muffs / 1 / 12.50
10330 / 19-Apr-2000 / Shovel / 1 / 16.75
(from http://sqlcourse2.com/items_ordered.html, Oct. 2004)
CUSTOMERS TABLE (key is customerid)
10101 / John / Gray / Lynden / Washington
10298 / Leroy / Brown / Pinetop / Arizona
10299 / Elroy / Keller / Snoqualmie / Washington
10315 / Lisa / Jones / Oshkosh / Wisconsin
10325 / Ginger / Schultz / Pocatello / Idaho
10329 / Kelly / Mendoza / Kailua / Hawaii
10330 / Shawn / Dalton / Cannon Beach / Oregon
10338 / Michael / Howell / Tillamook / Oregon
10339 / Anthony / Sanchez / Winslow / Arizona
10408 / Elroy / Cleaver / Globe / Arizona
10410 / Mary Ann / Howell / Charleston / South Carolina
10413 / Donald / Davids / Gila Bend / Arizona
10419 / Linda / Sakahara / Nogales / Arizona
10429 / Sarah / Graham / Greensboro / North Carolina
10438 / Kevin / Smith / Durango / Colorado
10439 / Conrad / Giles / Telluride / Colorado
10449 / Isabela / Moore / Yuma / Arizona
(from http://sqlcourse2.com/customers.html, Oct. 2004)
KEY DB EXAM 2 , FALL 2004
(1ST 2 TAKEN FROM A STANFORD EXAM)
1. Consider a one-attribute table T(A). Write the shortest SQL query you can come up with that returns all values for attribute A that appears in table T at least 3 times. You may assume there are no NULLs, and do not include duplicate A values in your result.
ANSWER (VERIFIED IN POSTGRES ON TABLE T):
select distinct A from T group by A having count(a) > 2;
2. Consider a table Chess(player, points) recording points earned by chess players. Each
attribute independently is a key—that is, each player appears only once and there are never
ties in points—and there are no NULL values. Consider the following SQL query over this
table:
select player
from Chess C1
where 2 > (select count(*) from Chess C2
where C2.points > C1.points)
State in English what is returned by this query. Please state the interpretation succinctly—
the correct answer requires only a few well-chosen words.
WHO ARE THE TOP 2 PLAYERS?
Use the items ordered table (from http://sqlcourse2.com/items_ordered.html, Oct. 2004) and the
customers table ( /customers.html,Oct 2004)
a) Select the average price of all of the items ordered
select avg(price) from items_ordered
b) What is the total number of rows in the items_ordered table
select count(*) from items_ordered
c) For all of the tents that were ordered in the items_ordered table, what is the price of the lowest tent? Your query should return the price only
select min(price) from items_ordered where item=’Tent’;
d) From the items_ordered table, select the item, maximum price, and minimum price for each specific item in the table for which the quantity=1
select item,max(price),min(price) from items_ordered where quantity=1 group by item ;
e) Answer this question using a join. Which state had the greatest total purchase sum? List the state and amount. (answer is Washington at 1608.75)
select state, sum(price) from items_ordered,customer
where items_ordered.customerid=customer.customerid
GROUP BY state HAVING sum(price) >= ALL
(select sum(price) from items_ordered,customer
where items_ordered.customerid=customer.customerid group by state);
f) Answer this question using a nested correlated query. Only considering ordered items for which the quantity = 1, which customerid/order_date records show a price for an item that is greater than the average price for that item? (price does not need to be shown)
select customerid,order_date,price from items_ordered I1 where quantity=1 and
price > (select avg(price) from items_ordered I2 where I1.item=I2.item) and quantity=1;
customerid | order_date | price
------+------+------
10439 | 1999-09-18 | 88
10410 | 1999-10-28 | 89.22
10438 | 1999-11-01 | 6.75
10410 | 2000-01-30 | 192.5
(4 rows)
4. EXPLAIN (IN ENGLISH) WHAT WOULD BE INCLUDED IN A LEFT OUTER JOIN OF CUSTOMERS AND ITEMS_ORDERED THAT WOULD NOT APPEAR IN A REGULAR JOIN.
WOULD INCLUDE CUSTOMERS WHO DON’T HAVE ANY ORDERS
5. DELETE A CUSTOMER IN THE CUSTOMER TABLE
6. Load the driver, establish the connection, create a statement to execute some sql.
7.
CREATE TRIGGER big_spender
BEFORE INSERT OR UPDATE
ON item_ordered FOR EACH ROW
EXECUTE PROCEDURE big_spender();
8.OUTLINE
RETURNS OPAQUE
SELECT INTO TotalSum price FROM items_ordered WHERE id=NEW.customerid;
TotalSum :=TotalSum + NEW.price;
LANGUAGE PLPGSQL
.