CREATE DATABASE Music DEFAULT CHARACTER SET utf8mb4;

USE Music;  (Command line only)

CREATE TABLE Artist (
  artist_id INTEGER NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255),
  PRIMARY KEY(artist_id)
) ENGINE = InnoDB;

CREATE TABLE Album (
  album_id INTEGER NOT NULL AUTO_INCREMENT,
  title VARCHAR(255),
  artist_id INTEGER,

  PRIMARY KEY(album_id),
  INDEX USING BTREE (title),

  CONSTRAINT FOREIGN KEY (artist_id)
    REFERENCES Artist (artist_id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB;

CREATE TABLE Genre (
  genre_id INTEGER NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255),
  PRIMARY KEY(genre_id)
) ENGINE = InnoDB;

CREATE TABLE Track (
  track_id INTEGER NOT NULL AUTO_INCREMENT,
  title VARCHAR(255),
  len INTEGER,
  rating INTEGER,
  `count` INTEGER,
  album_id INTEGER,
  genre_id INTEGER,

  PRIMARY KEY(track_id),
  INDEX USING BTREE (title),

  CONSTRAINT FOREIGN KEY (album_id) REFERENCES Album (album_id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FOREIGN KEY (genre_id) REFERENCES Genre (genre_id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB;


INSERT INTO Artist (`name`) VALUES ('Led Zepplin');
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.artist_id;

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

SELECT Track.title, Track.genre_id, Genre.genre_id, Genre.name 
    FROM Track JOIN Genre;

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

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

DELETE FROM Genre WHERE `name` = 'Metal';

DROP TABLE Track; DROP TABLE Album; DROP TABLE Genre; DROP TABLE Artist;

Fresh Database...

CREATE DATABASE Learning DEFAULT CHARACTER SET utf8mb4;

USE Learning;   (Command line only)

CREATE TABLE `Account` (
    account_id  INTEGER NOT NULL AUTO_INCREMENT,
    email       VARCHAR(128) UNIQUE,
    `name`        VARCHAR(128),
    PRIMARY KEY(account_id)
) ENGINE=InnoDB CHARACTER SET=utf8mb4;

CREATE TABLE Course (
    course_id     INTEGER NOT NULL AUTO_INCREMENT,
    title         VARCHAR(128) UNIQUE,
    PRIMARY KEY(course_id)
) ENGINE=InnoDB CHARACTER SET=utf8mb4;

CREATE TABLE Member (
    account_id    INTEGER,
    course_id     INTEGER,
    role          INTEGER,

    CONSTRAINT FOREIGN KEY (account_id) REFERENCES `Account` (account_id)
      ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY (course_id) REFERENCES Course (course_id)
       ON DELETE CASCADE ON UPDATE CASCADE,

    PRIMARY KEY (account_id, course_id)
) ENGINE=InnoDB CHARACTER SET=utf8mb4;

INSERT INTO `Account` (`name`, email) VALUES ('Jane', 'jane@tsugi.org');
INSERT INTO `Account` (`name`, email) VALUES ('Ed', 'ed@tsugi.org');
INSERT INTO `Account` (`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 (account_id, course_id, role) VALUES (1, 1, 1);
INSERT INTO Member (account_id, course_id, role) VALUES (2, 1, 0);
INSERT INTO Member (account_id, course_id, role) VALUES (3, 1, 0);

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

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

SELECT `Account`.name, Member.role, Course.title
  FROM `Account` JOIN Member JOIN Course
  ON Member.account_id = `Account`.account_id
    AND Member.course_id = Course.course_id
  ORDER BY Course.title, Member.role DESC, `Account`.name;