# 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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://certil-remy.gitbook.io/learn/sql/untitled-9.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.