SQL Exam Answers 2005
SQL Exam - Spring 2005 - INDE 498 A
Please use the following GARDEN database to answer all questions in the exam.
Please document any assumptions you make or issues you have with the questions.
Location Gardener
-------- --------
LocationID Name Sunlight Water | GardenerID Name Age
---------- ---- -------- ----- | ---------- ---- ---
0 East .28 .80 | 0 Mother 36
1 North .17 .84 | 1 Father 38
2 West .38 .48 | 2 Tim 15
3 South .45 .66 | 3 Erin 12
Plant
-----
PlantID Name Sunlight Water Weight
------- ---- -------- ----- -----
0 Carrot .26 .82 .08
1 Beet .44 .80 .04
2 Corn .44 .76 .26
3 Tomato .42 .80 .16
4 Radish .28 .84 .02
Planted
-------
PlantFK GardenerFK LocationFK Date Seeds
------- ---------- ---------- ---- -----
0 0 0 04-18-2005 28
0 1 1 04-14-2005 14
1 0 2 04-18-2005 36
2 1 3 04-14-2005 20
2 2 2 04-19-2005 12
3 3 3 04-25-2005 38
4 2 0 04-30-2005 30
Picked
------
PlantFK GardenerFK LocationFK Date Amount Weight
------- ---------- ---------- ---- ------ ------
0 2 0 08-18-2005 28 2.32
0 3 1 08-16-2005 12 1.02
2 1 3 08-22-2005 52 12.96
2 2 2 08-28-2005 18 4.58
3 3 3 08-22-2005 15 3.84
4 2 0 07-16-2005 23 0.52
Some notes on terms:
- The database is for a simple garden kept by a small family
- They plant their garden in the spring and pick their garden in summer
- The sunlight attribute refers to the percentage of a 24 hour day that the
location gets sunlight and the plant optimally wants sunlight.
- The water attribute refers to the percentage of average rainfall that
makes it to the root level for a location or is optimal for a plant.
- The plant (average expected) and picked (actual) weight is in kilograms
- The picked amount is the number of items (one carrot, one beet, an ear of
corn, one tomato, one radish) picked.
A reminder: the database software being used by this family is similar to
our postgres database engine. The database does not automatically do the
joining of tables via keys. You must manually provide that in your SQL
commands.
------------------- QUESTIONS START HERE --------------------------------
1. Put the letter (A, B, C or D) of the most typical work role from the list
here:
A. Organizational Manager
B. Data Analyst
C. Data Modeler
D. Database Administrator
into the blanks before the database tasks (put one letter in front of
each of the six tasks) here:
_A_ Answers those questions about entities and relationships
_D_ Offers expertise about computer storage devices
_D_ Designs a database backup strategy
_A_ Reviews reports prepared specifically for high-level use
_B_ 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 five tables in the GARDEN database, identify whether each is an
entity or relationship (type entity or relationship in each blank):
_entity_ Location
_entity_ Gardener
_entity_ Plant
_relationship_ Planted
_relationship_ Picked
3. Write a valid SQL statement I could run to create the location table
before I put any location data into it:
in a style like the SQL primer:
CREATE TABLE Location (LocationID INTEGER, Name VARCHAR(20), Sunlight DECIMAL(2.2), Water DECIMAL(2,2))
in my style from the project 2 database:
CREATE TABLE Location (LocationID serial, Name text, Sunlight real, Water real)
(any mix of the above data types would be fine)
4. Write a valid SQL statement to add an onion plant to the plant table:
in a style like the SQL primer:
INSERT INTO Plant (PlantID, Name, Sunlight, Water, Weight) VALUES (5, 'Onion', .45, .74, .19)
in a style from the project 2 database:
INSERT INTO Plant VALUES (5, 'Onion', 0.45, 0.74, 0.19)
5. Write a valid SQL statement that best records an onion planting event
based on your answer to question number 4:
in a style like the SQL primer:
INSERT INTO Planted (PlantFK,GardenerFK,LocationFK,Date,Seeds) VALUES (5, 1, 1, '4/30/2005', 32)
in a style from the project 2 database:
INSERT INTO Planted VALUES (5, 1, 1, '4/30/2005', 32)
6. For some reason, the beet crop did not succeed in producing edible
beets. The family wants to eliminate beets from their garden forever.
Write a valid SQL statement to delete the beet plant from the plant table:
DELETE FROM Plant WHERE PlantId = 1
7. write a valid SQL statement to delete beets from the planted table:
DELETE FROM Planted WHERE PlantFK = 1
Who planted the beets this year? _Mother_
8. Write a valid SQL statement that calculates the total weight of all
ears of corn that were picked from the garden:
SELECT SUM(Weight) AS corn_weight FROM Picked WHERE PlantFK = 2
which yields the result set:
corn_weight
-------------
17.54
(1 row)
9. Using a multiplicative conversion factor of 2.2 to convert kilograms to
pounds, write a valid SQL statement that shows the weight of the picked
crop in total pounds per plant type.
SELECT Plant.Name, SUM(2.2*Picked.Weight) AS Pounds FROM Plant, Picked
WHERE Plant.PlantID=Picked.PlantFK GROUP BY Plant.Name;
which yields the result set:
name | pounds
--------+-------------
Radish | 1.144
Corn | 38.588
Tomato | 8.448
Carrot | 7.348
(4 rows)
And, really, it would be good to ORDER BY something of use (pounds DESC or Name)
as well.
10. Write a valid SQL statement that would produce a result set like the
following:
name | name | date | amount
------+--------+------------+--------
Tim | Radish | 2005-07-16 | 23
Tim | Carrot | 2005-08-18 | 28
Tim | Corn | 2005-08-28 | 18
(3 rows)
SELECT Gardener.Name, Plant.Name, Date, Amount FROM Picked, Gardener, Plant
WHERE Plant.PlantId = Picked.PlantFK AND Gardener.GardenerId = Picked.GardenerFK
AND Picked.GardenerFK = 2 ORDER BY Date
11. You run the following valid VIEW SQL statement to produce a table in
memory:
CREATE VIEW weights AS
SELECT plant.name, picked.weight as picked_weight, picked.amount,
plant.weight FROM picked, plant WHERE picked.plantfk = plant.plantid;
This command creates the following view:
name | picked_weight | amount | weight
--------+---------------+--------+--------
Carrot | 1.02 | 12 | 0.08
Carrot | 2.32 | 28 | 0.08
Corn | 4.58 | 18 | 0.26
Corn | 12.96 | 52 | 0.26
Tomato | 3.84 | 15 | 0.16
Radish | 0.52 | 23 | 0.02
(6 rows)
Using that view, write a valid SQL statement that would produce a result
set like the following:
name | expected_weight | picked_weight | variance
--------+------------------+---------------+----------
Corn | 13.52 | 12.96 | -0.56
Corn | 4.68 | 4.58 | -0.10
Radish | 0.46 | 0.52 | 0.06
Carrot | 0.96 | 1.02 | 0.06
Carrot | 2.24 | 2.32 | 0.08
Tomato | 2.40 | 3.84 | 1.44
(6 rows)
SELECT name, (amount * weight) AS expected_weight, picked_weight,
(picked_weight-(amount * weight)) AS variance FROM weights ORDER BY variance;
12. Write a valid SQL statement that would produce a result set like the
following:
name | name | needed | available | variance
--------+-------+--------+-----------+----------
Carrot | East | 0.82 | 0.8 | -0.02
Carrot | West | 0.82 | 0.48 | -0.34
Carrot | North | 0.82 | 0.84 | 0.02
Carrot | South | 0.82 | 0.66 | -0.16
(4 rows)
SELECT plant.name, location.name, plant.water AS needed,
location.water AS available, location.water - plant.water AS variance
from plant, location WHERE plant.name='Carrot';
What would you name this result set in a report (what is its significance)?
_Carrot Planting Water Analysis (or something like that)_
13. You are investigating a Java Server Page (JSP) that runs using the GARDEN database.
You see the following piece of code as part of a dynamic block of Java:
...
ResultSet rs = st.executeQuery("SELECT * FROM planted, gardener " +
"WHERE gardener.name='Tim' AND " +
"gardenerID=gardenerFK");
int count=0;
while (rs.next()) {
count=count+1;
}
...
What will the count variable's value be after the while statement completes? _2_
because Tim was involved in two planted events which would be selected by the query... therefore,
two rows in the result set and two times through the loop (incrementing the count variable)
14. Write a valid SQL statement that would produce a result set like the
following:
plantfk | plant_date | pick_date | growing_days
---------+------------+------------+--------------
3 | 2005-04-25 | 2005-08-22 | 119
4 | 2005-04-30 | 2005-07-16 | 77
(2 rows)
SELECT Planted.PlantFK, Planted.Date AS plant_date, Picked.Date AS pick_date,
(Picked.Date-Planted.Date) AS growing_days FROM Planted, Picked
WHERE (Planted.PlantFK = Picked.PlantFK) AND Picked.PlantFK > 2
ORDER BY (Picked.Date-Planted.Date) DESC;
15. Write a valid SQL statement that calculates the average number of items produced per
seed planted for each plant type:
either:
SELECT Plant.Name, AVG(Picked.Amount/Planted.Seeds) AS yield FROM Plant, Planted, Picked
WHERE Planted.PlantFK = Picked.PlantFK AND Planted.LocationFK = Picked.LocationFK
AND Plant.PlantID=Picked.PlantFK GROUP BY Plant.Name
with an ORDER BY Plant.Name or other useful ORDER BY preferred at the end
name | yield
--------+------------------------
Carrot | 0.50000000000000000000
Corn | 1.5000000000000000
Radish | 0.00000000000000000000
Tomato | 0.00000000000000000000
(4 rows)
or:
SELECT Plant.Name, SUM(Picked.Amount)/SUM(Planted.Seeds) AS yield FROM Plant, Planted, Picked
WHERE Planted.PlantFK = Picked.PlantFK AND Planted.LocationFK = Picked.LocationFK
AND Plant.PlantID=Picked.PlantFK GROUP BY Plant.Name
with an ORDER BY Plant.Name or other useful ORDER BY preferred at the end
name | yield
--------+------------------------
Carrot | 0.00000000000000000000
Corn | 2.0000000000000000
Radish | 0.00000000000000000000
Tomato | 0.00000000000000000000
(4 rows)
Note that the postgres engine does not do automatic casting of integer types involved in
typical floating point math operations (division, for example). If we wanted to do a lot
of ratio analysis like this, we should make the types REAL or DECIMAL (#,#) instead. The
postgres database SQL engine likes to truncate results of integer math.
16. Extra Credit Question:
The dimensions of the different garden locations (in meters) are as follows.
What is the average amount of sunlight (as a percentage of a 24 hour day) for all
the gardens combined (a single number - show your calculation)?
East 3x4 = 12 : 12*.28 = 3.36
North 2x5 = 10 : 10*.17 = 1.70
West 3x4 = 12 : 12*.38 = 4.56
South 2x7 = 14 : 14*.45 = 6.30
-- -----
Totals: 48 15.92
== =====
Overall average is thus: 15.92/48.0 = 0.33
====