Project: Music Database

CREATE TABLE IF NOT EXISTS thumbnail
(
imageID INT NOT NULL AUTO_INCREMENT,
imageName VARCHAR(150) NOT NULL,
fileType VARCHAR(50) NOT NULL,
imageSize VARCHAR(50),
fileLocation VARCHAR(150) NOT NULL,
PRIMARY KEY (imageID)
);

CREATE TABLE IF NOT EXISTS genre
(
genreID INT NOT NULL AUTO_INCREMENT,
genreName VARCHAR(100) NOT NULL,
PRIMARY KEY (genreID)
);

CREATE TABLE IF NOT EXISTS language
(
languageCode CHAR(3) NOT NULL,
languageName VARCHAR(50) NOT NULL,
script VARCHAR(50),
PRIMARY KEY (languageCode)
);

CREATE TABLE IF NOT EXISTS artist
(
artistID INT NOT NULL AUTO_INCREMENT,
artistName VARCHAR(150) NOT NULL,
imageID INT,
PRIMARY KEY (artistID),
FOREIGN KEY (imageID) REFERENCES thumbnail(imageID)
);

CREATE TABLE IF NOT EXISTS album
(
albumID INT NOT NULL AUTO_INCREMENT,
albumName VARCHAR(150) NOT NULL,
yearReleased YEAR,
imageID INT,
PRIMARY KEY (albumID),
FOREIGN KEY (imageID) REFERENCES thumbnail(imageID)
);

CREATE TABLE IF NOT EXISTS song
(
songID INT NOT NULL AUTO_INCREMENT,
songName VARCHAR(150) NOT NULL,
yearReleased YEAR,
albumID INT,
imageID INT,
PRIMARY KEY (songID),
FOREIGN KEY (albumID) REFERENCES album(albumID),
FOREIGN KEY (imageID) REFERENCES thumbnail(imageID)
);

CREATE TABLE IF NOT EXISTS playlist
(
playlistID INT NOT NULL AUTO_INCREMENT,
playlistName VARCHAR(150) NOT NULL,
dateCreated DATE NOT NULL,
imageID INT,
PRIMARY KEY (playlistID),
FOREIGN KEY (imageID) REFERENCES thumbnail(imageID)
);

CREATE TABLE IF NOT EXISTS song_artist
(
artistID INT NOT NULL,
songID INT NOT NULL,
PRIMARY KEY (artistID, songID),
FOREIGN KEY (artistID) REFERENCES artist(artistID),
FOREIGN KEY (songID) REFERENCES song(songID)
);

CREATE TABLE IF NOT EXISTS artist_genre
(
genreID INT NOT NULL,
artistID INT NOT NULL,
PRIMARY KEY (genreID, artistID),
FOREIGN KEY (genreID) REFERENCES genre(genreID),
FOREIGN KEY (artistID) REFERENCES artist(artistID)
);

CREATE TABLE IF NOT EXISTS album_genre
(
genreID INT NOT NULL,
albumID INT NOT NULL,
PRIMARY KEY (genreID, albumID),
FOREIGN KEY (genreID) REFERENCES genre(genreID),
FOREIGN KEY (albumID) REFERENCES album(albumID)
);

CREATE TABLE IF NOT EXISTS song_playlist
(
dateAdded DATE NOT NULL,
songID INT NOT NULL,
playlistID INT NOT NULL,
PRIMARY KEY (songID, playlistID),
FOREIGN KEY (songID) REFERENCES song(songID),
FOREIGN KEY (playlistID) REFERENCES playlist(playlistID)
);

CREATE TABLE IF NOT EXISTS artist_playlist
(
dateAdded DATE NOT NULL,
artistID INT NOT NULL,
playlistID INT NOT NULL,
PRIMARY KEY (artistID, playlistID),
FOREIGN KEY (artistID) REFERENCES artist(artistID),
FOREIGN KEY (playlistID) REFERENCES playlist(playlistID)
);

CREATE TABLE IF NOT EXISTS album_playlist
(
dateAdded DATE NOT NULL,
albumID INT NOT NULL,
playlistID INT NOT NULL,
PRIMARY KEY (albumID, playlistID),
FOREIGN KEY (albumID) REFERENCES album(albumID),
FOREIGN KEY (playlistID) REFERENCES playlist(playlistID)
);

CREATE TABLE IF NOT EXISTS album_artist
(
artistID INT NOT NULL,
albumID INT NOT NULL,
PRIMARY KEY (artistID, albumID),
FOREIGN KEY (artistID) REFERENCES artist(artistID),
FOREIGN KEY (albumID) REFERENCES album(albumID)
);

