This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
cheatsheets:sql [2017/02/27 16:39] admin |
cheatsheets:sql [2020/06/13 20:09] (current) |
||
|---|---|---|---|
| Line 30: | Line 30: | ||
| SELECT FirstName, LastName FROM Customer WHERE FirstName NOT LIKE '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 | * Get the first and last name of customers whose second name is made of four letters | ||
| SELECT FirstName, LastName FROM Customer WHERE LastName LIKE '____' | 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 | ||