Udacity: Data Wrangling-Problem Set Elements of SQL

Quiz 1 SQL Scramble: Top 10 Composers 
Q: Which 10 Composers wrote the most songs in our dataset?
A: select composer, count(*) from Track group by composer order by count(*) desc limit 10;

Quiz 2 Warm up - Query for Song Length
Q: Which tracks in the dataset are between 2,500,000 and 2,600,000 milliseconds long?
A: select name, milliseconds from track where milliseconds>2500000 and milliseconds<2600000 order by milliseconds;

Quiz 3 Warm up - Join Artist to Album
Q: List albums written by either Iron Maiden or Amy Winehouse
A: select artist.name, album.title from album join artist on artist.artistid=album.artistid where name="Iron Maiden" or name="Amy Winehouse";

Quiz 4 Countries with most invoices
Q: Alright, now that you're warmed up let's get down to business! In this problem set we'll pretend the Chinook database is the data for your own personal music shop! We'll be marketing your shop and hosting Music Festival using answers we get from the data stored in your database. Let's get started :) First, you'd like to run a promotion targeting the 3 countries with the highest number of invoices. Write a query that returns the 3 countries with the highest number of invoices, along with the number of invoices for these countries.
A: QUERY ='''
SELECT BillingCountry, count(*) as num
from Invoice
group by BillingCountry
order by num desc
limit 3
'''

Quiz 5 Best Customer Emails
Q: The customer who has spent the most money will be declared your best customer. They definitely deserve an email thanking them for their patronage :)  Build a query that returns the person who has the highest sum of all invoices, along with their email, first name, and last name.
A:
QUERY ='''
SELECT Customer.Email, Customer.FirstName, Customer.LastName, sum(Invoice.Total)as num
from Customer join Invoice
on Invoice.CustomerId=Customer.CustomerId
group by Customer.CustomerId
order by num desc
limit 1
'''


Quiz 6: Project Rock Music
Q: Rock Music Lives on!  After the success of your recent email campaign, you're interested in targeting your long standing Rock Music audience! You'll need to collect a list of emails containing each of your Rock Music listeners. Use your query to return the email, first name, last name, and Genre of all Rock Music listeners! Return you list ordered alphabetically by email address starting with A. Can you find a way to deal with duplicate email addresses so no one receives multiple emails?

A:
QUERY ='''
SELECT customer.Email, customer.FirstName, customer.LastName, Genre.Name
from Customer, Invoice, InvoiceLine, Track, Genre
where Customer.CustomerId=Invoice.CustomerId
and Invoice.InvoiceId=InvoiceLine.InvoiceID
and InvoiceLine.TrackId=Track.TrackId
and Track.GenreId=Genre.GenreId
and Genre.Name="Rock"
group by customer.Email
order by customer.Email
'''

Quiz 7: Promotional Music Event
Q: Let's throw a promotional Music Festival in the city with the best customers! Which city have you made the most money from? Write a query that returns the 1 city that has the highest sum of invoice totals. Return both the city name and the sum of all invoice totals.

A:
QUERY ='''
SELECT customer.city,sum(invoice.Total)
from customer,invoice
where customer.CustomerId=Invoice.CustomerId
group by customer.city
order by sum(invoice.Total) desc
limit 1
'''

Quiz 8: Top City Favorite Music
Q: It would be really helpful to know what type of music everyone likes before, throwing this festival Lucky for us we've got the data to find out!  We should be able to tell what music people like by figuring out what music they're buying. Write a query that returns the BillingCity,total number of invoices associated with that particular genre, and the genre Name. Return the top 3 most popular music genres for the city Prague with the highest invoice total (you found this in the previous quiz!)

A:
QUERY ='''
SELECT Invoice.BillingCity, count(Genre.Name), Genre.Name
from Invoice, InvoiceLine, Track, Genre
where Invoice.InvoiceId=InvoiceLine.InvoiceID
and InvoiceLine.TrackId=Track.TrackId
and Track.GenreId=Genre.GenreId
and Invoice.BillingCity="Prague"
group by Genre.Name
order by count(Genre.Name) desc
limit 3
'''

Quiz 9: Getting Musicians
Q: Now that we know that our customers love rock music, we can decide which musicians to invite to play at the concert. Let's invite the artists who have written the most rock music in our dataset. Write a query that returns the Artist name and total track count of the top 10 rock bands.

A:
QUERY ='''
SELECT Artist.Name, count(Genre.Name) as num
from Artist, Album, Track, Genre
where Artist.ArtistId=Album.ArtistId
and Album.AlbumId=Track.AlbumId
and Track.GenreId=Genre.GenreId
and Genre.Name="Rock"
group by Artist.Name
order by num desc
limit 10
'''

Quiz 10: Heading to France
Q: The show was a huge hit! Congratulations on all your hard work :)  After the popularity of your first show you've decided to jump on the railway for an Alternative & Punk tour through France! What does the alternative punk scene look like throughout French cities in your dataset? Return the BillingCities in France, followed by the total number of tracks purchased for Alternative & Punk music. Order your output so that the city with the highest total number of tracks purchased is on top.

A:
QUERY = '''
SELECT Invoice.BillingCity, count(Invoice.InvoiceId) as NumInvoice
from Invoice, InvoiceLine, Track, Genre
where Invoice.InvoiceId=InvoiceLine.InvoiceId
and InvoiceLine.TrackId=Track.TrackId
and Track.GenreId=Genre.GenreId
and Invoice.BillingCountry="France"
and Genre.Name="Alternative & Punk"
group by Invoice.BillingCity
order by NumInvoice desc
'''