<commit_before>CREATE DATABASE Music DEFAULT CHARACTER SET utf8;
USE Music;

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

CREATE TABLE Album (album_id INTEGER NOT NULL AUTO_INCREMENT KEY, title VARCHAR(255), artist_id INTEGER, 
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 KEY, name VARCHAR(255)) ENGINE = InnoDB;

CREATE TABLE Track (track_id INTEGER NOT NULL AUTO_INCREMENT KEY, title VARCHAR(255), len INTEGER, rating INTEGER, count INTEGER, album_id INTEGER, genre_id INTEGER,
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 ('<NAME>');
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 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 ON Track.genre_id = Genre.genre_id and Track.album_id = Album.album_id JOIN Artist ON Album.artist_id = Artist.artist_id;

DELETE FROM Genre WHERE name = 'Metal'

DROP TABLE Track; DROP TABLE Album; DROP TABLE Genre; DROP TABLE Artist;
<commit_msg>Get back the old handout<commit_after>CREATE DATABASE People;

USE People;

CREATE TABLE Users (
  name VARCHAR(128), 
  email VARCHAR(128)
)

DESCRIBE Users;

INSERT INTO Users (name, email) VALUES ('Ted', '<EMAIL>')

DELETE FROM Users WHERE email='<EMAIL>'

UPDATE Users SET name=Charles WHERE email='<EMAIL>'

SELECT * FROM Users

SELECT * FROM Users WHERE email='<EMAIL>'

SELECT * FROM Users ORDER BY email

SELECT * FROM Users WHERE name LIKE '%e%'

SELECT * FROM Users ORDER BY email DESC LIMIT 2;

SELECT * FROM Users ORDER BY email LIMIT 1,2;

CREATE TABLE Users (
  user_id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY, 
  name VARCHAR(128), 
  email VARCHAR(128),
  INDEX ( name )
);

ALTER TABLE Users ADD INDEX ( name )
{'fraction_non_alphanumeric': 0.09752650176678446, 'fraction_numerical': 0.02049469964664311, 'mean_word_length': 5.140997830802603, 'pattern_counts': {'":': 0, '<': 8, '<?xml version=': 0, '>': 8, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4718460', 'n_tokens_mistral': 1054, 'n_tokens_neox': 920, 'n_words': 378}