"Learning MySQL", Chapter 5 Exercises, by Mike Murphy

1. Use one or more SELECT statements to find out how many tracks are on New Order's "Brotherhood" album

SELECT Artist.artist_id FROM Artist WHERE (((Artist.artist_name)="New Order"));

SELECT Album.album_id FROM Album WHERE (((Album.artist_id)=1) AND ((Album.album_name)="Brotherhood"));

SELECT Count(Track.track_id) AS Brotherhood_Count FROM Track WHERE (((Track.artist_id)=1) AND ((Track.album_id)=7));


2. Using a join, list the albums that we own by the band New Order

SELECT Album.album_name FROM Artist INNER JOIN Album ON Artist.artist_id = Album.artist_id WHERE (((Artist.artist_name)="New Order")) ORDER BY Album.album_name;


3. With INSERT statements, add the artist "Leftfield" to the database. Add the album "Leftism".

INSERT INTO artist VALUES (7, "Kylie Minogue");

INSERT INTO album VALUES (7, 1, "Leftfield");

INSERT INTO track VALUES (0,'Release the Pressure',7,1,'7.39');

INSERT INTO track VALUES (1,'Afro-Melt',7,1,'7.33');

INSERT INTO track VALUES (2,'Melt',7,1,'5.21');

INSERT INTO track VALUES (3,'Song of Life',7,1,'6.55');

INSERT INTO track VALUES (4,'Original',7,1,'6.00');

INSERT INTO track VALUES (5,'Black Flute',7,1,'3.46');

INSERT INTO track VALUES (6,'Space Shanty',7,1,'7.15');

INSERT INTO track VALUES (7,'Inspection Check One',7,1,'6.30');

INSERT INTO track VALUES (8,'Storm 3000',7,1,'5.44');

INSERT INTO track VALUES (9,'Open Up',7,1,'6.52');

INSERT INTO track VALUES (10,'21st Century Poem',7,1,'5.42');

INSERT INTO track VALUES (11,'Bonus Track',7,1,'1.22');


4. How long in minutes is the "Leftism" album you added in Q3

SELECT Sum(Track.time) AS Minutes_Sum FROM (Artist INNER JOIN Album ON Artist.artist_id = Album.artist_id) INNER JOIN Track ON (Album.album_id = Track.album_id) AND (Album.artist_id = Track.artist_id) WHERE (((Track.artist_id)=7) AND ((Track.album_id)=1));


5. Change the time for the Original track on the "Leftism" album to 6.22

UPDATE track SET time = 6.22 WHERE (artist_id = 7 AND album_id = 1 AND track_id = 4);


6. Remove the Bonus Track from the "Leftism" album

DELETE FROM track WHERE (artist_id = 7 AND album_id = 1 AND track_id = 11);