demo.txt

#=====================
#   view all songs
#=====================

use music_db;

select Song.Song_Name, Song.song_length, album.album_genre, album.album_name, artist.artist_name
from Song
inner join album on Song.album_id = album.album_id
inner join artist on artist.artist_id = album.artist_id;

#=====================
#   single artits songs
#=====================

use music_db;

select song.Song_Name, song.song_length, album.album_genre, album.album_name
from song
inner join album on song.album_id=album.album_id
inner join artist_song on artist_song.song_id = song.song_id
where artist_song.artist_id = 10;

#=====================
#   single artists albums
#=====================

use music_db;

select album.album_name, album.album_release_date, album.album_genre, album.album_length
from album
where album.artist_id = 10;

#=====================
#   songs in a single album
#=====================

use music_db;

select song.song_name, song.song_length, artist.artist_name
from song
inner join artist_song on artist_song.song_id = song.song_id
inner join artist on artist_song.artist_id = artist.artist_id
where song.album_id in (
select album_id
from song
where album_id = 30);

#=====================
#   ***log in as a user***????
#=====================


#=====================
#   users library view
#=====================

#  etl12003_db
#  par13002_db
#  zrb11002_db

use etl12003_db;

select music_db.song.song_name, music_db.song.song_length, userlibrary.rating, music_db.artist.artist_name, music_db.album.album_name
from USERLIBRARY
inner join music_db.song 
	on USERLIBRARY.song_id = music_db.song.song_id
inner join music_db.artist_song
	on music_db.artist_song.song_id = song.song_id
inner join music_db.artist
	on music_db.artist_song.artist_id = artist.artist_id
inner join music_db.album
	on song.album_id = album.album_id;

#=====================
#   user playlist view
#=====================

#  etl12003_db
#  par13002_db
#  zrb11002_db

use etl12003_db;

select music_db.song.song_name, music_db.song.song_length, music_db.artist.artist_name, music_db.album.album_name, userlibrary.rating
from playlist_song
left outer join userlibrary 
	on userlibrary.song_id = playlist_song.song_id
inner join music_db.song
	on music_db.song.song_id = playlist_song.song_id
inner join music_db.artist_song
	on music_db.artist_song.song_id = song.song_id
inner join music_db.artist
	on music_db.artist_song.artist_id = artist.artist_id
inner join music_db.album
	on music_db.song.album_id = music_db.album.album_id
where playlist_song.Playlist_ID 
in(
	select playlist_id 
    from playlist_song 
    where playlist_id = 867
); 

#=====================
#   get names of playlists
#=====================

#  etl12003_db
#  par13002_db
#  zrb11002_db

use zrb11002_db;

select playlist_name
from playlist_name;

#=====================
#   add song to user library
#=====================

#  etl12003_db
#  par13002_db
#  zrb11002_db

use etl12003_db;

insert into USERLIBRARY (Rating, Song_ID) value (5, 134);

#=====================
#   remove song from user library
#=====================

#  etl12003_db
#  par13002_db
#  zrb11002_db

use etl12003_db;

delete from USERLIBRARY 
where Song_ID = 134;

#=====================
#   add song to user playlist
#=====================

#  etl12003_db
#  par13002_db
#  zrb11002_db

use etl12003_db;

insert into Playlist_Song (Playlist_ID, Song_ID) value (867, 130);