HTML/XHTML/XML Test Answers
MY BEST ANSWERS ARE IN BLUE
Spring 2006
Please use the following tables from a LIGHTNING_STRIKE database to answer the questions on this test:
Table: STRIKE
ID Date Time Lat Lon Intensity
1 05-05-2006 02:02 41.34 -122.45 6235
2 05-05-2006 02:09 40.47 -120.47 16235
3 05-05-2006 03:32 42.14 -122.98 7779
4 05-05-2006 04:34 38.32 -122.17 4645
5 05-05-2006 05:02 39.04 -121.22 8989
...
Table: FIRES
ID Date Lat Lon Area
1 05-05-2006 32.34 -122.45 123.90
2 05-05-2006 37.19 -121.66 627.09
3 05-05-2006 40.47 -120.47 45.00
4 05-05-2006 42.14 -122.98 1774.9
5 05-05-2006 37.21 -120.47 2034.8
6 05-05-2006 42.04 -126.22 49.62
...
Table: PICTURES
ID Strike_FK Date Filename
1 1 05-05-2006 04938245.png
2 1 05-05-2006 04983284.png
3 2 05-05-2006 04773626.png
4 2 05-05-2006 04789789.png
5 4 05-05-2006 04323456.png
6 5 05-05-2006 04325342.png
...
1. Please create the SQL commands that would generate the STRIKE, FIRES, and PICTURES tables
CREATE TABLE STRIKE (
Date date,
Time text,
Lat real,
Lon real,
Intensity real,
ID serial);
CREATE TABLE FIRES (
Date date,
Lat real,
Lon real,
Area real,
ID serial);
CREATE TABLE PICTURES (
Strike_FK int,
Date date,
Filename text,
ID serial);
Please create the SQL commands that would insert the first record into each of the STRIKE, FIRES, and PICTURES tables:
INSERT INTO Strike VALUES ('05/05/2006', '2:02', 41.34, -122.45, 6235);
INSERT INTO Strike VALUES ('05-05-2006', '02:09', 40.47, -120.47, 16235);
INSERT INTO Strike VALUES ('05-05-2006', '03:32', 42.14, -122.98, 7779);
INSERT INTO Strike VALUES ('05-05-2006', '04:34', 38.32, -122.17, 4645);
INSERT INTO Strike VALUES ('05-05-2006', '05:02', 39.04, -121.22, 8989);
INSERT INTO Fires VALUES ('05/05/2006', 32.34, -122.45, 123.90);
INSERT INTO Fires VALUES ('05-05-2006', 37.19, -121.66, 627.09);
INSERT INTO Fires VALUES ('05-05-2006', 40.47, -120.47, 45.0);
INSERT INTO Fires VALUES ('05-05-2006', 42.14, -122.98, 1774.9);
INSERT INTO Fires VALUES ('05-05-2006', 37.21, -120.47, 2034.8);
INSERT INTO Fires VALUES ('05-05-2006', 42.04, -126.22, 49.62);
INSERT INTO Pictures VALUES (1, '05/05/2006', '04938245.png');
INSERT INTO Pictures VALUES (1, '05-05-2006', '04983284.png');
INSERT INTO Pictures VALUES (2, '05-05-2006', '04773626.png');
INSERT INTO Pictures VALUES (2, '05-05-2006', '04789789.png');
INSERT INTO Pictures VALUES (4, '05-05-2006', '04323456.png');
INSERT INTO Pictures VALUES (5, '05-05-2006', '04325342.png');
2. Please create an SQL command to generate an appropriate LIGHTNING_FIRES table that would connect the FIRES table to the STRIKE table:
CREATE TABLE LIGHTNING_FIRES (ID int, STRIKE_FK int, FIRE_FK int)
- or to fill simultaneously -
CREATE TABLE LIGHTNING_FIRES AS
SELECT STRIKE.ID AS ID, STRIKE.Date, STRIKE.Time, STRIKE.Lat, STRIKE.Lon, FIRES.ID AS FIRE_FK
FROM STRIKE, FIRES WHERE STRIKE.Lat=FIRES.Lat AND STRIKE.Lon=FIRES.Lon AND STRIKE.Date=FIRES.Date;
3. Please write an SQL command that would report a single number for the average intensity for all records in the STRIKE table:
SELECT AVG(Intensity) AS AVG_INTENSITY FROM STRIKE;
4. Please write an SQL command that would list all STRIKE IDs for those lightning STRIKEs that do not have a picture available
in the PICTURES table. Please show the result set the query would generate (with appropriate headers for each column returned):
SELECT ID FROM STRIKE S WHERE NOT EXISTS (SELECT STRIKE_FK FROM PICTURES WHERE STRIKE_FK=S.ID);
id
----
3
(1 row)
5. Please write an SQL command that would list the largest three FIRES in order of the maximum Area burned by the fire.
Please show the result set the query would generate (with appropriate headers for each column returned):
SELECT ID, Area FROM FIRES ORDER BY Area DESC LIMIT 3;
id | area
----+--------
5 | 2034.8
4 | 1774.9
2 | 627.09
(3 rows)
6. Please write an SQL command to report the total Area burned by these largest three FIRES (report a single number, please):
SELECT Sum(Area) FROM (SELECT ID, Area FROM FIRES ORDER BY Area DESC LIMIT 3) AS Subquery;
sum
---------
4436.79
(1 row)
7. Please create an SQL query that would match lightning STRIKES to FIRES based on these identifying features:
The lightning strike and fire occurred on the same date and
The lightning strike and fire occurred at the same latitude and longitude
SELECT STRIKE.ID AS ID, STRIKE.Date, STRIKE.Time, STRIKE.Lat, STRIKE.Lon, FIRES.ID AS FIRE_FK
FROM STRIKE, FIRES
WHERE STRIKE.Lat=FIRES.Lat AND STRIKE.Lon=FIRES.Lon AND STRIKE.Date=FIRES.Date;
id | date | time | lat | lon | fire_fk
----+------------+-------+-------+---------+---------
3 | 2006-05-05 | 03:32 | 42.14 | -122.98 | 4
2 | 2006-05-05 | 02:09 | 40.47 | -120.47 | 3
(2 rows)
8. Please create one or more SQL command that would fill the LIGHTNING_FIRES table you created in part 2
above with he results from your SQL query from part 7 above.
INSERT INTO LIGHTNING_FIRES VALUES (1, 3, 4);
INSERT INTO LIGHTNING_FIRES VALUES (2, 2, 3);
9. Please create the result set for the following query (with headers for each column returned):
SELECT STRIKE.Lat as LATITUDE, STRIKE.Lon as LONGITUDE, Filename AS PNG
FROM STRIKE, PICTURES WHERE Strike.ID=Strike_FK ORDER BY Filename DESC;
latitude | longitude | png
----------+-----------+--------------
41.34 | -122.45 | 04983284.png
41.34 | -122.45 | 04938245.png
40.47 | -120.47 | 04789789.png
40.47 | -120.47 | 04773626.png
39.04 | -121.22 | 04325342.png
38.32 | -122.17 | 04323456.png
(6 rows)
10. Please write the appropriate SQL ALTER command to add a FIRE_FK attribute to the STRIKES table (you
can look up the details in the SQLCommand reference provided on our class Web site):
ALTER TABLE STRIKE ADD FIRE_FK integer;
11. Please create one or more SQL command to fill the FIRE_FK attribute in the STRIKES table that you created in part 10 above.
UPDATE STRIKE SET FIRE_FK=3 WHERE STRIKE.ID=2;
UPDATE STRIKE SET FIRE_FK=4 WHERE STRIKE.ID=3;
12. 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 in front of each of the six tasks):
_B__ Asks questions about entities and relationships A. Data Analyst
during a database development project
_D__ Answers those questions about entities and relationships B. Data Modeler
_C__ Offers expertise about computer storage devices
_C__ Designs a database backup strategy C. Database Administrator
_A__ Understands the use of logic in asking for information
from the database tables D. Organizational Management
_D__ Reviews the reports prepared specifically for high level
use