editor
tutorials
tutorials
algorithms & data structures
help
challenges
pseudocode
sql
monthly
exam qs
questions
past papers
resources
tutorials
algorithms & data structures
slides
homework
past papers
data
grade thresholds
PSEUDOCODE
PRO
download
search
buy ($2)
contact
account
login/signup
my favourites
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