Day2_SQLTutorial

-- To source this file in a SQLite session run
--
-- sqlite> .read <filename>

-- ==========================================================================================================
-- SETUP
-- ==========================================================================================================

-- Most databases will have FOREIGN KEY support enabled by default. Not so with SQLite.
--
PRAGMA foreign_keys;
PRAGMA foreign_keys = ON;
--
-- Find out more about PRAMGA at https://www.sqlite.org/pragma.html.

-- Make that output more user friendly.
--
.mode column
.headers on

-- Let's find out about all of the special dot commands.
--
.help

-- ==========================================================================================================
-- CREATE TABLE
-- ==========================================================================================================

-- Data Types in SQLite: https://www.sqlite.org/datatype3.html

CREATE TABLE tb_horses
(
    horse_id          INTEGER PRIMARY KEY AUTOINCREMENT,
    horse_name        VARCHAR(128)
);

.separator " "

.import horse-racing-horses.csv tb_horses

CREATE TABLE tb_locations
(
    location_id       INTEGER PRIMARY KEY AUTOINCREMENT,
    location_name     VARCHAR(128)
);

.import horse-racing-locations.csv tb_locations

CREATE TABLE tb_bookmakers
(
    bookmaker_id      INTEGER PRIMARY KEY AUTOINCREMENT,
    bookmaker_name    VARCHAR(128)
);

.import horse-racing-bookmakers.csv tb_bookmakers

CREATE TABLE tb_races
(
    race_id           INTEGER PRIMARY KEY AUTOINCREMENT,
    race_time         CHARACTER(5),
    race_name         VARCHAR(256)
);

.import horse-racing-races.csv tb_races

CREATE TABLE tb_odds
(
    odds              REAL,
    horse_id          INTEGER NOT NULL,
    location_id       INTEGER NOT NULL,
    bookmaker_id      INTEGER NOT NULL,
    race_id           INTEGER NOT NULL,
    FOREIGN KEY (horse_id) REFERENCES tb_horses(horse_id) -- ,
    FOREIGN KEY (location_id) REFERENCES tb_locations(location_id),
    FOREIGN KEY (bookmaker_id) REFERENCES tb_bookmakers(bookmaker_id),
    FOREIGN KEY (race_id) REFERENCES tb_races(race_id)
);

.import horse-racing-odds.csv tb_odds

-- Get a list of tables.
--
.tables
--
-- Checking the structure of the table. Normally you would do this with
--
-- > DESCRIBE tb_Person
--
-- but SQLite is a little different. There are two options.
--
PRAGMA table_info(tb_odds);
.schema tb_odds
.schema
--
-- Note that these need to be run in the terminal client since they are not actually SQL.

-- ==========================================================================================================
-- SIMPLE QUERIES
-- ==========================================================================================================

SELECT * FROM tb_horses;

SELECT * FROM tb_races WHERE race_time = '15:00';

-- Ordering.
--
SELECT * FROM tb_races ORDER BY race_time;

-- ==========================================================================================================
-- TABLE JOINS
-- ==========================================================================================================

SELECT *
FROM tb_odds
INNER JOIN tb_horses
ON tb_odds.horse_id = tb_horses.horse_id
WHERE horse_name = 'Kazanan';

SELECT horse_name, AVG(odds)
FROM tb_odds
INNER JOIN tb_horses
ON tb_odds.horse_id = tb_horses.horse_id
GROUP BY horse_name;