Please Note that the database is available for SELECT commands at http://triton.cev.washington.edu/498a/sql.html.
-----------Cheese---------- | ---------------Cheese_Sales---------------------
Cheese_ID Name Qty Cost | Sale_ID Cheese_FK Customer_FK Qty Date
0 Cheddar 9 1.49 | 1411 0 3 1.0 06-01-2003
1 Swiss 10 1.29 | 1412 0 1 0.5 06-01-2003
2 Colby 7 1.39 | 1413 2 4 2.0 06-02-2003
3 Jack 12 1.59 | 1414 2 0 1.0 06-02-2003
4 Blue 14 2.19 | 1415 1 0 2.5 06-02-2003
5 Ricotta 8 1.44 | 1416 3 1 3.5 06-03-2003
| 1417 4 2 1.5 06-03-2003
| 1418 1 1 2.0 06-03-2003
----------Customer--------- | ------Taxes-------
Customer_ID Name State | Tax_ID State Rate
0 Ren NH | 0 MA .10
1 Phyllis NH | 1 RI .00
2 Cecil RI | 2 NH .05
3 Jin MA | 3 CT .08
4 Vlad MA |
1. Put the letter of the task (work) on the right in front of the person on the left who usually performs it
in a very large organization (put one letter from the right in front of each of the six tasks on the left):
A. Data Analyst
_B_ Designs a database backup strategy B. Database Administrator
_D_ Answers those questions about entities and relationships C. Data Modeler
_B_ Offers expertise about computer storage devices D. Organizational Management
_D_ Reviews reports prepared specifically for high-level use
_A_ Understands the use of logic in asking for information from the database tables
_C_ Asks questions about entities and relationships during a database development project
2. For the four tables in the CHEESE_DISTRIBUTOR database, identify whether each is an entity or relationship
(circle either Entity or Relationship for each table):
Cheese Entity Relationship
Cheese_Sales Entity Relationship
Customer Entity Relationship
Taxes Entity Relationship
3. To request that a Relational Database Management System prepare a result set showing a simple list of types of
Cheeses sold in June so far, the best SQL command would be (circle the one best answer):
A. SELECT Cheese.Name from Cheese
B. SELECT ALL Cheese.Name from Cheese_Sales
C. SELECT DISTINCT Cheese.Name from Cheese_Sales
D. SELECT ALL Cheese.Name from Cheese, Cheese_Sales WHERE Cheese_ID=Cheese_FK
E. SELECT DISTINCT Cheese.Name from Cheese, Cheese_Sales WHERE Cheese_ID=Cheese_FK
How many rows would be returned in the result set (NOT including the header row)? _5_
4. You want to see the Cheeses listed in alphabetical order. Which clause would you add to your SQL
query to produce the ordering you desire (circle the one best answer):
A. ALPHA ON Cheese.Name
B. ALPHA ASC
C. ORDER BY Cheese.Name
D. ORDER BY ALPHA
5. A colleague of yours in your organization processes the following valid SQL command:
ALTER TABLE Cheese ADD COLUMN Location text
You want to fill the new column such that all cheeses sold for less than $1.50/quarter-pound are stored
on the ground floor while the more expensive cheeses are stored in the cellar (basement). Complete the SQL
commands that will fill the column out correctly according to this requirement (fill in all the blanks):
UPDATE Cheese SET Location='Cellar' WHERE Cost>1.5
UPDATE Cheese SET Location='Ground' WHERE Cost<=1.5
Show the result sets for the commands numbered 6 through 9 (prepare the header row and the result detail row(s))
as an SQL processing engine would produce). Tell me why a query would return nothing if you think that is the case:
6. SELECT Cheese.Name FROM Cheese WHERE Qty<10 AND Cost<1.45
name
---------
Colby
Ricotta
7. SELECT Customer.Name FROM Cheese_Sales, Customer WHERE Cheese_FK=2 AND Customer_FK=Customer_ID
name
-------
Ren
Vlad
8. SELECT SUM(Cheese_Sales.Qty * Cheese.Cost * 4 * Taxes.Rate) AS MA_Taxes FROM Cheese,
Cheese_Sales, Taxes, Customer WHERE Cheese_Sales.Cheese_FK=Cheese.Cheese_ID AND
Cheese_Sales.Customer_FK=Customer.Customer_ID AND Taxes.State='MA' AND Taxes.State=Customer.State
ma_taxes
-----------
1.708
Note: Without the last 'AND Taxes.State=Customer.State clause in the query', the query would calculate
taxes on all sales multiplied by the MA tax rate. The intent was not to give a trick question and all
answers that made any sense at all were accepted.
9. SELECT Cheese.Name, AVG(Cheese_Sales.Qty * 4) AS Avg_Qty FROM Cheese, Cheese_Sales WHERE Date='06-02-2003' AND Cheese_ID=Cheese_FK GROUP BY Cheese.Name ORDER BY Cheese.Name ASC
name | Avg_Qty
----------+--------
Colby | 6.0
Swiss | 10.0
Note: A better question would not have multiplied the Qty * 4, since Qty is already in pounds.
You are investigating a Java Server Page that runs against the CHEESE_DISTRIBUTOR database. You see the following piece of code:
...
ResultSet rs = st.executeQuery("SELECT * FROM Cheese_Sales, Customer " +
"WHERE Customer.Name='Phyllis' AND " + Customer_ID=Customer_FK");
int count=0;
while (rs.next()) {
count=count+1;
}
...
10. What will the count variable's value be after the while statement completes? _3_
You want to produce the following SQL query engine result set:
Cheese_name | Name
-------------+------
Cheddar | Jin
(1 row)
11. Complete the following SQL command that will produce the desired result set:
SELECT Cheese.name AS Cheese_Name, Customer.Name FROM Cheese, Cheese_Sales, Customer
WHERE Sale_ID=1411 and Cheese_ID=Cheese_FK AND Customer_ID=Customer_FK;
12. A Cheese sale has just been made today, June 4, 2003. Which SQL command will properly set a record up in the Cheese_Sales Table:
A. UPDATE Cheese_Sales INSERT (1419, 2, 2, 3.5, '06-03-2003')
B. UPDATE Cheese_Sales NEW VALUES (1419, 2, 2, 3.5, '06-03-2003')
C. INSERT INTO Cheese_Sales VALUES (1419, 2, 2, 3.5, 06-03-2003)
D. INSERT INTO Cheese_Sales VALUES (1419, 2, 2, 3.5, '06-03-2003')
E. INSERT VALUES (1419, 2, 2, 3.5, 06-03-2003) INTO Cheese_Sales
** Would you use an executeQuery() or executeUpdate() SQL command statement method in
your Java Server Page to process the command above?
( circle either: executeQuery() or executeUpdate() )
13. You just received an order for 2 pounds of Swiss cheese from a new customer, Shirley, who lives in Norwalk, CT.
Write the two SQL commands you need to run against the database to set up the sale as of today (write the two
appropriate SQL statements below this line)?
INSERT INTO Customer VALUES (5,'Shirley','CT');
INSERT INTO Cheese_Sales VALUES (1420, 1, 5, 2.0, '06-04-2003');
Note: Of course, the system would also need to reduce the Swiss inventory in the Cheese table appropriately
You decide that just adding the Location column to the Cheese table is sloppy and prefer instead to
create a proper Location table that connects to the Cheese table by ID instead (you expect this cheese
distribution business to take off in popularity over the next few years).
14. What SQL statement would you run against the database to create a simple table that will contain a
location ID and location name (write the appropriate SQL statement below this line)?
CREATE TABLE Location (location_ID int, location text);
Note: You could also call the second column 'name' or something similar
15. What type of information will the Location attribute of the Cheese table hold using this new scheme (circle the letter before the best answer below)?
A. integer
B. real
C. text
D. date
E. double
You want to create an SQL query engine result set with a single row (tuple) that reports the Cheese with the
largest total sales before taxes (sum of the Qty sold * Cost attribute for all Cheeses sold) between June 1st
and today (including all sales made in questions on this exam).
16. Which Cheese would you expect to appear in the result set (circle the letter in front
of the best answer)?
A. Cheddar
B. Colby
C. Jack
D. Blue
E. Swiss
F. Ricotta
Note: The Query in the extra credit section shows results as:
top_seller | sales
------------+--------
Swiss | 33.54
(1 row)
17. Which of the following SQL fragments (pieces) should appear in the query you would write to obtain the above
result set (circle the letter before all fragments that should appear given the best query to write)?
A. MAX(SUM(Cheese.Cost*Cheese.Qty))
B. GROUP BY Cheese.Name
C. GROUP BY Cheese_Sales
D. MAX(Cheese.Cost*Cheese_Sales.Qty)
E. WHERE Name=
F. WHERE Cheese_ID=Cheese_FK
G. ORDER BY Cheese.Name
For extra credit (make sure you have time to answer this), write a valid SQL query to generate the result:
SELECT Cheese.Name AS top_seller, SUM(Cheese.Cost*4*Cheese_sales.Qty) as sales FROM Cheese, Cheese_sales
WHERE Cheese_ID=Cheese_FK GROUP BY Cheese.Name ORDER BY sales DESC LIMIT 1;