SQL Bear Organizer Lab
Last updated
Last updated
Timothy Treadwell has a lot on his plate protecting the bears of the Katmai National Park in Alaska. Help him keep track of all of his bear friends using SQL.
Use the CREATE TABLE
command to create a new table with various data types
Use the INSERT INTO
command to insert data (i.e. rows) into a database table
Use the SELECT
command with various functions and modifiers to write queries
This lab might seem a bit different than what you've seen before. Take a look at the file structure:
Let's briefly go over what is happening in the before
block that our tests will be using.
Before each test two important things happen.
First, a new in-memory database is created. Why do we do this? Let's say we run our tests and they add ten items to our database. If we did not use an in-memory store, those would be in there forever. This way our database gets thrown out after every running of the tests. You can learn more about in-memory databases here.
Next, a new SqlRunner
class is created. The SqlRunner
class lives in your bin
directory and was created to help connect to the database.
CREATE TABLE
Get the tests in spec/create_spec.rb
to pass. Your CREATE
statement should look something like this:
Your columns should be the following types:
column
type
id
integer
name
text
age
integer
gender
text
color
text
temperament
text
alive
boolean
Read about SQLite3 Datatypes to determine what your insert values are going to be. Be sure to pay attention to how booleans are expressed in SQLite3.
INSERT
Get the tests in spec/insert_spec.rb
to pass. Input the following 8 bears (you can make up details about them):
Mr. Chocolate
Rowdy
Tabitha
Sergeant Brown
Melissa
Grinch
Wendy
unnamed (the bear that killed Tim didn't have a name; refer back to how to create a record that doesn't have one value)
SELECT
Get the tests in spec/select_spec.rb
to pass. Note that for this section, the database will be seeded with external data from the lib/seed.sql
file so don't expect it to reflect the data you added above. Note: Since it's a Ruby file, write your queries as strings in the lib/sql_queries.rb
.
You may be expected to use SQL statements that you're not particularly familiar with. Make sure you use the resources and Google to find the right statements.
View SQL Bear Organizer on Learn.co and start learning to code for free.