Posted by Finding interesting data to improve your SQL skills can be a nuisance. There are of course plenty of freely available data sets out there, but analysing mortgage repayments over a 10 year cycle is not my idea of fun data to play with. (Apologies to any home loan brokers etc out there ).🤣
But how about checking out squirrels?!?! Yes, New York actually gathers data on squirrels from time to time. Just head on over to the their site to see the data they collect, or just click here to download the CSV file of data on 3000 squirrels.
To get you up and running quickly, I’ve pre-created the DDL to access the data as an external table, plus a view to sit on top of that table to present the data in a cleaner fashion. A couple of things to note if you’re comparing it to the descriptions on the New York site:
- Spaces in column names replaced with underscore
- The “Date” column renamed to “SightingDate”
- The “Lat/Long” column renamed to “Lat_Long”
Get the script below from my git repo and you’re good to go!
SQL> drop table squirrels purge;
Table dropped.
SQL> create table squirrels (
2 X number
3 ,Y number
4 ,Unique_Squirrel_ID varchar2(20)
5 ,Hectare varchar2(5)
6 ,Shift varchar2(3)
7 ,SightingDate varchar2(12)
8 ,Hectare_Squirrel_Number number
9 ,Age varchar2(10)
10 ,Primary_Fur_Color varchar2(30)
11 ,Highlight_Fur_Color varchar2(30)
12 ,Combination_of_Primary_and_Highlight_Color varchar2(100)
13 ,Color_notes varchar2(128)
14 ,Location varchar2(20)
15 ,Above_Ground_Sighter_Measurement varchar2(10)
16 ,Specific_Location varchar2(100)
17 ,Running varchar2(8)
18 ,Chasing varchar2(8)
19 ,Climbing varchar2(8)
20 ,Eating varchar2(8)
21 ,Foraging varchar2(8)
22 ,Other_Activities varchar2(128)
23 ,Kuks varchar2(8)
24 ,Quaas varchar2(8)
25 ,Moans varchar2(8)
26 ,Tail_flags varchar2(8)
27 ,Tail_twitches varchar2(8)
28 ,Approaches varchar2(8)
29 ,Indifferent varchar2(8)
30 ,Runs_from varchar2(8)
31 ,Other_Interactions varchar2(128)
32 ,Lat_Long varchar2(90)
33 )
34 organization external (
35 type oracle_loader
36 default directory temp
37 access parameters (
38 records delimited by newline
39 skip 1
40 fields terminated by ',' optionally enclosed by '"'
41 missing field values are null
42 (
43 x
44 ,y
45 ,unique_squirrel_id
46 ,hectare
47 ,shift
48 ,sightingdate
49 ,hectare_squirrel_number
50 ,age
51 ,primary_fur_color
52 ,highlight_fur_color
53 ,combination_of_primary_and_highlight_color
54 ,color_notes
55 ,location
56 ,above_ground_sighter_measurement
57 ,specific_location
58 ,running
59 ,chasing
60 ,climbing
61 ,eating
62 ,foraging
63 ,other_activities
64 ,kuks
65 ,quaas
66 ,moans
67 ,tail_flags
68 ,tail_twitches
69 ,approaches
70 ,indifferent
71 ,runs_from
72 ,other_interactions
73 ,lat_long
74 )
75 )
76 location ('Squirrel_Data.csv')
77 )
78 reject limit unlimited;
Table created.
SQL>
SQL> REM select * from squirrels;
SQL>
SQL> create or replace view v_squirrels as
2 select
3 x
4 ,y
5 ,unique_squirrel_id
6 ,hectare
7 ,shift
8 ,to_date(sightingdate,'MMDDYYYY') sightingdate
9 ,hectare_squirrel_number
10 ,age
11 ,primary_fur_color
12 ,highlight_fur_color
13 --,combination_of_primary_and_highlight_color
14 ,color_notes
15 ,location
16 ,case when lower(above_ground_sighter_measurement) != 'false' then to_number(above_ground_sighter_measurement) end above_ground_sighter_measurement
17 ,specific_location
18 ,case when running = 'false' then 'N' else 'Y' end running
19 ,case when chasing = 'false' then 'N' else 'Y' end chasing
20 ,case when climbing = 'false' then 'N' else 'Y' end climbing
21 ,case when eating = 'false' then 'N' else 'Y' end eating
22 ,case when foraging = 'false' then 'N' else 'Y' end foraging
23 ,other_activities
24 ,case when kuks = 'false' then 'N' else 'Y' end kuks
25 ,case when quaas = 'false' then 'N' else 'Y' end quaas
26 ,case when moans = 'false' then 'N' else 'Y' end moans
27 ,case when tail_flags = 'false' then 'N' else 'Y' end tail_flags
28 ,case when tail_twitches = 'false' then 'N' else 'Y' end tail_twitches
29 ,case when approaches = 'false' then 'N' else 'Y' end approaches
30 ,case when indifferent = 'false' then 'N' else 'Y' end indifferent
31 ,case when runs_from = 'false' then 'N' else 'Y' end runs_from
32 ,other_interactions
33 ,sdo_geometry(2001,4326,sdo_point_type(y, x, NULL),NULL,NULL) lat_long
34 from squirrels;
View created.
Now we are ready to ask the BIG questions about New York squirrels🤣
SQL> select primary_fur_color, count(*)
2 from v_squirrels
3 group by primary_fur_color
4 order by 2;
PRIMARY_FUR_COLOR COUNT(*)
------------------------------ ----------
55
Black 103
Cinnamon 391
Gray 2472
4 rows selected.
SQL>
Happy exploring!
Learning is not a spectator sport 
Hi Connor,
Just to continue the fun …
Looks like these are indeed individual squirrels, however, their NAME is missing from the file …
Why do I say “NAME” ?
Many, many years ago, when we still used to travel with my parents to nice places,
we “learned” that ALL the squirrels in the forest did react to the name “MARIANA” :):)
People simply gathered at the edge of the forest, and called them by this “common” name …
and, as by a “wonder-word”, all the squirrels came from the forest to pick up nuts and almonds
directly from people’s hands … and, after a short “validation check for integrity”, ran away with the “capture”,
probably to make their deposits for winter time :):)
It’s amazing what wonderful creatures do (still) live on this Earth …
while we, the “wise species” are busy destroying it every day 😦 😦
Have fun and enjoy a nice weekend,
Iudith Mentzel