SQL for Data-science Coursera Assignment Answers

Week 2 Coding

1. Find all the tracks that have a length of 5,000,000 milliseconds or more..

Code:

select Name, Milliseconds from Tracks where Milliseconds>=5000000;

How many tracks are returned?

Answer : 2

2. Run Query: Find all the invoices whose total is between $5 and $15 dollars.

Code:

select InvoiceId, Total from Invoices where total between 5 and 15;

While the query in this example is limited to 10 records, running the query correctly will indicate how many total records there are - enter that number below.

Answer : 168

3. Run Query: Find all the customers from the following States: RJ, DF, AB, BC, CA, WA, NY.

Code :

select FirstName, LastName, Company, State from Customers where State in ('RJ', 'DF', 'AB', 'BC', 'CA', 'WA', 'NY')

What company does Jack Smith work for?

Answer : Microsoft Corp

4. Run Query: Find all the invoices for customer 56 and 58 where the total was between $1.00 and $5.00.Run Query: Find all the invoices for customer 56 and 58 where the total was between $1.00 and $5.00.

Code :

select InvoiceID,InvoiceDate, CustomerId, Total from Invoices where CustomerId in (56, 58) AND (Total >= 1.00 and Total <= 5.00)

What was the invoice date for invoice ID 315?

Answer : 10-27-2012

5. Run Query: Find all the tracks whose name starts with 'All'.

Code :

select Name, TrackId from Tracks where Name LIKE "ALL%";

While only 10 records are shown, the query will indicate how many total records there are for this query - enter that number below.

Answer : 15

6. Run Query: Find all the customer emails that start with "J" and are from gmail.com.

Code :

select CustomerID, Email from Customers where Email like "J%gmail.com";

Enter the one email address returned (you will likely need to scroll to the right) below.

Answer : jubarnett@gmail.com

7. Run Query: Find all the invoices from the billing city Brasília, Edmonton, and Vancouver and sort in descending order by invoice ID.

Code :

select InvoiceId,BillingCity,Total from Invoices where BillingCity in ('Brasilia','Edmonton','Vancouver') order by InvoiceId desc;

What is the total invoice amount of the first record returned? Enter the number below without a $ sign. Remember to sort in descending order to get the correct answer.

Answer : 13.86

8. Run Query: Show the number of orders placed by each customer (hint: this is found in the invoices table) and sort the result by the number of orders in descending order.

Code :

select CustomerId , COUNT(InvoiceId) from Invoices group by CustomerId order by COUNT(InvoiceId) DESC

What is the number of items placed for the 8th person on this list? Enter that number below.

Answer : 7

9. Run Query: Find the albums with 12 or more tracks.

Code :

select TrackId, AlbumId from Tracks group by AlbumId having COUNT(DISTINCT TrackId) >= 12;

While the number of records returned is limited to 10, the query, if run correctly, will indicate how many total records there are. Enter that number below.

Answer : 158