Music - Answers

1) Find all albums with the word "best" in their name - what is the name of the classical musician who appears in these results?
SELECT title FROM album WHERE title LIKE "%best%";

2) Get the total number of albums
SELECT COUNT(albumid) FROM album;

3) Order the artists whose name starts with "The" alphabetically - what is the name of the first artist that DOESN'T have a number in their name?
SELECT name FROM artist WHERE name LIKE "The%" ORDER BY name;

4) Count the number of different cities customers are from
SELECT COUNT(DISTINCT City) FROM customer;

5) Find the customer with the phone number +453 3331 9991 - take their full name
SELECT firstname, lastname FROM customer WHERE phone = "+453 3331 9991";

6) Find all customers from India - take the initials of the first person
SELECT LEFT(firstname, 1), LEFT(lastname, 1) FROM customer WHERE country = "India" LIMIT 1;

7) Find all the customers with yahoo or gmail email addresses - take the first person's email
SELECT email FROM customer WHERE email LIKE "%@yahoo%" OR email LIKE "%@gmail%" LIMIT 1;

8) Find the customers who work for Google Inc - take the city the first person lives in
SELECT city FROM customer WHERE company = "Google Inc." LIMIT 1;

9) Find all the artists with the word "Orchestra" and "Sir" in their name - how many are there?
SELECT COUNT(*) FROM artist WHERE name LIKE "%Orchestra%" AND name LIKE "%Sir%";

10) Get the employees hired in 2003 - what month number (i.e. 1-12) was the earliest hire in 2003?
SELECT MONTH(HireDate) FROM employee WHERE YEAR(HireDate) = 2003 ORDER BY HireDate LIMIT 1;

11) Sum the total cost of all invoices
SELECT SUM(total) FROM invoice;

12) Find the average invoice amount to 2 decimal places of German buyers
SELECT ROUND(AVG(Total), 2) FROM invoice WHERE BillingCountry = "Germany";

13) Find all invoice and customer details for the customer with an ID of 33 - what is their first name and highest total spend. Separate your answer with a comma
SELECT customer.firstname, invoice.total FROM invoice INNER JOIN customer ON invoice.customerid = customer.customerid WHERE customer.customerid = 33 ORDER BY total DESC LIMIT 1;

14) Find the playlist names that the track with an ID of 190 is included in - what decade (50s, 60s, 70s etc) can you conclude that track is from?
SELECT * FROM playlisttrack INNER JOIN playlist ON playlisttrack.playlistid = playlist.playlistid WHERE playlisttrack.trackid = 190;

15) Find the difference in duration between the longest and shortest track
SELECT MAX(milliseconds) - MIN(milliseconds) FROM track;

16) Show the genre name, with the number of tracks, ordered from the genre with the most tracks to the least - what genre has the most tracks and how many does it have? Separate your answer with a comma
SELECT genre.Name, COUNT(genre.GenreId) AS SongCount FROM track INNER JOIN genre ON track.GenreId = genre.GenreId GROUP BY genre.GenreId ORDER BY SongCount DESC;

17) Find the name of the album and singer of all songs from the 'Soundtrack' genre - what year can we see most were written for?
SELECT track.Name, artist.Name, album.Title FROM track INNER JOIN album ON track.AlbumId = album.AlbumId INNER JOIN artist ON album.ArtistId = artist.ArtistId INNER JOIN genre ON track.GenreId = genre.GenreId WHERE genre.Name = "Soundtrack";

18) Get the full name of the most popular file format
SELECT mediatype.Name, COUNT(mediatype.MediaTypeId) FROM track INNER JOIN mediatype ON track.MediaTypeId = mediatype.MediaTypeId GROUP BY track.MediaTypeId ORDER BY COUNT(mediatype.MediaTypeId) DESC;

19) Get the artist name who produced the album "War"
SELECT artist.Name, album.Title FROM album INNER JOIN artist ON album.ArtistId = artist.ArtistId WHERE album.Title = "War";

20) Get the first name, last name and total spent for the customer who spent the most money - separate your answer with a space or comma
SELECT customer.FirstName, customer.LastName, SUM(Total) FROM invoice INNER JOIN customer ON invoice.CustomerId = customer.CustomerId GROUP BY invoice.CustomerId ORDER BY SUM(Total) DESC;1