This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
cheatsheets:sql [2017/03/06 16:19] admin |
cheatsheets:sql [2020/06/13 20:09] (current) |
||
|---|---|---|---|
| Line 50: | Line 50: | ||
| SELECT MAX(Milliseconds/1000) - MIN(Milliseconds/1000) FROM Track | 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 | ||