The Frog Database

The AmphibiaWeb website shows the different amphibians in different regions of the world. The link will show you the amphibians in the United States, organized by U.S. states.

We are going to create a simple database that uses some of the data from this website. This post will use the data from Wyoming, located here.

The website contains the following columns from their database:

• Scientific Name
• Photo
• Map
• Sound
• IUCN Red List Status
• Vernacular Name
• Family

Our database table will only utilize four of the columns:

• Scientific Name
• IUCN Red List Status
• Vernacular Name
• Family

Here will be our database structure:

Database: Amphibians

Table(s): Wyoming

First, lets create the database and table. We will only insert a few of the records:

1. CREATE DATABASE Amphibians;
2. GO
4. USE Amphibians;
5. GO
7. CREATE TABLE Wyoming
8. (
9.     amph_ID INT IDENTITY(1,1) PRIMARY KEY
10.     , scientific_Name VARCHAR(100)
11.     , rl_Status VARCHAR(75)
12.     , vernacular_Name VARCHAR(100)
13.     , family VARCHAR(100)
14. );
16. INSERT INTO Wyoming
17. (
18.     scientific_Name
19.     , rl_Status
20.     , vernacular_Name
21.     , family
22. )
23. VALUES
24. (
25.     'Bufo baxteri'
26.     , 'Extinct in the Wild (EW)'
27.     , 'Wyoming Toad'
28.     , 'Bufonidae'
29. );
31. INSERT INTO Wyoming
32. (
33.     scientific_Name
34.     , rl_Status
35.     , vernacular_Name
36.     , family
37. )
38. VALUES
39. (
40.     'Bufo boreas'
41.     , 'Near Threatened (NT)'
42.     , 'Western Toad'
43.     , 'Bufonidae'
44. );
46. INSERT INTO Wyoming
47. (
48.     scientific_Name
49.     , rl_Status
50.     , vernacular_Name
51.     , family
52. )
53. VALUES
54. (
55.     'Bufo cognatus'
56.     , 'Least Concern (LC)'
57.     , 'Great Plains Toad'
58.     , 'Bufonidae'
59. );
61. INSERT INTO Wyoming
62. (
63.     scientific_Name
64.     , rl_Status
65.     , vernacular_Name
66.     , family
67. )
68. VALUES
69. (
70.     'Bufo hemiophrys'
71.     , 'Least Concern (LC)'
72.     , 'Canadian Toad'
73.     , 'Bufonidae'
74. );

If you run a select all query on the new Amphibians table, you should get this result:

---------------------------------------------------------------------------------------

amph_ID scientific_Name rl_Status vernacular_Name family
1 Bufo baxteri Extinct in the Wild (EW) Wyoming Toad Bufonidae
2 Bufo boreas Near Threatened (NT) Western Toad Bufonidae
3 Bufo cognatus Least Concern (LC) Great Plains Toad Bufonidae
4 Bufo hemiophrys Least Concern (LC) Canadian Toad Bufonidae