​

SELECT * 

FROM Artist

​

​

SELECT * 

FROM Artist

LIMIT 10

​

​

SELECT i.billingcountry, sum(total) as 'TotalSales'

FROM invoice AS i

GROUP BY billingcountry

ORDER BY totalsales DESC

​

​

-- Filter an aggregate results using the 'HAVING' clause.

SELECT BillingCountry, sum(Total) as TotalSales

FROM Invoice AS i

GROUP BY BillingCountry

HAVING TotalSales > 100

ORDER BY totalsales DESC

​

​

​

SELECT i.billingcountry, max(total) as 'Largest Sale'

FROM invoice AS i

GROUP BY billingcountry

ORDER BY 'Largest Sale' DESC

​

​

SELECT Name, Title

FROM Artist AS A

JOIN Album as I

   ON A.ArtistID = I.ArtistID

​

​

SELECT Name, Title

FROM Artist AS A

LEFT JOIN Album as I

   ON A.ArtistID = I.ArtistID

​

​

SELECT DISTINCT Name

FROM Artist AS A

LEFT JOIN Album as I

   ON A.ArtistID = I.ArtistID

WHERE

I.ArtistID IS NULL

​

​

-- Join a table to itself and select the latest date per CustomerID.

SELECT I1.*  

FROM Invoice I1

LEFT JOIN Invoice I2

    ON 

    (

     I1.CustomerID = I2.CustomerID

     AND

     I1.InvoiceDate < I2.InvoiceDate

    )

WHERE

I2.CustomerID IS NULL

​

​

-- Order by Customer ID and Invoice Date and pull in the invoice date from the

-- previous row.

SELECT CustomerID

    , InvoiceDate

  , MIN(InvoiceDate) OVER (PARTITION BY CustomerID ORDER BY CustomerID,  InvoiceDate

                ROWS BETWEEN 1 PRECEDING and 1 PRECEDING) as PreviousInvoiceDate

FROM Invoice

ORDER BY CustomerID,  InvoiceDate