CREATE TABLE IF NOT EXISTS album_language
(
albumID INT NOT NULL,
languageCode CHAR(3) NOT NULL,
PRIMARY KEY (albumID, languageCode),
FOREIGN KEY (albumID) REFERENCES album(albumID),
FOREIGN KEY (languageCode) REFERENCES language(languageCode)
);

CREATE TABLE IF NOT EXISTS song_language
(
languageCode CHAR(3) NOT NULL,
songID INT NOT NULL,
PRIMARY KEY (languageCode, songID),
FOREIGN KEY (languageCode) REFERENCES language(languageCode),
FOREIGN KEY (songID) REFERENCES song(songID)
);

INSERT INTO thumbnail (fileType, imageSize, fileLocation, imageName)
VALUES ('JPEG', '316 x 316', '/databaseimg', 'santigoldAC'),
('PNG', '220 x 220', '/databaseimg', 'stereotypeaAC'),
('JPEG', '300 x 300', '/databaseimg', 'magmaAC'),
('PNG', '520 x 529', '/databaseimg', 'dummyAC'),
('JPEG', '300 x 300', '/databaseimg', 'veranosaudadeAC'),
('JPEG', '300 x 300', '/databaseimg', 'пасахAC'),
('JPEG', '340 x 270', '/databaseimg', 'sinmiedoAC'),
('PNG', '300 x 265', '/databaseimg', 'dioramaAC'),
('JPEG', '300 x 300', '/databaseimg', 'яблонныйсадAC'),
('JPEG', '300 x 265', '/databaseimg', 'interkomkomindAC'),
('JPEG', '700 x 500', '/databaseimg', 'santigoldAP'),
('PNG', '300 x 350', '/databaseimg', 'cibomattoAP'),
('PNG', '1000 x 800', '/databaseimg', 'gojiraAP'),
('JPEG', '150 x 150', '/databaseimg', 'portisheadAP'),
('PNG', '800 x 1000', '/databaseimg', 'judelineAP'),
('JPEG', '576 x 316', '/databaseimg', 'shortparisAP'),
('JPEG', '400 x 200', '/databaseimg', 'kaliuchisAP'),
('WEBP', '600 x 700', '/databaseimg', 'kenshiyonezuAP'),
('JPEG', '316 x 300', '/databaseimg', 'nephewAP'),
('JPEG', '700 x 600', '/databaseimg', 'safetypleaceAP'),
('PNG', '300 x 475', '/databaseimg', 'whenyoucomedownSP'),
('PNG', '750 x 1000', '/databaseimg', 'ВпервыйразSP'),
('JPEG', '100 x 100', '/databaseimg', 'dumb4allSP'),
('JPEG', '250 x 250', '/databaseimg', 'callmeshadowremixSP'),
('PNG', '1000 x 1000', '/databaseimg', 'boyznoizeAP'),
('PNG', '500 x 500', '/databaseimg', 'akinijingAP'),
('PNG', '540 x 400', '/databaseimg', 'msgloomAP'),
('JPEG', '500 x 500', '/databaseimg', 'ilyagadaevAP'),
('JPEG', '300 x 300', '/databaseimg', 'italoneAP'),
('JPEG', '200 x 200', '/databaseimg', 'pasalieuAP'),
('PNG', '300 x 400', '/databaseimg', 'amaiaAP'),
('PNG', '250 x 250', '/databaseimg', 'dellafuenteAP'),
('JPEG', '1000 x 1000', '/databaseimg', 'segabodegaAP'),
('JPEG', '700 x 700', '/databaseimg', 'mcmorenaAP');

INSERT INTO genre (genreName)
VALUES ('triphop'),
('metal'),
('experimental'),
('electronica'),
('rock'),
('hip-hop'),
('pop'),
('reggaeton'),
('punk');

INSERT INTO language (languageCode, languageName, script)
VALUES ('jpn', 'Japanese', 'kana'),
('dan', 'Danish', 'danish latin'),
('rus', 'Russian', 'cyrillic'),
('por', 'Portuguese', 'portuguese latin'),
('zho', 'Chinese', 'hanzi'),
('eng', 'English', 'latin'),
('spa', 'Spanish', 'spanish latin'),
('ara', 'Arabic', 'arabic script'),
('fra', 'French', 'french latin'),
('deu', 'German', 'german latin');

