-- Python for Everybody Database Handout

-- https://www.py4e.com/lectures3/Pythonlearn-15-Database-Handout.sql

-- Download and Install: http://sqlitebrowser.org/

-- Single Table SQL

CREATE TABLE "Users"
(
    "name"  TEXT,
    "email" TEXT
);

INSERT INTO Users (name, email)
VALUES ('Chuck', 'csev@umich.edu');
INSERT INTO Users (name, email)
VALUES ('Colleen', 'cvl@umich.edu');
INSERT INTO Users (name, email)
VALUES ('Ted', 'ted@umich.edu');
INSERT INTO Users (name, email)
VALUES ('Sally', 'a1@umich.edu');
INSERT INTO Users (name, email)
VALUES ('Ted', 'ted@umich.edu');
INSERT INTO Users (name, email)
VALUES ('Kristen', 'kf@umich.edu');

DELETE
FROM Users
WHERE email = 'ted@umich.edu';

UPDATE Users
SET name='Charles'
WHERE email = 'csev@umich.edu';

SELECT *
FROM Users;

SELECT *
FROM Users
WHERE email = 'csev@umich.edu';

SELECT *
FROM Users
ORDER BY email;

SELECT *
FROM Users
ORDER BY name DESC;

-- Multi-Table SQL:

CREATE TABLE "Artist"
(
    "id"   INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
    "name" TEXT
);

CREATE TABLE "Album"
(
    "id"      INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
    artist_id INTEGER,
    "title"   TEXT
);

CREATE TABLE "Genre"
(
    "id"   INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
    "name" TEXT
);

CREATE TABLE "Track"
(
    "id"     INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
    album_id INTEGER,
    genre_id INTEGER,
    len      INTEGER,
    rating   INTEGER,
    "title"  TEXT,
    "count"  INTEGER
);
INSERT INTO Artist (name) VALUES ('Led Zeppelin');
INSERT INTO Artist (name) VALUES ('AC/DC');

INSERT INTO Genre (name) VALUES ('Rock');
INSERT INTO Genre (name)
VALUES ('Metal');

INSERT INTO Album (title, artist_id)
VALUES ('Who Made Who', 2);
INSERT INTO Album (title, artist_id)
VALUES ('IV', 1);

INSERT INTO Track (title, rating, len, count, album_id, genre_id)
VALUES ('Black Dog', 5, 297, 0, 2, 1);
INSERT INTO Track (title, rating, len, count, album_id, genre_id)
VALUES ('Stairway', 5, 482, 0, 2, 1);
INSERT INTO Track (title, rating, len, count, album_id, genre_id)
VALUES ('About to Rock', 5, 313, 0, 1, 2);
INSERT INTO Track (title, rating, len, count, album_id, genre_id)
VALUES ('Who Made Who', 5, 207, 0, 1, 2);

SELECT Album.title, Artist.name
FROM Album
         JOIN Artist
              ON Album.artist_id = Artist.id;

SELECT Album.title, Album.artist_id, Artist.id, Artist.name
FROM Album
         JOIN Artist ON Album.artist_id = Artist.id;

-- Return all possible combinations
SELECT Track.title, Track.genre_id, Genre.id, Genre.name
FROM Track
         JOIN Genre;

SELECT Track.title, Genre.name
FROM Track
         JOIN Genre
              ON Track.genre_id = Genre.id;

SELECT Track.title, Artist.name, Album.title, Genre.name
FROM Track
         JOIN Genre
         JOIN Album
         JOIN Artist
              ON Track.genre_id = Genre.id AND Track.album_id = Album.id
                  AND Album.artist_id = Artist.id;


-- Many-Many Relationship

CREATE TABLE User
(
    id    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name  TEXT UNIQUE,
    email TEXT
);

CREATE TABLE Course
(
    id    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    title TEXT UNIQUE
);

CREATE TABLE Member
(
    user_id   INTEGER,
    course_id INTEGER,
    role      INTEGER,
    PRIMARY KEY (user_id, course_id)
);

INSERT INTO User (name, email)
VALUES ('Jane', 'jane@tsugi.org');
INSERT INTO User (name, email)
VALUES ('Ed', 'ed@tsugi.org');
INSERT INTO User (name, email)
VALUES ('Sue', 'sue@tsugi.org');

INSERT INTO Course (title)
VALUES ('Python');
INSERT INTO Course (title)
VALUES ('SQL');
INSERT INTO Course (title)
VALUES ('PHP');

INSERT INTO Member (user_id, course_id, role)
VALUES (1, 1, 1);
INSERT INTO Member (user_id, course_id, role)
VALUES (2, 1, 0);
INSERT INTO Member (user_id, course_id, role)
VALUES (3, 1, 0);

INSERT INTO Member (user_id, course_id, role)
VALUES (1, 2, 0);
INSERT INTO Member (user_id, course_id, role)
VALUES (2, 2, 1);

INSERT INTO Member (user_id, course_id, role)
VALUES (2, 3, 1);
INSERT INTO Member (user_id, course_id, role)
VALUES (3, 3, 0);

SELECT User.name, Member.role, Course.title
FROM User
         JOIN Member
         JOIN Course
              ON Member.user_id = User.id AND Member.course_id = Course.id
ORDER BY Course.title, Member.role DESC, User.name;
