Untitled

CREATE TABLE tbl_artists (
    artist_id INT AUTO_INCREMENT PRIMARY KEY,
    artist_name VARCHAR(100)
);


CREATE TABLE tbl_albums (
    album_id INT AUTO_INCREMENT PRIMARY KEY,
    album_title VARCHAR(100),
    artist_id INT,
    release_year INT,
  	album_copies INT,
    FOREIGN KEY (artist_id) REFERENCES tbl_artists(artist_id)
);

CREATE TABLE tbl_songs (
    song_id INT AUTO_INCREMENT primary KEY,
    song_title VARCHAR(200),
    duration INT,
    album_id INT,
    genre VARCHAR(50)
);

-- 1.) Add a Foreign Key to the tbl_songs Table for the album_id Column, Referencing the tbl_albums Table on album_id.
ALTER TABLE tbl_songs
add FOREIGN key (album_id) REFERENCES tbl_albums(album_id);

-- 2.) Modify the tbl_songs Table to Change the Data Type of duration from INT to TIME.
--MariaDB
ALter table tbl_songs
modify column duration TIME;

--MSSQL
ALter table tbl_songs
ALTER column duration TIME;

-- 3.) Delete the artist_id column from the tbl_artists TABLE and add it again with varchar(100)
ALter table tbl_artists
drop column artist_name;

alter table tbl_artists
add COLUMN artist_name varchar(100);

-- 4.) Add album_price decimal(10,2) in tbl_albums
ALTER TABLE tbl_albums
ADD album_price DECIMAL(10, 2);

-- 5.) Add album_sold INT in tbl_albums
ALTER TABLE tbl_albums
ADD album_sold INT;

INSERT INTO tbl_artists (artist_name)
VALUES 
('Sabrina Carpenter'),
('Taylor Swift'),
('Ariana Grande');

INSERT INTO tbl_albums (album_title, artist_id, album_copies, release_year, album_price, album_sold)
VALUES 
('Emails I Can’t Send', 1, 2022, 500, 799.99, 150), 
('1989', 2, 2014, 500, 599.99, 200),               
('Dangerous Woman', 3, 2016, 700, 649.49, 300);  


INSERT INTO tbl_songs (song_title, duration, album_id, genre)
VALUES 
('Nonsense', '02:40', 1, 'Pop'),
('Blank Space', '03:51', 2, 'Pop'),
('Into You', '04:04', 3, 'Pop');


-- 6.) Select all columns from tbl_songs and order the results by song_title in descending order.
SELECT * FROM tbl_songs  
ORDER BY song_title DESC;

-- 7.) Use the DISTINCT clause to get the album_title from tbl_albums and order the results in ascending order.
SELECT DISTINCT album_title FROM tbl_albums
ORDER BY album_title;

-- 8.) Delete the song where song_id is equal to 3.
DELETE FROM tbl_songs WHERE song_id = 3;

-- 9.) Select the album_id, album_title, and the maximum album_sold from tbl_albums, and order the results in descending order by album_sold.
SELECT  album_id,  album_title, max(album_sold)
FROM tbl_albums
GROUP BY album_id, album_title, album_sold
ORDER BY album_sold DESC;

/* 10.) Select the artist_name as ar.artist_name, album_title as al.album_title, and song_title as s.song_title from tbl_artists (ar), tbl_albums (al), 
 and tbl_songs (s), joining the tables on their respective artist_id and album_id columns, and order the results by artist_name in ascending order 
 and album_title in descending order. */
 
SELECT ar.artist_name,  al.album_title,  s.song_title
FROM tbl_artists ar
JOIN tbl_albums al ON ar.artist_id = al.artist_id
JOIN tbl_songs s ON al.album_id = s.album_id
ORDER BY ar.artist_name ASC,  al.album_title DESC;