wiki.javier.io

• Display the first and last name of all customers

SELECT FirstName, LastName FROM Customer

• Display the first and last name of all customers who are NOT from Paris

SELECT FirstName, LastName FROM Customer WHERE City <> 'Paris'

• Get the customer id and spend amount of all customers who have either spend less than $10 or more than $100

SELECT CustomerId, InvoiceTotal FROM Customer WHERE InvoiceTotal < 10 OR InvoiceTotal > 100

• Get the first, last name and city of customers who live in Brasil or are called 'Frank Sinatra'

SELECT FirstName, LastName, City FROM Customer WHERE Country = 'Brasil' OR (FirstName = 'Frank' AND LastName = 'Sinatra')

• Get the first name, city and total invoice of clients living neither in Paris, Prague nor Rome and whose invoices are between 35 and 40

SELECT FirstName, City, InvoiceTotal FROM Customer WHERE City NOT IN ('Paris', 'Prague', 'Rome') AND InvoiceTotal BETWEEN 35 AND 40

• Get the first name and invoice ordered first by invoice total descending and later by first name ascending.

SELECT FirstName, InvoiceTotal FROM Customer ORDER BY InvoiceTotal DESC, FirstName ASC

• Get the customer id, country and total invoice ordered by country first and later by total invoice, list first those customers living in Canada, USA or Mexico

SELECT CustomerId, Country, InvoiceTotal FROM Customer ORDER BY Country IN ('Canada,' 'USA', 'Mexico') DESC, InvoiceTotal

• Get the first and last name of customers whose first name doesn't start with P

SELECT FirstName, LastName FROM Customer WHERE  FirstName NOT LIKE 'P%'

• Get the first and last name of customers whose first name doesn't start with P, T or Z

SELECT FirstName, LastName FROM Customer WHERE  FirstName LIKE '[!P,T,Z]%'

• Get the first and last name of customers whose second name is made of four letters

SELECT FirstName, LastName FROM Customer WHERE LastName LIKE  '____'

• Get the name and bitrate (from the Byte and Millisecond columns), then order the result by bitrate from the highest to the lowest

SELECT Name, (Byte * 8) / (Millisecond / 1000) AS bitrate FROM Track ORDER BY bitrate DESC

• Get the total number of tracks along the space in Gb they take on HD

SELECT COUNT(TrackId), SUM(Bytes/1024/1024/1024) FROM Track

• Calculate the difference between the longest and shortest track in seconds

SELECT MAX(Milliseconds/1000) - MIN(Milliseconds/1000) FROM Track

• For all the countries in the customer table, display the name and total income generated by country

SELECT Country, SUM(InvoiceTotal) FROM Customer GROUP BY Country

• Just for Brasil, display the name of the state alongside the number of customers in each of them.

SELECT State, COUNT(CustomerId) FROM Customer WHERE Country = 'Brasil' GROUP BY State

• List all the countries having customers alonside the income these countries generate. Ensure the top earning countries come first.

SELECT Country, SUM(InvoiceTotal) AS TotalRevenue FROM Customer GROUP BY Country ORDER BY TotalRevenue DESC

• List all the customers having customers. Ensure the top earning countries come first.

SELECT Country FROM (SELECT Country, SUM(InvoiceTotal) FROM Customer GROUP BY Country ORDER BY 2 DESC)

• For countries listed in the customer table and generating more than $300, display their name alongside their revenue.

SELECT Country, SUM(InvoiceTotal) AS Revenue FROM Customer GROUP BY Country HAVING Revenue > 300

• For countries with more than 4 customers display their name alongside the revenue they generate.

SELECT Country, SUM(InvoiceTotal) FROM Customer GROUP BY Country HAVING COUNT(CustomerId) > 4