INSERT INTO artist (artistName, imageID)
VALUES ('santigold', 11),
('cibo matto', 12),
('gojira', 13),
('portishead', 14),
('judeline', 15),
('shortparis', 16),
('kali uchis', 17),
('kenshi yonezu', 18),
('nephew', 19),
('safetypleace', 20),
('akini jing', 26),
('boys noize', 25),
('ms* gloom', 27),
('ilya gadaev', 28),
('it alone', 29),
('pasalieu', 30),
('amaia', 31),
('della fuente', 32),
('sega bodega', 33),
('mcmorena', 34);

INSERT INTO album (albumName, yearReleased, imageID)
VALUES ('santigold', 2008, 1),
('stereo type a', 1999, 2),
('magma', 2016, 3),
('dummy', 1994, 4),
('verano saudade', '2025', 5),
('пасха', 2019, 6),
('яблонный сад', 2021, 9),
('sin miedo (del amor y otros demonios)', 2020, 7),
('diorama', 2012, 8),
('interkom kom ind', 2006, 10);

INSERT INTO song (songName, yearReleased, imageID, albumID)
VALUES ("you'll find a way", '2008', 1, 1),
('sunday, pt. 1', 1999, 2, 2),
('shooting star', 2016, 3, 3),
('sour times', 1994, 4, 4),
('mi breve juventud', 2025, 5, 5),
('двадцать', 2021, 9, 7),
('туту', 2019, 6, 6),
('новокузнецк', 2019, 6, 6),
('la luna enamorada', 2020, 7, 8),
('駄菓子屋商売 - Dagashiyashoubai', 2012, 8, 9),
('igen & igen &', 2006, 10, 10),
('if you come down', 2024, 21, NULL),
('call me shadow (boys noize remix)', 2024, 24, NULL),
('dumb 4 all', 2026, 23, NULL),
('в первый раз', 2023, 22, NULL);

INSERT INTO playlist (playlistName, dateCreated, imageID)
VALUES ('electronic music', '2024-08-08', 25),
('not electronic music?', '2024-08-10', 13);

INSERT INTO album_genre (albumID, genreID)
VALUES (1, 3),
(1, 7),
(2, 1),
(2, 3),
(2, 4),
(3, 2),
(4, 1),
(4, 4),
(5, 4),
(5, 7),
(6, 3),
(6, 4),
(7, 3),
(7, 4),
(7, 9),
(8, 7),
(8, 8),
(8, 10),
(9, 3),
(9, 4),
(9, 7),
(10, 5);

INSERT INTO artist_genre (artistID, genreID)
VALUES (1, 3),
(1, 7),
(2, 1),
(2, 4),
(3, 2),
(4, 1),
(4, 4),
(5, 4),
(5, 7),
(5, 8),
(6, 3),
(6, 4),
(6, 9),
(7, 7),
(7, 8),
(7, 10),
(8, 3),
(8, 4),
(8, 7),
(9, 5),
(10, 4),
(11, 7),
(12, 4),
(12, 9),
(12, 6),
(13, 3),
(13, 4),
(13, 7),
(14, 4),
(15, 4);

INSERT INTO song_language (songID, languageCode)
VALUES (1, 'eng'),
(2, 'eng'),
(3, 'eng'),
(4, 'eng'),
(5, 'spa'),
(6, 'rus'),
(7, 'rus'),
(8, 'rus'),
(9, 'spa'),
(10, 'jpn'),
(11, 'dan'),
(12, 'rus'),
(13, 'zho'),
(13, 'eng'),
(14, 'eng'),
(15, 'rus');

INSERT INTO album_language (albumID, languageCode)
VALUES (1, 'eng'),
(2, 'eng'),
(3, 'eng'),
(4, 'eng'),
(5, 'spa'),
(5, 'por'),
(5, 'eng'),
(6, 'rus'),
(6, 'fra'),
(6, 'eng'),
(7, 'rus'),
(8, 'spa'),
(9, 'jpn'),
(10, 'dan');

INSERT INTO song_playlist (dateAdded, songID, playlistID)
VALUES ('2023-08-08', 1, 1),
('2023-05-10', 1, 2),
('2023-10-10', 2, 1),
('2023-10-10', 3, 2),
('2023-10-10', 4, 1),
('2023-10-10', 5, 1),
('2023-10-12', 6, 2),
('2023-10-12', 7, 1),
('2023-10-12', 8, 1),
('2023-10-12', 9, 2),
('2023-10-13', 10, 1),
('2023-10-13', 10, 2),
('2023-10-14', 11, 1),
('2023-10-14', 12, 1),
('2023-10-20', 13, 1),
('2024-05-02', 14, 1),
('2024-05-05', 15, 1);

