SmartNinja WD2

20180516-Relationships-exercises.md

Exercises

• What order (Invoice) was the most expensive?

    SELECT Invoice.InvoiceId, MAX(Invoice.Total)
    FROM Invoice;

  Total: 25.86,
  InvoiceId: 404,
  CustomerId: "6",
  InvoiceDate: "2013-11-13 00:00:00",
  BillingAddress: "Rilská 3174/6",
  BillingCity: "Prague",
  BillingCountry: "Czech Republic",
  BillingPostalCode: "14300"

• Which one was the cheapest?

    SELECT Invoice.InvoiceId, MIN(Invoice.Total)
    FROM Invoice;

  0.99, InvoiceId: 6

• Which city (BillingCity) has the most orders?

    SELECT Cities.Name, MAX(Cities.NumOfOrders)
    FROM (
      SELECT Invoice.BillingCity Name, COUNT(Invoice.BillingCity) NumOfOrders
      FROM Invoice
      GROUP BY Name) Cities;

  Berlin: 14

• Calculate (or count) how many tracks have this MediaType: Protected AAC audio file.

    SELECT MediaType.Name, COUNT(Track.TrackId)
    FROM MediaType
    INNER JOIN Track ON MediaType.MediaTypeId=Track.MediaTypeId
    WHERE MediaType.Name='Protected AAC audio file';

  Protected AAC audio file: 237

• Find out what Artist has the most albums? (hint: check ORDER BY)

    SELECT Artists.Name, MAX(Artists.Albums)
    FROM (
      SELECT Artist.Name Name, COUNT(Album.AlbumId) Albums
      FROM Artist
      INNER JOIN Album ON Artist.ArtistId=Album.ArtistId
      GROUP BY Artist.Name) Artists;

  Iron Maiden: 21

• What genre has the most tracks?

    SELECT Genres.Name, MAX(Genres.NumberOfTracks)
    FROM (
      SELECT Genre.Name, COUNT(Track.TrackId) NumberOfTracks
      FROM Genre
      INNER JOIN Track ON Track.GenreId=Genre.GenreId
      GROUP BY Genre.Name) Genres;

  Rock: 1297

• Which customer spent the most money so far?

    SELECT Customers.FirstName, Customers.LastName, MAX(Customers.TotalMoneySpent)
    FROM (
      SELECT Customer.CustomerId, Customer.FirstName, Customer.LastName, SUM(Invoice.Total) TotalMoneySpent
      FROM Customer
      INNER JOIN Invoice ON Invoice.CustomerId=Customer.CustomerId
      GROUP BY Customer.CustomerId) Customers;

  {
    "FirstName": "Helena",
    "LastName": "Holý",
    "TotalMoneySpent": "49.62"
  }

• What songs were bought with each order? (hint: here you have to do a many-to-many SQL query with three tables: Track, Invoice and InvoiceLine. You have to do two JOINS here)

    SELECT Invoice.InvoiceId, Track.Name
    FROM Invoice
    INNER JOIN InvoiceLine ON Invoice.InvoiceId=InvoiceLine.InvoiceId
    INNER JOIN Track ON InvoiceLine.TrackId=Track.TrackId
    ORDER BY Invoice.InvoiceId;
  

  For example, two songs: "Balls to the Wall" and "Restless and Wild" were bought with the order that have id "1".