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;
Leave a Comment