> For the complete documentation index, see [llms.txt](https://certil-remy.gitbook.io/learn/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://certil-remy.gitbook.io/learn/sql/untitled-9.md).

# SQL Bear Organizer Lab

[Timothy Treadwell](http://en.wikipedia.org/wiki/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.

[![timothy-treadwell](https://camo.githubusercontent.com/c1cb98ee8dbe1b270ac6e3bfa0a2dbfdaedb6cb4/687474703a2f2f6d322e7061706572626c6f672e636f6d2f692f37342f3734363132312f6c616767686961636369616e74652d6d6f7274652d64656c6c756f6d6f2d6772697a7a6c792d736272616e61742d4c2d7272376165702e6a706567)](https://camo.githubusercontent.com/c1cb98ee8dbe1b270ac6e3bfa0a2dbfdaedb6cb4/687474703a2f2f6d322e7061706572626c6f672e636f6d2f692f37342f3734363132312f6c616767686961636369616e74652d6d6f7274652d64656c6c756f6d6f2d6772697a7a6c792d736272616e61742d4c2d7272376165702e6a706567)

## Objectives

1. Use the `CREATE TABLE` command to create a new table with various data types
2. Use the `INSERT INTO` command to insert data (i.e. rows) into a database table
3. Use the `SELECT` command with various functions and modifiers to write queries

## Lab Structure

This lab might seem a bit different than what you've seen before. Take a look at the file structure:

```
├── Gemfile
├── README.md
├── bin
│   ├── environment.rb # requires bundler and files
│   ├── run.rb # instantiates the SQLRunner class in the below file
│   └── sql_runner.rb # holds a class that handles executing your .sql files
├── lib
│   ├── create.sql # where you create your schema
│   └── insert.sql # where you insert your data
│   └── seed.sql # data for in-memory database
│   └── sql_queries.rb # where you write your sql queries
└── spec # all the specs
    ├── create_spec.rb # this tests your create.sql file
    ├── insert_spec.rb # this tests your insert.sql file
    ├── select_spec.rb # this tests the queries you write in this file
    └── spec_helper.rb
```

### A Note on Testing

Let's briefly go over what is happening in the `before` block that our tests will be using.

```
before do
  @db = SQLite3::Database.new(':memory:')
  @sql_runner = SQLRunner.new(@db)
  @sql_runner.execute_create_file
end
```

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](https://www.sqlite.org/inmemorydb.html).

Next, a new `SqlRunner` class is created. The `SqlRunner` class lives in your `bin` directory and was created to help connect to the database.

## Part 1: `CREATE TABLE`

Get the tests in `spec/create_spec.rb` to pass. Your `CREATE` statement should look something like this:

```
CREATE TABLE bears (
  //columns here
);
```

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](https://www.sqlite.org/datatype3.html) to determine what your insert values are going to be. Be sure to pay attention to how booleans are expressed in SQLite3.

## Part 2: `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)

## Part 3: `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.

## Resources

[SQL Datatypes](https://www.sqlite.org/datatype3.html) [SQL GROUP BY](https://www.sqlite.org/lang_select.html#resultset)

View [SQL Bear Organizer](https://github.com/learn-co-curriculum/SQL-bear-organizer-lab) on Learn.co and start learning to code for free.