Answers to the SQL Queries for Assignment #6

1. SELECT SUM(total) FROM invoice;
   

   Answer: $2,328.60

2. SELECT invoiceId, total FROM invoice
   WHERE invoiceId = 404;
   

   Answer: $25.86

3. SELECT invoiceId, SUM(unitPrice) FROM invoiceLine
   WHERE invoiceId = 404;
   

   Answer: $25.85999

4. SELECT firstName, lastName, customer.customerId, count(invoice.invoiceId) FROM customer
   JOIN invoice ON invoice.customerId = customer.customerId
   WHERE firstName = "John" AND lastName = "Gordon";
   

   Answer: 7

5. SELECT firstName, lastName, COUNT(invoice.invoiceId), AVG(total) FROM customer
   JOIN invoice ON invoice.customerId = customer.customerId
   WHERE firstName = "John" AND lastName = "Gordon";
   

   Answer: $5.37428

6. SELECT firstName, lastName, COUNT(invoice.invoiceId), SUM(total) FROM customer
   JOIN invoice ON invoice.customerId = customer.customerId
   WHERE firstName = "John" AND lastName = "Gordon";
   

   Answer: $37.62

7. SELECT firstName, lastName, invoice.invoiceId, track.name FROM customer
   JOIN invoice ON invoice.customerId = customer.customerId
   JOIN invoiceLine ON invoiceLine.invoiceId = invoice.invoiceId
   JOIN track ON track.trackId = invoiceLine.trackId
   WHERE firstName = "John" AND lastName = "Gordon";
   

   Answer: 38 songs with the following titles: "Your Time Has Come", "When My Left Eye Jumps", "Super Terrorizer", "Rock 'N' Roll Music", "Moon germs", "Meditação", "Jerusalem", "Heart Of Gold", "Evil Woman", "Esse Cara", "Dandelion", "Cornucopia", "Bowels Of The Devil", "Body Count Anthem", "The Duke", "Ponta de Areia", "Norwegian Wood", "Menestrel Das Alagoas", "Maria, Maria", "Lament", "Harvester Of Sorrow", "Fast And Loose", "Coração De Estudante", "Você Fugiu", "Salve Nossa Senhora", "You Know I'm No Good (feat. Ghostface Killah)", "Some Unholy War", "Intro / Stronger Than Me", "F**k Me Pumps", "Up Around The Bend", "Romaria (Renato Teixeira)", "Preciso Apender a Viver Só (Maysa)", "Down On The Corner", "Coração do Agreste (Fafá de Belém)", "Bad Moon Rising", "Real Love", "The Three Sunrises", "Sweetest Thing"

   
   NOTE ON ANSWER: As we want both the list of song NAMES and the count of song NAMES, we return the list of NAMES for our query, and observe the functionality within the SQLite Manager (at the bottom of the screen) that states "Number of Rows Returned." This value is 38, and can be confirmend by use of the COUNT(track.name) query.


8. SELECT employee.firstName, employee.lastName, employeeID, customer.firstName, customer.lastName FROM employee
   JOIN customer ON customer.supportRepId = employee.employeeId
   WHERE employee.firstName = customer.firstName AND employee.lastName = customer.lastname;
   

   Answer: None

9. SELECT firstName, lastName, customer.customerID, SUM(invoice.total) FROM customer
   JOIN invoice ON invoice.customerId = customer.customerId
   GROUP BY customer.customerID
   ORDER BY SUM(invoice.total) DESC
   LIMIT 5;
   

   Answer: "Helena Holý $49.62", "Richard Cunningham $47.62", "Luis Rojas $46.62", "Ladislav Kovács $45.62", "Hugh O'Reilly $45.62"