---
# System prepended metadata

title: SQL Tutorial

---

# SQL Tutorial

Rought ideas for tutorial. Some thoughts on this... track coffee shops.

* Show step by step approach
* Don't worry about getting it perfect to start



Working repo: https://github.com/cwxstat/sql-tutorial

```bash=
# Create database coffee.db

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> 
```

Create a simple database


```bash=
sqlite> create table coffee (shop text, rating int, price int, timeEnter DATE);
```

Create a trigger to update timeEnter, which updates on a new record.

```bash=
sqlite> CREATE TRIGGER insert_coffee_timeEnter AFTER  INSERT ON coffee
BEGIN

UPDATE coffee SET timeEnter = DATETIME('NOW')
         WHERE rowid = new.rowid;
END;
```

Next... let's see what we created. Run the `.schema` command.

```bash=
sqlite> .schema
CREATE TABLE coffee (shop text, rating int, price int, timeEnter DATE);
CREATE TRIGGER insert_coffee_timeEnter AFTER  INSERT ON coffee
BEGIN

UPDATE coffee SET timeEnter = DATETIME('NOW')
         WHERE rowid = new.rowid;
END;
sqlite> 
```

So, now add a test record.

```bash=
sqlite> insert into coffee (shop,rating,price) values ('Zoe`s Shop',5,10);
```

Let's see if the trigger works... select the record

```bash=
sqlite> select * from coffee;
Zoe`s Shop|5|10|2022-03-29 15:23:50

```

Quite Sqlite

```bash=
sqlite> .q
```

Load data from .csv

Assume we have the following csv: `coffee.csv`

```bash=
cat coffee.csv 
Mike's Coffee, 0, 20
Sue's  Coffee, 3, 8
Tom's  Coffee, 4, 7
```

Which can be download via curl

```bash=
curl https://storage.googleapis.com/montco-stats/coffee.csv -o coffee.csv
```

Example of how to load the data. Ignore the errors.

```bash=
sqlite3 coffee.db 
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .mode csv
sqlite> .import ./coffee.csv coffee
./coffee.csv:1: expected 4 columns but found 3 - filling the rest with NULL
./coffee.csv:2: expected 4 columns but found 3 - filling the rest with NULL
./coffee.csv:3: expected 4 columns but found 3 - filling the rest with NULL
sqlite> select * from coffee;
"Zoe`s Shop",5,10,"2022-03-29 15:23:50"
"Mike's Coffee",0,20,"2022-03-29 15:28:36"
"Sue's  Coffee",3,8,"2022-03-29 15:28:36"
"Tom's  Coffee",4,7,"2022-03-29 15:28:36"
sqlite> 
```