-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathsample_transactions.txt
201 lines (180 loc) · 20 KB
/
sample_transactions.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
--CS340 Databases
--Spring 2022
--Final Project - SQL Schema Part 4/5: Simple Transactions and Interesting Queries
--Seth Weiss, Orion Junkins
--weissse@oregonstate.edu
--junkinso@oregonstate.edu
Part 4: Sample Data Basic Queries
sqlite> SELECT * FROM Agency LIMIT 5;
┌───────────┬─────────────────────────────────┬────────────────────────────────────────┐
│ agency_id │ agency_name │ website_url │
├───────────┼─────────────────────────────────┼────────────────────────────────────────┤
│ 0 │ Central Oregon Avalanche Center │ https://www.coavalanche.org/ │
│ 1 │ Utah Avalanche Center │ https://utahavalanchecenter.org/ │
│ 2 │ Sierra Avalanche Center │ https://www.sierraavalanchecenter.org/ │
│ 3 │ Wallowa Avalanche Center │ https://wallowaavalanchecenter.org/ │
│ 4 │ Sawtooth Avalanche Center │ https://www.sawtoothavalanche.com/ │
└───────────┴─────────────────────────────────┴────────────────────────────────────────┘
sqlite> SELECT * FROM Forecaster LIMIT 5;
┌───────────┬─────────┬──────────┬───────────┐
│ frcstr_id │ fname │ lname │ agency_id │
├───────────┼─────────┼──────────┼───────────┤
│ 0 │ Joe │ Smith │ 0 │
│ 1 │ Jane │ Jones │ 6 │
│ 2 │ John │ Lee │ 1 │
│ 3 │ Jessica │ Thompson │ 2 │
│ 4 │ Jimmy │ Thompson │ 2 │
└───────────┴─────────┴──────────┴───────────┘
sqlite> SELECT * FROM Forecast LIMIT 5;
┌─────┬────────────┬───────────────────────┬────────────────────┬───────────────────────┬───────────┬───────────────────────┐
│ fid │ issue_date │ danger_below_treeline │ danger_at_treeline │ danger_above_treeline │ issued_by │ corresponds_to │
├─────┼────────────┼───────────────────────┼────────────────────┼───────────────────────┼───────────┼───────────────────────┤
│ 0 │ 12-2-21 │ 0 │ 1 │ 1 │ 0 │ Central Cascades │
│ 1 │ 12-26-21 │ 1 │ 1 │ 1 │ 1 │ Southern Mountains │
│ 2 │ 1/1/2022 │ 2 │ 2 │ 3 │ 2 │ Uintas │
│ 3 │ 1/1/2022 │ 2 │ 2 │ 2 │ 3 │ Central Sierra Nevada │
│ 4 │ 1/1/2022 │ 1 │ 2 │ 3 │ 4 │ Central Sierra Nevada │
└─────┴────────────┴───────────────────────┴────────────────────┴───────────────────────┴───────────┴───────────────────────┘
sqlite> SELECT * FROM Problem LIMIT 5;
┌─────┬─────┬──────────────┬──────┬────────────┐
│ pid │ fid │ problem_type │ size │ likelihood │
├─────┼─────┼──────────────┼──────┼────────────┤
│ 0 │ 0 │ Loose Dry │ 1 │ 1 │
│ 1 │ 0 │ Wind Slab │ 2 │ 1 │
│ 2 │ 1 │ Storm Slab │ 3 │ 4 │
│ 3 │ 1 │ Wind Slab │ 3 │ 4 │
│ 4 │ 2 │ Wind Slab │ 3 │ 4 │
└─────┴─────┴──────────────┴──────┴────────────┘
sqlite> SELECT * FROM Elevation LIMIT 5;
┌─────┬───────────┐
│ pid │ elevation │
├─────┼───────────┤
│ 0 │ 1 │
│ 1 │ 1 │
│ 2 │ 2 │
│ 3 │ 3 │
│ 4 │ 2 │
└─────┴───────────┘
sqlite> SELECT * FROM Aspect LIMIT 5;
┌─────┬────────┐
│ pid │ aspect │
├─────┼────────┤
│ 0 │ N │
│ 1 │ NE │
│ 2 │ E │
│ 3 │ SE │
│ 4 │ S │
└─────┴────────┘
sqlite> SELECT * FROM Observation LIMIT 5;
┌────────────────┬──────────────────┬───────────┬──────────────────────┬─────────────────────────────────┬─────────────┬──────────────────────────────────────────────────────────────────────────────────┐
│ observation_id │ observation_date │ avalanche │ observation_location │ zone_name │ observer_id │ obs_description │
├────────────────┼──────────────────┼───────────┼──────────────────────┼─────────────────────────────────┼─────────────┼──────────────────────────────────────────────────────────────────────────────────┤
│ 0 │ 12-2-21 │ 0 │ Tam McArthur Rim │ Central Cascades │ 0 │ Dug a hand pit, good bonding. │
│ 1 │ 12-15-21 │ 1 │ Mineral Fork │ Uintas │ 1 │ Saw remenants of slide. No cracking or wumphing. │
│ 2 │ 12-26-21 │ 1 │ Elephant's Hump │ Central Sierra Nevada │ 2 │ Huge slide off the hump, most likely human triggered. │
│ 3 │ 1-2-22 │ 0 │ Jackson Peak │ Northern Wallowas │ 3 │ Great snow, no cracks or wumphing, pit revealed potential persistent weak layer. │
│ 4 │ 1-14-22 │ 1 │ Avalanche Peak │ Sawtooth and Western Smoky Mtns │ 4 │ Observered an avalanche on Avalanceh Peak! │
└────────────────┴──────────────────┴───────────┴──────────────────────┴─────────────────────────────────┴─────────────┴──────────────────────────────────────────────────────────────────────────────────┘
sqlite> SELECT * FROM Observer LIMIT 5;
┌─────────────┬─────────┬──────────┬───────────────┐
│ observer_id │ fname │ lname │ observer_type │
├─────────────┼─────────┼──────────┼───────────────┤
│ 0 │ Joe │ Smith │ Forecaster │
│ 1 │ Jane │ Jones │ Forecaster │
│ 2 │ John │ Lee │ Forecaster │
│ 3 │ Jessica │ Thompson │ Forecaster │
│ 4 │ Jimmy │ Thompson │ Forecaster │
└─────────────┴─────────┴──────────┴───────────────┘
sqlite> SELECT * FROM Zone LIMIT 5;
┌─────────────────────────────────┬───────────┐
│ zone_name │ agency_id │
├─────────────────────────────────┼───────────┤
│ Central Cascades │ 0 │
│ Uintas │ 1 │
│ Central Sierra Nevada │ 2 │
│ Northern Wallowas │ 3 │
│ Sawtooth and Western Smoky Mtns │ 4 │
└─────────────────────────────────┴───────────┘
Part 5: Interesting Queries
1) Find the most common avalanche problem in Utah.
SELECT a.agency_name, p.problem_type, COUNT(p.problem_type)
FROM Forecast as fr
INNER JOIN Problem as p
ON fr.fid = p.fid
INNER JOIN Agency as a
ON fr.issued_by = a.agency_id
WHERE issued_by=1
GROUP BY p.problem_type
ORDER BY COUNT (p.problem_type) DESC
LIMIT 1;
┌───────────────────────┬──────────────┬───────────────────────┐
│ agency_name │ problem_type │ COUNT(p.problem_type) │
├───────────────────────┼──────────────┼───────────────────────┤
│ Utah Avalanche Center │ Wind Slab │ 2 │
└───────────────────────┴──────────────┴───────────────────────┘
2) Search forecasts from Colorado (agency id of 6) which have a problem on an eastern aspect and return an overview of each problem.
SELECT DISTINCT p.pid ,fr.issue_date, ag.agency_name, fr.corresponds_to as Zone, p.problem_type, p.size, p.likelihood, a.aspect, e.elevation
FROM Forecast as fr
JOIN Problem as p
ON fr.fid = p.fid
JOIN Aspect as a
ON p.pid = a.pid
JOIN Elevation as e
ON p.pid = e.pid
JOIN Forecaster as f
ON fr.issued_by = f.frcstr_id
JOIN Agency as ag
ON ag.agency_id = f.agency_id
WHERE a.aspect = "E" AND ag.agency_id=6
LIMIT 5;
┌─────┬────────────┬───────────────────────────────────────┬────────────────────┬──────────────────┬──────┬────────────┬────────┬───────────┐
│ pid │ issue_date │ agency_name │ Zone │ problem_type │ size │ likelihood │ aspect │ elevation │
├─────┼────────────┼───────────────────────────────────────┼────────────────────┼──────────────────┼──────┼────────────┼────────┼───────────┤
│ 2 │ 12-26-21 │ Colorado Avalanche Information Center │ Southern Mountains │ Storm Slab │ 3 │ 4 │ E │ 2 │
│ 15 │ 1/2/2022 │ Colorado Avalanche Information Center │ Southern Mountains │ Cornice Collapse │ 2 │ 3 │ E │ 1 │
│ 21 │ 1/3/2022 │ Colorado Avalanche Information Center │ Southern Mountains │ Persistent Slab │ 1 │ 2 │ E │ 1 │
└─────┴────────────┴───────────────────────────────────────┴────────────────────┴──────────────────┴──────┴────────────┴────────┴───────────┘
3) Find all forecasts that involve a Wind Slab problem. Order first by zone (corresponds_to) then by issue date.
SELECT fid, issue_date, danger_below_treeline, danger_at_treeline, danger_above_treeline, issued_by as Agency, corresponds_to AS Zone, pid, problem_type, size, likelihood
FROM Forecast
NATURAL JOIN Problem
WHERE problem.problem_type = "Wind Slab"
ORDER BY corresponds_to, issue_date
LIMIT 5;
┌─────┬────────────┬───────────────────────┬────────────────────┬───────────────────────┬────────┬─────────────────────────────────┬─────┬──────────────┬──────┬────────────┐
│ fid │ issue_date │ danger_below_treeline │ danger_at_treeline │ danger_above_treeline │ Agency │ Zone │ pid │ problem_type │ size │ likelihood │
├─────┼────────────┼───────────────────────┼────────────────────┼───────────────────────┼────────┼─────────────────────────────────┼─────┼──────────────┼──────┼────────────┤
│ 0 │ 12-2-21 │ 0 │ 1 │ 1 │ 0 │ Central Cascades │ 1 │ Wind Slab │ 2 │ 1 │
│ 3 │ 1/1/2022 │ 2 │ 2 │ 2 │ 3 │ Central Sierra Nevada │ 6 │ Wind Slab │ 3 │ 2 │
│ 4 │ 1/1/2022 │ 1 │ 2 │ 3 │ 4 │ Central Sierra Nevada │ 8 │ Wind Slab │ 2 │ 2 │
│ 10 │ 1/3/2022 │ 1 │ 1 │ 1 │ 10 │ Sawtooth and Western Smoky Mtns │ 18 │ Wind Slab │ 1 │ 3 │
│ 8 │ 1/2/2022 │ 3 │ 4 │ 4 │ 8 │ Southern Mountains │ 14 │ Wind Slab │ 2 │ 4 │
└─────┴────────────┴───────────────────────┴────────────────────┴───────────────────────┴────────┴─────────────────────────────────┴─────┴──────────────┴──────┴────────────┘
4) Find all observation data from Oregon.
SELECT DISTINCT
o.observation_date,
z.zone_name,
o.observation_location,
CASE WHEN avalanche = 0 THEN 'No' ELSE 'Yes' END AS avalanche,
o.obs_description
FROM Observation AS o
NATURAL JOIN Zone AS z
NATURAL JOIN Agency AS a
WHERE agency_id IN (0, 3)
LIMIT 5;
┌──────────────────┬───────────────────┬──────────────────────┬───────────┬──────────────────────────────────────────────────────────────────────────────────┐
│ observation_date │ zone_name │ observation_location │ avalanche │ obs_description │
├──────────────────┼───────────────────┼──────────────────────┼───────────┼──────────────────────────────────────────────────────────────────────────────────┤
│ 12-2-21 │ Central Cascades │ Tam McArthur Rim │ No │ Dug a hand pit, good bonding. │
│ 1-2-22 │ Northern Wallowas │ Jackson Peak │ No │ Great snow, no cracks or wumphing, pit revealed potential persistent weak layer. │
└──────────────────┴───────────────────┴──────────────────────┴───────────┴──────────────────────────────────────────────────────────────────────────────────┘
5) Find the first and last name of forecasters who have not contributed an observation.
SELECT f.fname, f.lname FROM Forecaster AS f LEFT JOIN Observer AS o ON f.fname = o.fname WHERE o.fname IS NULL;
┌───────────────┬────────────┐
│ fname │ lname │
├───────────────┼────────────┤
│ Jorge │ Lopez │
│ Joey JoJo Jr. │ Shabadoo │
│ Big │ Foot │
│ Healy │ Fettuccine │
└───────────────┴────────────┘