INSERT INTO artist_playlist (dateAdded, artistID, playlistID)
VALUES ('2023-08-08', 1, 1),
('2023-08-08', 1, 2),
('2023-10-10', 2, 1),
('2023-10-10', 3, 2),
('2023-10-10', 4, 1),
('2023-10-10', 5, 1),
('2023-10-12', 6, 1),
('2023-10-12', 6, 2),
('2023-10-12', 7, 2),
('2023-10-13', 8, 1),
('2023-10-13', 8, 2),
('2023-10-14', 9, 1),
('2023-10-14', 10, 1),
('2023-10-20', 11, 1),
('2023-10-20', 12, 1),
('2024-05-02', 13, 1),
('2023-10-14', 14, 1),
('2023-10-14', 15, 1),
('2023-10-10', 16, 1);

INSERT INTO album_playlist (dateAdded, albumID, playlistID)
VALUES ('2023-08-08', 1, 1),
('2023-08-08', 1, 2),
('2023-10-10', 2, 1),
('2023-10-10', 3, 2),
('2023-10-10', 4, 1),
('2023-10-10', 5, 1),
('2023-10-12', 6, 1),
('2023-10-12', 7, 2),
('2023-10-12', 8, 2),
('2023-10-13', 9, 1),
('2023-10-13', 9, 2),
('2023-10-14', 10, 1);

INSERT INTO song_artist (songID, artistID)
VALUES (1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5),
(6, 6),
(7, 6),
(8, 6),
(9, 7),
(10, 8),
(11, 9),
(12, 10),
(12, 14),
(12, 15),
(13, 11),
(13, 12),
(14, 13),
(15, 6);

INSERT INTO album_artist (albumID, artistID)
VALUES (1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5),
(5, 16),
(5, 17),
(5, 18),
(5, 19),
(5, 20),
(6, 6),
(7, 6),
(8, 7),
(9, 8),
(10, 9);



/* 2 action queries (e.g., create, alter, update, delete): */

ALTER TABLE thumbnail
RENAME image;

ALTER TABLE language
DROP COLUMN script;

/* 2 join queries (e.g., inner, left, right, etc.): */

SELECT song.songName, playlist.playlistName, song_playlist.dateAdded
FROM song_playlist
JOIN playlist ON playlist.playlistID = song_playlist.playlistID
JOIN song ON song.songID = song_playlist.songID;

SELECT songName, song.yearReleased, albumName, album.yearReleased
FROM song
LEFT JOIN album ON song.albumID = album.albumID
ORDER by song.yearReleased, album.yearReleased;

/* 2 queries using a subquery */

SELECT language.languageCode AS language_of, album.albumName AS earliest_album
FROM album_language
JOIN album ON album.albumID = album_language.albumID
JOIN language ON language.languageCode = album_language.languageCode
WHERE album.yearReleased =
(SELECT MIN(yearReleased) FROM album);

SELECT AVG(songs_in_playlist)
FROM (
SELECT COUNT(song_playlist.songID) AS songs_in_playlist
FROM song
JOIN song_playlist ON song.songID = song_playlist.songID
JOIN playlist ON song_playlist.playlistID = playlist.playlistID
GROUP BY playlist.playlistID
ORDER BY songs_in_playlist DESC)
AS avg_songs_in_playlist;

/* 2 queries using aggregate functions (sum, avg, count, etc.) */

SELECT album.albumName, artist.artistName, yearReleased AS earliest_year
FROM album_artist
JOIN album ON album.albumID = album_artist.albumID
JOIN artist ON artist.artistID = album_artist.artistID
WHERE album.yearReleased = (SELECT MIN(yearReleased) FROM album)
ORDER BY yearReleased;

SELECT genre.genreName, COUNT(album_genre.albumID) AS album_count
FROM genre
JOIN album_genre ON genre.genreID = album_genre.genreID
JOIN album ON album_genre.albumID = album.albumID
GROUP BY genre.genreID, genre.genreName
ORDER BY album_count DESC;

/* 1 query using a simple function */

SELECT REPLACE(artistName, 'ms*', 'joon')
FROM artist where artistID = 13;

/* 1 query of your choosing */

SELECT * FROM artist
WHERE artistName LIKE '% %';